1
Q
Normalization
A
- series of steps used to evaluate and
modify table structures to ensure that every non-key column in every table is directly dependent on the
primary key - ensure that the database structure is capable of evolving in a controlled manner over time
- aims to rearrange the database to avoid these kinds of indirect or partial relationships, making the database more straightforward and efficient to use
- results: reduced redundancies, fewer anomalies, improved efficencies
2
Q
Two purpose of normalization
A
- eliminate redundant data: splitting
tables with redundant data into two or more tables without the redundancy - Ensure the data within a table are related
3
Q
problems with unnormlized data
A
- Update Problem:
performing the same update in several locations bc of the same data is repeated - Data Inconsistency Problem:
When the same data is repeated in several records, they can be inconsistent - Data Redundancy Problem:
unnecessary repetition of data in the database of non-key fields - Insert Problem
- Deletion Problem
4
Q
Normal Forms
A
- Normalization involves the process of applying rules called
normal forms to table structures that produce a design
that is free of data redundancy problems
5
Q
DBDL
A
- DataBase Definition Language
- standardized way of describing entities
in a relational database in a written format
6
Q
1NF
A
- two dimensional table format
- no repeating group
- primary key is identified
- contains all the features of UNF plus the elimination of multi-value attributes or non-atomic fields
7
Q
2NF: Partial Dependencies
A
- builds on 1NF by eliminating partial functional dependencies
- All non-key attributes are fully functional and dependent on the primary key
8
Q
2 variations of Partial Dependencies
A
- An attribute partially dependent on a
composite primary key - An attribute dependent on more than one
field, but only a single field PK exists
9
Q
Surrogate Keys
A
- replacement key for what was the
original fields and therefore, those original fields must be used
for the dependency analysis. - If a surrogate key is introduced too
early, then determining partial and transitive dependencies is
greatly complicated. - Don’t introduce the surrogate keys to
replace composite keys until after normalization has been
completed.
10
Q
3NF: Transitive Dependencies
A
- builds on 2NF by eliminating remaining transitive function dependencies
- Non-key attributes are not dependent on other non-key attributes
- every non-key column must depend directly on the primary key
- further reduces redundancy and ensures that each piece of information is stored only once
11
Q
Functional dependency
A
- occurs when one or more attributes in a table uniquely determines another attribute
- product_id → prod_desc
- whse_id, product_id → bin, qty
- whse_id → whse_address, city, prov, pcode
12
Q
derived field
A
- not directly observed but rather is derived from one or more existing fields
13
Q
direct field
A
- contain data that is entered or captured directly and does not depend on other fields for its value
14
Q
UNF
A
- basic form simple utilized for obtaining and grouping attributes that are required. Repeating groups of data are typically indicated using parenthesis()
15
Q
Partial Dependency
A
- a non-key column is
dependent on part of the primary key but is not dependent on the entire primary key - [product_id, whse_id, product_desc, bin,qty, whse_address, city, prov, pcode]
-The prod_desc column is dependent on the product_id key but is not determined by the whse_id key.
- The whse_address column is dependent on the whse_id key but is not related to the product_id key
16
Q
Transitive Dependencies
A
- an attribute depends on another non-pk attribute in the same entity
17
Q
A
