Logical Modelling Sample Solutions
Customer Orders
1
Rental System
Note:
● Surrogate keys of MAINT_NO and DAM_NO added to MAINTENANCE and
DAMAGE
● Since the model is to be implemented in Oracle date and time can be stored in a
single DATE type attribute
Did you arrive at this model?
Remember that every model you create via top down design must have the final entities
checked to ensure they are in 3NF with no insert, update or delete anomalies. If you apply
this to MAINTENANCE you will see:
MAINTENANCE (maint_no, maint_date, maint_type, maint_cost, owner_no, prop_no)
prop_no -> owner_no is a transitive dependency so this entity is not in 3NF
The 3NF form would be:
MAINTENANCE (maint_no, maint_date, maint_type, maint_cost, prop_no)
PROPERTY (prop_no, owner_no)
2
The relationship between MAINTENANCE and OWNER is a redundant relationship (see
Coronel & Morris 5-4d (ed 12) 5.4.4 (ed 11)). Sometimes such relationships are deliberately
left in the design as they simplify the design, here it would more easily allow a connection
between maintenance and the owner who should be charged for the maintenance. Deciding
to maintain such a relationship, involves overheads (here a redundant owner_no in
Maintenance) and needs to be a clear and documented conscious decision.
The model without this relationship would be:
This model can be improved by connecting DAMAGE and PAYMENT – in this way you will
be able to connect a given payment with a given act of damage. Here we will assume a
business rule that says a damage if billed to a tenant must be paid in full in one payment.
3
An improved model to track payment for a damage:
Part of the case study indicates that the payment type must be recorded as Rent, Bond or
Damage. This should be enforced in one of two ways:
● by adding a CONSTRAINT (see Alexandria on how to add such a constraint), or
● by using a LOOKUP table.
A constraint becomes part of the database structure, if the user wishes to add a new option,
say ‘F’ for future rent payments then the structure must be altered. On the other hand if a
lookup table is used all that needs to happen is a new row needs to be added to the lookup
table with the appropriate values.
4
Controlling pay_type via a CONSTRAINT (CHK_PAY_TYPE)
5
6
Controlling pay_type via a LOOKUP TABLE (PAYTYPE)
7
The schema files for both approaches are provided as part of this solution.
To capture the full run of your schema file you must insert a SPOOL and ECHO on
command at the top of your file, and a SPOOL OFF and ECHO off at the end of your script.
For example – added to the top of your script:
— Capture run of script in file called rental-run.txt
set echo on
SPOOL rental-run.txt
— Database Teaching Team
— Logical Rental Model Schema script file
— Schema file includes example constraint to control pay_type
— Added drop commands for sequences
here add drop sequence commands, sql developer does not add these
….. the SQL Developer Generated Script goes here ……
—————————————————–
set echo off
SPOOL off
Please check the sample solution schema files to see this in action.
8