50268-Solution/DDL-DML.sql
/* The following code includes the data definition language statements and data manipulation language statements for all the tables in AMCDB Database.*/
DDL Statements for each of the Tables are given below.
Functional Dependencies and Key Constraints have been successfully implemented.*/
CREATE TABLE Customers
(
C_Num integer Primary Key NOT NULL auto_increment,
C_FName varchar(30),
C_LName varchar(30),
C_MobileNum varchar(15) NOT NULL,
C_Adress varchar(50)
);
CREATE TABLE Orders
(
O_Num integer Primary Key NOT NULL auto_increment,
Order_Date date,
C_Num integer NOT NULL,
foreign key (C_Num) references Customers(C_Num)
);
CREATE TABLE Stores
(
S_Num integer Primary Key NOT NULL auto_increment,
S_Name varchar(30),
S_Phone varchar(15) NOT NULL,
S_Fax varchar(15),
S_Email varchar(30),
S_Street varchar(30),
S_Suburb varchar(30),
S_State varchar(30),
S_PostCode varchar(6)
);
-- The department will have unique phone number and email ID at each store. The same Store can have many departments each of which has three unique attributes.
CREATE TABLE Department
(
D_Num integer Primary Key NOT NULL auto_increment,
D_Title varchar(13),
D_Phone varchar(15) Unique,
D_Email varchar(30) Unique,
S_Num integer NOT NULL,
foreign key (S_Num) references Stores(S_Num)
);
CREATE TABLE Store_Department
(D_Num integer NOT NULL,
S_Num integer NOT NULL,
foreign key (S_Num) references Stores(S_Num),
foreign key (D_Num) references Department(D_Num)
);
CREATE TABLE Employee
(
E_Num integer Primary Key NOT NULL auto_increment,
E_FName varchar(30),
E_LName varchar(30),
E_MobileNum varchar(15),
E_Email varchar(30),
E_TFN varchar(30),
Employment_Type varchar(6),
E_Salary float,
Hourly_Rate float,
Joining_Date date,
Manager_Status varchar(1),
S_Num integer NOT NULL,
D_Num integer NOT NULL,
foreign key (S_Num) references Stores(S_Num),
foreign key (D_Num) references Department(D_Num)
);
CREATE TABLE Products
(
P_Num integer Primary Key NOT NULL auto_increment,
P_Name varchar(30),
Brand varchar(30),
Description varchar(100),
Price float
);
CREATE TABLE Product_Order
(
P_Num integer NOT NULL,
O_Num integer NOT NULL,
Quantity_Ordered integer NOT NULL,
foreign key (O_Num) references Orders(O_Num),
foreign key (P_Num) references Products(P_Num)
);
CREATE TABLE Product_Store
(
P_Num integer NOT NULL,
S_Num integer NOT NULL,
Quantity_Available integer,
Quantity_Ordered integer,
foreign key (P_Num) references Products(P_Num),
foreign key (S_Num) references Stores(S_Num)
);
CREATE TABLE Suppliers
(
Sup_Num integer Primary Key NOT NULL auto_increment,
S_Detail varchar(100)
);
CREATE TABLE Product_Supplier
(
Sup_Num integer NOT NULL,
P_Num integer NOT NULL,
foreign key (P_Num) references Products(P_Num),
foreign key (Sup_Num) references Suppliers(Sup_Num)
);
/* One supervisor who already exists as an employee can supervise one department.
We assume a store manager can supervise a department in the same store.
So the supervisor can supervise only in the same department of the store where he is employed.
The table Employee caould be further normalized by bifurcating the department number from it. */
CREATE TABLE Supervisor
(
Supervisor_Num integer primary key NOT NULL,
D_Num integer NOT NULL,
foreign key (D_Num) references Employee(D_Num),
foreign key (Supervisor_Num) references Employee(E_Num)
);
/* DML Statements for each of the Tables are given below.
The data has been entered in acoordance with the queries to retrieve records successfully in each case.*/
-- Table: Customers
INSERT INTO Customers (C_FName,C_LName,C_MobileNum,C_Adress)
VALUES ('Elizabeth','Ross','708123234','1024 Chalet Court Melbourne AU');
INSERT INTO Customers (C_FName,C_LName,C_MobileNum,C_Adress)
VALUES ('Rachel','Shaw','708234345','171 Berkley Avenue Melbourne AU');
INSERT INTO Customers (C_FName,C_LName,C_MobileNum,C_Adress)
VALUES ('Tom','Moore','708345567','1200 Fort Worth Street Bathurst AU');
INSERT INTO Customers (C_FName,C_LName,C_MobileNum,C_Adress)
VALUES ('Russell','Crow','708345568','1340 Rambo Street Sydney AU');
INSERT INTO Customers (C_FName,C_LName,C_MobileNum,C_Adress)
VALUES ('Ashton','Kutcher','708345569','12 Romeo Street Sydney AU');
-- Table: Orders
INSERT INTO Orders (Order_Date,C_Num)
VALUES('2018-4-12',1);
INSERT INTO Orders (Order_Date,C_Num)
VALUES('2018-5-13',2);
INSERT INTO Orders (Order_Date,C_Num)
VALUES('2017-11-28',3);
INSERT INTO Orders (Order_Date,C_Num)
VALUES('2019-1-4',1);
INSERT INTO Orders (Order_Date,C_Num)
VALUES('2017-10-10',2);
INSERT INTO Orders (O_Num,Order_Date,C_Num)
VALUES(1005,'2017-10-10',1);
-- Table: Stores
INSERT INTO Stores(S_Name,S_Phone,S_Fax,S_Email,S_Street,S_Suburb,S_State,S_PostCode)
VALUES('StoreA','7063454563','7063454564','storeA@AMC,com','4 legacy drive','Arlington','Melbourne','12345');
INSERT INTO Stores(S_Name,S_Phone,S_Fax,S_Email,S_Street,S_Suburb,S_State,S_PostCode)
VALUES('StoreB','8172343452','8172343453','storeB@AMC,com','7 Fort Worth','Fort Worth','Sydney','23456');
INSERT INTO Stores(S_Name,S_Phone,S_Fax,S_Email,S_Street,S_Suburb,S_State,S_PostCode)
VALUES('StoreC','8171171171','8171171172','storeC@AMC,com','7th Avenue','Newark','Adelaide','34567');
INSERT INTO Stores(S_Name,S_Phone,S_Fax,S_Email,S_Street,S_Suburb,S_State,S_PostCode)
VALUES('StoreD','8172271171','8172271172','storeD@AMC,com','7th George Avenue','Newark','Adelaide','34567');
INSERT INTO Stores(S_Name,S_Phone,S_Fax,S_Email,S_Street,S_Suburb,S_State,S_PostCode)
VALUES('StoreE','8173371171','8173371172','storeE@AMC,com','7th george Avenue','Newark','Adelaide','34567');
-- Table: Department
INSERT INTO Department(D_Title,D_Phone,D_Email, S_Num)
VALUES('HR','7063454564','
[email protected]',1);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Sales','7063454565','
[email protected]',1);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Finance','7063454566','
[email protected]',1);
INSERT INTO Department(D_Title,D_Phone,D_Email, S_Num)
VALUES('HR','8172343453','
[email protected]',2);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Sales','8172343454','
[email protected]',2);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Finance','8172343455','
[email protected]',2);
INSERT INTO Department(D_Title,D_Phone,D_Email, S_Num)
VALUES('HR','8171171172','
[email protected]',3);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Sales','8171171173','
[email protected]',3);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Finance','8171171174','
[email protected]',3);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Account','8172343456','
[email protected]',2);
INSERT INTO Department(D_Title,D_Phone,D_Email,S_Num)
VALUES('Account','7063454567','
[email protected]',1);
-- Table: Store_Department
INSERT INTO Store_Department(D_Num,S_Num)
VALUES(5,1);
INSERT INTO Store_Department(D_Num,S_Num)
VALUES(6,1);
INSERT INTO Store_Department(D_Num,S_Num)
VALUES(7,2);
-- Table: Employee
INSERT INTO Employee(E_FName,E_LName,E_MobileNum,E_Email,E_TFN,Employment_Type,E_Salary,Hourly_Rate,Joining_Date,Manager_Status,S_Num,D_Num)
VALUES('aaa','zzz','9876543211','
[email protected]','AB23','Full',70000,null,'2017-8-12','Y',1,3);
INSERT INTO Employee(E_FName,E_LName,E_MobileNum,E_Email,E_TFN,Employment_Type,E_Salary,Hourly_Rate,Joining_Date,Manager_Status,S_Num,D_Num)
VALUES('bbb','yyy','9876543212','
[email protected]','BC34','Casual',null,25,'2017-10-15','N',3,3);
INSERT INTO Employee(E_FName,E_LName,E_MobileNum,E_Email,E_TFN,Employment_Type,E_Salary,Hourly_Rate,Joining_Date,Manager_Status,S_Num,D_Num)
VALUES('ccc','ppp','9876543213','
[email protected]','CD45','Full',75000,null,'2018-3-3','Y',3,4);
INSERT INTO Employee(E_FName,E_LName,E_MobileNum,E_Email,E_TFN,Employment_Type,E_Salary,Hourly_Rate,Joining_Date,Manager_Status,S_Num,D_Num)
VALUES('fff','mmm','9876543214','
[email protected]','CD47','Full',65000,null,'2018-8-3','Y',2,10);
INSERT INTO Employee(E_FName,E_LName,E_MobileNum,E_Email,E_TFN,Employment_Type,E_Salary,Hourly_Rate,Joining_Date,Manager_Status,S_Num,D_Num)
VALUES('eee','ttt','9876543215','
[email protected]','CD48','Full',65000,null,'2018-9-3','Y',3,11);
INSERT INTO Employee(E_FName,E_LName,E_MobileNum,E_Email,E_TFN,Employment_Type,E_Salary,Hourly_Rate,Joining_Date,Manager_Status,S_Num,D_Num)
VALUES('ddd','www','9876543216','
[email protected]','CD49','Full',65000,null,'2018-11-3','Y',3,11);
-- Table: Products
INSERT INTO Products(P_Name,Brand,Description,Price)
VALUES('Shoe','Gimmy Choo','Size 7',234.99);
INSERT INTO Products(P_Name,Brand,Description,Price)
VALUES('Bag','Da Milano','Black Clutch',20.99);
INSERT INTO Products(P_Name,Brand,Description,Price)
VALUES('Tie','Luis Philip','Bow tie Tux',24.99);
-- Table: Product_Order
INSERT INTO Product_Order(P_Num,O_Num,Quantity_Ordered)
VALUES(1,2,2);
INSERT INTO Product_Order(P_Num,O_Num,Quantity_Ordered)
VALUES(2,2,1);
INSERT INTO Product_Order(P_Num,O_Num,Quantity_Ordered )
VALUES(3,4,1);
INSERT INTO...