12 Apr The data manipulation language?(DML)?is used to create,?read,?update,?and delete data from a table.?The data definition language?
The data manipulation language (DML) is used to create, read, update, and delete data from a table. The data definition language (DDL) is used to create and alter the structure of the database itself, like creating a table, it's columns, define and modify the primary and foreign keys, stored procedures, delete columns, tables, and even the entire database. Your SQL statements should require a variety of SQL capabilities, such as various kinds of join, aggregate functions, etc. (This presupposes a good initial domain choice.)
Follow these steps to complete your assignment:
- Populate your database with sample data to allow testing of the schema and the various transactions. Each table should have a minimum of ten rows.
- After you've entered data, create some sample queries to see if the output is what you expected.
- Create some sample reports to make sure these also produce similar results.
- Make any necessary corrections to the database design during this stage and before you enter all of your personal records. If you find any problems, correct the design.
- Enter all of your personal records.
- Write your SQL Script.
Attach is the last paper to reference
1
4
Artist
PK Artist_ID (varchar)
Name (char)NOT NULL
Event
PK Event_ID(varchar)
Date(datetime)
Organizers
PK Organizer_ID(varchar)
Name(char) NOT NULL
Manager
PK Manager_ID(varchar)
Name(char) NOT NULL
Album
PK Album_ID(varchar)
Title(char) NOT NULL
FK Artist_ID(varchar)
Year(year)
FK Label_ID(varchar)
Label
PK Label_ID(varchar)
Name(char) not null
(
Producer
PK Producer_ID(varchar)
Name(char) NOT NULL
Release
PK Release_ID(varchar)
Date(datetime)
1 1
*
*
1
The following Is the DDL SQL statement that is used to create the Musician database:
mysql> CREATE DATABASE Musician;
mysql> use musician
mysql> create table Artist
-> (Artist_ID varchar (255) PRIMARY KEY,
-> Name char (255) NOT NULL);
mysql> CREATE TABLE Label
-> (Label_ID varchar (255) PRIMARY KEY,
-> Name char (255) NOT NULL);
mysql> CREATE TABLE Event
-> (Event_ID varchar (255) PRIMARY KEY,
-> Date datetime);
mysql> CREATE TABLE Organizers
-> (Organizer_ID varchar (255) PRIMARY KEY,
-> Name char (255) NOT NULL);
mysql> CREATE TABLE Producer
-> (Producer_ID varchar (255) PRIMARY KEY,
-> Name char (255) NOT NULL);
mysql> CREATE TABLE Manager
-> (Manager_ID varchar (255) PRIMARY KEY,
-> Name char (255) NOT NULL);
mysql> CREATE TABLE Album
-> (Album_ID varchar (255) PRIMARY KEY,
-> Title char (255) NOT NULL,
-> FOREIGN KEY (Label_ID) REFERENCES Label(Label_ID),
-> Year year,
-> FOREIGN KEY (Artist_ID) REFERENCES Artist(Artist_ID);
Reference
Captain, F. A. (2018). Six-step relational database design: A step by step approach to relational database design and development. Fidel A Captain.
Embley, D. W., & Thalheim, B. (2012). Handbook of conceptual modeling: Theory,
Schmidt & Brodie, M. L. (2018). Relational database systems: Analysis and comparison. Springer Science & Business Media.
Safety Engineering and Risk Analysis. https://doi.org/10.1115/imece2016-66791
