This topic is intended to introduce the Logical Database Design and the Relational Model. At the end of the topic it is desired from the reader to be able to: Concisely define each of the following key terms: Key or Primary Key Surrogate Primary Key Second Normal Form Foreign Key Recursive Foreign Key Partial Functional Null Values Normalization Dependency Entity Integrity Rule Normal Form Third Normal Form Referential Integrity Functional Dependency Transitive Dependency Constraint Determinant Synonyms, Homonym Well-Structured Relation Candidate Key and Alias Database Anomalies First Normal Form Enterprise Key
List five properties of relations. State two essential properties of a candidate key. Give a concise definition of each of the following: first normal form, second normal form, and third normal form. Briefly describe four problems that may arise when merging relations. Transform an E-R (or EER) diagram into a logically equivalent set of relations. Create relational tables that incorporate entity integrity and referential integrity constraints. Use normalization to decompose a relation with anomalies into well-structured relations.
Logical Database Design is the process of transforming the conceptual data model into a logical data model— one that is consistent and compatible with a specific type of database technology. Conceptual data modeling is about understanding the organization—getting the right requirements. While Logical database design is about creating stable database structures—correctly expressing the requirements in a technical language. The objective of logical database design is to translate the conceptual design (which represents an organization’s requirements for data) into a logical database design that can be implemented via a chosen database management system. The resulting databases must meet user needs for data sharing, flexibility, and ease of access. An experienced database designer often will do logical database design in parallel with conceptual data modeling if he knows the type of database technology that will be used. Although there are other data models, we have two reasons for emphasizing the relational data mode. First, this model is the one most commonly used in contemporary database applications. Second, some of the principles of logical database design for the relational model apply to the other logical models as well. The Relational Data Model is a form of logical data model, and as such it is different from the conceptual data models. The E-R data model is not a relational data model, and an E-R model may not obey the rules for a well-structured relational data model, called normalization. The E-R model was developed for other purposes like understanding data requirements and business rules about the data—not structuring the data for sound database processing, which is the goal of logical database design.
The process of transforming an EER model into the relational model is supported by Many CASE tools today at the technical level; however, it is important that you understand the underlying principles and procedures. Normalization is the process of designing well-structured relations. It is an important component of logical design for the relational model.
2. The Relational Data Model
The Relational Data Model was first introduced in 1970 by E. F. Codd. In the beginning some early research projects were launched to prove the feasibility of the relational model and to develop prototype systems. Commercial RDBMS products started to appear about 1980. Today RDBMSs have become the dominant technology for database management, and there are literally hundreds of RDBMS products for computers ranging from smartphones and personal computers to...