Final Flashcards
(23 cards)
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
Composite Key
A primary key composed of one or more columns
Natural Key
A composite primary key which is composed of attributes (fields) which already exist in the real world
Surrogate Key
A primary key which is internally generated (typically auto-incremental integer value) that does not exist in the real world. IE personId
Candidate Key
A candidate key is a column or group of columns that can uniquely identify a row in the table without referring to any other source. One of these is selected as Primary Key
Prime Attribute is
An attribute that is attribute(s) that is a subset of a key.
Superkey is
Attributes that are a superset of a key
How do you identify 3NF?
A relation schema R is in 3NF if, whenever a functional dependency X -> A holds in R, either
(a) X is a superkey of R, or (b) A is a prime attribute of R.
Subkey
One or several attributes that determine some but not all the attributes in a row
