Describe how you have represented weak entities, supertype and subtype entities,
ID: 3869261 • Letter: D
Question
Describe how you have represented weak entities, supertype and subtype entities, if any exist.
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
OwnerID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
OwnerFirstName
Char (30)
No
Yes
None
OwnerLastName
Char (30)
No
Yes
None
OwnerPhone
Char (12)
No
Yes
None
Format: ###-###-####
OwnerEmailAddress
VarChar (100)
No
No
None
OwnerStreet
Char (30)
No
Yes
None
OwnerCity
Char (20)
No
Yes
None
OwnerState
Char (2)
No
Yes
WA
Format: AA
OwnerZip
Char (10)
No
Yes
None
Format: #####-####
(a) OWNER
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
PropertyID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
PropertyName
Char (30)
No
Yes
None
PropertyStreet
Char (30)
No
Yes
None
PropertyCity
Char (20)
No
Yes
None
PropertyState
Char (2)
No
Yes
WA
Format: AA
PropertyZip
Char (10)
No
Yes
None
Format: #####-####
OwnerID
Integer
FK1
Yes
None
REF: OWNER
(b) PROPERTY
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
RepairID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
DescriptionOfRepair
Char (200)
No
Yes
None
DateOfRepair
Date
No
Yes
None
Format: yyyy-mm-dd
CostOfRepair
Numeric (9,2)
No
Yes
None
EquipmentID
Integer
FK1
Yes
None
REF: EQUIPMENT
(c) REPAIR
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
EquipmentID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
DatePurchased
Date
No
Yes
None
Format: yyyy-mm-dd
WarrentyEnd
Date
No
No
None
Format: yyyy-mm-dd
PurchasePrice
Numeric (9,2)
No
Yes
None
EquipmentUsedFor
Char (100)
No
No
None
InvoiceNumber
Interger (25)
No
Yes
None
(d) EQUIPMENT
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
TrainingID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
TrainingName
Char (25)
No
Yes
None
DescriptionOfTraining
Char (250)
No
No
None
EquipmentID
Integer
FK1
Yes
None
REF: EQUIPMENT
(e) TRAINING
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
SubPropertyID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
SubPropertyName
Char (30)
No
Yes
None
PropertyID
Integer
FK1
Yes
None
REF: PROPERTY
(f) SUB_PROPERTY
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
PropertyServiceID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
ServiceDate
Date
No
Yes
None
Format: yyyy-mm-dd
ServiceDescription
Char (100)
No
Yes
None
HoursWorked
Integer
No
Yes
None
CostPerHour
Numeric (9,2)
No
Yes
None
TotalCost
Numeric (9,2)
No
Yes
None
EmployeeID
Integer
FK1
Yes
None
REF: EMPLOYEE
SubPropertyID
Integer
FK2
Yes
None
REF: SUB_PROPERTY
(g) PROPERTY_SERVICE
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
EmployeeID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
EmployeeLastName
Char (30)
No
Yes
None
EmployeeFirstName
Char (30)
No
Yes
None
EmployeePhone
Char (12)
No
Yes
None
Format: ###-###-####
EmployeeEmailAddress
Char (100)
No
No
None
EmployeeAddress
Char (30)
No
Yes
None
EmployeeCity
Char (20)
No
Yes
None
EmployeeState
Char (2)
No
Yes
WA
EmployeeZip
Char (10)
No
Yes
None
Format: #####-####
EmployeeWage
Numeric (9,2)
No
Yes
None
EmployeeExperience
Char (50)
No
Yes
None
(h) EMPLOYEE
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
PropertyServiceID
Integer
PK, FK1
Yes
None
REF: PROPERTY_SERVICE
EquipmentID
Integer
PK, FK2
Yes
None
REF: EQUPMENT
(i) EQUIPMENT_USE
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
EmployeeID
Number
PK, FK1
Yes
None
REF: EMPLOYEE
TrainingID
Number
PK, FK2
Yes
None
REF: TRAINING
(j) EMPLOYEE_TRAINING
Column Name
Data Type (Length)
Key
Required
Default Value
Remarks
OwnerID
Integer
PK
Yes
DBMS supplied
Surrogate Key:
Initial value=1
Increment=1
OwnerFirstName
Char (30)
No
Yes
None
OwnerLastName
Char (30)
No
Yes
None
OwnerPhone
Char (12)
No
Yes
None
Format: ###-###-####
OwnerEmailAddress
VarChar (100)
No
No
None
OwnerStreet
Char (30)
No
Yes
None
OwnerCity
Char (20)
No
Yes
None
OwnerState
Char (2)
No
Yes
WA
Format: AA
OwnerZip
Char (10)
No
Yes
None
Format: #####-####
Explanation / Answer
a.OWNER -Each row can be identified using the Surrogate key OwnerID
b .PROPERTY - Each row can be identified using the Surrogate key PropertyID
c.REPAIR - Each row can be identified using the Surrogate key RepairID
d.EQUIPMENT - Each row can be identified using the Surrogate key EquipmentID
e.TRAINING- Each row can be identified using the Surrogate key TrainingID
f.SUB_PROPERTY - This is a subtype of PROPERTY
g. PROPERTY_SERVICE - Each row can be identified using the PropertyServiceID
h.EMPLOYEE - Each row can be identified using the EmployeeID
i.EQUIPMENT_USE - Weak type as each row cannot be identified using a single key
j.EMPLOYEE_TRAINING - Weak type as each row cannot be identified using a single key