Final Flashcards
Insertion anomaly
when certain attributes cannot be inserted into the database without the presence of other attributes
Deletion Anomaly
when certain attributes are lost because of the deletion of other attributes
Modification/Update Anomaly
when one or more instances of duplicated data is updated, but not all. IE the zip code for an address changes but only some of the users get the updated zip code because the zip codes are stored redundantly
Functional Dependancy
a relationship that exists when one attribute uniquely determines another attribute
Armstrong’s Rules
Reflexivity: if Y is a subset of X, then X –> Y
Augmentation: If X–>Y then XZ–>YZ
Transitivity: If X–>Y and Y–>Z then X–>Z
Example of Reflexivity
{phone} is a subset of {sid, phone, fname, etc..}
Therfore {sid, phone, fname, etc…} –> {phone}
Example of Augmentation
You can add an attribute to both sides of an FD
if sid -> major
then sid, fname -> major, fname
Doesn’t matter what order we write these in because they are sets (unordered)
Union Rule
if X determines Y and X determines Z then X must also determine Y and Z (see Figure 11.4).
if X->Y and X->Z then X->YZ
Decomposition Rule
if X determines Y and Z, then X determines Y and X determines Z separately
if X->YZ then X->Y and X->Z
What is a view?
A view is a named query that can be used like a table
How is a view created in SQL?
CREATE VIEW viewName AS
“SQL STATEMENT HERE”
How are views stored?
In pre-compiled form
What is a role?
It’s similar to a user category and used to allow or restrict actions on a table.
Primary Key
one or more fields that uniquely identifies a row in a table. Minimum amount of attributes used
Foreign Key
a relationship between columns in two database tables
