Normalization

Improvement over 1NF

* Eliminate update anomalies * Eliminate redundancies * Eliminate insert anomalies * Eliminate delete anomalies

Primary Key

- A field whose values are unique for each record (row) in a table. - Primary key ensures that no two records in a database contain the same value for that field. - Creating relationship between tables ---> Composed from many columns - Composed Primary Key

Foreign Key

A key field that identifies records in a different table. The foreign key is used to establish a relationship with another table or tables.

Second Normal Form

A table must have 1. First normal form 2. Every NON KEY COLUMNS must DEPEND on the entire PRIMARY KEY (data which not dependent on primary key must be moved to another table) 3. Eliminate PARTIAL KEY dependencies by creating multiple tables

Third Normal Form

A table must have 1. A No Non Key Column depends on another non key column 2. All TRANSITIVE DEPENDENCIES are eliminated (each column must depend directly on the primary key; all attributes that are not dependent upon the primary key must be eliminated) "If A determines B, and B determines C, then A determines C."

First Normal Form

A table must have 1. An ATOMIC VALUE value in each intersection 2. Eliminate REPEATING GROUP in a each RECORD 3. DESIGNATED PRIMARY KEY

Unique/Candidate/AlternateKey

Combination of attributes that can be uniquely used to identify a database record without any extraneous data * used strictly for data retrieval purpose

Entity Relationship

Conceptual model that shows the structural organization of entities, relationship, and attributes

Fourth Normal Form

FORBIDS INDEPENDENT relationship between primary key columns and non key columns

Normalization is a process refining table, keys, and columns, and relationships to create an efficient database

Normalization usually involves - Dividing a database into two or more tables - Defining relationship between the tables

Many to Many

One instance of entity A is associated with many instances in entity B

One to One

One instance of entity A is associated with one other instance of another entity B * Row in one table correspond to one row in another table * Least frequently encountered * Tables could be combined into a single table * Keep tables separate for security purposes

One to Many

One instance of entity A is associated with zero, one or many instances of another entity b there is only one instance of entity a * Most common * One side in the base table and Many side in Related table

A repeating group is

SUBSET OF ROWS in a table all DEPEND on the same key

Many to Many relationship should be eliminated through the addition of a

bridge table or an association table which result in one to many relationship Student - Game table StudentID - FK GameID - FK StudentID and GameID is a composite primary key