Steps to design a relational database
1. Understand the requirements
- Do: Collect information about what users want for the database and analyze it
- Output: Document a list of requirements for the database
2. Conceptual design
- Do: Based on the requirements for the database, construct conceptual data models
- Entities, attributes, and relationships for the data models
- Output: Draw an ER diagram (Entity-Relationship Diagram)
- Related post: Entity Relationship (ER) Diagram
3. Logical design
- Do: Based on the ER diagram, construct a logical design of the database (schema)
| How to construct a logical, relational schema from ER diagram |
|---|
| Rule 1: Transform all entities to relations. Bring the entities’ attributes to the relation. |
| Rule 2: Transform Many-to-Many relationships to relations. Bring the relationship’s attributres to the relation. The keys of each relationship become Foreign Keys to the entities. The Primary Key for this relation is composed of both keys of the relationships. |
| Rule 3: Express One-to-Many relationships as Foreign Key. We can include the primary key of the relation (One) as a Foreign Key on the other relation. One-to-Many relationships with weak entity: Same as above, we can include the primary key of (One) as a Foreign Key on the other relation (N). However, we also need to include this Foreign Key as part of Primary Key of the relation (N). |
| Rule 4: Express One-to-One relationships as Foreign Key. |
| Rule 5: Transform composite attributes to relations. Relational database does not allow composite attributes. |
Output: Construct a logical schema for the database
4. Physical design and implementation
- Do: SQL command to create database table (Database Definition Language)
- Output: Run the SQL command to implement the database.
References
- 데이터베이스 개론 8장: 데이터베이스 설계