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 t
...
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]
