Needs to be done in Excel Spreadsheets. Please

Assignment 1 Database Relationships & Normalization

A real estate company hired you to create a new database for its leasing department. This will be used to manage information related to tenants, buildings, apartments and parking lots.

Note: Multiple buildings can share a common parking lot. A building can have multiple parking lots.

Upon request you are provided with the following:

• The company collects information about all its tenants, buildings, apartments and parking lots shown in APPENDIX A (below)
• After further investigation you also receive additional information regarding the operation of the company shown in APPENDIX B (below):

1. Based on your previous experience you must do the following: Organize the data (you can add more fields to Appendix A) to create data groups/tables. [15 Marks]
   1. List and show the groups separately (prior to drawing the table in the following questions)
2. Determine & list the primary keys - clearly identifying the related tables [3 marks]
3. Determine & list the foreign keys - clearly identifying the related tables [2 Marks]
4. Determine the data types for each field in all tables as shown APPENDIX A - (0.5 points each) [11 Marks]
5. Determine and briefly explain all the relationships that will exist between tables in the database [7 Marks]
6. Create an entity relationship diagram for all tables showing the associations between each [12 Marks]
   1. All tables must be in 3^rd normal form [10 Marks]

/60

NOTE:

• Answer each question separately
• Use Microsoft Excel/Word/ PowerPoint to create your submission. Draw the tables and the diagrams using the tools available; you may also use any other drawing tool the final submission for all diagrams must be in a common format (word / excel / jpg/ pdf)

APPENDIX A:

The following list is NOT grouped/organized you can add to this list as you see fit

• Tenant ID
• Street
• Parking ID
• Tenant Last Name
• Postal Code
• Building ID
• Tenant Email
• Parking Lot Name
• Apartment ID
• Country
• Unit Number
• Building Name
• Address ID
• Leased
• City
• Tenant First Name
• Province
• Tenant Phone Number

APPENDIX B:

Information regarding the company operations:

• An apartment belongs to one building
• Multiple tenants can lease the same apartment
• Buildings can share parking lots
• A tenant can only lease one apartment
• Buildings may have one or more apartments
• Parking lots can belong to more than one building