M4 Lecture Notes: Logical Database Design, Constraints, and QA

This assignment presents M4 lecture notes focusing on logical database design (LDBD), exploring key concepts such as transforming Conceptual Database Design (CDBD) into LDBD models, specifying constraints (referential integrity and rules), and quality assurance/validation techniques. The notes cover model validation using methods like the Sharp’s method and discuss referential integrity options with case studies (Racecourse, Lost Baggage, QSALE). Rule specification is illustrated with the Widget Case. The notes also delve into traditional design reviews, defect density, and agile validation approaches. The document emphasizes the interrelatedness of QA techniques for both logical and conceptual data models, providing practical examples and exercises to enhance understanding. The lecture elaborates on the importance of semantics in selecting appropriate referential integrity options and how these selections can refine the understanding of the model being designed, even leading to invalidation if the model is not robust. The assignment provides examples for students to understand and apply LDBD principles.

M4 Lecture Notes: Logical DB Design
M4 Lecture Notes
Orientation
This module deals with the three major issues from the LDBD scope, namely:
a) Transforming CDBD into LDBD model as RM (trivial in case of IDEF1x, but peruse
some of the M4 readings to understand how elaborate it can be in case of UML etc.),
b) Specification of constraints (RI and rules), and
c) Quality Assurance or Validation techniques.
Note that order of topic introduction may be different from the order of a practical
LDBD process execution.
At the start of this module please review CB Chapter 17 LDBD (and Chapter 16
section 3 again). The scope of logical database design covers specification of
constraints (RI and rules) but their importance in practice warrants more elaborate
treatment then shown within the CB textbook. The M4 Lecture Notes will provide
those missing details of ‘How to’ specify (design) constraints.
On the other hand traditional use of Normalization as a LDDB design (technique)
option is largely abandoned in practice. The Normalization, see also CB Chapters 14
and 15 has its uses (limited as it may be), as a QA technique is treated later on.
This lecture also elaborates on interrelatedness of key techniques recommended for
use in quality assurance (for logical data base design and for the conceptual and
logical data models). Techniques are presented with case studies. Having in mind
that traditional QA technique was Design Review (feedback on models and
comments in discussions are our replacement for live interactive design review
sessions).
- Model Validation: see example Movie Mega-Theater
o Fitness test
o Simplified Sharp’s method
- Referential Integrity options: see examples Racecourse, Lost Baggage,
QSALE , and for self-review of RI option specification Maintenance View,
and Crimes case studies
- Rule specification: Widget Case
- Logical Database Design: (additional examples in readings)
- Translate a Logical Data Model to a RDBMS (Oracle): Used Books
Note that model validation technique using transaction paths (from CB 16.3) has
major relevancy with PDBD and will be covered in Module 5.
1. Data Model Validation
Traditional Design Reviews

M4 Lecture Notes: Logical DB Design
In a dialog between developers and involved in design and other available
developers a review of the design proceeds in a more or less defined manner with
an aim of improving a design by pointing out potential problems, hard to
understand elements, and suggesting alternatives. Typical measure used with
review is defect density (DD). The defect density is number of defects discovered
divided by number of distinct elements in a solution (usually expressed as a % of
defect). For example, if we assume that in a simple model for a Used Book case
study (below) the only error is using a complete instead of incomplete
generalization (to allow for possible extensions with return and other transactions
types) ad we ignore attributes the number of concepts/elements in a solution is 12
(six entities and six relationships), with one error as stated then DD is 1/12 i.e. 8%.
Typical DD for student work are 25-40% and for professional work products under
25%; in cases of DD over a threshold say 20% additional reviews may be scheduled.
CUSTOMER
Cust_Number
Address
Observation
Used Book Store- draft
l
BOOK
Book_Number
Title
Language
Publisher
Authors
Format
COPY
Book_Number (FK)
Copy _Number
Est_value
Desc ription
TR_type
BUY
Transaction_Number (FK)
Price_Paid
Extra_cost
SELL
Transaction_Number (FK)
Sold_f or
Total
ShippingData
alternative_shipping (FK)
TRANSACTION
Transaction_Number
Book_Number (FK)
Copy _Number (FK)
DateTR
TR_type
Cust_Number (FK)
Figure 1 Used Books Case

