Database Design Methodologies

(1)

Database Design Methodology - 1

Database Design

Methodologies

Critical Success Factors in Database Design

o Work interactively with the users as much as possible.

o Follow a structured methodology throughout the data modeling process.

o Employ a data-driven approach.

o Incorporate structural and integrity considerations into the data models.

o Combine conceptualization, normalization, and transaction validation techniques into the data modeling methodology.

Critical Success Factors in Database Design

o Use diagrams to represent as much of the data models as possible.

o Good documentation of additional data semantics. o Build a data dictionary to supplement the data

model diagrams.

o Be willing to repeat steps.

Database Design Methodology

3 main phases:

o Conceptual database design

Construct a model of the information used, independent of all physical considerations. o Logical database design

Construct a model of the information used, based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations. o Physical database design

(2)

Methodology Overview

Step 1 Build local conceptual data model for each user view

Step 2 Build and Validate Local Logical Data Model Step 3 Build and Validate Global Logical Data Model Step 4 Translate Global Logical Data Model for Target

DBMS

Step 5 Design Physical Representation

Step 6 Design and Implement Security Mechanisms Step 7 Monitor and Tune the Operational System

Methodology Overview - Conceptual Database Design

Step 1 Build local conceptual data model for each user

view

Step 1.1 Identify entity types Step 1.2 Identify relationship types

Step 1.3 Identify and associate attributes with entity or relationship types

Step 1.4 Determine attribute domains

Step 1.5 Determine candidate and primary key attributes Step 1.6 Specialize/generalize entity types (optional step) Step 1.7 Draw Entity–Relationship diagram

Step 1.8 Review Local Conceptual Data Model with User

Methodology Overview –

Logical Database Design for Relational Model

Step 2 Build and Validate Local Logical Data Model Step 2.1 Map Local Conceptual Data Model to Local

Logical Data Model

Step 2.2 Derive Relations from Local Logical Data Model Step 2.3 Validate Model using Normalization

Step 2.4 Validate Model against User Transactions Step 2.5 Draw Entity-Relationship Diagram Step 2.6 Define Integrity Constraints

Step 2.7 Review Local Logical Data Model with User

Methodology Overview –

Logical Database Design for Relational Model

Step 3 Build and Validate Global Logical Data Model Step 3.1 Merge Local Logical Data Models into Global

Model

Step 3.2 Validate Global Logical Data Model Step 3.3 Check for Future Growth

Step 3.4 Draw Final Entity-Relationship Diagram Step 3.5 Review Global Logical Data Model with Users

(3)

Methodology Overview –

Physical Database Design for Relational Databases

Step 4 Translate Global Logical Data Model for Target

DBMS

Step 4.1 Design Base Relations for Target DBMS Step 4.2 Design Integrity Rules for Target DBMS Step 5 Design Physical Representation Step 5.1 Analyze Transactions

Step 5.2 Choose File Organization Step 5.3 Choose Secondary Indexes

Step 5.4 Consider the Introduction of Controlled Redundancy

Step 5.5 Estimate Disk Space _{Database Design Methodology - 10}

Methodology Overview –

Physical Database Design for Relational Databases

Step 6 Design and Implement Security Mechanisms Step 6.1 Design User Views

Step 6.2 Design Access Rules

Step 7 Monitor and Tune the Operational System

Step 1.1 Identify entity types

Branch Advert Staff Newspaper Supervisor Interview Secretary Client Property_for_Rent Lease_Agreement Private_Owner Inspection Business_Owner

(4)

Step 1.2 Identify relationship types

o Branch Has Staff Relationship

o Staff Manages Property_for_Rent relationship

Step 1.2 Identify relationship types

o Property_for_Rent DescribedIn Advert relationship

o Client Views Property_for_Rent relationship

Sk

et

ch

o

f S

up

er

vi

so

r's

L

oc

al

C

on

ce

pt

ua

l D

at

a

M

od

el

Step 1.3 Identify and associate attributes with

entity or relationship types

(5)

Step 1.3 Identify and associate attributes with

entity or relationship types

Step 1.4 Determine attribute domains

o For attributes in the Supervisor’s local conceptual data model of the DreamHome company.

(e.g. Domain of Branch_No attribute of Branch entity includes a three-character string, with values ranging from B1 to B99).

Step 1.5 Determine candidate and primary key

(6)

Step 1.6 Specialize / Generalize Entity Types (Optional Step)

Additional Example

Step 1.6 Specialize / Generalize Entity Types (Optional Step)

Additional Example

St

ep

1 .7

D

ra

w

S

up

er

vi

so

r's

L

oc

al

C

on

ce

pt

ua

l D

at

a

M

od

el

Step 2.1 Map Local Conceptual Data Model to Local Logical

Data Model

To refine the local conceptual data model to remove undesirable features and to map this model to a local logical data model:

(a) Remove M:N relationships. (b) Remove complex relationships. (c) Remove recursive relationships. (d) Remove relationships with attributes. (e) Remove multi-valued attributes. (f) Re-examine 1:1 relationships. (g) Remove redundant relationships.

