Chapter 7: ER Diagrams Flashcards
Define design phases
1) chracterize fully needs of users
2) choose data model, apply concepts of chosen data model, translates requirements into conceptual schema of database
3) also indicates functional requirements of enterprise
what are the two final design phases?
logical design: deciding on the database schema, require that we find “good” collection of relation schemas
business decision: what attributes to be recorded
CS decision: what relation schemas, how should attributes be distributed
physical design: deciding on physical layout of the database
ER modeling
database can be modeled as collection of entities, relationship among entities
entity: object that exists and is distinguishable from other objects
attributes: domain of an attribute is the set of permitted values
entity set: set of entities of the same type that share same properties (set of person, companies, trees, etc).
what can the attribute also be?
it can ALSO be property of a relationship set; meaning that instructor student may be related, but the advisor relationship may have date which tracks down when they first met up
DATE is a property of the relationship set
define binary relationship
between two entity sets, most relationship sets are binary, in fact
what are cadinality constraints?
one to one, one to many, many to one, many to many
define super key vs candidate key vs primary key
super key: set of one or more attributes whose values uniquely determine each entity
candidate key: MINIMAL super key (think social security number, as compared to NAME+SSN+ADDRESS)
primary key: one candidate key selected
keys for relationship sets: what forms a super key of a relationship set?
combination of primary keys of participating entity sets; a pair of entity sets can have at most one relationship in a particular relationship set
define a weak entity set
one whose existence is dependent on another entity, called the IDENTIFYING entity; instead of associating a primary key with a key entity, we use the identifying entity, along w/ extra attributes called discriminator to uniquely weak entity
define total v. partial participation
total participation: indicated by dbl line, every entity in set participates in at least one relationship in the relationship set
EVERY student must have an associated instructor
partial participation: single line, that is next to dbl line, don’t have to participate in any relationship
how do we express weak entity sets?
we depict it via double rectangle; and we underline discriminator of weak entity set w/ dashed lined
relationship set connecting weak entity set to identifying strong entity set is depicted by double diamond
primary key for section = (PRIMARY KEY IN COURSE, discriminators)
how do we represent entity sets as tables
strong entity sets do not change
weak entity sets now have a column for the primary key of the identifying entity set
how do we represent relationship sets
many-to-many relationship set represented as schema w/ attributes for the primary keys of the two participating entity sets
advisor = (s_id, i_id)
how do we represent many to one/vice versa sets that are total on the many side
add extra attribute to the many side, containing primary key of the one side
total means that they are NOT weak; look at slide 36
what happens if participation is partial on the many side?
replacing schema by an extra attribute in the schema corresponding to the many side results in null values
