Chapter 7: Relational Database Design Flashcards
What is the data format of OLTP?
Make sure updates to database are minimal
What is the data format of OLAP?
Make sure to keep joins and combining of data to a minimum
What is ETL and what does it do?
ETL: Extract, Transform, & Load
Format of data is transformed and loaded into another database (OLTP -> OLAP)
What makes a good OLTP table?
- Table is about one thing
- The table has a key column(s) and all other columns depend on/describe the key column
- The table is in BCNF (Boyce-Codd Normal Form) or 3NF (Third Normal Form): table does 1 and 2
When does possible repetition of information occur?
When you combine two tables that do not have the same PK, they describe two different things.
What is a block and how does it affect tables?
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)
How are good tables designed? Describe the diagram from class.
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
What is a Functional Dependency?
- if one or more attribute is dependent on another
- all intricate relationships between columns
- is a fundamental concept behind the concept of keys
When does lossy decomposition occur and what can it result in?
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
What are the validation questions for checking if a schema abides by BCNF and 3NF?
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
What are some uses of a functional dependency?
- test relations to see if they are legal under a given set of functional dependencies
- specify constraints on the set of legal relations
What is a trivial FD?
A functional dependency that is satisfied by all instances of a relation
(AB -> A or A -> A)
Explain the family diagram of how to decompose schema R into R1 and R2?
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)
What form allows for dependency preservation?
3NF
What makes 3NF different than BCNF?
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.
What is the purpose of normalization?
To form good tables, different forms have different requirements for how a table is structured. This is why BCNF and 3NF exist.
What is dependency preservation?
All FDs in the original table can be verified in single schemas after decomposition.
What are Armstrong’s Axiom’s?
Reflexivity/Reflexive Axiom, Augmentation Axiom, Transitivity Axiom
What is the Reflexivity/Reflexive Axiom?
If a is a subset of b or vice-versa, a -> b.
this is important because it says a relationship is trivial (AB -> A)
What is the Augmentation Axiom?
if a -> b then ya -> yb
What is the Transitivity Axiom?
if a -> b and b -> c => a ->c
What are the axioms derived from armstrong’s?
Union axiom, decomposition axiom, and pseudotransitivity axiom
What is the Union Axiom?
If a -> b holds and a -> c holds, then a -> bc holds.
What is the Decomposition Axiom?
If a -> bc holds, then a -> b holds and a -> c holds.
What is the Pseudotransitivity Axiom?
If a -> b holds and yb -> g holds, then ay -> g holds.
Describe the Closure of Attribute Sets algorithm?
- set result = a
- Armstrong’s axiom’s guarantee sound & complete
- based on reflexivity axiom (minimally determines itself)
Loop
- 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
How do you get F+ from F?
- find all possible combinations for LHS
- run CAS on each of 1
- for each RHS, find all possible combinations.
What is a canonical cover?
the “minimal” set of functional dependencies equivalent to F, having no redundant dependencies or redundant parts of dependencies
What is the easiest way to justify a key?
To run CAS on it, a -> a+
Why are candidate keys more important than super keys?
CKs are more useful than SKs because CKs are minimal SKs, adding onto CKs makes a SK.
How do you determine a SK?
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
How do you identify a CK?
- Find all given combinations of R to get all LHS
- 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
- 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**
What conditions at the end of the design process are required and what are preferrable?
Required: 1. BCNF 2. LLJD Preferable: 3. Dependency Preservation
