Chapter 7: Relational Database Design

Question 1

What is the data format of OLTP?

Answer 1

Make sure updates to database are minimal

Question 2

What is the data format of OLAP?

Answer 2

Make sure to keep joins and combining of data to a minimum

Question 3

What is ETL and what does it do?

Answer 3

ETL: Extract, Transform, & Load

Format of data is transformed and loaded into another database (OLTP -> OLAP)

Question 4

What makes a good OLTP table?

Answer 4

1. Table is about one thing
2. The table has a key column(s) and all other columns depend on/describe the key column
3. The table is in BCNF (Boyce-Codd Normal Form) or 3NF (Third Normal Form): table does 1 and 2

Question 5

When does possible repetition of information occur?

Answer 5

When you combine two tables that do not have the same PK, they describe two different things.

Question 6

What is a block and how does it affect tables?

Answer 6

A block is a specific data amount that the computer reads off of the disk at a time, computers read in blocks.
You want a row of data within a table to be within the size of a block (as defined by the OS)

Question 7

How are good tables designed? Describe the diagram from class.

Answer 7

Take in a
-1: table schema R
-2: closure of functional dependencies
and then
-I. check if R is good based on the FDs, if it is then pass it on otherwise
-II. decompose R into R1 and R2 and repeat steps 1 and 2 until entire table is processed
lastly check by
-joining all sub-tables made by decomposition
-if the produced table is identical to R then you have made a good table and achieved Lossless join decomposition

Question 8

What is a Functional Dependency?

Answer 8

• if one or more attribute is dependent on another
• all intricate relationships between columns
• is a fundamental concept behind the concept of keys

Question 9

When does lossy decomposition occur and what can it result in?

Answer 9

Lossy decomposition occurs when a table is split into sub-tables on a column that wasn’t a PK
-can result in lost data or duplicated data when tables are joined together again

Question 10

What are the validation questions for checking if a schema abides by BCNF and 3NF?

Answer 10

BCNF/3NF
1. is the functional dependency trivial
2. is the LHS a SK. purpose: “is table about one thing” or (LHS)+ = R
3NF
3. is each individual attribute in the RHS individually a part of a CK. purpose: since it is identified by a CK, then the element is identified by a key in that table and is recognized as a valid member of that table.
-condition 3 implies that a non-key determines other attributes in the table

Question 11

What are some uses of a functional dependency?

Answer 11

• test relations to see if they are legal under a given set of functional dependencies
• specify constraints on the set of legal relations

Question 12

What is a trivial FD?

Answer 12

A functional dependency that is satisfied by all instances of a relation
(AB -> A or A -> A)

Question 13

Explain the family diagram of how to decompose schema R into R1 and R2?

Answer 13

When a table contains a secondary relationship, where there is a dependency, remove that dependency and make a new sub-table, but keep the PK of the sub-table in the original table, where it is now a FK in the original table.
R ->
R1 = (a, b)
R2 = (R-b+a)

Question 14

What form allows for dependency preservation?

Answer 14

3NF

Question 15

What makes 3NF different than BCNF?

Answer 15

3NF adds an extra condition where if non-SK dependent attributes in a schema are individually apart of a CK then because they depend on a key they are valid.

Question 16

What is the purpose of normalization?

Answer 16

To form good tables, different forms have different requirements for how a table is structured. This is why BCNF and 3NF exist.

Question 17

What is dependency preservation?

Answer 17

All FDs in the original table can be verified in single schemas after decomposition.

Question 18

What are Armstrong’s Axiom’s?

Answer 18

Reflexivity/Reflexive Axiom, Augmentation Axiom, Transitivity Axiom

Question 19

What is the Reflexivity/Reflexive Axiom?

Answer 19

If a is a subset of b or vice-versa, a -> b.

this is important because it says a relationship is trivial (AB -> A)

Question 20

What is the Augmentation Axiom?

Answer 20

if a -> b then ya -> yb

Question 21

What is the Transitivity Axiom?

Answer 21

if a -> b and b -> c => a ->c

Question 22

What are the axioms derived from armstrong’s?

Answer 22

Union axiom, decomposition axiom, and pseudotransitivity axiom

Question 23

What is the Union Axiom?

Answer 23

If a -> b holds and a -> c holds, then a -> bc holds.

Question 24

What is the Decomposition Axiom?

Answer 24

If a -> bc holds, then a -> b holds and a -> c holds.

Question 25

What is the Pseudotransitivity Axiom?

Answer 25

If a -> b holds and yb -> g holds, then ay -> g holds.

Question 26

Describe the Closure of Attribute Sets algorithm?

Answer 26

1. set result = a
   - Armstrong’s axiom’s guarantee sound & complete
   - based on reflexivity axiom (minimally determines itself)

Loop

2. If Ln is in “result” then Rn should be in “result” so put Rn in “result”
   - based on transitivity axiom. a -> ABC, if A is in result then A -> y should also be in result

Question 27

How do you get F+ from F?

Answer 27

1. find all possible combinations for LHS
2. run CAS on each of 1
3. for each RHS, find all possible combinations.

Question 28

What is a canonical cover?

Answer 28

the “minimal” set of functional dependencies equivalent to F, having no redundant dependencies or redundant parts of dependencies

Question 29

What is the easiest way to justify a key?

Answer 29

To run CAS on it, a -> a+

Question 30

Why are candidate keys more important than super keys?

Answer 30

CKs are more useful than SKs because CKs are minimal SKs, adding onto CKs makes a SK.

Question 31

How do you determine a SK?

Answer 31

Using the given functional dependencies, derive all other functional dependencies, and if one key is able to determine all members of the set then it is a SK.
-derivations are easiest done through axioms

Question 32

How do you identify a CK?

Answer 32

1. Find all given combinations of R to get all LHS
2. Run CAS on all possible LHS and find if a closure of a key produces R. If the key size > 1 then proceed to step 3
3. make subsets of non-minimal SKs and run CAS on them & if their subsets’ closure’s do not return R then it is a CK
   * *If a single element is found to be a CK then all other keys that contain that element in addition to other elements cannot be CKs**

Question 33

What conditions at the end of the design process are required and what are preferrable?

Answer 33

Required:
1. BCNF
2. LLJD
Preferable:
3. Dependency Preservation