CPSC 343 Review Information — Exam 2

Exam 2 is a written in-class (one hour) exam covering conceptual modeling (ER/EER diagrams). Expect three kinds of questions:

• short answer questions about data modeling and ER/EER concepts — definitions, what a construct means, and when to use one construct over another
• reading ER/EER diagrams — determining whether a statement is true according to a given diagram or expressing the data requirements and constraints captured by a diagram in plain language
• drawing ER/EER diagrams, as in the homeworks — modeling a described situation, including identifying required (NOT NULL) attributes and constraints that the diagram cannot capture

Short answers should be short — generally 1-3 sentences, or 1-2 sentences per part for a question with several parts. A list rather than complete sentences is fine where appropriate. Time is limited, so don't write more than necessary! For diagrams, correct notation and neatness matter, but you don't need to redraw a diagram to improve layout or fix a mistake (cross out and correct) as long as it is still readable.

The topics covered on the exam are listed below. The items under "Self check" are study goals — examples of the kinds of things you should be able to do rather than actual exam questions or a complete list of what the exam might ask. As a starting point for studying, try to explain each topic without notes (including an example of your own where appropriate) and then answer the associated self check items. As a pacing guide, you should be able to start writing answers for definition/recall questions right away and "read a diagram" questions after a minute or two of thinking. "Draw a diagram" questions will take longer, but you should be able to identify the main entity types and relationships within a few minutes of reading the description.

For practice reading and drawing diagrams, see the "Example question types" and "Practice" sections at the end.

Data Modeling Concepts

Topics:

• what a data model is and the three kinds of components
• the three levels of data modeling and the specific model used at each level in this course
• why data modeling matters
• goals of good design

Self check:

• Name and describe the three kinds of components a data model can have, and explain why the ER model has only some of them.
• Describe the conceptual, logical, and physical levels of data modeling, including what kinds of concepts each deals with and which model or system we use at each level.
• Explain what it means for the conceptual model to be implementation independent.
• Explain who a conceptual model is a communication tool between.
• Name and describe the goals of good design and explain why correctness involves more than using the notation correctly.

ER Model Basics

Topics:

• entities and entity sets (entity types), relationships, attributes and the notation for each
• the types of attributes and the notation for each
• keys and key attributes
• relationship attributes

Self check:

• Distinguish an entity from an entity set and explain why we usually say "entity" when we really mean "entity set".
• Given a list of names from a scenario, identify which are entity sets, which are relationships, and which are attributes.
• Explain the difference between simple and composite, single-valued and multivalued, and stored and derived attributes and give an example of each.
• Explain what a key is and what it means when an entity type has more than one key attribute underlined.
• Explain when an attribute belongs on a relationship rather than on one of the participating entity types.

Relationship Constraints

Topics:

• participation constraints and the associated notation
• cardinality constraints and the associated notation
• (min,max) notation for explicit lower and upper bounds
• multiway (ternary) relationships and the limits of cardinality notation for them
• roles

Self check:

• Explain what a participation constraint specifies and what a cardinality constraint specifies.
• Explain the meaning of 1:1, 1:N, and M:N cardinality in terms of how many entities of one set an entity of the other set can be related to.
• Read and write constraints in (min,max) notation and explain what that notation can express that the may/must and one/many notation can't.
• Explain what a cardinality constraint on a ternary relationship means and give an example of a constraint that can't be expressed with that notation.
• Explain when a ternary relationship is needed rather than several binary relationships.
• Explain what a role is and why roles are needed for a relationship like SUPERVISES. Explain why participating in two roles is not the same thing as cardinality.

Weak Entities

Topics:

• weak entity types, identifying (owner) entity types, and identifying relationships and the associated notation
• partial (weak) keys

Self check:

• Define a weak entity type and explain what makes it different from a regular entity type.
• Explain what a partial key is and what is needed in addition to it to uniquely identify a weak entity.
• Describe the participation and cardinality constraints required for the weak entity type in an identifying relationship and explain why they are required.
• Given a scenario, decide whether something should be modeled as a weak entity type and identify its partial key and owner.

Design Principles and Decisions

Topics:

• design principles for ER diagrams
• entity vs. attribute vs. relationship: promoting attributes to relationships or entity types, demoting entity types to attributes
• entity vs. relationship: capturing the existence of a relationship vs. specific occurrences
• using only the key to refer to another entity
• deciding whether to include a constraint (key, NOT NULL, participation, cardinality)
• what an ER diagram doesn't show
• what the conceptual model does and doesn't capture about security and access control

