Database Management/Database Models

This lesson allows users to learn about the logical structure of a database that determines how data can be stored and organized in a relational database.

Objectives and Skills
Objectives and skills for this lesson include:

Readings

 * 1) Watt: Database Design (2nd edition) - Chapter 4 (Types of Data Models)
 * 2) Watt: Database Design (2nd Edition) - Chapter 5 (Data Modeling)
 * 3) Watt: Database Design (2nd Edition) - Chapter 10 (ER Modeling)
 * 4) Watt: Database Design (2nd Edition) - Chapter 11 (Functional Dependencies)
 * 5) Tutorial's Point: DBMS Normalization
 * 6) Tutorial's Point: Data Models

Multimedia

 * 1) YouTube: Database Models
 * 2) Youtube: Types of functional dependencies with the example | Normalization video
 * 3) YouTube: Normalization - 1NF, 2NF, 3NF, and 4NF
 * 4) YouTube: Summary of Armstrong's Axiom
 * 5) YouTube: Conceptual, Logical & Physical Data Models
 * 6) YouTube: Normalization and Anomaly Types

Activities

 * 1) Read Watt: Database Design (2nd Edition) - Chapter 10 (ER Modeling). Complete end of chapter exercises.
 * 2) Read Watt: Database Design (2nd Edition) - Chapter 11 (Functional Dependencies).

Lesson Summary

 * Armstrong’s axioms are a set of inference rules developed by William W. Armstrong. They infer all of the functional dependencies within a relational database.
 * Database designs also include ER (entity-relationship model) diagrams. An ER diagram helps to design databases in an efficient way.
 * A entity-relationship (E-R) model was developed by Peter Chen.
 * A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table.
 * Normalization is the branch of relational theory that provides design insights. It is the process of determining how much redundancy exists in a table. The goals of normalization are to: 1) be able to characterize the level of redundancy in a relational schema 2) provide mechanisms for transforming schemas in order to remove redundancy.
 * Data redundancy can result in insertion, update, and deletion anomalies.

Key Terms

 * Anomaly
 * An anomaly is an inconsistent, incomplete or conflicting state of a database.


 * Armstrong's axioms
 * Armstrong's axioms are a set of axioms (or, more precisely, inference rules) used to infer all the functional dependencies on a relational database.


 * axiom of augmentation


 * If {X} holds {Y}  and {Z} is a set of attributes, then {XZ}  holds {YZ} . It means that attribute in dependencies does not change the basic dependencies.


 * axiom of reflexivity
 * If {X} is a set of attributes and {Y}  is a subset of {X}, then {X}  holds {Y} . Hereby, {X}  holds {Y}  [ {X to Y} ] means that {X}  functionally determines {Y}.


 * axiom of transitivity
 * If {X} holds {Y}  and {Y}  holds {Z}, then {X}  holds {Z}.


 * composition
 * If {X to Y} and {A to B} then {XA to YB}.


 * DBA
 * Database administrators (DBAs) use specialized software to store and organize data.


 * data modeling
 * A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.


 * database logical design
 * A logical data model or logical schema is a data model of a specific problem domain expressed independently of a particular database management product in terms of data structures such as relational tables and columns, object-oriented classes, or XML tags.


 * database physical design
 * A physical data model (or database design) is a representation of a data design as implemented, or intended to be implemented, in a database management system.


 * DBDL
 * Database design language.


 * decomposition
 * If {X to YZ} then {X to Y} and {X to Z}.


 * deletion anomaly
 * A deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted.


 * dependency diagram
 * A dependency diagram, shown in Figure 11.6, illustrates the various dependencies that might exist in a non-normalized table.


 * dependent
 * The right side of the functional dependency diagram.ref>Wikipedia: Functional dependency


 * designer
 * application programmers and/or business analysts who design the layout of the database.


 * determinant
 * The left side of the functional dependency diagram(usually a PK). ref


 * entity relationship diagram (ERD)
 * The entity relationship (ER) data model is suited to data modeling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations. ER models, also called an ER schema, are represented by ER diagrams.


 * functional dependency
 * A functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation. []


 * hierarchical model
 * In a hierarchical model, data is organized into a tree-like structure, implying a single parent for each record. Hierarchical structures were widely used in the early mainframe database management systems. This structure allows one one-to-many relationship between two types of data.


 * inference rules
 * In logic, a rule of inference, inference rule or transformation rule is a logical form consisting of a function which takes premises, analyzes their syntax, and returns a conclusion (or conclusions).


 * insertion anomaly
 * An insertion anomaly occurs when inserting inconsistent information into a table. When a new record is inserted, verification is required to check that the data is consistent with existing rows in table.


 * network model
 * The network model expands upon the hierarchical structure, allowing many-to-many relationships in a tree-like structure that allows multiple parents.


 * non-normalized table
 * A table that has data redundancy in it.


 * redundancy
 * Redundancy is generally undesirable because it causes problems maintaining consistency after updates.


 * relational model
 * The relational model was introduced by E.F. Codd in 1970[2] as a way to make database management systems more independent of any particular application. Three key terms are used extensively in relational database models: relations, attributes, and domains. A relation is a table with columns and rows. The named columns of the relation are called attributes, and the domain is the set of values the attributes are allowed to take.


 * schema
 * The structure and the constraints of data in a database.


 * set type
 * represents a limited type of one to many relationship based on the network model


 * transactions
 * units of work designed to meet goals for users.


 * union
 * This rule suggests that if two tables are separate, and the PK is the same, you may want to consider putting them together. It states that if X determines Y and X determines Z then X must also determine Y and Z.


 * update anomaly
 * Changing existing information incorrectly in a table is called an update anomaly.


 * Alternate Key
 * An alternate key is a column that could be a primary key but was not chosen.