Lesson 4 Flashcards
What is essential to a good database design?
Reduces Redundancy
Provides access
Ensures data accuracy and integrity
Accomodates your data processing and reporting neeeds
Define the purpose of the database
- Determine its objective
-determine what kind of infomraton you need to record into the database by sampling queries and what results you want from the query
- gather the inforomation you needm divide them into individual tables
- determine what the columns (fields) of each table will be
- Design Process
- Make sure each row has a unique key
- Must have a value! Can never be null
- Keep it simple if you can
- Use an integer type primary key
- Figure out the Primary Key for each table
Types of Cardinality
- One to One
- One to many
- Many to many
In a relationship, only one row of a table is linked to at most one row on the other table. In this case, a ___ relationship is useful to store data that is optional
One to One
In a ___ relationship, one row of one table can link to many rows in a table.
The advantage of a ___ relationship is to store the frequently used information in the parent table (the table with “one”), whiel referencing this information many times in the child table (the table with “many”). In the child table, there should be a foreign key, which is the parent table’s primary key
One to many
In ___ relationship, one or more rows of one table can link to 0,1 or many rows in the other table. To implement this relationship, we must use a mapping or intermediary or junction table.
Many to many
- Do you have enough columns to represent your data.
- Could you derive a column from a combination of columns
- Are you entering duplicate information
- Do you have any empty fields in individual records
Can a large table be split into row
- Refine the design
What are the Normalization Rules
First Normal form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
A table follows ____ if for every row and column intersection (a cell in a table) in the table, there exists a single value and never a list of values. This is known as the atomic rule. Use the one to many relationship to follow this.
First Normal Form (1NF)
A table follows ____ if it is 1NF and every non-key column is fully dependent on the primary key. This also applies when a primary key has multiple columns, every non-key column should still depend on the entire set and not part of it
Second Normal Form (2NF)
A table is ___ if it is 2NF and the non-key columns are independent of each other
Third Normal Form (3NF)
The primary key cannot contain NULL! Otherwise, it cannot uniquely identify the row. This includes for multiple column primary key as well, none of the columns can be NULL
Entity Integrity Rule
Each foreign key valye must be matched to a primary key value in the table referenced (or parent table). In turn, this means that you can only insert a row with a foreign key in the child table only if that value (of the foreign key) exist in the parent table.
Referential Integrity Rule
These are validation rules to ensure for correct values (5 digit zip codes, validating credit card number)
Business logic Integrity
