HAFH (Home Away from Home) Realty Company leases apartments to corporate clients. HAFH Realty Company Property...

Transcribed Image Text:

• HAFH ("Home Away from Home") Realty Company leases apartments to corporate clients. HAFH Realty Company Property Management Database will keep track of HAFH buildings, apartments, corporate clients, building managers, cleaning staff members, and building inspectors. The property management database for the realty company HAFH will capture data about the following: • For each building: BuildingID (unique) and BNoOfFloors (number of floors in the building); • For each apartment: AptNo (partially unique, i.e., unique within a building) and ANoOf Bedrooms (number of bedrooms in the apartment); • For each corporate client: CCID (unique), CCName (unique), CCLocation, and CCIndustry; • For each manager: ManagerID (unique), MFullName (composed of MFirstName and MLastName), multiple MPhone numbers, MBDate, MAge (derived from MBdate and current date), MSalary, and MBonus (not every manager will have a bonus); • For each staff member: SMemberID (unique) and SMemberName; • For each inspector: InsID (unique) and InsName; • Each building has one or more apartments. Each apartment is located in exactly one building. • Each apartment is either leased to one corporate client or to no one. Each corporate client leases between zero and many apartments. • Each corporate client refers between zero and many corporate clients. Each corporate client can be referred by one corporate client or is not referred by any. • Each apartment is cleaned by either one or two staff members. Each staff member cleans between five and ten apartments. • Each manager manages one or many buildings. Each building is managed by exactly one manager. • Each manager resides in exactly one building. Each building either has one manager residing in it or no managers residing in it. • Each inspector inspects one or many buildings. Each building is inspected by one or many inspectors. • For each building that a particular inspector inspects, the dates of the last inspection by the inspector and of the next future inspection by the inspector are recorded." BuildingID BNoOfFloors) ManagerID ResidesIn MPhone DateNext MAge Inspects BUILDING Manages MANAGER MBdate INSPECTOR DateLast LocatedIn MSalary MBonus (0) %% (MFullName) InsID MLastName InsName AptNo ANoOfBedrooms MFirstName APARTMENT O (5,10) Cleans (1,2) STAFFMEMBER Leases SMemberID SMemberName) CCName CCID CCIndustry CORPCLIENT Refers CCLocation Apply the ER-to-Relational Mapping Algorithm in Chapter 9 (pages 290-296) to the HAFH ("Home Away from Home") Realty Company ER diagram. Following the example in the document on Canvas named ER_to_Relational_Algorithm_applied_to_the_COMPANY_ER_diagram.docx list the outputs from each step of the algorithm in Chapter 9 (pages 290-296) using the template below. For foreign key attributes, please use the same names as their matching primary key attributes, except that in step 4 when mapping the Refers relationship please use the foreign key name CCIDreferred by. (A different foreign key name is necessary in that case because the foreign key attribute and its matching primary key attribute are in the same table.) Step 1. List only the tables (or "relations") that would be created in "Step 1: Mapping of Regular Entity Types" and all of those table's attributes that are created in step 1. Specify the primary key of each table you list. Specify any additional unique keys. If there are no Regular Entity Types, then write, "There are no Regular Entity Types." Your answer for Step 1 goes here. Step 2. List only the tables (or "relations") that would be created in "Step 2: Mapping of Weak Entity Types" and all of those table's attributes that are created in step 2. Specify the primary key of each table you list. If there are no Weak Entity Types, then write, "There are no Weak Entity Types.' "1 Your answer for Step 2 goes here. Step 3. List only the tables (or "relations") that would have attributes added to them in "Step 3: Mapping of Binary 1:1 Relationship Types" using the Foreign key approach. For each table you list, specify only the attributes added in step 3. Indicate which attributes are foreign keys. If there are no 1:1 Relationship Types, then write, "There are no 1:1 Relationship Types." Your answer for Step 3 goes here. Step 4. List only the tables (or "relations") that would have attributes added to them in "Step 4: Mapping of Binary 1:N Relationship Types" using the Foreign key approach. For each table you list, specify only the attributes added in step 4. Indicate which attributes are foreign keys. If there are no 1:N Relationship Types, then write, "There are no 1:N Relationship Types." Your answer for Step 4 goes here. Step 5. List only the tables (or "relations") that would be created in "Step 5: Mapping of Binary M:N Relationship Types" using the Foreign key approach. For each table you list, specify only the attributes created in step 5. Specify the primary key of each table you list. Indicate which attributes are foreign keys. If there are no M:N Relationship Types, then write, "There are no M:N Relationship Types." Your answer for Step 5 goes here. Step 6. List only the tables (or "relations") that would be created in "Step 6: Mapping of Multivalued Attributes." For each table you list, specify only the attributes created in step 6. Specify the primary key of each table you list. Indicate which attributes are foreign keys. If there are no Multivalued Attributes, then write, "There are no Multivalued Attributes." Your answer for Step 6 goes here. • HAFH ("Home Away from Home") Realty Company leases apartments to corporate clients. HAFH Realty Company Property Management Database will keep track of HAFH buildings, apartments, corporate clients, building managers, cleaning staff members, and building inspectors. The property management database for the realty company HAFH will capture data about the following: • For each building: BuildingID (unique) and BNoOfFloors (number of floors in the building); • For each apartment: AptNo (partially unique, i.e., unique within a building) and ANoOf Bedrooms (number of bedrooms in the apartment); • For each corporate client: CCID (unique), CCName (unique), CCLocation, and CCIndustry; • For each manager: ManagerID (unique), MFullName (composed of MFirstName and MLastName), multiple MPhone numbers, MBDate, MAge (derived from MBdate and current date), MSalary, and MBonus (not every manager will have a bonus); • For each staff member: SMemberID (unique) and SMemberName; • For each inspector: InsID (unique) and InsName; • Each building has one or more apartments. Each apartment is located in exactly one building. • Each apartment is either leased to one corporate client or to no one. Each corporate client leases between zero and many apartments. • Each corporate client refers between zero and many corporate clients. Each corporate client can be referred by one corporate client or is not referred by any. • Each apartment is cleaned by either one or two staff members. Each staff member cleans between five and ten apartments. • Each manager manages one or many buildings. Each building is managed by exactly one manager. • Each manager resides in exactly one building. Each building either has one manager residing in it or no managers residing in it. • Each inspector inspects one or many buildings. Each building is inspected by one or many inspectors. • For each building that a particular inspector inspects, the dates of the last inspection by the inspector and of the next future inspection by the inspector are recorded." BuildingID BNoOfFloors) ManagerID ResidesIn MPhone DateNext MAge Inspects BUILDING Manages MANAGER MBdate INSPECTOR DateLast LocatedIn MSalary MBonus (0) %% (MFullName) InsID MLastName InsName AptNo ANoOfBedrooms MFirstName APARTMENT O (5,10) Cleans (1,2) STAFFMEMBER Leases SMemberID SMemberName) CCName CCID CCIndustry CORPCLIENT Refers CCLocation Apply the ER-to-Relational Mapping Algorithm in Chapter 9 (pages 290-296) to the HAFH ("Home Away from Home") Realty Company ER diagram. Following the example in the document on Canvas named ER_to_Relational_Algorithm_applied_to_the_COMPANY_ER_diagram.docx list the outputs from each step of the algorithm in Chapter 9 (pages 290-296) using the template below. For foreign key attributes, please use the same names as their matching primary key attributes, except that in step 4 when mapping the Refers relationship please use the foreign key name CCIDreferred by. (A different foreign key name is necessary in that case because the foreign key attribute and its matching primary key attribute are in the same table.) Step 1. List only the tables (or "relations") that would be created in "Step 1: Mapping of Regular Entity Types" and all of those table's attributes that are created in step 1. Specify the primary key of each table you list. Specify any additional unique keys. If there are no Regular Entity Types, then write, "There are no Regular Entity Types." Your answer for Step 1 goes here. Step 2. List only the tables (or "relations") that would be created in "Step 2: Mapping of Weak Entity Types" and all of those table's attributes that are created in step 2. Specify the primary key of each table you list. If there are no Weak Entity Types, then write, "There are no Weak Entity Types.' "1 Your answer for Step 2 goes here. Step 3. List only the tables (or "relations") that would have attributes added to them in "Step 3: Mapping of Binary 1:1 Relationship Types" using the Foreign key approach. For each table you list, specify only the attributes added in step 3. Indicate which attributes are foreign keys. If there are no 1:1 Relationship Types, then write, "There are no 1:1 Relationship Types." Your answer for Step 3 goes here. Step 4. List only the tables (or "relations") that would have attributes added to them in "Step 4: Mapping of Binary 1:N Relationship Types" using the Foreign key approach. For each table you list, specify only the attributes added in step 4. Indicate which attributes are foreign keys. If there are no 1:N Relationship Types, then write, "There are no 1:N Relationship Types." Your answer for Step 4 goes here. Step 5. List only the tables (or "relations") that would be created in "Step 5: Mapping of Binary M:N Relationship Types" using the Foreign key approach. For each table you list, specify only the attributes created in step 5. Specify the primary key of each table you list. Indicate which attributes are foreign keys. If there are no M:N Relationship Types, then write, "There are no M:N Relationship Types." Your answer for Step 5 goes here. Step 6. List only the tables (or "relations") that would be created in "Step 6: Mapping of Multivalued Attributes." For each table you list, specify only the attributes created in step 6. Specify the primary key of each table you list. Indicate which attributes are foreign keys. If there are no Multivalued Attributes, then write, "There are no Multivalued Attributes." Your answer for Step 6 goes here.

Answer rating: 100% (QA)

Based on the instructions and the ER diagram provided we can list the tables relations that would be created for each step and specify the primary keys additional unique keys and foreign keys where applicable Step 1 Tables to be created BUILDING APARTMENT MANAGER STAFFMEMBER INSPECTOR CORPCLIENT and REFERS BUILDING BuildingID BNoOfFloors Primary key ... View the full answer