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

edit

Objectives and skills for this lesson include:

Readings

edit
  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

edit
  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

edit
  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

edit
  • 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

edit
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[1]
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. [[2]]
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

edit
Enable JavaScript to hide answers.
Click on a question to see the answer.
  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

edit

References

edit
  1. Wikibooks: Database Design/Functional Dependencies
  2. Wikipedia: Database Design
  3. {[Wikipedia: Peter Chen]]
  4. Wikibooks: Database Design/Functional Dependencies
  5. Wikibooks: Normalization
  6. Adrienne Watt. Database Design – 2nd Edition
  7. Wikipedia: Armstrong's axioms
  8. Wikipedia: Armstrong's axioms
  9. Wikipedia: Armstrong's axioms
  10. Wikipedia: Armstrong's axioms
  11. Wikipedia: Armstrong's axioms
  12. Wikipedia: Database administrator
  13. Wikipedia: Database model
  14. Wikipedia: Logical data model
  15. Wikipedia: Physical data model
  16. https://acronyms.thefreedictionary.com/DBDL
  17. Wikipedia: Armstrong's axioms
  18. Wikibooks: Database normalization
  19. Wikibooks: Database Design/Functional Dependencies
  20. Wikibooks: Introduction to Database Systems
  21. Wikibooks: Database Design/The Entity Relationship Data Model
  22. Wikipedia: Database model
  23. Wikipedia: Rule of Inference
  24. Wikibooks: Database normalization
  25. Wikipedia: Database model
  26. Wikibooks: Database Design/Functional Dependencies
  27. Wikibooks: Database Design/ER Modeling
  28. Wikipedia: Database model
  29. Wikibooks: Introduction to Database Systems
  30. Database Design-2nd Edition: Types of Data Models
  31. Wikibooks: Database Design/Functional Dependencies
  32. Wikibooks: Database Design/Functional Dependencies
  33. Wikibooks: Database normalization
  34. Wikibooks: Database Design/Functional Dependencies
  35. Wikipedia: Relational Model
  36. Wikipedia: Functional Dependency
  37. Wikipedia: Database administrator
  38. Wikibooks: Database Design/Functional Dependencies
  39. Wikipedia: Database Design