M4 Lecture Notes: Logical DB Design
Figure 2 Model validation process, alternatives and classification
2. Model Validation (Agile Validation)
Using elements of the Sharp’s Method for
In terms of its aim the Sharp’s method is closest to normalization, it addresses
attribute placement in any given table and does not depend on modeling notation.
Method is based on obtaining experts acceptance for systematic data samples
implied by the model- where any case of unaccepted data sample represent a defect
to be investigated. The method requires an expert with knowledge of domain
semantics but does not require knowledge of the normalization vocabulary. In our
experience the method can be sufficiently explained and its understanding tested in
one class hour. To illustrate how effective method is examples are simple but could
easily be modeled, let us try one from the Exercises.

M4 Lecture Notes: Logical DB Design
s emes ter
SemYear
validation illustration
only
enrolment
SemYear (FK)
Student Id (FK)
Section Number (FK)
cours e id (FK)
letter grade
ins tructor
ins tructor id
Name
SECTION
SemYear (FK)
Section Number
cours e id (FK)
ins tructor id (FK)
cours e
cours e id
Catalog Description
STUDENT
Student Id
Name
Enrolment
Student Course Semester Section Grade Model Expert
1 DB F07 S1 A Y Y
other DB F07 S1 A Y Y
1 other F07 S1 A Y Y
1 DB other S1 A Y Y
1 DB F07 other A Y N*
N* means- that student can’t enroll in the same semester in two sections of the same course. Can this rule be
represented structurally (in a model) or need to be enforced programmatically?
Solution is left as an exercise for students (to use data modeling more carefully or try
normalization).
The next example is a model that has to pass preliminary (feasibility) review.

M4 Lecture Notes: Logical DB Design
The model covers performance and records a schedule for a small town single
mega-theater operation. Initial set of interesting questions by the user (a sample
of four) is given as follows:
1) Daily gross for all movies shown
2) Daily schedule of showings by movie, theater, and starting time for each
showing
3) Each week, the movies with a bottom three minimal gross revenue total
4) Daily number of showings with capacity use under 50%.
The first step of the approach is a preliminary review by developers.
Model on top of the Figure 3 was considered feasible (developers claimed answers to
user queries reachable, i.e. access path shown on the model for each question). The
next step was performed by a user following sentence variation step from the
Sharp’s method in a manner shown above. The Expert column was populated by a
user. Note that figure 3, for convenience of showing table content clearly uses IDEF1X
notation. We selected a Showtime entity table for review, as follows:
Movie Time Theater Date Model Expert
m1 t1 r1 x1 Y Y
other t1 r1 x1 Y N*
m1 other r1 x1 Y Y
m1 t1 other x1 Y Y
m1 t1 r1 other Y Y
After validation by a user, developer’s job is to analyze N-case(s) and to fix model
imperfection(s). The N case above can be read as: different movies could be
shown at some starting time <t1> in the theater <r1> on the date <x1>, and that
is not allowed. Figure below contrast models before and after the improvement
triggered by validation.
Figure 3 Discussion Models:
MOVIE
m#
name
description
stars-r ating
rating- restiction
DATE
Date
Day
THEATER
theater #
capacity
After :
Before:
SHOWTIME
name ( FK)
theater # (FK)
Date (FK)
starting -time
tickets-sold
THEATER
theater #
capacity
DATE
Date
Day
MOVIE
name
description
stars-r ating
rating- restiction

M4 Lecture Notes: Logical DB Design
MOVIE_COPY
CopyN umber
name ( FK)
M_COPY
Copy-code
m# (FK)
theater # (FK)
Date (FK)
starting -time
tickets-sold
Copy-code (FK)
Without copies
model will have
to make Movie as FK only
MOVIE
name
description
stars-r ating
rating- restiction
DATE
Date
Day
THEATER
theater #
capacity
SHOWTIME
theater # (FK)
Date (FK)
starting -time
tickets-sold
CopyN umber (FK)
For additional examples see exercises and additional examples and exercises on a CD.

