Database Design Methodology: Conceptual Database Design

Step 1: Build Local Conceptual Data Model for each User View

1.1Identify entity types

Main entities in the user’s view

  • URS (Event Table), Object Class diagrams, System Flow Charts, DFD
  • Synonyms

Document entity types

Record the names and description of entities

1.2Identify relationships

Relationships that exist between the entities

Branch has staff

Staff Manages Property

Renter views the Property

Determine type of relationships

One-to-One

One-to-Many

Many-to-Many

  • Binary
  • Complex
  • Recursive

Document relationship types

E-R modeling (First cut)

1.3Identify and associate attributes with entity or relationship types

Nouns or phrases

Simple or composite attributes

Derived or calculated attribute

Number of staff that works at a Branch

Age, Total monthly salary, Number of properties

Document attributes

Attribute names and descriptions

Data type and length

Default values

Validation rules

Derived or Multi-valued attribute

1.4Determine attribute domains

Pool of values from which one or more attributes draw their values

Valid branch numbers: 11-99

Marks:0-99

Telephone number: a 13 digit string

Gender: M or F

Document attribute domains

1.5Determine candidate and primary key attributes

Identify candidate keys

Choose primary key

  • Minimal set of attributes
  • Least likely to change
  • Fewest characters
  • Smallest minimum
  • Easiest to use

Remaining will be alternate keys

1.6Draw Entity-Relationship Diagram

ERD is conceptual representation of a user view

1.7Review Local conceptual data model with user

Ensure the ERD is a ‘true’ representation of the user’s view.

  • Describing transactions
  • Transaction pathways

Constraints

Database Integrity Constraints

Entity Integrity

Attribute Domain Constraints

Referential Integrity

Enterprise Constraints

Data Model Types

Hierarchical

Network

Relational

Object-oriented

Database Normalization

1NF, 2NF, 3 NF

Logical Database Design

Outline

  • Views- Local logical model
  • Removing non-compatible features
  • Validate the Model- Normalization
  • Add Integrity Constraints
  • Review the Design with users

Logical database Design:

  • Logical database design
  • based on a specific data model (e.g. relational)
  • Independent of a particular DBMS and other physical considerations
  • Logical structure of the database
  • Designing the relations (DBDL)

Input: Local Conceptual data model (ERD with attributes & P.Keys)

Step 2.1 Remove non-compatible features

  • *.* binary
  • *.* Recursive
  • Complex
  • Multi-valued attributes

Step 2.2 Derive relations for local logical data model (Use DBDL)

  • Strong entity (Client, Staff,…)
  • Weak entity (Preferences…)
  • 1:* binary relationship
  • Staff- Client
  • 1:1 binary relationships
  • Mandatory participation on both sides
  • Client states Preferences – merge
  • Mandatory participation on one side
  • Client states preferences
  • Optional participation on both sides
  • Staff uses car
  • *.* binary relations
  • Client views properties
  • Complex relationship types
  • Multi-valued attributes

Example: Relations for the Staff view of DreamHome

Step 2.3 Validate relations using Normalization

  • To avoid data duplication
  • For maximum processing efficiency
  • Anomalies
  • Convert the relations into 3rd Normal Form.

Step 2.4 Validate relations against user transactions

Step 2.5 Define Integrity constraints

  • Required data (Not null- Position)
  • Attribute domain constraints
  • Entity Integrity
  • Enterprise Constraints
  • Referential Integrity
  • Case 1: Insert tuple into child relation
  • Staff- Propertyforrent; Matching
  • Case 2: Delete tuple from Child relation
  • R.I. Unaffected. Propertyforrent
  • Case 3: Update foreign key of child
  • Existing value of staff/ Null
  • Case 4: Inserting a tuple into parent
  • Doesn’t affect R.I. (Staff)
  • Case 5: Deleting tuple from parent (Staff)
  • Several Strategies
  • No Action (Prevent Deletion)
  • Cascade (Delete all related tuples)
  • Set Null (F.Keys Null)
  • Set Default (Staff_Num->Managers)
  • No Check (Do Nothing)
  • Case 6: Update P.Key of parent tuple (Staff)
  • R.I. will be lost
  • Use above strategies

Example: R.I. constraints for the relations in the Staff view of DreamHome

Step 2.6 Review Local Logical Data Model with user

3. Build and Validate Global Logical data Model

3.1 Merge local logical data models into global model