COSC 4385 - Final Exam Review

What are the different types of normal forms?

- 1NF - 2NF - 3NF

What is a deletion anomaly?

- A deletion anomaly is when you delete data from a relation (table) and the following problem(s) occur: 1. Loss of data 2. Unintended deletion 3. Data inconsistency

What is a modification anomaly?

- A modification anomaly is when you update data from a relation (table) and the following problem(s) can occur: 1. Update anomaly 2. Insertion/Deletion anomaly 3. Inconsistent data Ex: Having too many relations that could be reduced to one table

What is a key?

- A superkey with an additional property. - Any removal from k will cause it to not be a superkey (minimal)

What an insertion anomaly?

- An insertion anomaly is when you insert data into a relation (table) and the following problem(s) occur: 1. Missing data 2. Duplicate data 3. Inconsistent data

What are the basic constraint types supported by SQL?

- Key Constraint: PK cannot be duplicated - Entity Integrity: PK cannot be null - Referential Integrity: FK has to exists in PK (value has to exists)

What is a candidate key?

- Keys that can be chosen as the primary key of a relation. The rest of the candidate keys will be secondary keys.

Why is normalization important?

- Reduces data redundancy - Ensures a table represent a single subject - Each row/column (tuple/attribute) contain only one value - No data will be stored in more than 1 table - All nonprime attributes are dependent on the PK - Each table has no insertion, deletion, or modification anomalies.

What is data redundancy?

- Redundant data that takes up too much space. Issues include the following: - Insertion Anomalies - Deletion Anomalies - Modification Anomalies

How do fix a violation of referential integrity?

- SET NULL - SET DEFAULT - CASCADE

What is Normalization?

- The process of normalizing relations to reduce data redundancy and remove any anomalies. - Decomposing bad relationships through their attributes - Use concept of Functional Dependencies

What are nonprime attributes?

- it is not a member of any candidate key.

What is a prime attribute?

- member of some candidate key

What is Functional Dependency (FD)?

A relationship between two attributes within a single relation. (Column interaction in a table)

What is a superkey?

An attribute, or set of attributes, that uniquely identifies a tuple within a relation.

What is DDL?

Data Definition Language; for creating data structures - Schemas, tables, indexes, clusters, and sequences

What is DML?

Data manipulation Language; for manipulating data. - Insert, delete, update, and retrieve

What is 1NF?

First normal form (1NF) sets the very basic rules for an organized database: - Eliminate duplicative columns from the same table. - Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key). 1. Each attribute should contain atomic values 2. Each attribute myst be unique 3. All values for a column must be the same type 4. Each row must be unique

What is 2NF?

The requirements to satisfy the 2nd NF: - All requirements for 1st NF must be met. - Redundant data across multiple rows of a table must be moved to a separate table. - The resulting tables must be related to each other by use of foreign key. 1. Must be 1NF 2. Not contain partial dependencies (attributes that depend on only one part of the PK) Note: Already in 2NF if it has only 1 PK

What is 3NF?

The requirements to satisfy the 3rd NF: - All requirements for 2nd NF must be met. - Eliminate fields that do not depend on the primary key; That is, any field that is dependent not only on the primary key but also on another field must be moved to another table 1. Must be in 2NF 2. No transitive dependency (attributes are dependent on a nonprime column rather than the PK)