C170 project, 2022/2023 Exam preview, graded A+

Document Content and Description Below

1NF 2NF Highlighted fields will form a Composite primary key. City State and Zip were separated And are now atomic No duplicate data, each of the above tables has a Primary Key, and a key (foreign) that can refer to another table in the group. 3NF No transitive dependency. New table has 2 of the PKs from the other tables as Foreign Keys. Each table has a primary key which can refer to another one of the tables in this group. 1:M One Customer can place many Orders ER DIAGRAM Customer table has single pk ORDER TABLE DATE DATETIME CUSTOMERID INT (fk) DONUT_ORDER_ID INT (pk) QUANTITY TABLE NOTES VARCHAR (255) QUANTITY INT DONUT_ORDER_ID INT (pk) (fk) DONUT_ID INT (pk)(fk) DONUT TABLE DONUT_ID INT (pk) DONUT_NAME VARCHAR (100) DONUT_DESCRIPTION VARCHAR (255) UNIT_PRICE DECIMAL (10,2) DONUT ORDER ID DONUT ID DATE CUSTOMER ID LAST NAME FIRST NAME STREET ADDRESS CITY STATE ZIP CODE HOME PHONE MOBILE PHONE OTHER PHONE DESCRIPTION QTY UNIT PRICE NOTES DONUT NAME DONUT ORDER ID(PK) DATE CUSTOMER ID LAST NAME FIRST NAME STREET ADDRESS APT CITY STATE ZIP CODE HOME PHONE MOBILE PHONE OTHER PHONE NOTES DONUT ID (PK) DONUT NAME DONUT DESCRIPTION UNIT PRICE DONUT ORDER ID (PK) (FK) QUANTITY DONUT ID (PK)(FK) CUSTOMER ID (PK) LAST NAME FIRST NAME STREET ADDRESS APT CITY STATE ZIP CODE HOME PHONE MOBILE PHONE OTHER PHONE DATE CUSTOMER ID (FK) NOTES DONUT ORDER ID (PK) DONUT ID (PK) DONUT NAME DONUT DESCRIPTION UNIT PRICE DONUT ORDER ID (PK)(FK) QUANTITY DONUT ID (PK)(FK)One order can have many different quantities 1:M One donut order can hold many different quantities of donut types of donut types 1:M create table customers( customer_id INT, CUSTOMER TABLE CUSTOMER_ID INT (pk) LAST_NAME VARCHAR (30) FIRST_NAME VARCHAR (30) STREET_ADDRESS VARCHAR (100) APT INT, CITY VARCHAR (30) STATE VARCHAR (30) ZIP_CODE INT HOME_PHONE VARCHAR (20) MOBILE_PHONE VARCHAR (20) OTHER_PHONE VARCHAR (20)last_name varchar (30), first_name varchar (30), street_address varchar (100), apt int, city varchar (30), state varchar (30), zip_code INT, home_phone varchar (20), mobile_phone varchar (20), other_phone varchar (20), primary key (customer_id) ) ; create table orders( donut_order_id int,orderdate date, customer_id int, notes varchar (255) primary key (donut_order_id) ) ; create table donuts( donut_id int, donut_name varchar(100), donut_description varchar (255), unit_price decimal (10,2), primary key (donut_id) ) ;create table qty( quantity int, donut_order_id int, donut_id int, primary key (donut_order_id, donut_id) ) ; create view customerdata as select concat(first_name,’ ‘,last_name) as customername, street_address,apt,city,state,zip_code, home_phone,mobile_phone,other_phone from customerscreate index donutinfo on donuts (donut_name) insert into customers ( last_name,first_name,street_address,apt,city,state,zip_code, home_phone,mobile_phone,other_phone

[Show More]

Last updated: 3 years ago

Preview 1 out of 10 pages

Buy Now

Instant download