Chapter 9 of the textbook delved into database design, which has two steps — (1) creating a logical database model and (2) creating a physical database model — which are often done parallel with other system design steps.
Logical database modeling has four key steps:
- Develop a logical data model for each known user interface (form and report) for the application using normalization principles.
- Combine normalized data requirements from all user interfaces into one consolidated logical database model; this step is called view integration.
- Translate the conceptual E-R data model for the application or enterprise, developed without explicit consideration of specific user interfaces, into normalized data requirements.
- Compare the consolidated logical database design with the translated E-R model and produce, through view integration, one final logical database model for the application.
In the physical design, you choose things like datatype, file organization, storage media, and much more. It’s where you really start to worry about storage space and speed.
One of the most important aspects of database design is picking primary keys. Primary keys are a unique field (or fields) that allow an individual record to be identified. Records from one table can then be associated with those from another, by way of foreign key, which is the primary key from another table. Normalization and the different normal forms are also based around the primary keys. In first normal form, there are unique rows and no attributes with multiple values in them. In second normal form, each nonprimary key attribute is identified by the whole primary key. In third normal form, nonprimary key attributes do not depend on each other.