Exam 2

Question 1

Functional Dependency

Answer 1

A functional dependency is a relationship between attributes. A functional dependency is
desirable when a key determines the rest of the row.

However, if a “key” determines only some of the attributes, then it’s the key of some other
object and those attributes belong in their own table. If they stay in the same table then it
leads to redundancy and modification anomalies.

Question 2

First Normal Form

Answer 2

First Normal Form means that attributes in a table must be:

Atomic, meaning that a single attribute cannot contain a list, array, or other structure that
must be searched or processed to determine its contents.

Non-repeating, meaning that an attribute cannot be duplicated multiple times in a row to
produce a list within a single row.

Question 3

Deletion Anomaly

Answer 3

A deletion anomaly is the side effect of placing data about more than one object in the same
table. When a row is deleted, data about both objects is eliminated.

Example: Deleting a part from a parts list also deletes the vendor from the database.

Question 4

Second Normal Form

Answer 4

Second Normal Form means all the attributes in a row are functionally dependent on the
whole key, not just part of the key. This only affects tables with composite keys.

Put another way, there are no partial-key dependencies.

Question 5

Why do normalization problems occur?

Answer 5

Normalization problems occur because a table represents two objects.

Dividing the table
into two, one for each object (“decomposition”), eliminates redundancy, solves modification
anomalies, and brings the table to a higher normal form.

Question 6

What are second and third normal forms concerned with?

Answer 6

Second and Third Normal Forms are both concerned with an attribute that’s a non-key
determinant. In 2NF, the non-key determinant is a component of the table’s composite key,
and in 3NF the non-key determinant is outside of the table’s key.

Question 7

Describe 1NF, 2NF and 3NF

Answer 7

1NF eliminates non-atomic and repeating attributes in a table. 2NF and 3NF describe a
determinant that doesn’t belong to the key.

Question 8

Describe BCNF

Answer 8

BCNF is the general statement that covers all non-key determinants. That means that all
attributes describe the key.

Question 9

What are the three characteristics of unnormalized tables?

Answer 9

An unnormalized table is undesirable because it has these negative characteristics:
- Unnormalized tables have data redundancies.
- Data redundancies cause modification anomalies.
- Modification anomalies make the data unreliable and hard to maintain.
the other answers (more tables, more joins, and slower performance) are drawbacks of
normalized tables. The benefits of normalization usually outweigh the drawbacks.

Question 10

What is Data Redundancy in a table?

Answer 10

Data Redundancy in a table is the same fact stated more than once.

• It’s caused by two objects sharing the same table.
• It’s undesirable because it leads to modification anomalies.

Question 11

What is the most common cause of unnormalized tables?

Answer 11

The cause of a table’s normalization problems is almost always that the table models more
than one real-world object. This causes the table to have data redundancy that results in
modification anomalies.

Question 12

What is Fourth Normal Form?

Answer 12

Fourth Normal Form says that a table may not contain two or more independent multivalued dependencies. A key point is independent. Two multi-valued dependencies that are
interrelated are not independent.

For example, a cable TV company may offer a set of channels (one multi-valued
dependency) and it may cover a set of regions (another multi-valued dependency). If it
offers the same set of channels in each of its regions, the two dependencies are
independent of each other and should not appear together in the same table.

However, if the cable TV company offers a different set of channels within each of its
different regions, then we have to know which region we’re considering before we know
which set of channels is offered. These two dependencies are not independent, and must
appear together in the same table to establish their relationship.

Question 13

What problems do 2NF, 3NF and BCNF address?

Answer 13

2NF, 3NF, and BCNF deal with problems involving functional (non-key) dependencies in
which a determinant points to a specific value.

Question 14

What problems do 4NF and 5NF address?

Answer 14

4NF and 5NF are about multi-valued

dependencies in which a determinant points to a set of values.

Question 15

How common are 5NF normalization issues?

Answer 15

Fifth Normal Form problems are rare, depend on business rules, and are seldom
encountered by database practitioners.

