Assignment 1 Database Relationships & Normalization - PROG2390 Small Bussiness.

Software being used is Spreadsheets Excel to make the diagrams.

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]

      Assignment 1 Database Relationships & Normalization

      This assignment must be done in groups of 2. If you are unable to find a group member one will be assigned.

      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:

   2. The company collects information about all its tenants, buildings, apartments and parking lots shown in APPENDIX A (below)
   3. After further investigation you also receive additional information regarding the operation of the company shown in APPENDIX B (below):
   5. 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)
   6. Determine & list the primary keys - clearly identifying the related tables [3 marks]
   7. Determine & list the foreign keys - clearly identifying the related tables [2 Marks]
   8. Determine the data types for each field in all tables as shown APPENDIX A - (0.5 points each) [11 Marks]
   9. Determine and briefly explain all the relationships that will exist between tables in the database [7 Marks]
   10. 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]

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