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.^[1]
• Database designs also include ER (entity-relationship model) diagrams. An ER diagram helps to design databases in an efficient way.^[2]
• A entity-relationship (E-R) model was developed by Peter Chen.^[3]
• A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table.^[4]
• 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.^[5]
• Data redundancy can result in insertion, update, and deletion anomalies. ^[6]

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.^[7]

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.^[8]

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} .^[9]

axiom of transitivity

If {X} holds {Y} and {Y} holds {Z} , then {X} holds {Z} .^[10]

composition

If {X to Y} and {A to B} then {XA to YB} .^[11]

DBA

Database administrators (DBAs) use specialized software to store and organize data.^[12]

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.^[13]

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.^[14]

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.^[15]

DBDL

Database design language.^[16]

decomposition

If {X to YZ} then {X to Y} and {X to Z} .^[17]

deletion anomaly

A deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted.^[18]

dependency diagram

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

dependent

The right side of the functional dependency diagram.ref>Wikipedia: Functional dependency</ref>

designer

application programmers and/or business analysts who design the layout of the database.^[20]

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.^[21]

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.^[22]

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).^[23]

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.^[24]

network model

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

non-normalized table

A table that has data redundancy in it.^[26]

redundancy

Redundancy is generally undesirable because it causes problems maintaining consistency after updates.^[27]

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.^[28]

schema

The structure and the constraints of data in a database.^[29]

set type

represents a limited type of one to many relationship based on the network model ^[30]

transactions

units of work designed to meet goals for users.^[31]

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.^[32]

update anomaly

Changing existing information incorrectly in a table is called an update anomaly.^[33]

Alternate Key

An alternate key is a column that could be a primary key but was not chosen.^[34]

Review Questions

1. A ________ is a table structure definition (a set of column definitions) along with the data appearing in that structure.

   A relation is a table structure definition (a set of column definitions) along with the data appearing in that structure.^[35]
2. _____________________ is a relationship that exists when one attribute uniquely determines another attribute.

   Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.^[36]
3. ______________________'s role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.

   Database administrator's role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.^[37]
4. If X determines Y, and Y determines Z, then X must also determine Z is _____________________.

   If X determines Y, and Y determines Z, then X must also determine Z is Axiom of transitivity.^[38]
5. The ________ design of the database specifies the physical configuration of the database on the storage media.

   The physical design of the database specifies the physical configuration of the database on the storage media.^[39]
6. The two types of modeling are ___________ and _________.

   The two types of modeling are logical data model and physical data model.
7. The ________ also defines the candidate key.

   The primary key also defines the candidate key.
8. With functional dependency, attributes on the left of the arrow are ________ while _______ are on the right.

   Determinate, Dependent
9. The best way to create a table while avoiding anomalies is to _________ your table.

   The best way to create a table while avoiding anomalies is to Normalize your table.
10. _________________ are a set of inference rules used to infer all the functional dependencies on a relational database.

    Armstrong's Axioms.
11. An _________________ occurs when you are inserting inconsistent information into a table.

    insertion anomaly.
12. An _________________ occurs when you are editing information incorrectly in a table.

    update anomaly.

See Also

• Wikipedia: Armstrong's Axioms
• Quizlet: Types of Database Models
• http://www.databaseanswers.org/data_models/index_all_models.htm - A large resource of example relational database models on a wide range of topics and applications.
• Lucid ChartTutorial

References