Update Operations
Date 09/26/2022
Topic Update Operations
Professor John DeMastri
Week 4
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
Update operations
Update operaton terminology note: in practice there are two different uses of the term "update. operation":
Update operation as a collective term for insert, delete and modify operations
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 real-world value, the same modification has to be made multiple times
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, ClientID -> ClientName
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, B
A, B -> A
Augmented Functional Dependency
Functional dependency that contains an existing functional dependency
For example, if a functional dependency:
A -> B
Then
A, C -> B
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
(Review) Nested Queries
Date 09/27/2022
Topic Nested Queries
Professor John DeMastri
Week 4
Correlated Nested Queries (outside in)
select c.name, c.SurfaceArea
from country c
where EXISTS (
select *
from CountryLanguage cl
where c.code=cl.CountryCode
and Language='English'
);
Update Operations
Date 09/28/2022
Topic Update Operations
Professor John DeMastri
Week 4
Partial functional dependency
Occurs when a column of a relation is functionally dependent on a component of a composite primary key.
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
Transitive functional dependency
Occurs when nonkey columns functionally determine other nonkey columns of a relation
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
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
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
First normal form (1NF)
A table is in 1NF if each row is unique and no column in any row contains multiple values
1NF 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 1NF
Related multivalued columns - columns in a table that refer to the same real-world concept (entity) and can have multiple values per record
Second Normal Form (2NF)
A table is in 2NF if it is in 1NF and if it does not contain partial functional dependencies
Normalization of a relation to 2NF creates additional relations for each set of partial dependencies in a relation
The primary key of the additional relation is the portion of the primary key that functionally determines the columnsin the original relation
Third Normal Form (3NF)
Normalization of a relation to 3NF creates additional relations for each set of transitive dependencies in a relation.