M4 Lecture Notes: Logical DB Design
2. Referential Integrities
In the M11 Lecture slides you have seen the syntax and available options, now we
will review several examples:
a) The Racecourse Case Study
w hy not use
SET NULL?
Discuss RI options as if there is such
database for betting in Las Vegas
PA RTICIPATION
Racetrack_Name (FK)
Date_Race (FK)
Event (FK)
Horse_ID (FK)
Jockey
Status
FinalPosition
Starting_Number
Handicap
RA CE_EV ENT
Event
Racetrack_Name (FK)
Date_Race
Of f ical_Observer
RA CETRA CK
Racetrack_Name
RTLocation
HORSE
Horse_ID
Horse_Name
Birthdate
SIR (FK)
DA ME (FK)
Ow ner
Stable_Location
As kingPrice_Last
The first case illustrates importance of semantics of the case for using appropriate
RI options. In the example above the default option for optional foreign keys SET
NULL is not useful as it will eliminate ancestors with their winning statistics thus
lowering the value of this database for those betting on horses progeny (on delete
of a parent horse). Similarly even if a race track is closed we cannot cascade a
delete risking loss of participation in formation of horse success in the race on that
racetrack (loosing valuable history data), again cascade option needs to be replace
with RESTRICT (NO ACTION) one on the path to participation from racetrack. Note
that on a direct line between a root and the entity to be preserved it usually suffice
to place a one RESTRICT (typically as close as possible in order to minimize impact

M4 Lecture Notes: Logical DB Design
on the rest of the DB)– in the above example between race_event and
participation. All other FK relationships can be set to CASCADE.
b) Lost Baggage Case Study
The point of this case is to illustrate a need to protect future instances with another
layer of RESTRICT (in red) when at the time of earliest possible deletes on the
parent (root) lower level descendants (to be protected) may not yet exists. A good
example is lost baggage that is not yet reported at the time of a canceled flight.
contantain /
desc ribed content
checked forac company ing
Exam ple - Sele ction of
Re fer ential Inter gr ity Options
Lost_Indicator
REPORTED_LOST_ITEM
Bagage_Bar_Code_N umber (FK)
Item_Number
Item_Desc ription
LOST_BAGAGE
Bagage_Bar_Code_N umber (FK)
Ask ing_Value
Status_Not_Found
ACTUAL_FLIGH T
FLight_N umber
Flight_Datetime
Airc raf t_Ass igned
Etc
BAGAGE
Bagage_Bar_Code_N umber
Tic ket_Number (FK)
Lost_Indicator
FLight_N umber (FK)
Flight_Datetime (FK)
RESER VATI ON
Tic ket_Number
Pas senger_N ame
Seat_Number
Pric e_Category

M4 Lecture Notes: Logical DB Design
Now a rather complete example with discussion showing QA value of selecting RI
options:
c) QSALE Case Study with discussion
This example will be used to illustrate RI options and also to highlight the fact that
selecting RI options can lead to refined understanding of the situations being
modeled and invalidation if the model as given. This case illustrates use of RI option
selection as not only a step in LDBB modeling but also in validation of a model: if we
can place all reasonable options, without changing the model, we gain additional
confidence in that model (not that we can say the model is valid, but we can say
that we were not able to invalidate it).
Data Model illust rating QSALE,
the SQL Play book databs e
SUPLIER
SPLN O: N UMBER (3)
SUPLNAME: VAR CH AR2(30)
EMPLOY EE
EMPN O: VAR CHAR 2(15)
EMPFN AME: VARC HAR 2(15)
EMPSALARY : N UMBER(9,2)
DEPTNAME: VAR CH AR2(10)
BOSS: VAR CHAR 2(15)
DELIVER Y
DELNO: NU MBER(3)
DELQTY : N UMBER (9)
ITEMN AME: VARC HAR 2(30)
DEPTNAME: VAR CH AR2(10)
SPLN O: N UMBER (3)
DEPAR TMENT
DEPTNAME: VAR CH AR2(10)
EMPN O: VAR CHAR 2(15)
DEPTFLOOR : N UMBER(1)
DEPTPHONE: VARC HAR 2(15)
QI TEM
ITEMN AME: VARC HAR 2(30)
ITEMTYPE: VARC HAR 2(8)
ITEMC OLOR : VAR CH AR2(10)
QSALE
SALEN O: N UMBER (4)
SALEQTY : NUMBER(9)
dept name: VARC HAR 2(10)
itemname: VARC HAR 2(30)
Relationship: Employee to Employee: (BOSS references EMPNO)
Recommended Referential Integrity Rule: SET NULL on Parent Delete
Reason:
If an employee were deleted that was in a boss position for multiple employees then you would
not want to cascade delete to the other employees. You would simply want to set the other
values to null so a new boss could be assigned.
Note here a problem in nullifying for all affected records (there is redundancy in this DB).
Relationship: Department to Employee

