1. Consider the table:

VisitNo	VisitDate	PatNo	PatAge	PatCity	PatZip	ProvNo	ProvSpecialty	Diagnosis
V10020	1/13/2018	P1	35	Denver	80217	D1	internist	Ear Infection
V10020	1/13/2018	P1	35	Denver	80217	D2	NURSE PractiTIoner	INFLUENZA
V93030	1/20/2018	P3	17	Englewood	80113	D2	NURSE PractiTIoner	pregnancy
V82110	1/18/2018	P2	60	Boulder	85932	D3	cardiologist	murmur
V34210	1/19/2018	P2	60	BOULDER	85932	D3	CARDIOLOGIST	IRREGULAR BEAT
V34211	1/25/2018	P5	17	GV	80111	D4	InTERNIST	EAR INFECTION

1. State the functional dependencies. (Assume that different patients can have the same zip code - PatZip)
2. Transform this table into one or more tables in BCNF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints.

2. For the following Customer and Order tables, first describe the problem with each table, then explain the fixes needed to comply with BCNF.

3. Consider the table:

STUDENT (Number, Name, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname)

Assume that students pay different dorm costs, depending on the type of room they have, but that all members of a club pay the same cost. Assume that students can have multiple nicknames.

1. State any multivalued dependencies.
2. State the functional dependencies.
3. Transform this table into two or more tables such that each table is in BCNF and in 4NF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints.

Customer CustomerName CustomerPhone CustomerAddress Gustavo Gomez 113-456-7890 24 St. SE. Middle-of-Nowhere, WA 98000 Order OrderID | Customer# | OrderDate | ltemDescription | Item# | Lineltem | ltemQty ltemPrice | ltemTota!Amount 345 345 1234 /1/2013 iPhone Headset AP544 1 24.99 2499 1234 /1/2013iPhone AP300 2 24.99124.99