Question 16

What are 2NF, 3NF and BCNF concerned with?

Answer 16

2NF, 3NF, and BCNF are all concerned with non-key functional dependencies. 2NF and
3NF describe specific configurations of non-key dependencies, while BCNF is a catch-all
that addresses any other kind of non-key dependency. Some textbooks don’t distinguish
among these three normal forms and lump them all under BCNF.

Question 17

What problems are caused by Redundancy?

Answer 17

Redundancy:
- Causes modification anomalies.
- Is a maintenance headache to keep multiple copies of data synchronized.
- Requires extra storage.
Redundancy is a result of un-normalized tables, and the elimination of redundancy is the
goal of normalization.

Question 18

What conditions must a table meet to be considered normalized?

Answer 18

If all of these conditions are met, then a table is very well (though perhaps not perfectly)
normalized:
- 1NF – All attributes are atomic
- 2NF, 3NF, BCNF – The only determinants in any functional dependency are keys
- 4NF – There are not two or more independent multi-valued dependencies.

Question 19

What are some characteristics of Redundancy?

Answer 19

Repetition of a fact (including repetition of a pairing of values) is called Redundancy.

• It causes modification anomalies
• It’s caused by a non-key functional dependency.
• Which in turn occurs when a single table models more than a single object.

Question 20

Describe DKNF.

Answer 20

DKNF states that every constraint on a table is a consequence of its Domains and Keys. If
a table has a constraint that “date-of-birth” and “age” agree with each other, this constraint
cannot be enforced by either a key of the table, or the domains of the attributes.
If a table has a pair of columns that provide the same information but in different terms
(such as “date-of-birth” and “age”), this is a redundancy and the table isn’t in Domain/Key
Normal Form.

Question 21

Describe decomposition measures.

Answer 21

Correcting a normalization problem almost always involves decomposing a table into two
new tables.

• Repeating Attributes (1NF) – use the multivalued attribute patern
• Non-Key (Functional) Dependencies (2NF, 3NF, BCNF) – separate table for each
  dependency
• Two Independent Multi-valued Dependencies (4NF) – separate table for each
  dependency

Sometimes the problem is caused by an attribute being in the wrong table, and can be
corrected by simply moving the attribute into its correct table.

Question 22

What is a Multi-Valued Dependency?

Answer 22

A Multi-valued dependency means a determinant (“key”) points to a set of values rather
than to just a single value.

A multi-valued dependency occurs in the intersection table forming a many-to-many
relationship.

Question 23

What does the acronym ACID stand for?

Answer 23

ACID is an acronym for Atomic, Consistent, Isolated, Durable; these are the properties that
well behaved transactions must possess to be useful.

• Atomic means that a transaction will execute in its entirety or not at all.
• Consistent means that if the database was in a consistent state when a transaction
  started, it will still be in a consistent state when it ends.
• Isolated means that transactions will not interfere with each other.
• Durable means that a transaction will not produce results that will be inadvertently lost.

Question 24

What is a Recoverable Transaction Schedule?

Answer 24

A Recoverable Transaction Schedule is one in which no transaction commits with data
read from another uncommitted transaction.

Why is committing with data read from an uncommitted transaction unrecoverable? If the
other transaction rolls back, the committed transaction has committed invalid data and it’s
too late to roll back. There’s no way to recover!

Question 25

What is a Cascading Rollback?

Answer 25

Cascading Rollback: A transaction reads data from an uncommitted second transaction.
The uncommitted transaction rolls back, so the first transaction must roll back too because
the data it read is now invalid.

Question 26

What is a Serial Transaction Schedule?

Answer 26

A Serial Transaction Schedule has no interleaving of the queries of different transactions
(no concurrency). This is a single-transaction strategy.

Question 27

What is a Serializable Transaction Schedule?

Answer 27

A Serializable Transaction Schedule interleaves its operations in such a way that
transactions don’t interfere with each other.

