Data Models, Schemas, and Database Normalization

Categories of Data Models

• Entity: Represents a real-world object or concept.
• Attribute:
  • Represents some property of interest.
  • Further describes an entity.
• Relationship: Represents an association among two or more entities.
• Entity-Relationship model
• Relational data model: Used most frequently in traditional commercial DBMSs.
• Object data model:
  • New family of higher-level implementation data models.
  • Closer to conceptual data models.
• Physical data models: Describe how data is stored as files in the computer.
• Access path: Structure that makes the search for particular database records efficient.
• Index:
  • Example of an access path.
  • Allows direct access to data using an index term or a keyword.

Schemas, Instances, and Database State

• Database schema: Description of a database.
• Schema diagram: Displays selected aspects of the schema.
• Schema construct: Each object in the schema.
• Database state or snapshot: Data in the database at a particular moment in time.

Database Keys

• Super key: May have a combination of redundant attributes.
• Candidate Key: Super key without redundancy.
  • Minimal key: Cannot be reduced further.
  • There can be more than one candidate key.
• Primary Key: A Candidate Key that is used by the database designer for unique identification of each row in a table. A Primary Key can consist of one or more attributes of a table.
• Foreign Key: A key used to link two tables together. A Foreign Key is a field (or collection of fields) in one table that refers to the Primary Key in another table.

Database Normalization

First Normal Form (1NF)

As per First Normal Form, no two rows of data must contain a repeating group of information, i.e., each set of columns must have a unique value, such that multiple columns cannot be used to fetch the same row.

Second Normal Form (2NF)

As per the Second Normal Form, there must not be any partial dependency of any column on the primary key. It means that for a table that has a concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence.

• If any column depends only on one part of the concatenated key, then the table fails the Second Normal Form.

Third Normal Form (3NF)

Third Normal Form applies that every non-prime attribute of the table must be dependent on the primary key. There should not be a case that a non-prime attribute is determined by another non-prime attribute.

• This transitive functional dependency should be removed from the table, and the table must be in Second Normal Form.

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form is a higher version of the Third Normal Form. This form deals with a certain type of anomaly that is not handled by 3NF. A 3NF table that does not have multiple overlapping candidate keys is said to be in BCNF.