Database Design

Entity-Relationship (ER)

•  Describes data in terms of objects and relationships  
•  Widely used to develop an initial database design

à This chapter

•       Introduction to ER model

•       Discussion how its features allow us to model a wide range of data faithfully

DataBase

Database Design and ER Diagrams

Data design: 6 steps

1.    Requirement Analysis

2.    Conceptual Database Analysis

3.    Logical Database Design

4.    Schema Refinement

5.    Physical Database Design

6.    Application and Security Design

Requirement Analysis

•       What data is to be stored in the database?

•       What applications must be built on top of it?

•       What operations are most frequent and subject to performance requirements

à Several methodologies and some automated tools

Conceptual Database Design

•       High-level description of the data to be stored in the database

•       Along with constraints

•       Uses ER model

•       The initial design must be sufficiently precise to enable a straightforward translation into a data model supported by a commercial database system (relational model)

Logical Database Design

•       Choose a DBMS to implement the database design

•       Convert the conceptual database design into a database schema in the data model of the chosen DBMS

Ex. Convert an ER schema into a relational database schema

Beyond ER Design

•       ER diagram is just an approximation of the data

•       A more careful analysis can often refine the logical schema obtained at the end of step 3

•       Once they have a good logical schema

–      Consider performance criteria

–      Design physical schema

–      Address security issues

Ensure that users are able to access the data they need

Schema Refinement

•       Analyze the collection of relations in relational database schema

–      Identify potential problems

–      Refine the problems

Can be guided by some elegant and powerful theory (normalization theory)

Physical Database Design

•       Consider typical expected workloads

•       Refine the database design to ensure that it meets the desired performance criteria

–      Indexes on some tables

–      Clustering some tables

Substantial redesign of parts of the database schema

Application and Security Design

•       Identify entities and processes involved in the application

•       Describe the role of each entity in every process

•       For each role

–      Identify the parts of the database that must/must not be accessible

•       Take steps to ensure that these access rules are enforced

A DBMS provides several mechanisms to assist

Entities, Attributes, and Entity Sets

•       An entity is an object in the real world that is distinguishable from other objects

Ex. Toy, toy department, manager of toy department, address of the manager

•       An entity set is a collection of similar entities

Ex. An entity set employee contains employees of toy and appliance department

•       Attributes  are used to describe an entity

Ex. Employee entity set contains name, rate, and parking lot as attributes

•       A domain is used to define possible values of  an attribute

Ex. A domain of rate attribute consists of integers 1 through 10

•       A key is a minimal set of attributes whose values uniquely identity an entity in the set

–      Candidate key

–      Primary key 

ER