Logical Data Modeling - Relational Model and Normalization

Midterm

What was the relational model commonly adopted to build?

Both logical and internal data models

Ordered list of attribute values. This is the row of the table

Tuple

How is a relational schema denoted?

Name(Attribute1, Attribute2, …AttributeN)

A set S of relation schemas that belong to the same database

Relational Database Scheme

T/F: All values in a tuple are considered atomic

True

Where must each value in a tuple be from?

The domain of the attribute for that column

What are the four types of constraints in the relational model?

Domain

Key

Entity Integrity

Referential Integrity

When are integrity constraints specified

When relations are designed and created in DBMS

When are integrity constraints checked by the DBMS?

When relation status is changed

Constraint where every value in a tuple must be from the domain of its attribute

Domain Constraint

What is a key constraint

Each row(tuple) has a key that uniquely identifies that tuple in the table

Set of attributes of a relation where no two tuples will have the same value for the set of attributes

Superkey

A ______ is a minimal superkey

key

What does a key being a “minimal” superkey mean

No two tuples of R have the same value for this set of attributes

If any attribute is removed from this set then it won’t be a superkey

T/F: not every key is also a superkey

False, any key is also a superkey but not every superkey is a key

Any set of attributes that ______ a key is a ________

contains, superkey

If a relation has several cadidate keys, one is chose to be the ________

Primary key

Constraint where the primary key attributes of each relation cannot have null values in any tuple

Entity integrity constraint

Constraint used to specify a relationship among tuples. All foreign key constraints are enforced

Referential Integrity Constraint

In a referential integrity constraint, what must the value in the foreign key be?

Either a value existing in the corresponding primary key or a null

How do you map a multivalued attribute to the relational schema?

It becomes a separate relation with a foreign key taken from the superior entity

virtual table in a relational database that presents data from one or more underlying tables

View

Underlying table whose value is really stored in the database

Base table

T/F: Views store data

False

What do views display

Only necessary information

What are the benefits of using views

Data abstraction: hides complexity of base tables and simplifies data access

Security: restricts access to sensitive data by allowing specific users to see it

Simplified queries: enables reuse of complex queries

Logical data independence: allows changes to database schema without affecting how others access the data

What is the goal of relational schema design

Avoid redundancy and anomalies

What are the four problems when relations are not well structured?

Redundant storage: some information stored repeatedly

Update Anomalies: if one copy of repeated data is updated, inconsistency occurs unless all copies are updated

Insertion Anomalies: adding new rows forces user to create duplicate data

Delete Anomalies: deleting rows can cause loss of data that would be needed for future rows

Relation that contains a minimal amount of redundancy, and allows users to insert, delete, and update tuples in a relation without errors or inconsistency

Well-structured relation

An effect where updating a value of an attribute, like its name, does not update anywhere else. This causes inconsistencies.

Anomaly

The value of one attribute determines the value of another attribute

Functional Dependency

When is a functional dependency a fully functional dependency

If removal of any attribute from X means that the dependency won’t hold anymore

 If X, A -> Y, and you remove A, X still determines Y.

Partial dependency

 If A -> B, and B -> C, then A -> C

Transitive dependency

What is the main technique for schema refinement

Decomposition

Systematic process of decomposing relations with anomalies to produce smaller, well-structured relations

Normalization

What is normalization used for?

Used for evaluating and correcting relational schemas to minimize data redundancies

State of a particular relation determined based on functional dependencies

Normalization form

What are the normalization forms

1NF: First normal form

2NF: Second normal form

3NF: Third normal form

BCNF: Boyce-Codd normal form

4NF: Fourth normal form

What are the conditions for 1NF?

Every attribute is atomic and single

The relation has a relation name

What are the conditions for 2NF?

It is in 1NF

It includes no partial dependencies

T/F: it isn’t possible to exhibit transitive dependencies in 2NF

False

What do we have to do if the relation is not in 2NF

Decompose and set up new relation for each partial key together with its dependent attribute types

Keep a relation with the original primary key and any attributes that are fully functionally dependent on it

What are the steps for the normalization process to 2NF

1. Write each key component on a separate line

2. Create a relation per each key component and assign corresponding dependent attributes

What are the conditions for 3NF

it is a 2NF

it contains no transitive dependencies

What are the steps for the normalization process to 3NF

1. Identify each new determinant

2. Identify the dependent attributes and add them in a new table

3. Remove the dependent attributes of the transitive dependencies from the original table. All that should be left is the foreign key