Appendix C Order System Specification (ERD and Tables)

CUSTOMER _places ORDER _has ITEM PRODUCT

f ulf ills

CREATE TABLE customer(

Cust_no char(3) PRIMARY KEY, Cust_name varchar2(10),

Cust_address varchar2(15));

As ORDER is a reserved word in Oracle porder is used as an alternative table name CREATE TABLE porder(

Order_no char(3) PRIMARY KEY,

Cust_no char(3) REFERENCES customer(Cust_no), Order_date date);

CREATE TABLE product(

Prod_no char(4) PRIMARY KEY, Prod_desc varchar2(30),

A practical introduction using Oracle SQL

161

Appendices

CREATE TABLE item(

Order_no char(3) REFERENCES porder(Order_no), Prod_no char(4) REFERENCES product(Prod_no), Qty number(5),

PRIMARY KEY(Order_no,Prod_no)); Sample data:

customer

Cust_no. Name Address

001 Sainsburys Leeds 17

003 Morrisons Leeds 1

007 Asda Morley

015 Netto Bradford

Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more

MASTER IN MANAGEMENT

[email protected] Follow us on IE MIM Experience www.ie.edu/master-management

#10 WORLDWIDE

Personalize your program

Length: 1O MONTHS Av. Experience: 1 YEAR Language: ENGLISH / SPANISH Format: FULL-TIME Intakes: SEPT / FEB

• STUDY IN THE CENTER OF MADRID AND TAKE ADVANTAGE OF THE UNIQUE OPPORTUNITIES THAT THE CAPITAL OF SPAIN OFFERS

• PROPEL YOUR EDUCATION BY EARNING A DOUBLE DEGREE THAT BEST SUITS YOUR

PROFESSIONAL GOALS

• STUDY A SEMESTER ABROAD AND BECOME A GLOBAL CITIZEN WITH THE BEYOND BORDERS EXPERIENCE

93%

OF MIM STUDENTS ARE WORKING IN THEIR SECTOR 3 MONTHS FOLLOWING GRADUATION

A practical introduction using Oracle SQL Appendices

porder

Order No. Cust_ No. Date

X01 003 01-JAN-2012

Y01 007 23-DEC-2012

Z01 001 17-JAN-2013

Z02 015 04-FEB-2013

product

Prod no Prod_Desc Price

A301 Bread 0.50

A302 Milk 2.50

B001 Butter 1.50

C002 Chocolate 2.00

item

Order No. Prod no Qty

X01 A302 400 Z01 A301 750 X01 B001 900 Y01 C002 210 Z02 A302 340 Z01 C002 720

A practical introduction using Oracle SQL

163

Appendices

12.4

Appendix D. Normalisation Template

UNF 1NF 2NF 3NF Relation / Table Name

1. List all the attributes below from a single document / table. 2. Identify the unique identifier / primary key. Show in bold or colour. May need an artificial key. 3. Identify any repeating attribute group(s). Show in (….) or colour. 1. Place repeated attribute group(s) if any in a new relation. 2. Include the UNF unique identifier as a foreign key in the new relation. 3. Identify the additional attribute(s) in the new relation to form a compound key with the foreign key. 1. Remove any part key dependent attributes to a new relation. 2. Identify

identifier for each new relation. 3. Include foreign key in the original relation. 1. Remove any non-key dependent attributes to a new relation(s). 2. Identify the unique identifier for the new relation(s). 3. Include a foreign key in the original relation.

A practical introduction using Oracle SQL Bibliography

13 Bibliography

Barker, R. 1990, “CASE Method: Entity Relationship Modelling”. Addison-Wesley Professional.

Chen, Peter  (March 1976). “The Entity-Relationship Model – Toward a Unified View of Data”.  ACM Transactions on Database Systems 1 (1): pp 9–36.

Codd, E.F. 1970, “A Relational Model of Data for Large Shared Data Banks”. Communications of the ACM 13 No. 6: pp. 377–387.

Connolly, T. & Begg, C., 2015. “Database Systems A Practical Approach to Design, Implementation, and Management” 6th _{ed. Pearson Education.}

Hay, D. & Lynott, M., 2008. TDAN Newsletter. [Online] Available at: http://www.tdan.com/view-special-features/8457