• Conflict Equivalent to a serial transaction schedule.
• Conflicting operations are in the same order as in a Serial schedule.
• Transactions are correctly isolated.

In a Serial transaction schedule, on the other hand, the transactions aren’t interleaved. A
transaction starts after the previous one completes.

Question 28

What are read locks also called?

Answer 28

Shared Locks

Question 29

What are write locks also called?

Answer 29

Exclusive Locks

Question 30

What is an implicit lock?

Answer 30

An Implicit Lock is one that all SQL statements automatically set when they execute
outside a transaction. They prevent conflict with other SQL statements executing at the
same time.

Question 31

What is Two-Phase Locking?

Answer 31

Two-Phase Locking is a rule that requires each transaction to request and release locks in
two phases:
- A growing phase in which locks are established or upgraded,
- A shrinking phase in which locks are downgraded or released.
The benefit of Two-Phase Locking is that when all transactions use it, it always produces
Serializable schedules.

Question 32

Why use Two-Phase Locking?

Answer 32

The reason to use Two-Phase Locking is that it’s easy to automate, and when all transactions use it, then the transaction schedules are always Serializable.

A Serializable Schedule correctly isolates its transactions because it’s Conflict-Equivalent
to a Serial Schedule. That is, conflicting operations occur in the same order as they would
in a Serial Schedule.

Question 33

How does SQL automatically handle locking?

Answer 33

The Transaction Isolation Level is SQL’s way to automatically handle locking in
transactions.

Question 34

How do you enable automatic locking?

Answer 34

SET TRANSACTION ISOLATION LEVEL activates automatic Two-Phase Locking.
Depending on your application, you could choose the Serialiazible Isolation Level to ensure
complete isolation (serializablility) while avoiding unrecoverable schedules:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
This sets an environment variable that affects all transactions in the current session.

Question 35

What provides an up-to-the-second database backup?

Answer 35

The Transaction Log Files provide an up-to-the-second database backup. When a
change is made to a row, the query that caused the change is written into the transaction
log.

If the database is lost, it’s restored from the last full backup, then the operations in the
transaction logs are applied to the database. This restores the data to it’s state right before
the failure.

Question 36

What is Recovery Via Rollback?

Answer 36

Recovery via rollback means we use the transaction logs to undo the last transactions
from the database, undoing each transaction in the reverse order it was originally
performed.

If we discover that a transaction has corrupted the data, we can roll it back to a point before
the corruption occured.

Question 37

Do we need to make backups?

Answer 37

Even though the transaction logs save the transactions that have been applied to the
database (which acts as a real-time backup), we still need to make a periodic full backup
of the database.
During recovery, the full backup gives us a starting point to which we apply the latest
transactions from the log.

Question 38

What is the strength of a Distributed Database?

Answer 38

The Distributed Database’s strength is that the database takes care of geographically
distributing the tables on its own, without help from the application. The application never
knows that the database is geographically scattered.

Question 39

Do applications need to be specially written to use in a Distributed Database?

Answer 39

Applications don’t need to be specially written to work with a distributed database.

Question 40

What is the Two-Phase Commit Protocol?

Answer 40

Two-Phase Commit Protocol allows distributed transactions to either all commit together,
or all roll back together. The two phases are called the Prepare Phase, and the Commit
Phase.

Question 41

Describe the characteristics of a View in SQL.

Answer 41

A View in SQL is a virtual table.

• Can be used in place of a real table in SQL statements.
• Its contents are the output of a SELECT statement.
• It’s maintained by the database and needs no special attention once it’s declared.

Question 42

What is a SQL Injection Attack?

Answer 42

A SQL Injection Attack is performed by an attacker who types SQL commands into user
input fields. These user input fields are intended to send data to the database, but instead
send the attacker’s queries to the database instead.

The goal is to gain unauthorized access to the data, or to vandalize it.

Question 43

What is a good way to protect the DB from a SQL Injection Attack?

Answer 43

