PART II Question 1: The diagram below (also attached in DBST651_final_part2_ERD.

Question

PART II Question 1:

The diagram below (also attached in DBST651_final_part2_ERD.pdf ) is an ERD for an order entry database for a company. A customer orders products through company employee.

Study the ERD and answer the following questions:

I. Identify all relationships and specify cardinality and business rules. For example: 1:M between Customer and Orders: a customer can place many orders; an order will be placed by one and only one customer.

II. For each entity, identify primary key and foreign key if any. For foreign key, also specify parent entity and matching attribute in parent entity.

III. Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

IV. Write SQL statement for the following scenario:
a. Add a new customer John Smith with custono 1 and custzip 23456.
b. Save changes permanently.
c. Changer customer John Smith zip from 23456 to 20001.
d. Cancel the change made in step c, restore data to its original status prior to step c.
e. Delete customer John Smith.
f. Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

ORDERS CUSTOMER ORDNO VARCHAR2 (8 BYTE) ORD DATE DATE CUSTNO VARCHAR2 (8 BYTE) CUST FIRSTNAME VARCHAR2 20 BYTE) CUSTNO VARCHAR2 (8 BYTE) CUST LASTNAME VARCHAR2 30 BYTE) EMPNO VARCHAR2 (8 BYTE) VARCHAR2 (50 BYTE CUSTSTREET ORD NAME VARCHAR2 (50 BYTE CUST CITY VARCHAR2 30 BYTE) ORD STREET VARCHAR2 (50 BYTE ORD CITY VARCHAR2 300 BYTE) Pi CUST STATE VARCHAR2 BYTE) CUSTZP VARCHAR2 (10 BYTE) ORD STATE VARCHAR2 BYTE) CUSTBAL NUMBER 12,2) ORDZIP VARCHAR2 (10 BYTE PRODUCT ORDER LINE P PRODNO VARCHAR2 (8 BYTE) PF ORDN0 VARCHAR2 (8 BYTE PROD NAME VARCHAR2 (50 BYTE) PF PRODNO VARCHAR2 (8 BYTE) PROD MFG QTY NUMBER 00 PRODOOH NUMBER CO) PROD PRICE NUMBER (12,2) PROD NEXT SHIPDATE DATE EMPLOYEE EMPNO EMPFIRSTNAME VARCHAR2 BYTE) EMPLASTNAME VARCHAR2 80 BYTE) EMP PHONE VARCHAR2 (15 BYTE) EMPEMAIL VARCHAR2 (50 BYTE) SUPEMPNO VARCHAR2 (8 BNTE) EMP COMMRATE NUMBER 3.3) NOT NULL P: Primary Key F: Foreign Key U: Unique PF: Primary Key and Foreign Key

Explanation / Answer

1) Identify all relationships and specify cardinality and business rules. For example: 1:M between Customer and Orders: a customer can place many orders; an order will be placed by one and only one customer.

there will be 1 to 1 mapping between orders and employee since only single employee will deliver the single order
there will be 1 to many mapping between the Product and Order_Line since since a single order_line can contains multiple products.
there will be one to one mapping between order_line and orders

2) For each entity, identify primary key and foreign key if any. For foreign key, also specify parent entity and matching attribute in parent entity.

Customer: Primarykey custno

orders: primary key orderno
       foreign key custno refrences customer(custno)
       foreign key empno refrences employee(empno)
      
Product: primary key ProdNo

orderLine: primary key ordno
       foreign key prodno refrences Product(prodno)
         
3) Write SQL DDL statements to create tables in Oracle also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type

create table customer(
   custno varchar2(8),
   customerfirstname varchar2(100),
   customerlastname varchar2(100),
   custstreet varchar2(100),
   custcity varchar2(100),
   custstate varchar2(100),
   custzip varchar2(100),
   custbal number(12,2),
   primary key custno
);

create table orders(
   ordno varchar2(10),
   orddate date,
   custno varchar2(8),
   empno varchar2(8),
   ordname varchar2(50),
   ordstreet varchar2(50),
   ordstate varchar2(50),
   ordcity varchar2(50),
   ordzip varchar2(10),
   primary key(ordno),
   foreign key (custno) refrences customer(custno),
   foreign key (empno) refrences employee(empno)
)         

create table employee(
   empno varchar2(8),
   empfirstname varchar2(100),
   emplastname varchar2(100),
   empphone varchar2(100),
   empemail varchar2(100) not null,
   supempno varchar2(8),
   empcommrate number(3,3),
   primary key (empno)
)

create table order_line(
   ordno varchar2(8),
   prodno varchar2(8),
   qty number,
   primary key (ordno),
   foreign key (prodno) refences product(prodno)
)

create table product(
   prodno varchar2(8),
   prodname varchar2(50),
   prodmfg varcar2(20),
   prodqon number,
   prodprice number,
   prodnextshipdate date,
   primary key (prodno)
)

4)
a. Add a new customer John Smith with custono 1 and custzip 23456.

insert into customer values('1','John','smith',null,null,null,'23456',null,0)
b. Save changes permanently.
commit;
c. Changer customer John Smith zip from 23456 to 20001.

update customer set custzip = '20001' where custfirstname ='John' and custlastname ='Smith' and custzip='23456';

d. Cancel the change made in step c, restore data to its original status prior to step c.
rollback;
e. Delete customer John Smith.

delete from customer where customerfisrtname ='John' and customerlastname ='Smith'
f. Save changes permanently.
commit;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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