Question: Design an Oracle/MySQL database using SQL DDL based on the above relat

Question

Question:

Design an Oracle/MySQL database using SQL DDL based on the above relational schema. Below is a guideline:   

-Create tables (make appropriate assumptions. See below database state)

-Populate the tables (Insert data). Use the above database state

-Add foreign keys   

Please put up all SQL DDL statements.

2). Relational database Schema Employees Endovee number Finste nome Last-namel City Customers Customer number fint-name last-namel City Item Item_number Item_name price quantity Order Orderanumber Customer number temploper numberItem number fumargleite frecite maten

Explanation / Answer

Create Table : i

Construct: create table <table_name> (

column_name1 type,

column_name2 type,

..

..

PRIMARY KEY(<column_name>)

)

Foreign Key : A foreign key in a table should always be a primary key in the other table which will be refrenced by the current table

Declaration of Foregn Key: Key Word FOREIGN KEY (<column_name>) REFRENCES <table_name>(column_name)

We can also give name to the Foreign key with syntax as below

CONSTRAINt <name> FOREIGN KEY (<column_name>) REFRENCES <table_name>(column_name)

Insert Syntax: INSERT INTO <table_name> values (value_for_field1,value_for_field2,...).

create table employees (
Employee_number int ,
fName varchar(10),
lName varchar(20),
city varchar(20),
PRIMARY KEY (Employee_number)
)

create table customers (
Customer_number int ,
fName varchar(10),
lName varchar(20),
city varchar(20),
PRIMARY KEY (Customer_number)
)

create table item (
Item_number int,
item_name varchar(20),
price decimal(10,2),
quantity int,
PRIMARY KEY (Item_number)
)

create table order (
Order_number int,
Customer_number int,
Employee_number int,
Item_number int,
quantity int,
Date_of_Reciept date,
Ship_date date,
PRIMARY KEY (Order_number),
CONSTRAINT FK_CustomerOrder FOREIGN KEY (Customer_number) REFERENCES customers(Customer_number),
CONSTRAINT FK_EmployeeOrder FOREIGN KEY (Employee_number) REFERENCES employees(Employee_number),
CONSTRAINT FK_ItemOrder FOREIGN KEY (Item_number) REFERENCES item(Item_number)
)

insert into employees values (10021, "John","Wayne","Boston");
insert into employees values (10022, "Tony","Romo","Boston");
insert into employees values (10023, "Bruce","Wilson","Boston");
insert into employees values (10024, "Dimitry","Hulsin","Boston");
insert into employees values (10025, "Tim","Harper","Boston");
insert into employees values (10026, "Tony","Stark","New Jesersey");
insert into employees values (10027, "John","Snow","Winterfell");

insert into customers values (80090, "Bill","Mares","Hartford");
insert into customers values (80091, "Bill","Tom","Brady","Los Angeles");
insert into customers values (80092, "George","Clinton","Indianapolis");
insert into customers values (80093, "Nick","Davidson","Hartford");
insert into customers values (80094, "John","Wang","Hartford");

insert into item values (54040, "The Matrix Poster",7.95,2);
insert into item values (54041, "Wood Skin Car steering Wheel Cover",34.99,1);
insert into item values (54042, "Blue earbuds",9.99,1);
insert into item values (54043, "SONYXBE483A HD TV",60.00,1);
insert into item values (54044, "Gldan Men's Assorted V-neck T-shirts Multipack ",15.00,2);

insert into order values(30020,80090,10023,54040,2,To_DATE('2018-06-14','YYYY-MM-DD'),To_DATE('2018-06-16','YYYY-MM-DD'));
insert into order values(30021,80091,10024,54041,1,To_DATE('2018-02-22','YYYY-MM-DD'),To_DATE('2018-02-24','YYYY-MM-DD'));
insert into order values(30022,80092,10025,54042,1,To_DATE('2018-05-13','YYYY-MM-DD'),To_DATE('2018-05-15','YYYY-MM-DD'));
insert into order values(30023,80093,10026,54043,1,To_DATE('2018-06-03','YYYY-MM-DD'),To_DATE('2018-06-05','YYYY-MM-DD'));
insert into order values(30024,80094,10027,54044,2,To_DATE('2018-06-30','YYYY-MM-DD'),To_DATE('2018-07-02','YYYY-MM-DD'));

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.