Database Systems Introduction to Databases and Data Warehouses
Database Systems Introduction to Databases and Data Warehouses CHAPTER 4 - Update Operations, Update Anomalies, and Normalization Copyright (c) 2016 Nenad Jukic and Prospect Press
UPDATE OPERATIONS § Insert operation • Used for entering new data in the relation § Delete operation • Used for removing data from the relation § Modify operation • Used for changing the existing data in the relation Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 2
UPDATE OPERATIONS Example of an insert operation Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 3
UPDATE OPERATIONS Example of a delete operation Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 4
UPDATE OPERATIONS Example of a modification operation Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 5
UPDATE OPERATIONS § Update operation terminology note: in practice there are two different uses of the term “update operation”: • A) Update operation as a collective term for insert, delete and modify operations • B) Update operation as a synonym for the modify operation § In this chapter we will use the term update operation as defined in A) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 6
UPDATE ANOMALIES § Update anomalies - anomalies in relations that contain redundant (unnecessarily repeating) data, caused by update operations • Insertion anomaly - occurs when inserting data about one real- world entity requires inserting data about another real-world entity • Deletion anomaly - occurs when deletion of data about a real-world entity forces deletion of data about another real-world entity • Modification anomaly - occurs when, in order to modify one realworld value, the same modification has to be made multiple times Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 7
Example relation AD CAMPAIGN MIX Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 8
Example relation AD CAMPAIGN MIX (contains redundant data) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide 9
Examples of update anomalies in the relation AD CAMPAIGN MIX Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Functional dependency - occurs when the value of one (or more) column(s) in each record of a relation uniquely determines the value of another column in that same record of the relation § For example A→B Client. ID → Client. Name Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Two functional dependency notations Client. ID → Client. Name Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES Example Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Streamlining functional dependencies • Not all functional dependencies need to be depicted • The following types of functional dependencies can be omitted: o Trivial functional dependencies o Augmented functional dependencies o Equivalent functional dependencies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Trivial functional dependency - occurs when an attribute (or a set of attributes) functionally determines itself or its subset • For example: A→A A, B → A Campaign. Mgr. ID, Campaign. Mgr. Name → Campaign. Mgr. Name • Trivial functional dependencies are not depicted Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Augmented functional dependency - functional dependency that contains an existing functional dependency • For example if a functional dependency : A→B exists in a relation, then : A, C → B is an augmented functional dependency • Does not add new information to what is already described by the existing functional dependency • It can be omitted Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § AD CAMPAIGN MIX example • Functional dependencies (Set 3): Ad. Campaign. ID → Ad. Campaign. Name, Start. Date, Duration, Campaign. Mgr. ID, Campaign. Mgr. Name • Augmented functional dependencies (in Set 5) due to Set 3: Ad. Campaign. ID, Mode. ID → Ad. Campaign. Name, Start. Date, Duration, Campaign. Mgr. ID, Campaign. Mgr. Name can be omitted Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § AD CAMPAIGN MIX example • Functional dependencies (Set 2): Mode. ID → Media, Range • Augmented functional dependencies (in Set 5) due to Set 2: Ad. Campaign. ID, Mode. ID → Media, Range can be omitted Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Equivalent functional dependency - occurs when two columns (or sets of columns) that functionally determine each other determine other columns • If one of the equivalent functional dependencies is depicted, the other equivalent functional dependency can be omitted • For example if functional dependencies : A→B B→A exists in a relation, then : A→B B→A are equivalent functional dependencies, and: A → B, X B → A, X are equivalent functional dependencies, and: Y, A → B, X Y, B → A, X are equivalent functional dependencies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § AD CAMPAIGN MIX example • Because the functional dependency: Ad. Campaign. ID → Ad. Campaign. Name and the functional dependency: Ad. Campaign. Name → Ad. Campaign. ID are equivalent, Set 3 and Set 4 are equivalent sets and Set 5 and Set 6 are also equivalent sets • Set 4 can be omitted from depiction because it is equivalent to Set 3 • Set 6 can be omitted from depiction because it is equivalent to Set 5 Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES Example - streamlining functional dependencies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES Example - streamlining functional dependencies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES Example – streamlined functional dependencies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES Example – streamlined functional dependencies (another notation) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Types of functional dependencies • The functional dependencies that are used as a basis for the typical normalization process can be classified in one of the three categories: o o o Partial functional dependency Full key functional dependency Transitive functional dependency Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Partial functional dependency - occurs when a column of a relation is functionally dependent on a component of a composite primary key • Only composite primary keys have separate components, while single-column primary keys do not have separate components • Hence, partial functional dependency can occur only in cases when a relation has a composite primary key Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Partial functional dependencies (in AD CAMPAIGN MIX example) Ad. Campaign. ID → Ad. Campaign. Name, Start. Date, Duration, Campaign. Mgr. ID, Campaign. Mgr. Name Mode. ID → Media, Range Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Full key functional dependency - occurs when a primary key functionally determines the column of a relation and no separate component of the primary key partially determines the same column • If a relation has a single component (non-composite) primary key, the primary key fully functionally determines all the other columns of a relation • If a relation has a composite key, and portions of the key partially determine columns of a relation, then the primary key does not fully functionally determine the partially determined columns Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Full key functional dependency (in AD CAMPAIGN MIX example) Ad. Campaign. ID, Mode. ID → Budget. Pctg Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Transitive functional dependency - occurs when nonkey columns functionally determine other nonkey columns of a relation • Nonkey column is a column in a relation that is neither a primary nor a candidate key column. Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES § Transitive functional dependency (in AD CAMPAIGN MIX example) Campaign. Mgr. ID → Campaign. Mgr. Name Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
FUNCTIONAL DEPENDENCIES Functional dependencies in the relation AD CAMPAIGN MIX (types indicated) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example relation RECRUITING Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example relation RECRUITING (functional dependencies shown) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Normalization - process used to improve the design of relational databases § Normal form - term representing a set of particular conditions (whose purpose is reducing data redundancy) that a table has to satisfy • From a lower to a higher normal form, these conditions are increasingly stricter and leave less possibility for redundant data Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § There are several normal forms, most fundamental of which are: • First normal form (1 NF) • Second normal form (2 NF) • Third normal form (3 NF) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § The normalization process involves examining each table and verifying if it satisfies a particular normal form § If a table satisfies a particular normal form, then the next step is to verify if that relation satisfies the next higher normal form § If a table does not satisfy a particular normal form, actions are taken to convert the table into a set of tables that satisfy the particular normal form Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Normalizing to first normal form is done on non-relational tables in order to convert them to relational tables § Normalizing to subsequent normal forms (e. g. , second normal form, third normal form) improves the design of relational tables that contain redundant information and alleviates the problem of update anomalies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § First Normal Form (1 NF) - A table is in 1 NF if each row is unique and no column in any row contains multiple values • 1 NF states that each value in each column of a table must be a single value from the domain of the column • Every relational table is, by definition, in 1 NF • Related multivalued columns - columns in a table that refer to the same real-world concept (entity) and can have multiple values per record • Normalizing to 1 NF involves eliminating groups of related multivalued columns Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example: Normalizing a table to 1 NF Non-relational table (not in 1 NF). Normalizing the table to 1 NF by increasing the number of records Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example: Normalizing a table to 1 NF Non-relational table (not in 1 NF). Normalizing the table to 1 NF by creating a new, separate table Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example: Normalizing a table to 1 NF Non-relational table (not in 1 NF) with two groups of related multivalued columns Normalizing the table to 1 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Second Normal Form (2 NF) - A table is in 2 NF if it is in 1 NF and if it does not contain partial functional dependencies • If a relation has a single-column primary key, then there is no possibility of partial functional dependencies o Such a relation is automatically in 2 NF and it does not have to be normalized to 2 NF • If a relation with a composite primary key has partial dependencies, then it is not in 2 NF, and it has to be normalized it to 2 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Second Normal Form (2 NF) • Normalization of a relation to 2 NF creates additional relations for each set of partial dependencies in a relation o o The primary key of the additional relation is the portion of the primary key that functionally determines the columns in the original relation The columns that were partially determined in the original relation are part of the additional table • The original table remains after the process of normalizing to 2 NF, but it no longer contains the partially dependent columns Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example: Normalizing a table to 2 NF Pressly Ad Agency - relation AD CAMPAIGN MIX Pressly Ad Agency example - normalized to 2 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Third Normal Form (3 NF) - A table is in 3 NF if it is in 2 NF and if it does not contain transitive functional dependencies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Third Normal Form (3 NF) • Normalization of a relation to 3 NF creates additional relations for each set of transitive dependencies in a relation. o o The primary key of the additional relation is the nonkey column (or columns) that functionally determined the nonkey columns in the original relation The nonkey columns that were transitively determined in the original relation are part of the additional table. • The original table remains after normalizing to 3 NF, but it no longer contains the transitively dependent columns Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example: Normalizing a table to 3 NF Pressly Ad Agency example - normalized to 2 NF Pressly Ad Agency example - normalized to 3 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Example: Normalizing a table to 3 NF Pressly Ad Agency example – relational schema of 3 NF relations Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Pressly Ad Agency relation AD CAMPAIGN MIX – not normalized, prone to update anomalies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Pressly Ad Agency example—normalized relations with data Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Pressly Ad Agency example—normalized relations with data, update anomalies resolved Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Another normalization example: Normalizing a table to 2 NF Central Plane University - relation RECRUITING Central Plane University example - normalized to 2 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Another normalization example: Normalizing a table to 3 NF Central Plane University example - normalized to 3 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Central Plane University relation RECRUITING – not normalized, prone to update anomalies Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Central Plane University example - normalized relations with data (redundancy eliminated and update anomalies resolved) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Normalization Exceptions • In general, database relations are normalized to 3 NF in order to eliminate unnecessary data redundancy and avoid update anomalies • However, normalization to 3 NF should be done judiciously and pragmatically, which may in some cases call for deliberately not normalizing certain relations to 3 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Normalization exception example: relation SALES AGENT Relation SALES AGENT Functional dependencies in the relation SALES AGENT Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Normalization exception example: should relation SALES AGENT be normalized? SALES AGENT example in 3 NF Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § Denormalization - reversing the effect of normalization by joining normalized relations into a relation that is not normalized, in order to improve query performance • The data that resided in fewer relations prior to normalization is spread out across more relations after normalization • This has an effect on the performance of data retrievals • Denormalization can be used in dealing with the normalization vs. performance issue § Denormalization is not a default process that is to be undertaken in all circumstances • Instead, denormalization should be used judiciously, after analyzing its costs and benefits Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Denormalization example Pressly Ad Agency example—a retrieval of data Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
NORMALIZATION § ER-Modeling versus Normalization • ER modeling followed by mapping into a relational schema is one of the most common database design methods • When faced with a non-normalized table, instead of identifying functional dependencies and going through normalization to 2 NF and 3 NF, a designer can analyze the table and create an ER diagram based on it (and subsequently map it into a relational schema) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
ER-Modeling vs. Normalization example - ER diagram of the Pressly Ad Agency Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
ER-Modeling vs. Normalization example - mapped ER diagram, identical to the relational schema resulting from the normalization process (the relational schema of 3 NF relations for the normalized Pressly Ad Agency example) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
ADDITIONAL STREAMLINING OF DATABASE CONTENT § Designer-added entities (tables) and keys • Even if a relation is in 3 NF additional opportunities for streamlining database content may still exist • Designer-added entities (tables) and designer-added keys can be used for additional streamlining Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Designer-added entities (tables) and keys example - augmented ER diagram of the Pressly Ad Agency example Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Designer-added entities (tables) and keys example - augmented ER diagram of the Pressly Ad Agency example mapped into a relational schema Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
Designer-added entities (tables) and keys example - mapped relations populated with data Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
ADDITIONAL STREAMLINING OF DATABASE CONTENT § Designer-added entities (tables) and keys • Augmenting databases with designer added tables and keys is not a default process that is to be undertaken in all circumstances • Instead, augmenting databases with designer added tables and keys should be done judiciously, after analyzing pros and cons for each augmentation Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 4 – Slide
- Slides: 69