Analyse Different Approaches Database Design

Analyse Different Approaches Database DesignDatabase Design – Design Me

 

thodology

Suneth Fernando

Objectives

Discuss the general process and goals of database design

Define user views and explain their function

Define database design language and use it to document database designs

Create an entity-relationship diagram to visually represent a database design

Present a methodology for database design at the information level and view examples illustrating

this methodology

Objectives

Explain the physical-level design process

Examine some alternative approaches to entityrelationship diagrams

Discuss top-down and bottom-up approaches to database design and examine the advantages and disadvantages of both methods

Use a survey form to obtain information from users prior to beginning the database design

process

Objectives

Review existing documents to obtain information from users prior to beginning the database design process

Discuss special issues related to implementing one-to-one relationships and many-to-many

relationships involving more than two entities

Discuss entity subtypes and their relationships to nulls

Learn how to avoid potential problems when merging third normal form relations

Database Design

User Views

  • Requirements necessary to support a particular user’s operations

Information-level Design Methodology (Top-Down)

  • Represent user view as collection of tables
  • Normalize these tables
  • Identify all keys
  • Merge the result into design

Normalize the Tables

Represent all keys

  • Primary, alternate, secondary, foreign Database Management Assignment HelpDatabase Design Language (DBDL)
  • Mechanism for representing tables and keys

DBDL Notation

Table name followed by columns in parentheses

  • Primary key column(s) underlined

AK identifies alternate keys

SK identifies secondary keys FK identifies foreign keys

Entity-Relationship Diagrams (Top Down)

Merge the Result into the Design

Physical-Level Design

Undertaken after information-level design completion

Most DBMSs support primary, candidate, secondary, and foreign keys

DB programmers must include logic to ensure the uniqueness of primary keys and enforce other

conditions

Top-Down vs. Bottom-Up

Bottom-up

  • Design starts at low level
  • Specific user requirements drive design process

Top-down

  • Begins with general database that models overall enterprise
  • Refines the model until design is achieved

Survey Form

Used to collect information from users

Must contain particular elements

Existing Documents

Aid in collecting user requirements Collect information similar to that collected with survey forms

  • Entity information
  • Attribute information
  • Relationships
  • Functional dependencies
  • Processing information

Sample Sales Data

Result obtained by splitting Sales table into three tables

Result obtained by joining three tables–2 rows are in error. Must be converted to 4NF.

Table Split to Avoid Nulls