Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
43 Cards in this Set
- Front
- Back
|
What are the characteristics of relations
|
Rows contain data about an entity
Columns contain data about attributes of the entity All entries in a column are of the same kind Cells of the table hold a single value The order of the columns is unimportant The order of rows is unimportant no two rows may be identical |
|
|
What are the terms for rows and columns in a relation
|
Tuples and attributes respectively
|
|
|
What is a functional dependency
|
When the value of one or more attributes determines the value of another attribute
|
|
|
What is a determinant
|
When a functional dependency determines the value of another
|
|
|
What is a composite determinant
|
When two or more attributes are a determinant for another attribute
|
|
|
What is a key
|
A combination or more columns that is used to identify particular rows in a relationship
|
|
|
What is a composite key
|
A key that has two or more columns
|
|
|
What is a candidate key
|
A determinant that determines all of the other columns in a relation
|
|
|
What is a primary key
|
A candidate key that the DBMS will use as it's primary means for finding rows in a table
|
|
|
How many primary keys are there per table
|
only one
|
|
|
How many columns can a primary key be?
|
As many as it needs
|
|
|
What do you do if there are no candidate keys?
|
Declare the whole table a primary key
|
|
|
What is a surrogate key?
|
An artificial column that is added to the table to serve as the primary key.
|
|
|
What is the preferred data type of primary keys
|
Numeric
|
|
|
What is a foreign key
|
A column or composite of columns that is the primary key of a table other than the one in which it appears
|
|
|
What is a referential integrity constraint
|
A statment that limits the values of the foreign key
|
|
|
What is a deletion anomaly
|
When you delete a row and it causes you to permanatley lose data
|
|
|
What is an insertion anomaly
|
When you are forced to insert data into a row that you do not know
|
|
|
What is an update anomaly
|
When you make an error in the updates and it is a mistake, and it is hard to go back and fix the errors
|
|
|
How do you get to 1NF
|
Put each data on it's own layer
|
|
|
How do you get to 2NF
|
Put it in 1NF and make each table's primary key determine the entire table
|
|
|
How do you get to 3NF
|
Put it in 2NF and haven no non-key attributes determine another non-key attribute
|
|
|
How do you get to BCNF
|
Put it in 3NF and make every determinant a candidate key
|
|
|
What is a non-prime attribute
|
An attribute that is not contained in any candidate key
|
|
|
What is the process to putting a table in BCNF
|
1. Identify every functional dependency
2. Identify every candidate key 3. If there is a functional dependency that has a determinant that is not a candidate key A. Move the columns of that functional dependency into a new relation B. Made the determinant of that functional dependency the primary key of the new relation C. Leave a copy of the determinant as a foreign key in the original relation D. Create a referential integrity constraint between the original relation and the new relation 4. Repeat step 3 until every determinant of every relation is a candidate key |
|
|
How do you model for a 1:N parent optional relationship
|
Specify FOREIGN KEY constratint, set foreign key to null
|
|
|
How do you model for a 1:N parent required relationship
|
Speicfy FOREIGN KEY constraint set foreign key to NOT NULL
|
|
|
how do you model for a 1:1 parent optional relationship
|
Specify FOREIGN KEY constraint. Specify foreign key UNIQUE constraint. Set foreign key to NULL
|
|
|
How do you model for a 1:1 parent required relationship
|
Specify FOREIGN KEY constraint. Specify foreign key UNIQUE constratint. Set foreign key to NOT NULL
|
|
|
how do you model for a Casual Relationship
|
Create a foreign key column, but do not specify a FOREIGN KEY constraint.
|
|
|
What is the action on the parent on insert when parent is required
|
none
|
|
|
What is the action on the child on insert when parent is required
|
Get a parent
|
|
|
What is the action on the parent on update when parent is required
|
Change the children's foreign key value to match new values (Cascade update) or prohibit
|
|
|
What is the action on the child on insert when parent is required
|
Ok if the new foreign key value matches existing parent or prohibit
|
|
|
What is the action on the parent on delete when parent is required
|
Delete Children (Cascade Delete) or prohibit
|
|
|
What is the action on the child on delete when parent is required
|
none
|
|
|
What is the action on the parent on insert when child is required
|
Get a child or prohibit
|
|
|
What is the action on the child on insert when child is required
|
None
|
|
|
What is the action on the parent on update when child is required
|
Update the foreign key of at least one child or prohibit
|
|
|
What is the action on the update on update when child is required
|
If not last child OK, if last child prohibit or find a replacement
|
|
|
What is the action on the parent on delete when child is required
|
None
|
|
|
What is the action on the child on delete when child is required
|
If not last child OK, if last child prohibit or find a replacement
|
|
|
What does the Check constraint do and how do you implement it
|
It checks the insert against the provided dataset
CHECK (DATA IN ('Data', 'Data',' 'Data')) CHECK (Data > Data) |