Using Prepared Statements protects against SQL Injection because the query in a
Prepared Statement is pre-parsed: the query engine analyzes a statement’s syntax only
once, at startup time, rather than every time a statement is sent to the database.

Therefore, the query engine doesn’t look at the syntax of the code that the attacker entered.
The attacker’s code is treated as data instead of as an executable SQL statement.

Question 44

What is a Stored Procedure?

Answer 44

A stored procedure is invoked by a call from an application’s code, or by the DB
Administrator from the command line.

Question 45

What is a Trigger?

Answer 45

A trigger is fired in response to an INSERT, DELETE, or UPDATE statement.

Question 46

What is a View in SQL?

Answer 46

A SQL View is a pre-defined query that stands between the tables and the user.

A View appears to the user as if it were an ordinary table. This extra layer of processing doesn’t
improve performance. Purposes of a view are:

• Display computed columns
• Hide complicated syntax
• Hide rows and columns
• Hide schema changes

Question 47

What is a Page in the DBMS?

Answer 47

The DBMS sees the disk divided into chunks called Pages. A Page is the smallest unit of
disk I/O and the fundamental unit of disk storage for a DBMS.

Question 48

What is the most commonly used data structure in SQL?

Answer 48

The B+ Tree is the most commonly used data structure for DBMS systems. It provides
storage for very large sets of data. Indexes using a B+ tree can be searched very rapidly
using few disk I/O operations.

The Hash Table is also used for the same purpose, but is less common because the B+
tree is more efficient for range searches.

Question 49

In an ISAM file, what is used for the index and how are the data items stored?

Answer 49

In an ISAM file, the index is a B+ tree and the data is stored in a linked list. The B+ tree is
fast to search, and putting the data into a linked list keeps the data sorted to match the
order of the leaf nodes in the tree. This makes range searches fast.

Question 50

What are the advantages of a B+ tree over a Binary Tree?

Answer 50

Fewer disk reads are required to find a value in a B+ tree than in a Binary Tree. Since many
values are stored in each node of the B+ Tree, it’s wider but less deep than a Binary Tree.
Since a disk read is required for each level while traversing a tree (because a decision
must be made at each level), fewer levels mean fewer disk reads.

Question 51

What is a Query Tree?

Answer 51

A Query Tree is the sequence of relational algebra operations in a query execution plan.
The tree structure imparts a specific sequence on the relational operations embedded
within the tree.

Question 52

What layer of the DBMS works with or knows anything about the file system?

Answer 52

The Disk Space Manager is the only layer of the DBMS that sees the file system. Higher
layers of the DBMS, even the Buffer Manager, know nothing of the hardware or operating
system lurking below.

Question 53

How many relational algebra operators are there and is the language complicated?

Answer 53

There are only five fundamental relational operators, and a few more derived ones.
Relational algebra is a simple language.

Question 54

What are scripted relational query languages like SQL based on?

Answer 54

Tuple Relational Calculus (TRC)

Question 55

What are visual query systems based on?

Answer 55

Domain Relational Calculus (DRC)

Question 56

What is the Buffer Manager and what are its functions?

Answer 56

The Buffer Manager maintains the Buffer Pool, which is the area of memory in which the
working database is kept.
It has these functions:

• Manages the part of the database residing in working memory.
• Replaces the OS’s virtual memory page swapper with it’s own buffer replacement policy.
• Decides what pages need to be written to disk.

Question 57

Why doesn’t the Query Manager just execute the first plan it comes up with?

Answer 57

It’s quicker for the Query Engine to generate many candidate execution plans and estimate
the execution cost of each one than it is to just execute the first plan it comes up with.

Question 58

What is the Buffer Manager?

Answer 58

The Buffer Manager is the DBMS layer that writes the logs used for crash recovery. It’s in
a position to control the sequence that the logs and the data are committed to the disk.
(Write the Logs first, and the Data second).
This sequence is important for committed transactions to be durable no matter at what
point in the process a failure occurs.