Answer To: Project requirements: 1.Students develop their projects along with their database concepts gained...
Deepti answered on Apr 20 2021
/*
The Data Definition Language script is included below followed by Data
Modification Language script for all the tables of the database.
*/
/*DDL Script*/
CREATE TABLE Customers
(
C_Num integer Primary Key NOT NULL auto_increment,
CFName char(30),
CLName char(30),
CMobileNum char(15) NOT NULL,
CAdress varchar(50)
);
CREATE TABLE Orders
(
O_Num integer Primary Key NOT NULL auto_increment,
OrderDate date,
C_Num integer NOT NULL,
foreign key (C_Num) references Customers(C_Num)
);
CREATE TABLE Stores
(
SNum integer Primary Key NOT NULL auto_increment,
SName char(30),
SPhone char(15) NOT NULL,
SFax char(15),
SEmail char(30),
SStreet char(30),
SSuburb char(30),
SState char(30),
SPostCode char(6)
);
CREATE TABLE Department
(
D_Num integer Primary Key NOT NULL auto_increment,
DTitle char(13),
DPhone char(15),
DEmail char(30),
S_Num integer NOT NULL,
foreign key (S_Num) references Stores(S_Num)
);
CREATE TABLE Employee
(
E_Num integer Primary Key NOT NULL auto_increment,
EFName char(30),
ELName char(30),
EMobileNum char(15),
EEmail char(30),
ETFN char(30),
EmploymentType char(6),
ESalary float,
HourlyRate float,
JoiningDate date,
ManagerStatus char(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,
PName char(30),
PBrand char(30),
PDescription varchar(100),
PPrice float
);
CREATE TABLE Product_Order
(
P_Num integer NOT NULL,
O_Num 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,
SDetail 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)
);
CREATE TABLE Supervisor
(
Supervisor_Num integer NOT NULL,
E_Num integer Primary Key NOT NULL auto_increment,
foreign key (E_Num) references Employee(E_Num)
);
/*DML Script*/
INSERT INTO Customers (CFName,CLName,CMobileNum,CAdress)
VALUES ('Elizabeth','Ross','708123234','1024 Chalet Court Melbourne AU');
INSERT INTO Customers (CFName,CLName,CMobileNum,CAdress)
VALUES...