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




0 comments:

Post a Comment