relational database Flashcards
what are relations made up of
an instance and a relation schema
instance
a table with rows and columns
relation schema
specifies the relations name and type of each column
cardinality
number of rows
degree/arity
number of fields/attributes
contraints
logical rules that establish the integrity of data
integrity constraints
a condition that must be true for any database instance
legal instance
satisfies all specified integrity constraints
what are the 3 types of integrity contstriants
domain constraints
entity integrity constraints
referential integrity
domain constraints
the value of the attribute must be an element of the attributes domain
entity integrity constraint
a key value cannot be duplicated or left empty
referential integrity
connects tables together through a foreign key
what is the role of a foreign key
a peripheral attribute that creates referential integrity between entity sets
in which two ways can foreign keys be made
importing the primary key from one table to another
creating a new table that holds the primary key of both tables
what are some constraints for foreign keys
they must have the same name and domain type as the referencing relation
related entities must have the same value
how do we derive a foreign key for 1:1 relationships
import the primary key from one table to the other and declare it as a foreign key
what is the difference between deleting a tuple using SET DEFAULT and SET NULL
default; removes the current foreign key and leaves a placeholder
null; completely removes the relationship
how do you delete an entity from a referenced table
get the primary key value of the table
find all the tuples with the fk in the referencing table and select them
set the fk to the default value or null (if null delete all tuples in the referenced table)
how do you derive a fk 1:1 both partial participation
pk from one table becomes the UNIQUE fk for the other
how do you derive a fk 1:1 both total participation
can combine into a single table and include all attributes choosing which pk to stay as the pk
or you can keep them separate and make one the NULL and UNIQUE fk
how do you derive a fk 1:1 only one total participation
add the not null and unique fk to the totally participating side
on delete cascade/reject
what is the difference between on delete cascade and reject
cascade; when removing the fk in its table you have to remove the related entity
reject; you cannot remove the fk as there is a related entity
how do you derive a fk 1:M both total participation
combine the relationship with the many entity set
the pk from the 1 side becomes the fk (! null)
how do you derive a fk M:N both total participation
create a relationship set and create the schema for it
use both pk to create a composite primary key
must delete on cascade
composite primary key
two pk joined together that allows for combinations
how do you derive a fk from a weak entity set
combine the relation with the weak entity and use the string pk as the fk
delete on cascade
