Database Normalization Assignment: Tables 1, 2, 3, and 4

This document presents a detailed solution to a database normalization assignment, meticulously addressing the transformation of database tables through various normal forms. The assignment covers four tables, each normalized to 1st, 2nd, 3rd, and Boyce-Codd Normal Form (BCNF). The solution explains the process of achieving each normal form, detailing the identification of dependencies, the splitting of tables to eliminate redundancies, and the establishment of primary and foreign key relationships. Table 1 focuses on boat charter information, Table 2 on pet-related data, Table 3 on property services, and Table 4 on beer manufacturing and purchase data. Each table's normalization process is thoroughly explained, providing a comprehensive understanding of database design principles and normalization techniques to ensure data integrity and efficiency. The assignment offers a practical guide to database normalization, illustrating how to apply these concepts to real-world scenarios.

normalization assignment
Student
29 September, 2016

Contents
TABLE 1..................................................................................................................................................2
1ST NORMAL FORM............................................................................................................................2
2ND NORMAL FORM...........................................................................................................................2
3RD NORMAL FORM............................................................................................................................3
BCNF..................................................................................................................................................3
TABLE 2..................................................................................................................................................3
1ST NORMAL FORM............................................................................................................................4
2ND NORMAL FORM...........................................................................................................................4
3RD NORMAL FORM............................................................................................................................4
BCNF..................................................................................................................................................5
TABLE 3..................................................................................................................................................5
1ST NORMAL FORM............................................................................................................................5
2ND NORMAL FORM...........................................................................................................................6
3RD NORMAL FORM............................................................................................................................6
BCNF..................................................................................................................................................6
TABLE 4..................................................................................................................................................7
1ST NORMAL FORM............................................................................................................................7
2ND NORMAL FORM...........................................................................................................................7
3RD NORMAL FORM............................................................................................................................8
BCNF..................................................................................................................................................8

