LOGICAL DATABASE DESIGN

1
LOGICAL DATABASE DESIGN
2

• Starting Point --gt Local Conceptual Models
• Database design methodology conceptual, logical
  and physical database design.
• Logical database Design
• The process of constructing a model of
  information used in an enterprise based on a
  specific data model, but independent of a
  particular DBMS or other physical considerations.
• Step2 Build and validate local logical data
  model for each user view.
• Step3 Build and validate global logical data
  model

3
Step2 Build and Validate Local Logical Data
model for each user view.

• Step2.1 Map local conceptual data model to local
  logical data model.
• Step2.2 Derive relations from logical data
  model.
• Step2.3 Validate model using Normalization.
• Step2.4 Validate model against user transactions
• Step2.5 Draw E-R Diagram.
• Step2.6 Define integrity constraints
• Step2.7 Review local logical data model with
  user.

4
Step2.1 Map local conceptual data model to local
logical data model.

• Objectives
• Remove MN relationships.
• Remove complex relationships.
• Remove recursive relationships
• Remove relationships with attributes
• Remove multi-valued attributes
• Re-examine 11 relationships
• Remove redundant relationships.

5
Step2.2 Derive relations from logical data model

• Objective To derive relations from the local
  logical data model
• Relationship
• primary key/ foreign key mechanism
• identify the parent and child entities
• Strong entity types
• Staff (Staff_No, Fname, Lname, Street, City,
  Postcode, Position, Sex, Salary)
• PrimaryKey Staff_No
• Weak entity types
• include a foreign key - - - of the owner entity
• the primary key of a weak entity is partially or
  fully derived from the owner entity.
• Next-of-Kin (Staff_No,Name, Address, Tel_No,
  Relationship)
• Primary Key Staff_No, Name
• Foreign Key Staff_No, references Staff(Staff_No)

6
Step2.3 Validate model using Normalization

• Objective To validate a local logical data model
  using the technique of normalization.
• Organizes the data according to functional
  dependencies
• A normalized relationship is robust and free of
  update anomalies
• Normalization provides a flexible database design
  that can easily be extended
• First Normal Form (1NF), removes repeating
  relationship
• Second Normal Form (2NF), removes partial
  dependencies on the primary key
• Third Normal Form (3NF), removes transitive
  dependencies on the primary key

7
Step2.4 Validate model against user transactions

• Objective
• To ensure that the local logical data model
  supports the transactions that are required by
  the user views.
• ER diagram, data dictionary, primary key/
  foreign key links shown in the relations
• Likely that we have omitted an entity, a
  relationship or an attribute.

8
Staff_No
Property_No
M
1
Property
Manages
Staff

• (a) Insert details for a new member of staff
• First check that the new staff no does not
  already exist --gt Prohibit the insertion
  otherwise insert the new staff details..
• (b) Delete details of a given staff no
• Search for the given staff no, if not found --gt
  user error, otherwise delete the tuple.

9
Step2.5 Draw E-R Diagram

• Objective
• To identify the final ER diagram that is a local
  logical representation of the given data given in
  a users view of the enterprise.

10
Step2.6 Define integrity constraints

• Objective to define the entity constraints given
  in a users view of the enterprise.
• Five types of integrity constraints
• Required data
• Attribute domain constraints
• Entity integrity
• Referential Integrity
• Enterprise constraints

11

• Required data
• Not allowed to hold nulls
• e.g. every member of staff must have an
  associated position
• Attribute Domain Constraints
• e.g. M or F for Gender
• Entity Integrity
• Primary key of entity cannot hold nulls
• Referential Integrity
• The foreign key contains a value that matches
  with an existing occurrence in the parent table.

12

• Case1 Insert occurrence into child relation
  (Property)
• To ensure referential integrity Staff_No --
  property occurrence.
• Case2 Delete occurrence from child relation
• No problem as referential integrity is not
  affected.
• Case3 Update foreign key of a child occurrence
• Ensure referential integrity
• Case4 Insert occurrence into parent table
• Does not cause a problem for referential
  integrity . Parent without any children . Staff
  member without a property to manage.

13

• Case5 Delete occurrence from Parent relation
  (Staff)
• referential integrity is lost if there exists a
  child's occurrence.
• Several strategies
• NO ACTION Prevent deletion from parent relation,
  if any child reference.
• CASCADE When the parent occurrence is deleted,
  delete any child occurrences (not wise)
• SET NULL When the parent occurrence is deleted
  the child occurrences are set to NULL
• SET DEFAULT The foreign key values in all child
  occurrences should be set to default values.
• Eg Handled by another (default) member of staff
  -- Manager
• NO CHECK When a parent occurrence is deleted do
  nothing to ensure referential integrity exists.

14

• Case6 Update the primary key of parent
  occurrences (Staff)
• If the primary key value of the parent occurrence
  is updated, referential integrity is lost if
  there exists a child occurrence referencing the
  old primary key value.
• Enterprise Constraints
• Called business rules.
• Eg No member of staff can manage more than 10
  properties at a time
• Document all Integrity Constraints

15
Step2.6 Review local logical data model with user

• Objective
• To ensure that the logical data model is a true
  representation of the users view.
• Relationship between the logical data model and
  data flow diagram
• Each datastore should represent a whole number of
  entity types
• Attributes on data flows should belong to entity
  types

16
Step3 Build and Validate Global Logical data
Model

• Objective
• To combine the individual data logical models
  into a single global logical data model that can
  be used to represent the part of the enterprise
  that we are interested in modeling.
• Step3.3 Merge local logical data models into
  global model
• Step3.2 Validate global logical data model
• Step3.3 Check for future growth
• Step3.4 Draw final ER diagram
• Step3.5 Review global logical data model with
  users.

17
Step3.1 Merge local logical data models into
global model

• Objective
• To merge the individual local logical data models
  into a single global logical data model of the
  enterprise.
• Typical tasks
• Review the names of entities and their primary
  keys
• Review the names of relationships
• Merge entities form local views
• Include (without merging) entities unique to each
  user view.
• Merge relationships from the local views
• Include (without merging) entities unique to each
  local view.
• Check for missing entities or relationships
• Check foreign keys
• Check integrity constraints
• Draw the global logical data model
• Update the documentation

18
Merging
Staff (Staff_No, Name, Position, Sex, Salary,
Branch_No) Primary Key Staff_No Foreign Key
Branch_No references Branch(Branch_No) Staff
(Staff_No, Fname, Lname, Address,
Branch_No) Primary Key Staff_No Foreign Key
Branch_No references Branch(Branch_No) Staff
(Staff_No, Fname, Lname, Address, Position, Sex,
Salary, Branch_No) Primary Key Staff_No Foreign
Key Branch_No references Branch(Branch_No)
View1
View2
Global View
19

• Merge entities from the local views
• Merge entities with the same name and primary key
• Merge entities with the same name using different
  primary keys
• Merge entities with different names using
  different primary keys

20
Merging equivalent entities using different
primary keys
View1 Staff (Staff_No, Name, Position, Sex,
Salary, Branch_No) Primary Key Name Alternate
Key Staff_No Foreign Key Branch_No references
Branch(Branch_No) View2 Staff (Staff_No, Fname,
Lname, Address, Branch_No) Primary Key
Staff_No Alternate Keys Fname, LName Foreign
Key Branch_No references Branch(Branch_No)
21

• Step3.2 Validate Global Logical Data Model
• Step3.3 Check for future growth
• Step3.4 Draw final ER diagram
• Step3.5 Review global logical data model with
  users.