To Homework

ISP 121 -- Homework H1 (Solutions)

Database Normalization

Normalize each of the following tables:

• Rewrite the field names under appropriate table names.
• Put the initial PK in front of the columns forming the primary key and FK in front of each foreign key.

1. Personnel Table
   
   PK First Name
   PK Last Name
   Address
   City
   State
   Zip
   Annual Income
   Insurance Provider Name
   

   EmployeePhoneNumbers Table
   

   FK First Name
   FK Last Name
   Phone Number
   Type of Phone
   

2. Daycare Table
   
   PK ParentLastName
   PK ParentFirstName
   ParentAddress
   ParentCity
   ParentState
   ParentZip
   

   Phone Table
   

   FK ParentLastName
   FK ParentFirstName
   PhoneNumber
   TypeofPhone
   

   ChildInfo Table
   

   PK FK ParentLastName
   PK FK ParentFirstName
   PK ChildFirstName
   Age
   

   Allergies Table
   

   FK ParentLastName
   FK ParentFirstName
   FK ChildFirstName
   AllergyType
   

3. CD Table
   
   PK CDTitle
   Artist
   YearRecorded
   Genre
   YearPurchased
   

   TrackInfo Table
   

   FK CDTitle
   TrackTitle
   TrackLength
   

4. RealEstateAgentListings Database

   Listing Table
   

   PK ListingID
   Address
   City
   NumberStories
   NumberofBedrooms
   FrameType
   Basement (Yes or No)
   HeatingType
   Taxes
   SchoolDistrict
   

   Room Info Table
   

   FK ListingID
   Width
   Length
   FlooringType
   

   Agent-Listing Table
   

   FK AgentID
   FK ListingID
   

   Agent Table
   

   Name
   PK AgentID
   Address
   City
   Phone