Self check:

• Explain how including or omitting a constraint can make the design too restrictive or too permissive.
• Explain why a relationship that can be deduced from other relationships shouldn't be included.
• Describe when an attribute should be promoted to a relationship and when it should be promoted to an entity type.
• Describe when an entity type should be demoted to an attribute and how the cardinality of the relationship determines whether the attribute is single-valued or multivalued.
• Explain why an attribute that refers to another entity (e.g. a student ID stored as an attribute of an enrollment) is a design error and how it should be modeled instead.
• Explain the difference between modeling a purchase as a relationship and as an entity type, including what each can and can't record. Explain why a relationship can't record the same pair of entities more than once.
• Describe the questions to ask when deciding whether to include a constraint and give an example of each.
• Give examples of requirements that can't be captured in an ER diagram and describe what to do with them.
• Explain to what extent security and access control are captured in the conceptual model.

Enhanced ER (EER): Specialization and Generalization

Topics:

• superclasses and subclasses; the is-a relationship; inheritance of attributes and relationships
• specialization vs. generalization
• subclass membership: predicate-defined, attribute-defined, user-defined
• total vs. partial specialization; disjoint vs. overlapping subclasses; notation
• multiple specializations of the same superclass
• shared subclasses

Self check:

• Explain what specialization captures and give examples of a subclass that represents a role and subclasses that represent kinds of things.
• Explain what a subclass inherits from its superclass and when a subclass is worth including (rather than just an attribute or relationship on the superclass).
• Distinguish predicate-defined, attribute-defined, and user-defined subclass membership.
• Distinguish total from partial specialization and disjoint from overlapping subclasses and explain what each combination means for which subclass(es) a superclass entity may belong to.
• Explain why the d/o notation is only used when a specialization has more than one subclass.
• Define a shared subclass and explain what it inherits.
• Given a set of statements about subclasses, determine which specializations are needed and which constraints apply to each.

Enhanced ER (EER): Category Types

Topics:

• category (union) types; notation
• category type vs. specialization

Self check:

• Define a category type and give an example.
• Explain what the attributes of a category entity are.
• Explain the difference between modeling OWNER as a category of PERSON, BANK, and COMPANY and modeling PERSON, BANK, and COMPANY as a specialization of OWNER.
• Given a scenario, decide whether specialization, a category type, or neither is appropriate and justify the choice.

Example Question Types

Beyond the conceptual questions above, expect questions that ask you to apply your knowledge to a given diagram or description. For example:

• Given an ER diagram, determine whether each of a list of statements is true according to the diagram (e.g. "a project can be controlled by more than one department," "two dependents can have the same name").
• Given an ER/EER diagram, describe the data requirements and constraints it represents in plain language (without database-specific technical terms).
• Given a description of a situation, draw an ER or EER diagram capturing it, including participation, cardinality, and key constraints. Identify which attributes are required (NOT NULL), note any constraints present that the diagram doesn't capture, and state any assumptions made where the description is lacking.
• Identify the attribute types (composite, multivalued, derived) in a description or diagram.
• Express a described constraint in (min,max) notation or describe the meaning of a constraint given in that notation.
• Determine whether a ternary relationship is needed for a described situation and what its cardinality constraints mean.
• Identify a design error in a given diagram and fix it.
• Decide whether something should be modeled as an attribute, a relationship, an entity type, or a weak entity type and justify the choice.
• Decide whether a described constraint should be included in the model and explain why or why not.
• Given a set of statements about subclasses, draw the corresponding specialization(s) with the correct total/partial and disjoint/overlapping constraints or identify which statements are or aren't consistent with a given EER diagram.
• Decide whether specialization or a category type is appropriate for a described situation and explain.

Practice

• Work through the homework problems and the example problems from class (in the slides), including any you didn't complete the first time.
• For diagrams you've drawn, compare with a classmate's or a posted solution. Where they differ, decide whether the difference is a different (but reasonable) modeling choice or an error — and if it's a choice, be able to explain the tradeoff.
• For each diagram you draw, read it back: describe in plain language what it says and check that against the original description. Check that you've listed the NOT NULL attributes, the constraints the diagram can't express, and your assumptions.