Final Exam Review Flashcards - CIS 458

This set of flashcards covers normalization principles, SQL commands and syntax (DDL, DML, DCL), and Transaction Management concepts based on the course lecture notes.

What is defined as an anomaly in a database table?

is a problem caused by redundant data in a table.

Three types of anomalies

1. Insertion

2. Deletion

3. Modification

Composite key

A primary key made up of two or more attributes that together uniquely identify a row in a table.

Functional dependency

Occurs when one attribute uniquely determines another attribute

Student ID → StudentName; One StudentID determines exactly one student name

Full dependency

A non-key attribute depends on the entire composite key.

(StudentID, CourseID) → Grade; Grade depends on both attributes together

Requirements for First Normal Form (1NF)?

Having no repeating groups and having atomic values (one value per cell).

Requirements for Second Normal Form (2NF)?

The table must be in 1NF and have no partial dependencies.

Requirements for Third Normal Form (3NF)?

The table must be in 2NF and have no transitive dependency for non-key attributes.

What are the three major functions of SQL?

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

3. Data Control Language (DCL)

Which SQL commands belong to Data Definition Language (DDL)?

CREATE, ALTER, and DROP.

Which SQL commands belong to Data Manipulation Language (DML)?

SELECT, INSERT, UPDATE, and DELETE.

Which SQL commands belong to Data Control Language (DCL)?

GRANT and REVOKE.

What elements are required when creating a table in SQL?

Each table must have a name and several attributes, and each attribute must have a name and a data type.

What is Structured Query Language (SQL)?

The language we use to create, manipulate, and maintain a relational database

What is Data Definition Language (DDL)?

Used to create, modify, and delete database objects

What is Data Manipulation Language (DML)?

Used to manipulate data

What is Data Control Language (DCL)?

Used to control user permissions

What is the DEFAULT constraint?

Provides automatic value if non entered

What is the NOT NULL constraint?

Must have a value

Transaction

A logical unit of work that must be entirely completed or aborted.

ACID properties of a transaction

1. Atomicity

2. Consistency

3. Isolation

4. Durability

What is the UNIQUE constraint?

Value must be unique and not duplicate

What are the types of locks used in concurrency control? —

• Shared Lock

• Exclusive Lock

What are the phases in Two-Phase Locking (2PL).

• Growing Phase

• Shrinking Phase

What is a deadlock?

Two transactions wait forever for each other's resources.

Partial dependency

A non-key attribute depends on only part of a composite key

(StudentID, CourseID) → StudentName; StudentName depends only on StudentID

Which form is this?

1NF

Which form is this?

2NF

Which form is this?

3NF

What is the PRIMARY KEY constraint?

Uniquely identifies each row

What is the FOREIGN KEY constraint?

References a primary key from another table

How to change the definition of tables?

ALTER table

What is the solutions for a deadlock?

• Deadlock detection and recovery

• Timeouts

• Deadlock prevention.

Deadlock detection and recovery

Allows deadlocks occurs but detect them and recover

Timeouts

If transaction exceeds the time limit, it aborts/roll back

Deadlock prevention

Lock all records at the beginning at the transaction

Growing Phase

When the transaction acquires locks only (Lock Only Mode)

Shrinking Phase

When the transaction releases locks only (Unlock Only Mode)

Insertion Anomaly

Inconsistency when adding data

Deletion Anomaly

Inconsistency when removing data

Modification Anomaly

Inconsistency when updating data

Atomicity Property

All operations happen or nothing happens

Consistency Property

Database remains valid

Isolation Property

Transactions do not interfere

Durability Property

Committed changes are permanent.

Shared Lock

Allows reading but no updating and prevents exclusive locks

Exclusive Lock

No access permitted, used when preparing updates.

Alias

Temporary name for a column or table

Subquery

A query inside another query

Concurrency Control

Managing simultaneous transactions safety

Problems from simultaneous transactions?

• Lost update

• Dirty Read

• Unrepeatable Read

Solutions to simultaneous transactions

Serializability

Serializability

Ensures transaction behave as if executed one at a time