Databases

What is the difference between data and information? 

• Data - random content, no context
• Information - data within a context, data with MEANING

Database Anatomy

• Table
• Columns - topics/headers/descriptors of data
• Field
• Rows - individual instances of data
• Records

Primary key - unique identifies (each record must be unique)

Foreign Key - relating information in other table

Table Relationships

One to one - Both tables can have only one record on either side of the relationship. Each primary key value relates to only one (or no) record in the related table. Most one-to-one relationships are forced by business rules and don't flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.

       -e.g. Spouses are 1 to 1 records. You can only have 1 spouse.

One to many - The primary key table contains only one record that relates to none, one, or many records in the related table.

       -e.g. Parent-child relationships are one-to -many, as parents can have many children

Many to many - Each record in both tables can relate to any number of records (or no records) in the other table. Many-to-many relationships require a third table, known as a bridge table, because relational systems can't directly accommodate the relationship.

         -e.g. Students can take many courses, and courses can have many students

Integrity Rules

Entity Integrity - Each entity has a unique key

Referential Integrity - Foreign key value is null or matches primary key values in related table

Database Efficiency

Normalization  - reduces repetitive entries an makes the design and structure of the database as efficient as possible. There are multiple levels to this normalization (1 NF, 2 NF, 3 NF)

1 NF 

• Eliminates duplicative columns from the same table.
• Identifies each row with a unique column (the primary key
• Steps
• Eliminate repeating groups
• Identify primary key
• Identify all dependencies

2 NF

• It is in 1 NF
• There are no partial dependencies
• Steps
• Start with 1 NF formula
• Write each key component w/ partial dependency) on separate line
• Write original (composite) key on last line
• Each component is new table
• Write dependent attributes after each key

3 NF

• It is in 2 NF
• There are no transitive dependencies
• Steps
• Start with 2NF format
• Break off the TP pieces and create separate tables