The 5 steps in building a database for an application
1. Understand the real-world domain; 2. specify it with a conceptual model (ER); 3. translate to the DBMS model (relational); 4. create the schema (DDL); 5. load data (DML).
In the database design steps, logical design vs physical design
Logical: how data will be structured logically (entities, attributes, keys, relationships). Physical: how data will be stored (storage, performance).
Why work through conceptual problems in the data model?
A data model is more generalized and abstract than a database design, and it is easier to change a data model than a database design.
The three basic concepts of the ER model
Entity sets, relationship sets, attributes.
Entity vs entity set
Entity: an object that exists and is distinguishable from other objects. Entity set: a set of entities of the same type that share the same properties.
Relationship set (definition)
A mathematical relation among n ≥ 2 entities, each taken from entity sets: {(e1, …, en) | e1 ∈ E1, …, en ∈ En}.
Can a relationship set have attributes? Example?
Yes — advisor may have the attribute date (when the student started being associated with the advisor).
proj_guide: what degree, between which entity sets?
Ternary — between instructor, student and project.
The four mapping cardinalities of a binary relationship set
One to one, one to many, many to one, many to many.
Directed line (→) vs undirected line (—) in an E-R diagram
→ signifies "one"; — signifies "many".
advisor one-to-many (instructor to student): what does it allow?
An instructor is associated with several (including 0) students; a student with at most one instructor.
Composite vs simple attribute
Composite: can be subdivided to yield additional attributes (address, phone number). Simple: cannot be subdivided (age, gender, marital status).
Best way to handle a multivalued attribute
Create a new entity composed of the original multivalued attribute's components (several new attributes can cause structural problems).
Derived attribute (with examples)
An attribute whose value is calculated from other attributes — age from date of birth; total cost = quantity ordered × unit price.
In an entity box, what do { phone_number } and age ( ) mean?
{ phone_number }: a multivalued attribute. age ( ): a derived attribute.
The two conditions for a weak entity
1. It is existence-dependent on its parent entity. 2. It inherits at least part of its primary key from its parent entity.
Existence independence
The entity exists apart from all of its related entities — a strong (regular) entity.
How is a weak entity set drawn?
A double rectangle; its discriminator is underlined with a dashed line; the identifying relationship set is a double diamond.
Rectangle, diamond, underline in an E-R diagram
Rectangle: entity set (attributes inside). Diamond: relationship set. Underline: primary key attributes.
What are roles in a relationship?
Labels for the part each occurrence of an entity set plays when the entity sets need not be distinct — in prereq, "course_id" and "prereq_id".
Naming rule for entity names
Descriptive of the objects, using terminology familiar to the users, and singular.
Total vs partial participation
Total (mandatory, minimum 1, double line): every entity participates in at least one relationship. Partial (optional, minimum 0): some entities may not participate.
Chen l..h: what do minimum 1, maximum 1 and maximum * mean?
Minimum 1: total participation. Maximum 1: at most one relationship. Maximum *: no limit.
Crow's foot: oval vs vertical hash mark
Oval: minimum 0 (optional participation). Vertical hash mark: minimum 1 (mandatory participation).
How is a many-to-many relationship set reduced to a schema?
A schema with the primary keys of the two participating entity sets and any descriptive attributes — advisor = (s_id, i_id).
Weak entity set → schema
A table that includes a column for the primary key of the identifying strong entity set — section(course_id, sec_id, sem, year).
Multivalued attribute M of entity E → schema
A separate schema EM with the primary key of E and an attribute for M; each value is a separate tuple — inst_phone = (ID, phone_number).
Where does the foreign key go for a one-to-many relationship that is total on the many-side?
In the "many" side: add an attribute holding the primary key of the "one" side (dept_name in instructor).
One-to-one relationship set: which side gets the extra attribute?
Either side can be chosen to act as the "many" side.
Is a schema for the relationship between a weak entity set and its identifying strong entity set needed?
No — it is redundant (section already contains the attributes of sec_course).
How many arrows may leave a ternary relationship?
At most one — more than one arrow has two possible meanings, so it is outlawed.
Why replace parents (child, father, mother) with father and mother?
Two binary relationships allow partial information (e.g., only the mother being known).
Specialization vs generalization
Specialization: top-down — sub-groupings become lower-level entity sets. Generalization: bottom-up — entity sets with the same features combine into a higher-level set. They are inversions, drawn the same way (ISA triangle).
Attribute inheritance
A lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.
Partial vs total completeness; which is the default?
Partial: not every supertype occurrence is in a subtype (the default). Total: every supertype occurrence is in at least one subtype.
Disjoint vs overlapping subtypes: implementation
Disjoint (d): one subtype discriminator attribute in the supertype. Overlapping (O): one discriminator attribute for each subtype.
Specialization schemas: drawback of method 1 and of method 2
Method 1 (higher-level key + local attributes): accessing two relations for an employee's information. Method 2 (all attributes in each schema): name, street, city stored redundantly for people who are both students and employees.
Aggregation: what is it, and what is the eval_for schema?
Treating a relationship as an abstract entity, allowing relationships between relationships. eval_for (s_ID, project_id, i_ID, evaluation_id); proj_guide becomes redundant.
UML class diagrams vs E-R diagrams
UML class diagrams correspond to E-R diagrams, with several differences — e.g., the position of the cardinality constraint is reversed.