Modeling and Designing Databases

1. The Database Design Process

There are three major stages in the database design.

• Requirements analysis
• Conceptual design
• Logical design

2.The Entity Relationship(ER) Model

• Representing Entities
• Representing Relationships
• Partial and Total Participation
• Entity or Attribute?

1.  Is the item of direct interest to the database?
2. Does the item have component of its own?
3. Can the object have multiple instances?
4. Is the object often nonexistent or unknown?       

• Entity or Relationship?
• Intermediate Entities
• Weak and Strong Entities

3.Database Normalization(数据库规范化)

   The main objective of the normal forms is to reduce data redundancy and improve data integrity.

• The first normal form(1NF) [No Repeating Groups]has the following goals:

1. Eliminate repeating groups in individual tables.
2. Create a separate table for each set of related data.
3. Indentify each set of related data with a primary key.

• The goals of Second normal form(2NF) [Eliminate Redundant Data]are:

1. Create separate tables for sets of values that apply to multiple records
2. Relate these tables with a foreign key.

• Third normal form (3NF) [Eliminate Data Not Dependent on Key]adds one more goal

1. Elimate fields that do not depend on the key.

4.Using the Entity Relationship Model

• Mapping Entities and Relationships to Database Tables

1. Map the entities to database tables
2. Map the relationship to database tables

• Creating a Bank Database ER Model

1. Many to many relationship(N:M)
2. One to many relationship(1:N)