Ch4/w4 Flashcards
compoennts of relational model
data strcutue
data manipulation
data integrity
data strcurue
tables & keys
data manipulation
what are the sql operations to retrieve or modofy data
data integirty
mechanisms for implementing business rules that maintain integrity of data
whaat is a relation
named 2D data!
rows (Records) columns(Attributes/fields)
domain constraints
constrain allowable value for an attribute
entity integ constraitns
all PK not Null and unique
referential integrity constirans
rules that maintian consitency between the rows of 2 related tables
what is referential integirty
if a FK is not null, then the insance it poitns to MUST exist
3 approaches to ref integ
1, restrict: dont allow parent side if related drows exist inn dep side
2. cascade: auto delete dep that corespon with parent to be deleted
3. set to null: set fk to null if parent side deleted
how ar eref integrity constraints dwan?
via arrows forom DEPENDANT TO PARENT TABLE
Binary relations mapping
1:M
1:1
M:N
1:M: pk on one side becomes fk on nany side
1:1 pk on mandatory becomes fk on optional
m:n: create a new relation with pk of 2 ents as pk
associate ents mapping
- id not assigned
- id assigned
not assigned: Def PK for the association relation is pk of 2 ents
assigned: natural and pfamiliar to end users
Unary relationships
1:M
M:M
1:M- recursive fk in the same relation
M:M one for ent type on eofor associative relation in which the PK has 2 attributes
mpaping ternanry
associate ent has a fk to each ent in relatioship
mapping generlaizaiton/specializaiton
supertype/subtype to realitons
take the pk of the highest ent and then use the first letter and then create unique ids
what is data nromalizaiton
tool to validate and improve logical design TO AVOID DUPLICATION! AN DBE WELL STRUCTURED
What is a well structured relations
- min data redundancy, allow users to insert, delete and update rows w/o causing data inconsistenciesg
goal of well structured relations: avid anomalies (3 types)
insertion: adding new rows forces user to crate duplicate data
deletion: deleting rows may cause a loss of data
modificaiton: changing data forces changes oto ther rows because of duplciation
tabble w/mv attribute
–remvoe MV
first normal form
– remove partial depend
second normal form
– remove transitive dpeend
third nomal form
– remove remainng anomalies from multiple cand kess
boyce-codd
– remove mv depend
fourht normal form
– remove any left anomalies
fifth normal for
–
multivalues
partial depend
transitive depend
remaiing anomalies
multive depend
remaining anomalies
mptrmr
mp trudeau ran more runs
what is a functional dependncy
value of one attirbute determines the value of another attirbute
what is a candidate key
unieuq identifier (a candidate to be the PK!)
each non ey field is functionally dependant on every can key!
no transitive depends
no functional depeds on no pk attirbutes! the pk is a determinant for another attribute whic in tur determinant for a 3rd
The attribute on the left-hand side of the arrow in a functional dependency is the:
DETERMINANT!!!
DETERMINANT –> DEPENDANT
