Updated Database Design Final Exam Study Guide

These flashcards cover essential concepts in database design, including normalization, SQL basics, and advanced topics for an exam review.

What is a Functional Dependency?

Attribute B depends on A if each A maps to exactly one B.

What is a Transitive Dependency?

If A → B and B → C, then A → C, which violates 3NF.

What does 1NF stand for and what does it require?

1NF stands for First Normal Form and requires atomic values with no repeating groups.

What is the requirement for a table to be in 2NF?

The table must be in 1NF and have no partial dependency; the whole primary key must determine all attributes.

What is the criteria for a table to be in 3NF?

The table must be in 2NF and have no transitive dependencies.

What is BCNF and how does it differ from 3NF?

BCNF (Boyce-Codd Normal Form) is stricter than 3NF; for every functional dependency X → Y, X must be a superkey.

Provide an example of a BCNF violation.

Teacher → Course is a violation because Teacher isn’t a superkey.

What does 4NF require of a table?

No multivalued dependencies unless they're from a candidate key.

What does 5NF ensure regarding table decompositions?

No redundancy after joining decomposed tables; decomposing should not create or lose records.

What does the SELECT statement do in SQL?

It retrieves data from one or more tables.

What are aggregate functions in SQL?

Functions that perform calculations on a set of values, e.g., COUNT(), SUM(), AVG(), MAX(), MIN().

What are the different types of JOINS in SQL?

INNER, LEFT, RIGHT, FULL OUTER.

What is a subquery?

A query nested inside another query.

What is the difference between a non-correlated and a correlated subquery?

A non-correlated subquery runs once, while a correlated subquery runs for each row processed by the outer query.

What is a VIEW in SQL?

A virtual table created by a query that can be used like a regular table.

What types of indexes are there in SQL?

Clustered and Non-clustered indexes.

What is a stored procedure?

Stored SQL code that is compiled once and executed many times.

List one advantage of using stored procedures.

They provide security by allowing code execution on the server.

What does a Trigger do in SQL?

Stored code that automatically runs in response to certain events such as INSERT, UPDATE, or DELETE.

What are the possible events for a Trigger?

Triggers can respond to INSERT, UPDATE, and DELETE actions.

What do the isolation levels in database transactions define?

The degree to which the operations in one transaction are isolated from those in other transactions.

What is the purpose of a shared lock in database locking?

To allow multiple transactions to read a resource simultaneously.

What is a deadlock?

A situation where two transactions are waiting on each other, preventing both from proceeding.

What does Serializable isolation level provide?

Full isolation ensuring that transactions appear to be executed in a serial sequence.