M4 Lecture Notes: Logical DB Design
Recommended Referential Integrity Rule: let us try the SET NULL on Parent Delete of
DEPTNAME here first.
Reason:
If a department is removed thus you would not want to remove all employees in department just
have them available for reassignment.
Unfortunately, the QSALE DB model as shown requires foreign key here but neither
RESSTRICT nor CASCADE are viable options (note also that we continue with the policy of
never using a DEFAULT such as SYSTEM etc. here). So we just found a subtle defect in a
model – change the relationship between DEPARTMENT and EMPLOYEE into optional one
and then use the SET NULL option!
Relationship: Employee to Department
Recommended Referential Integrity Rule: RESTRICT on Parent Delete
Reason:
Obviously if an employee (the boss is here a manger which is redundant, not in 3NF) was deleted
you would not want to remove an entire department with a cascade. According to the data,
employee sales are not tracked so thus an employee may be deleted in principle but not for this
reason; it is best to RESTRICT until you reassign them or better yet relax the relationship to
optional and SET NULL.
Relationship: Department to Delivery
Recommended Referential Integrity Rule: CACADE on Parent Delete
Reason:
If a department does not exist after a delete, then there would most likely not be a need to keep a
record of deliveries to that department (and you cannot set to NULL a mandatory relationship)
and you would not want to set the department to NULL even if is not a mandatory relationship
(i.e. it must be a department for a delivery to be entered in a database) because that would only
create confusion in reporting as the past deliveries could not be reassigned to another
department. NOTE: If you do need to track deliveries longer than the life of department than a
RESTRICT could be used.
Relationship: Department to Sale (QSALE)
Recommended Referential Integrity Rule: RESTRICT on Parent Delete
Reason:
If a department is to be removed from the database and sale records are still needed CASCADE
cannot be used as sales should not be deleted. The core of the system is sales from the business,
so recommend the RESTRICT.
Relationship: QITEM to QSALE
Recommended Referential Integrity Rule: RESTRICT Parent Delete
Reason:
If item were removed from the system then you would not want to cascade your sales since you
do need that information. Thus by removing item you, in a way, invalidate the sale record all
together, but sales must be saved. Thus by restricting the removal you would keep the integrity
intact and aggregate reporting available.

M4 Lecture Notes: Logical DB Design
Relationship: QITEM to DELIVERY
Recommended Referential Integrity Rule: RESTRICT Parent Delete
Reason:
If Item is removed you would not want to cascade delete all deliveries. You would most likely
need that for inventory and comparisons to sales. If you deleted and item you would not want to
remove all delivery records. NOTE: If the item had no deliveries (and sales and you do not need
its inventory) you can still delete the Item.
Relationship: SUPLIER to DELIVERY
Recommended Referential Integrity Rule: RESTRICT on Parent Delete
Reason:
If a supplier is removed from the system you may, based on information given, need to still show
deliveries that were made to departments for inventory reasons and for comparisons to sales, this
will also work for your advantage if supplier is needed for purposes of billing or issues with
deliveries (alternate but frequent general reasons you may want to restrict this relationship as
well).
As RI option specification is a standard problem in practice and also figures as part of the final
examination content it will be of use to do more independent practice, so here are some
additional case studies (Maintenance View, and Crimes) for self-review.
Review RI options
MECHANIC
employeeNumber
MECHANIC_FOR_AC
aircraftNumber (FK)
employeeNumber (FK)
ENGINE
engineNumber
aircraftNumber (FK)
manufacturer_code (FK)
MANUFACTURER
manufacturer_code
AIRCRAFT_TYPE
type_code
manufacturer_code (FK)
SEAT
aircraftNum ber (FK)
s eat
AIRCRAFT
aircraftNumber
type_code (FK)
model_Number (FK)
MAINTENANCE_RECORD
recordNumber
owningRecord (FK)
employeeNumber (FK)
MECHNANIC_FOR_AM
model_Number (FK)
employeeNumber (FK)
type_code (FK)
AIRCARAFT_MODEL
type_code (FK)
model_Number

M4 Lecture Notes: Logical DB Design
CRIMINALS
criminals _id: NUMBER(6)
name: VARCHAR2(24)
addres s: VARCHAR2(256)
phone: VARCHAR2(14)
violent_offender: NUMBER(1)
probation: NUMBER(1)
ALIASSES
crim inals _id: NUMBER(6)
alias: VARCHAR2(24)
Period_Used_end: DATE
INDIV_CRIMES
crim e_id: NUMBER()6
crim inals _id: NUMBER(6)
crim e_clas s: VARCHAR2(32)
date_charged: DATE
appeal_s tatus: NUMBER(2)
fine: NUMBER(8,2)
court_fee: NUMBER(8,2)
payment_date: DATE
amount_paid: NUMBER(8,2)
charge_s tatus: NUMBER(6)
OFFICERS
officerid: NUMBER(6)
name: VARCHAR2(24)
officer_status : VARCHAR2(10)
ARRESTING_OFFICERS
crim e_id: NUMBER()6
officerid: NUMBER(6)
SENTENCES
crime_id: NUMBER()6
type: NUMBER
number_of_vialoations : NUMBER(6)
Arres ts really
RI (options :C/R)