Database Management/Database Models
Appearance
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 | edit source]Objectives and skills for this lesson include:
Readings
[edit | edit source]- Watt: Database Design (2nd edition) - Chapter 4 (Types of Data Models)
- Watt: Database Design (2nd Edition) - Chapter 5 (Data Modeling)
- Watt: Database Design (2nd Edition) - Chapter 10 (ER Modeling)
- Watt: Database Design (2nd Edition) - Chapter 11 (Functional Dependencies)
- Tutorial's Point: DBMS Normalization
- Tutorial's Point: Data Models
Multimedia
[edit | edit source]- YouTube: Database Models
- Youtube: Types of functional dependencies with the example | Normalization video
- YouTube: Normalization - 1NF, 2NF, 3NF, and 4NF
- YouTube: Summary of Armstrong's Axiom
- YouTube: Conceptual, Logical & Physical Data Models
- YouTube: Normalization and Anomaly Types
Activities
[edit | edit source]- Read Watt: Database Design (2nd Edition) - Chapter 10 (ER Modeling). Complete end of chapter exercises.
- Read Watt: Database Design (2nd Edition) - Chapter 11 (Functional Dependencies).
Lesson Summary
[edit | edit source]- 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 | edit source]- 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 | edit source]-
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]
-
_____________________ 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]
-
______________________'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]
-
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]
-
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]
-
The two types of modeling are ___________ and _________.The two types of modeling are logical data model and physical data model.
-
The ________ also defines the candidate key.The primary key also defines the candidate key.
-
With functional dependency, attributes on the left of the arrow are ________ while _______ are on the right.Determinate, Dependent
-
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.
-
_________________ are a set of inference rules used to infer all the functional dependencies on a relational database.Armstrong's Axioms.
-
An _________________ occurs when you are inserting inconsistent information into a table.insertion anomaly.
-
An _________________ occurs when you are editing information incorrectly in a table.update anomaly.
See Also
[edit | edit source]- 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
[edit | edit source]- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikipedia: Database Design
- ↑ {[Wikipedia: Peter Chen]]
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikibooks: Normalization
- ↑ Adrienne Watt. Database Design – 2nd Edition
- ↑ Wikipedia: Armstrong's axioms
- ↑ Wikipedia: Armstrong's axioms
- ↑ Wikipedia: Armstrong's axioms
- ↑ Wikipedia: Armstrong's axioms
- ↑ Wikipedia: Armstrong's axioms
- ↑ Wikipedia: Database administrator
- ↑ Wikipedia: Database model
- ↑ Wikipedia: Logical data model
- ↑ Wikipedia: Physical data model
- ↑ https://acronyms.thefreedictionary.com/DBDL
- ↑ Wikipedia: Armstrong's axioms
- ↑ Wikibooks: Database normalization
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikibooks: Introduction to Database Systems
- ↑ Wikibooks: Database Design/The Entity Relationship Data Model
- ↑ Wikipedia: Database model
- ↑ Wikipedia: Rule of Inference
- ↑ Wikibooks: Database normalization
- ↑ Wikipedia: Database model
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikibooks: Database Design/ER Modeling
- ↑ Wikipedia: Database model
- ↑ Wikibooks: Introduction to Database Systems
- ↑ Database Design-2nd Edition: Types of Data Models
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikibooks: Database normalization
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikipedia: Relational Model
- ↑ Wikipedia: Functional Dependency
- ↑ Wikipedia: Database administrator
- ↑ Wikibooks: Database Design/Functional Dependencies
- ↑ Wikipedia: Database Design