Database Normalization to BCNF

This practical assignment focuses on database normalization, specifically Boyce-Codd Normal Form (BCNF). Students are given four tables, each with a set of functional dependencies. The task is to normalize these tables into BCNF, showing all work and including the student's initials in the primary key of each resulting table. The assignment emphasizes proper formatting and allows for partial credit based on the demonstrated work, even if the final answer is incorrect. Students may need to introduce surrogate keys or other variables to achieve BCNF, which should be clearly explained in their work. The assignment tests understanding of functional dependencies and the process of decomposing relations to eliminate redundancy and anomalies.

Please normalize the following tables into BCNF. our answer must be in proper form, and you must
include your initials in the primary key for each table.
Example of proper form and initials:
TABLE_NAME(PrimaryKey_TJE, ForeignKey_TJE, attribute1, attribute2, attribute3….)
Please include any work you might have when building these normalized relations. If I see the work, and
you get the problem wrong, I can still give you partial credit. If you need to add a surrogate key or any
other variable, please describe that in your work.

Table 1.)
BoatName Make Model Length
OwnerLast
Name
OwnerFirst
Name OwnerPhone Address City State ZIP CharterDate CustomerName Amount
Far Horizon Catalina Morgan 38 Princeton Darryl 206-543-6677 2345 15th NESeattle WA 98115 21-Jun-12 Smith, Sally 5,000.00$
Ebb Tide Hunter 38 38 Tulsa Bill 503-486-8786 1324 24th NEPortland OR 97215 21-Jun-12 Sailors, Mike 5,500.00$
Far Horizon Catalina Morgan 38 Princeton Darryl 206-543-6677 2345 15th NESeattle WA 98115 28-Jun-12 Tully, Jason 5,000.00$
Far Horizon Catalina Morgan 38 Princeton Darryl 206-543-6677 2345 15th NESeattle WA 98115 4-Jul-12 Atkins, Merle 5,000.00$
Foreign Shores Hans Christian 38 MK II 38 Berkely George 425-765-4455 4567 35th WBellevue WA 98040 4-Jul-12 Smith, Sally 6,000.00$
Seafarer V Endeavour 37 37 Tulsa Bill 503-486-8786 1324 24th NEPortland OR 97215 4-Jul-12 Travis, Chet 4,500.00$
Seafarer V Endeavour 37 37 Tulsa Bill 503-486-8786 1324 24th NEPortland OR 97215 18-Jul-12 Watson, John 4,500.00$
Midnight on the Water Sabre 32 32 Oxford Kelly 503-578-7574 2435 36th SEAstoria OR 97103 18-Jul-12 Renborn, "Doc" 4,000.00$
Ebb Tide Hunter 38 38 Tulsa Bill 503-486-8786 1324 24th NEPortland OR 97215 27-Jul-12 Sailors, Mike 5,500.00$
Foreign Shores Hans Christian 38 MK II 38 Berkely George 425-765-4455 4567 35th WBellevue WA 98040 27-Jul-12 Tully, Jason 6,000.00$
Functional Dependencies:
(Make, Model) -> Length
BoatName -> (Make, Model, Length, OwnerLastName, OwnerFirstName, OwnerPhone, Address, City, State, ZIP)
OwnerPhone -> (OwnerLastName, OwnerFirstName, Address, City, State, ZIP)
ZIP -> (City, State)
(CharterDate, BoatName, CustomerName) -> Amount

Table 2.)
PetName PetType PetBreed PetDOB OwnerLastName OwnerFirstName OwnerPhone OwnerEmail Service Date Charge
King Dog Std. Poodle 27-Feb-12 Downs Marsha 201-823-5467 [e-mail] Ear Infection 17-Aug-14 65.00$
Teddy Cat Cashmier 1-Feb-11 James Richard 201-735-9812 [e-mail] Nail Clip 5-Sep-14 27.50$
Filo Dog Std. Poodle 17-Jul-13 Downs Marsha 201-823-5467 [e-mail]
AJ Dog Collie Mix 5-May-13 Frier Liz 201-823-6578 [e-mail] One year shots 5-May-14 42.50$
Cedro Cat Unknown 6-Jun-10 James Richard 201-735-9812 [e-mail] Nail Clip 5-Sep-14 27.50$
Woolley Cat Unknown ??? James Richard 201-735-9812 [e-mail] Skin Infection 3-Oct-14 35.00$
Buster Dog Border Collie 11-Dec-09 Trent Miles 201-634-7865 [e-mail] Laceration Repair 5-Oct-14 127.00$
Jedah Cat Abyssinian 1-Jul-06 Evans Hilary 210-634-2345 [e-mail] Booster Shots 4-Nov-14 111.00$
Functional Dependencies:
PetBreed -> PetType
(PetName, PetDOB) -> (PetBreed, OwnerEmail)
OwnerEmail -> (OwnerPhone, OwnerLastName, OwnerFirstName)
(Date, Service, PetName, PetDOB) -> Charge

Table 3.)
PropertyName Type Street City ZIP ServiceDate Description Amount
Eastlake Building Office 123 Eastlake Seattle 98119 5/5/2012 Lawn Mow 42.50$
Elm St Apts Apartment 4 East Elm Lynnwood 98223 5/8/2012 Lawn Mow 123.50$
Jeferson Hill Office 42 West 7th St Bellevue 98040 5/8/2012 Garden Service 53.00$
Eastlake Building Office 123 Eastlake Seattle 98119 5/10/2012 Lawn Mow 42.50$
Eastlake Building Office 123 Eastlake Seattle 98119 5/12/2012 Lawn Mow 42.50$
Elm St Apts Apartment 4 East Elm Lynnwood 98223 5/15/2012 Lawn Mow 123.50$
Eastlake Building Office 123 Eastlake Seattle 98119 5/19/2012 Lawn Mow 42.50$
Functional Dependencies:
Property Name -> (Type, Street, City, ZIP)
(ServiceDate, Description, PropertyName) -> Amount

Table 4.)
(ManufacturerName, BeerName) -> (Percent Alcohol, Beer Type)
ManufacturerName -> (ManufacturerState, ManufacturerCity, ManufacturerPhone)
(PurchaseDate, BeerName, ManufacturerName) -> NumberOfBottleOrCans