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 |
|
| | 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.
