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. 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 PROPERTY 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
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 contraint), 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 (PAY_TYPE)
7
The schema files for both aproaches are provided as part of this solution.
To capture the full run of your schema file you must insert a SPOOL and ECHO on 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
– Week 4 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