Logical Databases Design Methodology

1
Chapter 11

• Logical Databases Design Methodology
• Worked Example
• Transparencies

2
Removing Client Views Property_for_Rent (MN)
relationship
2
3
Removing recursive Supervises and Supports
relationships
3
4
Supervisor's local logical data model (Version 1)
4
5
Supervisor's local logical data model displaying
the transactions supported
5
6
Step 2.5 Draw Supervisor's local logical data
model (Final Version)
6
7
Comparison of entities and their primary keys in
Supervisors and Managers views
7
8
Comparison of relationships in Supervisors and
Managers views
8
9
Merging the Staff entities from Supervisor's and
Manager's views
9
10
Merging the Advert entities from Supervisor's and
Manager's views
10
11
Step 3.4 Draw Global logical data model of
DreamHome case study
11
12
Chapter 11 - Objectives

• How to use the logical database design
  methodology, described in Chapter 8.
• How to use this methodology to create a logical
  database design for the DreamHome case study.

12
13
Step 2.1 Map local conceptual data model to
local logical data model

• (1) Remove MN relationships.
• (2) Remove complex relationships.
• (3) Remove recursive relationships.
• (4) Remove relationships with attributes.
• (5) Remove multi-valued attributes
• (6) Re-examine 11 relationships.
• (7) Remove redundant relationships.

13
14
Step 2.2 Derive relations from local logical
data model

• To represent entities and relationships described
  in Supervisors view of DreamHome.
• For example, the composition of Viewing relation
  is
• Viewing (Property_No, Client_No, Date_View,
  Comments)
• Primary Key Property_No, Client_No, Date_View
• Foreign Key Property_No references
  Property_for_Rent(Property_No)
• Foreign Key Client_No references
  Client(Client_No)

14
15
Step 2.3 Validate model using normalization

• First Normal Form (1NF) - removes repeating
  groups
• Second Normal Form (2NF) - removes partial
  dependencies on the primary key
• Third Normal Form (3NF) - removes transitive
  dependencies on the primary key
• BoyceCodd Normal Form (BCNF) - removes remaining
  anomalies from all functional dependencies.

15
16
Step 2.4 Validate model against user
transactions

• We can ensure that the information (entities,
  relationships, and attributes) required by each
  transaction is supported by the model by
  providing a description of how we may achieve the
  transaction.
• We can diagrammatically representing each
  transaction on the Supervisors local logical
  data model.

16
17
Step 2.6 Define integrity constraints

• Required data
• Attribute domain constraints
• Entity integrity
• Referential integrity
• Enterprise constraints

17
18
Step 3.1 Merge local logical data models into
global model

• (1) Review the names of entities and their
  primary keys.
• (2) Review the names of relationships.
• (3) Merge entities from the local views.
• (4) Include (without merging) entities unique
  to each local view.
• (5) Merge relationships from the local views.
• (6) Include (without merging) relationships
  unique to each local view.
• (7) Check for missing entities and
  relationships.
• (8) Check foreign keys.
• (9) Check integrity constraints.
• (10) Draw the global logical data model.
• (11) Update the documentation.

18
19
Step 3.2 Validate global logical data model

• Although validated the Supervisors and Managers
  data models before building global logical data
  model - we may have introduced errors during the
  process of merging the data models.
• Very important to validate the global logical
  data model using the rules of normalization and
  against the required transactions.

19
20
Step 3.3 Check for future growth

• Important global logical data model is capable of
  being extended at a later stage as the users
  requirements change.

20
21
Step 3.5 Review global logical data model with
users

• Important to review global logical data model
  with users of each view. If the model contains
  any errors we must repeat the appropriate step(s)
  in the methodology.
• Process of review is repeated until all the users
  are satisfied with the global logical data model.
• When data model is signed off by the users, we
  proceed to physical design of the database.

21