Week 10

Question 1

Normalization

Answer 1

• 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

Question 2

Two purpose of normalization

Answer 2

• eliminate redundant data: splitting
  tables with redundant data into two or more tables without the redundancy
• Ensure the data within a table are related

Question 3

problems with unnormlized data

Answer 3

• 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

Question 4

Normal Forms

Answer 4

• Normalization involves the process of applying rules called
  normal forms to table structures that produce a design
  that is free of data redundancy problems

Question 5

DBDL

Answer 5

• DataBase Definition Language
• standardized way of describing entities
  in a relational database in a written format

Question 6

1NF

Answer 6

• 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

Question 7

2NF: Partial Dependencies

Answer 7

• builds on 1NF by eliminating partial functional dependencies
• All non-key attributes are fully functional and dependent on the primary key

Question 8

2 variations of Partial Dependencies

Answer 8

• An attribute partially dependent on a
  composite primary key
• An attribute dependent on more than one
  field, but only a single field PK exists

Question 9

Surrogate Keys

Answer 9

• 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.

Question 10

3NF: Transitive Dependencies

Answer 10

• 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

Question 11

Functional dependency

Answer 11

• 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

Question 12

derived field

Answer 12

• not directly observed but rather is derived from one or more existing fields

Question 13

direct field

Answer 13

• contain data that is entered or captured directly and does not depend on other fields for its value

Question 14

UNF

Answer 14

• basic form simple utilized for obtaining and grouping attributes that are required. Repeating groups of data are typically indicated using parenthesis()

Question 15

Partial Dependency

Answer 15

• 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

Question 16

Transitive Dependencies

Answer 16

• an attribute depends on another non-pk attribute in the same entity