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

Subject:Computer SciencePrice: Bought3

Share With

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

 

1. Describe how you have represented weak entities, supertype and subtype entities, if any exist. 

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

 

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

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