TABLE 1
(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
1ST NORMAL FORM
In 1st normal the data should be atomic, that is, it is further indivisible. As we see in the table below,
the data is atomic and hence already in 1st normal form.
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$
2ND NORMAL FORM
2nd normal form deals with removing the partial dependency on the primary key. Thus we need to
split the table further to remove any partial dependency.
Table boat
BoatName (primaryKey_) -> Make, Model, Length
Table owner
OwnerPhone (PrimaryKey_) -> (OwnerLastName, OwnerFirstName, Address, City, State, ZIP)
Table Zip
ZIP (PrimaryKey_) -> City, State
Table boat_charter_info
CharterDate, BoatName, CustomerName (PrimaryKey_) -> Amount

3RD NORMAL FORM
In 3rd normal form we remove any transitive dependencies, if they exist, by creating primary key –
foreign key relationship. Thus the relations after implying 3NF are:
Table boat
BoatName (primaryKey_) -> Make, Model, Length
Table boatOwner
boatName (primaryKey_, foreignKey_) -> ownerPhone(foreignKey_)
Table owner
OwnerPhone (PrimaryKey_) -> (OwnerLastName, OwnerFirstName, Address, ZIP (foreignKey_)
Table Zip
ZIP (PrimaryKey_) -> City, State
Table boat_charter_info
CharterDate, BoatName, CustomerName (PrimaryKey_) -> Amount
BCNF
In BCNF, the 3NF is in stricter terms. This means that the dependency should be on a Super key, that
is, for X → A, X must be a super-key. The tables are already in BCNF
Table boat
BoatName (primaryKey_) -> Make, Model, Length
Table boatOwner
boatName (primaryKey_, foreignKey_) -> ownerPhone(foreignKey_)
Table owner
OwnerPhone (PrimaryKey_) -> (OwnerLastName, OwnerFirstName, Address, ZIP (foreignKey_)
Table Zip
ZIP (PrimaryKey_) -> City, State
Table boat_charter_info
CharterDate, BoatName, CustomerName (PrimaryKey_) -> Amount
TABLE 2
PetBreed -> PetType

(PetName, PetDOB) -> (PetBreed, OwnerEmail)
OwnerEmail -> (OwnerPhone, OwnerLastName, OwnerFirstName)
(Date, Service, PetName, PetDOB) -> Charge
1ST NORMAL FORM
In 1st normal the data should be atomic, that is, it is further indivisible. As we see in the table below,
the data is atomic and hence already in 1st normal form.
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$
2ND NORMAL FORM
2nd normal form deals with removing the partial dependency on the primary key. Thus we need to
split the table further to remove any partial dependency.
Pet table
PetBreed(PrimaryKey_) -> PetType
Pet_Info table
PetName (PrimaryKey_), PetDOB(PrimaryKey_) -> PetBreed, OwnerEmail
Owner table
OwnerEmail (PrimaryKey_) -> (OwnerPhone, OwnerLastName, OwnerFirstName)
Service table
Service -> charge
Pet_Service table
(Date, PetName, PetDOB) (PrimaryKey_)-> Service
3RD NORMAL FORM
In 3rd normal form we remove any transitive dependencies, if they exist, by creating primary key –
foreign key relationship. Thus the relations after implying 3NF are:
Pet table

PetBreed(PrimaryKey_) -> PetType
Pet_Info table
PetName (PrimaryKey_), PetDOB(PrimaryKey_) -> PetBreed (ForeignKey_),OwnerEmail(ForeignKey_)
Owner table
OwnerEmail (PrimaryKey_) -> OwnerPhone, OwnerLastName, OwnerFirstName
Service table
Service -> charge
Pet_Service table
(Date, PetName(ForeignKey_), PetDOB(ForeignKey_)) (PrimaryKey_)-> Service(ForeignKey_)
BCNF
In BCNF, the 3NF is in stricter terms. This means that the dependency should be on a Super key, that
is, for X → A, X must be a super-key. The tables in BCNF are:
Pet table
PetBreed(PrimaryKey_) -> PetType
Pet_Info table
(PetName ,PetDOB, OwnerEmail) (PrimaryKey_) -> PetBreed (ForeignKey_)
Owner table
(OwnerEmail, OwnerPhone) (PrimaryKey_) -> OwnerLastName, OwnerFirstName
Service table
Service -> charge
Pet_Service table
(Date, PetName, PetDOB) (PrimaryKey_)-> Service(ForeignKey_)
TABLE 3
Property Name -> (Type, Street, City, ZIP)
(ServiceDate, Description, PropertyName) -> Amount
1ST NORMAL FORM
In 1st normal the data should be atomic, that is, it is further indivisible. As we see in the table below,
the data is atomic and hence already in 1st normal form.

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$
2ND NORMAL FORM
2nd normal form deals with removing the partial dependency on the primary key. Thus we need to
split the table further to remove any partial dependency.
Table property
Property Name(PrimaryKey_) -> (Type, Street, ZIP)
Table ZIP
ZIP (PrimaryKey_) -> city
Table service
(ServiceDate, Description, PropertyName) (PrimaryKey_) -> Amount
3RD NORMAL FORM
In 3rd normal form we remove any transitive dependencies, if they exist, by creating primary key –
foreign key relationship. The relations in 3NF are:
Table property
Property Name(PrimaryKey_) -> (Type, Street, ZIP(ForeignKey_))
Table ZIP
ZIP (PrimaryKey_) -> city
Table service
(ServiceDate, Description, PropertyName(ForeignKey_)) (PrimaryKey_) -> Amount
BCNF
In BCNF, the 3NF is in stricter terms. This means that the dependency should be on a Super key, that
is, for X → A, X must be a super-key. The tables are already in BCNF because there are no more
candidate keys to convert to a super key. Hence they are:
Table property
Property Name(PrimaryKey_) -> (Type, Street, ZIP(ForeignKey_))

Table ZIP
ZIP (PrimaryKey_) -> city
Table service
(ServiceDate, Description, PropertyName(ForeignKey_)) (PrimaryKey_) -> Amount
TABLE 4
(ManufacturerName, BeerName) -> (Percent Alcohol, Beer Type)
ManufacturerName -> (ManufacturerState, ManufacturerCity, ManufacturerPhone)
(PurchaseDate, BeerName, ManufacturerName) -> NumberOfBottleOrCans
1ST NORMAL FORM
In 1st normal the data should be atomic, that is, it is further indivisible. As we see in the table below,
the data is atomic and hence already in 1st normal form.
2ND NORMAL FORM
2nd normal form deals with removing the partial dependency on the primary key. Thus we need to
split the table further to remove any partial dependency.
Beer table

(ManufacturerName, BeerName) (PrimaryKey_) -> (Percent Alcohol, Beer Type)
Manufacturer table
ManufacturerName(PrimaryKey_) -> ManufacturerCity, ManufacturerPhone
Area table
ManufacturerCity (PrimaryKey_) -> ManufacturerState
Purchase table
(PurchaseDate, BeerName, ManufacturerName) (PrimaryKey_) -> NumberOfBottleOrCans
3RD NORMAL FORM
In 3rd normal form we remove any transitive dependencies, if they exist, by creating primary key –
foreign key relationship. There is no transitive dependency left hence relations in 3NF are:
Beer table
(ManufacturerName (ForeignKey_), BeerName) (PrimaryKey_) -> (Percent Alcohol, Beer Type)
Manufacturer table
ManufacturerName(PrimaryKey_) -> ManufacturerCity(ForeignKey_), ManufacturerPhone
Area table
ManufacturerCity (PrimaryKey_) -> ManufacturerState
Purchase table
(PurchaseDate, BeerName(ForeignKey_), ManufacturerName(ForeignKey_)) (PrimaryKey_) ->
NumberOfBottleOrCans
BCNF
In BCNF, the 3NF is in stricter terms. This means that the dependency should be on a Super key, that
is, for X → A, X must be a super-key. The tables are already in BCNF because there is no candidate
key present in the above tables
Beer table
(ManufacturerName (ForeignKey_), BeerName) (PrimaryKey_) -> (Percent Alcohol, Beer Type)
Manufacturer table
ManufacturerName(PrimaryKey_) -> ManufacturerCity(ForeignKey_), ManufacturerPhone
Area table
ManufacturerCity (PrimaryKey_) -> ManufacturerState
Purchase table

(PurchaseDate, BeerName(ForeignKey_), ManufacturerName(ForeignKey_)) (PrimaryKey_) ->
NumberOfBottleOrCans