Answer only question #4

Answer only question #4
For each entity in the E-R model, specify a table structure, determine the data types and additional column properties using the metadata tables (use the Template table Figure D3.1 below as a guide for metadata table format and see Figure 5-26 on page 342 in the textbook for an example), identify primary keys and foreign keys, and verify normalization on the resulting tables.  OWNER  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Owner_ID  Integer  Primary Key  Yes  None  Surrogate Key: Initial Value=1, Increment=1   Owner_Name  Char (45)  No  Yes  None      Email  Char (75)  No  Yes  None      Type  Char (15)  No  Yes  None       PROPERTY  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks  Property_ID  Integer   Primary Key  Yes  None  Surrogate Key: Initial Value=1, Increment=1   Property_Name  Char (15)  No  Yes  None     Street  Char (90)  No  Yes  None     City  Char (15)  No  Yes  None     State  Char (2)  No  Yes  None   Format: AA  Owner_ID  Integer (10)  Foreign Key  Yes  None   REF: OWNER    SUB_PROPERTY  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks  Subproperty_ID  Integer  Primary Key  Yes  None  Surrogate Key: Initial Value=1, Increment=1  Subproperty_Type  Char (45)  No  Yes  None     Property_ID  Integer  Foreign Key   Yes  None  REF: PROPERTY    SERVICE  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Service_ID  Integer  Primary  Yes  None  Surrogate Key: Initial Value=1, Increment=1  Subproperty_ID  Integer  Foreign Key  Yes  None  REF: SUB_PROPERTY  Employee_ID  Integer  Foreign Key  Yes  None  REF: EMPLOYEE  Service_Date  Date (10)  No  Yes  None  Format: MM/DD/YYYY  Hours_Worked  Decimal (5)  No  Yes  None       SERVICE_EQUIPMENT  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Service_ID  Integer  Primary Key, Foreign Key  Yes  None  REF: SERVICE   Equipment_ID  Integer  Primary Key, Foreign Key  Yes  None  REF: EQUIPMENT    EQUIPMENT  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Equipment_ID  Integer  Primary Key  Yes  None  Surrogate Key: Initial Value=1, Increment=1   Equipment_Name  Char (45)  No  Yes  None       EMPLOYEE  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Employee_ID  Integer  Primary Key  Yes  None  Surrogate Key: Initial Value=1, Increment=1   Last_Name  Char (25)  No  Yes  None      First_Name  Char (25)  No  Yes  None      Cell_Phone  Char (12)  No  Yes  None  Format: ###-###-####   Experience_Level  Char (15)  No  Yes  None       EMPLOYEE_TRAINING  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Employee_ID  Integer  Foreign Key  Yes  None  REF: EMPLOYEE   Training_ID  Integer  Foreign Key  Yes  None  REF: TRAINING    EQUIPMENT_TRAINING  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks  Training_ID  Integer  Primary Key  Yes  None   Surrogate Key: Initial Value=1, Increment=1   Equipment_ID  Char (10)  Foreign Key  Yes  None  REF: EQUIPMENT   Training_Date  Date (10)  No  Yes  None  FORMAT: MM/DD/YYYY    EQUIPMENT_REPAIR  Column Name  Data Type (Length)  Key  Required  Default Value  Remarks   Repair_ID  Integer  Primary Key  Yes  None   Surrogate Key: Initial Value=1, Increment=1   Repair_Date  Date (10)  No  Yes  None   FORMAT: MM/DD/YYYY   Description  Char (100)  No  Yes  None     Cost  Currency  No  Yes   None   Format: Standard   Equipment_ID  Integer (10)  Foreign Key  Yes  None  REF: EQUIPMENT    Describe how you have represented weak entities, supertype and subtype entities, if any exist.  Document referential integrity constraint enforcement actions; use the Template table Figure D3.2 below as a guide and see Figure 5-29 on page 347 in the textbook for an example.  Figure D3.2 – RI Template to document the referential integrity constraint enforcement  Relationship  Referential Integrity Constraint  Cascading Behavior  Parent  Child     On Update  On Delete  OWNER    OWNER_ID   Owner_ID in PROPERTY must exist in OWNER_ID in OWNER   No   Yes  SERVICE  SERVICE_ID   Service_ID in SERVICE_EQUIPMENT must exist in SERVICE_ID in SERVICE  No    No  EQUIPMENT_TRAINING     TRAINING_ID   Training_ID in EMPLOYEE_TRAINING must exist in Training_ID in EQUIPMENT_TRAINING   No   No   SERVICE     SUPROPERTY_ID   Subproperty_ID in SUB_PROPERTY must exist in Subproperty_ID in SERVICE  No   Yes  Employee      Employee_ID   Employee_ID in EMPLOYEE_TRAINING must exist in Employee_ID in EMPLOYEE  No    No  EQUIPMENT_REPAIR    Equipment_ID  Equipment_ID in EMPLOYEE_TRAINING must exist in EQUIPMENT_ID in EQUIPMENT_REPAIR   No   No  EQUIPMENT   Equipment_ID   Equipment_ID in EMPLOYEE_TRAINING must exist in EMPLOYEE_ID in EQUIPMENT  No   No    Document any business rules that you think might be important. Describe how you would validate that your design is a good representation of the data model upon which it is based.  Implement your DB design in MS Access. Create tables, create relationships, enter sample data ( see the sample data in previous deliverables and/or feel free to make up some sample data as appropriate), create data entry forms and reports as necessary, and use SQL or QBE to query the DB and test its operational performance.