Removing data structures that are difficult to implement in relational databases.

(7)

Removing Advertises M:N Relationship

Removing Leases Complex Relationship

Removing Supervises Recursive Relationship

Removing WorksAt Relationship with

Hours_Worked Attribute

(8)

Removing Tel_No Multi-valued Attribute

Non-Redundant Relationships

A relationship is redundant if the same information can be obtained from other relationships.

An Example Logical Data Model

Supervisor's local

logical data model

(Version 1)

(9)

Step 2.2 Derive Relations From Local Logical Data Model

o Strong Entity Types: create a relation that includes all simple attributes.

Eg. Staff(Staff_No, FName, LName, Street, City, Postcode, Position, Sex, Salary)

o Week Entity Types: create a relation that includes all simple attributes. In addition, include as a foreign key the primary key of the owner entity.

Eg. Next-of-Kin (Staff_No, NName, Address, Tel_No, Relationship), Foreign key Staff_No references Staff(Staff_No)

Step 2.2 Derive Relations From Local Logical Data Model

o 1:1 Relationship Types: post a copy of the primary key attribute(s) of entity E1 into the E2 relation, to act as a foreign key. (The indentification of E1 and E2 depends on the participation constraints)

Eg. - Staff(Staff_No, Fname, Lname, ..)

- Branch (Branch_No, Address, .., Manager_No), Foreign Key Manager_No references Staff(Staff_No)

Step 2.2 Derive Relations From Local Logical Data Model

o 1:M Relationship Types of E1 to E2: post a copy of the primary key attribute(s) of entity E1 into the E2 relation, to act as a foreign key.

Eg. – Branch (Branch_No, Address, ..) – Staff (Staff_No, Fname, .. Branch_No),

Foreign Key Branch_No references Branch (Branch_No)

Step 2.2 Derive Relations From Local Logical Data Model

o Superclass/subclass relationships: Example 1:

All_Property(Property_No, Address, Type, Rent, Price) Example 2:

Property_For_Rent(Property_No, Address, Type, Rent) Property_For_Sale(Property_No, Address, Type, Price) Example 3:

(10)

Step 2.3 Validate Model using Normalization

To ensure that each relation derived from the logical data model is in at least Boyce-Codd Nornal Form. Issues:

o A normalized design organizes the data according to its functional dependencies => lies somewhere between conceptual and physical design

o The logical design may not be the final design. Eg. some normalized relations are denormalized in the physical design.

o Normalization forces us to understand completely each attribute.

o A normalized design is free of update anomalies, and the DB can be easily extended.

Step 2.4 Validate Model Against User Transactions

To ensure that the logical data model supports the

transactions that are required by the user view. Example transactions

(a) Insert details for new members of staff.

(b) Delete details of a member of staff, given the staff number.

Supervisor's local

logical data model

displaying the

transactions supported

Step 2.5 Draw

Supervisor's local

logical data model

(Final Version)

(11)

Step 3.1 Merge Local Logical Data Models into

Global Models

(a) Review the names of entities and their primary keys. (b) Review the names of relationships.

(c) Merge entities from the local views.

(d) Include (without merging) entities unique to each local view. (e) Merge relationships from the local views.

(f) Include (without merging) relationships unique to each local view.

(g) Check for missing entities and relationships. (h) Check foreign keys.

(i) Check Integrity Constraints. (j) Draw the global logical data model. (k) Update the documentation.

Comparison of entities and their primary keys in

Supervisor’s and Manager’s views

Comparison of relationships in Supervisor’s and

(12)

Merging the Advert entities from Supervisor's and

Manager's views

Step 3.1 Merge Local Logical Data Models into

Global Models

Step 3.4 Draw Global logical

data model of DreamHome

case study

Physical Database Design (Steps 4-7)

o Sources of information for the physical design process includes global logical data model and documentation that describes model.

o Logical database design is concerned with the what, physical database design is concerned with the how. o Physical Database Design is the process of producing

a description of the implementation of the database on secondary storage.

o Describes the storage structures and access methods used to achieve efficient access to the data.

(13)

Step 4 Translate Global Logical Data Model for Target DBMS

Step 5 Design Physical Representation

o Typical Disk Configuration

Step 5.1 Analyze transactions

Example - Sample Transactions

(A) Insert details for a new member of staff, given the branch address.

(B) List rental properties handled by each staff member at a given branch address.

(C) Assign a rental property to a member of staff, checking that a staff member does not manage more than 10 properties already.

ER Model for Sample Transactions showing

Expected Occurrences

(14)

Transaction Usage Map for Sample Transactions

Analysis of Selected Transaction A

(15)

Step 5.4 Consider introducing controlled redundancy

Example 1:

Step 5.4 Consider introducing controlled redundancy

Example 2: Combined renter and interview relations

Step 5.4 Consider introducing controlled redundancy

Example 2: Duplicating LName attribute in the

Property_for_Rent relation

Step 5.5 Estimate disk space requirements

(16)

Step 5.5 Estimate disk space requirements

o Comparison of Space Requirements for Property_for_Rent Relation