BMGT402 Database Design Considerations Flashcards

What is the goal of a good database design? (Slide 3)

Meet all logical requirements and design conventions.

Why can a logically good database design still fail? (Slide 3)

It may fail practical needs such as performance, speed, security, or information generation.

Why do database designers make design compromises? (Slide 3)

Because design goals can conflict.

How can high processing speed affect database design? (Slide 3)

It may limit relationship complexity or require derived attributes.

What can maximum information generation sacrifice? (Slide 3)

Clean design structures and high transaction speed.

What four other areas may require design compromise? (Slide 4)

Performance, security, shared access, and data integrity.

What is a primary key? (Slide 10)

A single attribute or combination of attributes that uniquely identifies each entity instance.

What three functions does a primary key serve? (Slide 10)

Uniquely identifies instances, guarantees entity integrity, and works with foreign keys to implement relationships.

What is a natural key or natural identifier? (Slide 11)

A generally accepted real-world identifier familiar to end users.

How does a natural key relate to business vocabulary? (Slide 11)

It is familiar to end users and used in day-to-day business vocabulary.

What are the six desirable primary key characteristics? (Slide 12)

Unique values; nonintelligent; no change over time; preferably single-attribute; preferably numeric; security-compliant.

Why must primary key values be unique? (Slide 12)

Each entity instance must be uniquely identified; nulls are not allowed.

What does nonintelligent mean for a primary key? (Slide 12)

The key should have no embedded semantic meaning beyond identification.

Why should a primary key not change over time? (Slide 12)

Changing it requires updating referring foreign keys and increases workload.

Why are single-attribute primary keys preferred? (Slide 12)

They simplify foreign keys and avoid cumbersome multi-attribute keys.

Why are numeric primary keys preferred? (Slide 12)

DBMS counters can generate unique numbers automatically.

What DBMS constructs can support incrementing keys? (Slide 12)

IDENTITY or SEQUENCE.

What does security-compliant mean for a primary key? (Slide 12)

It must not use attributes that pose security risks, such as SSNs.

In what two cases are composite keys useful? (Slide 13)

Identifying composite entities in M:N relationships and weak entities.

What is the benefit of a composite key in an M:N relationship? (Slide 13)

It ensures each permitted combination of parent keys occurs only once.

How can a composite key identify a weak entity? (Slide 13)

Combine the parent key with a distinguishing attribute of the weak entity.

What is a surrogate key? (Slide 15)

A system-assigned primary key, generally numeric and auto-incremented.

When is a surrogate key especially helpful? (Slide 15)

No natural key; candidate key has semantic meaning; or candidate key is too long or cumbersome.

What are the four special flexible design cases? (Slide 18)

Placement of foreign keys, time-variant history, fan traps, and redundant relationships.

Where is a foreign key normally placed in a 1:M relationship? (Slide 19)

On the many side, referencing the primary key on the one side.

Why should a 1:1 relationship not have foreign keys in both entities? (Slide 19)

The slide advises choosing one FK placement to avoid unnecessary complexity and redundancy.

For 1:1 with one mandatory and one optional side, where does the FK go? (Slide 19)

Place the mandatory-side entity PK as an FK in the optional-side entity; make the FK NOT NULL.

For 1:1 with both sides optional, how is FK placement chosen? (Slide 19)

Choose the placement with the fewest nulls or the entity where the relationship role is played.

For 1:1 with both sides mandatory, what should be considered? (Slide 19)

Choose as in the both-optional case or reconsider whether the entities should be combined.

What is time-variant data? (Slide 21)

Data whose values are a function of time.

What normally happens to old attribute values without history tracking? (Slide 21)

They are replaced by new values without preserving previous values.

How can a database preserve time-variant history? (Slide 21)

Create a new history entity in a 1:M relationship with the original entity.

What should a history entity contain? (Slide 21)

The value, date of change, and other pertinent attributes.

What is a design trap? (Slide 23)

A relationship improperly or incompletely identified and inconsistent with the real world.

What is a fan trap? (Slide 23)

One entity is the one side of two 1:M relationships, creating an improperly designed model.

What is a redundant relationship? (Slide 26)

Multiple relationship paths between related entities.

What can redundant relationships cause? (Slide 26)

Data redundancies and anomalies.

Can redundant relationships ever be used? (Slide 26)

Yes; some designs use them to simplify the design, though they risk anomalies.

What must be done with business rules? (Slide 30)

Document and verify them with end users; make them precise, clear, and simple.

What four modeling elements should business rules identify? (Slide 30)

Entities, attributes, relationships, and constraints.

What documentation should accompany business rules? (Slide 30)

Source, justification, date, and approving authority sign-off.

How should entity names be written? (Slide 31)

Short, meaningful, unique business nouns; document abbreviations, synonyms, and aliases.

How should attribute names be written? (Slide 31)

Unique within entity, descriptive, entity-prefix when appropriate, no reserved words or spaces/special characters.

What suffixes are recommended for primary key attribute names? (Slide 31)

_ID, _NUM, or _CODE.

How should relationship names be written? (Slide 31)

Active or passive verbs that clearly indicate the relationship.

What should each entity represent? (Slide 32)

One subject and a set of distinguishable instances.

What normal form should entities meet? (Slide 32)

3NF or higher; anything below 3NF should be justified.

What is required for entity granularity and keys? (Slide 32)

Define the granularity clearly and choose a PK supporting it.

How should attributes be structured? (Slide 33)

Simple and single-valued (atomic).

What attribute metadata should be documented? (Slide 33)

Default values, constraints, synonyms, and aliases.

What should be documented for derived attributes? (Slide 33)

Clearly identify them and include their source(s).

When might redundant attributes be justified? (Slide 33)

Transaction accuracy, performance, or maintaining history.

What dependency must nonkey attributes have? (Slide 33)

They must be fully dependent on the primary key.

What should relationship documentation specify? (Slide 34)

Participants, participation, connectivity, and cardinality.

What operations should an ER model be validated against? (Slide 35)

Inserts, updates, and deletions.

Why should data redundancy be minimized? (Slide 35)

To ensure single-place updates.

What is the minimal data rule? (Slide 35)

All that is needed is there, and all that is there is needed.

Scenario (Slide 5): EMPLOYEE_V1 stores EMP_SPOUSE in every employee row, including employees not married to coworkers. What design problem occurs?

Unnecessary NULL values.

Scenario (Slide 5): James and Louise each store the other as EMP_SPOUSE. They divorce. What must be updated?

Both employee rows must be updated consistently or contradictory spouse data may remain.

Scenario (Slide 6): EMPLOYEE and MARRIED_V1 are separate tables. MARRIED_V1 lists (345,347) and (347,345). What is redundant?

The same marriage is recorded twice, allowing inconsistent updates.

Scenario (Slide 6): MARRIED_V1 uses EMP_NUM and EMP_SPOUSE to refer to employee IDs. What naming issue does the slide identify?

Synonyms are used for the same data.

Scenario (Slide 6): MARRIED_V1 lists James (346) married to Anne (349), while Anne (349) is listed with James (346) in the figure? What should be checked?

Ensure each marriage is represented consistently and not duplicated; the slide specifically asks about James’s marriage.

Scenario (Slide 7): MARRIAGE, MARRPART, and EMPLOYEE tables represent marriages. What is the purpose of MARRPART?

It links employees to a marriage through MAR_NUM and EMP_NUM.

Scenario (Slide 7): MARRIAGE stores MAR_DATE. The organization no longer needs marriage dates. What should the designer reconsider?

Whether a separate MARRIAGE entity is still needed for the required business information.

Scenario (Slide 7): A MARRIAGE-to-MARRPART model allows multiple participants per marriage. What business rule must be verified?

How many people may participate in one marriage.

Scenario (Slide 12): STUDENT_ID 650973 and student name John Q. Smith are candidate identifiers. Which is preferable and why?

STUDENT_ID; it has less semantic meaning and is less likely to change.

Scenario (Slide 12): An employee changes their name and EMP_NAME is the PK. What happens to referencing rows?

Referring foreign keys may need updates, increasing database workload.

Scenario (Slide 12): EMPLOYEE uses SSN as PK. Which desirable PK characteristic is violated?

Security-compliant.

Scenario (Slide 14): STUDENT and CLASS have an M:N relationship resolved by ENROLL. Which fields form the ENROLL composite PK?

STU_NUM and CLASS_CODE (as shown in the diagram).

Scenario (Slide 14): ENROLL contains STU_NUM, CLASS_CODE, and ENROLL_GRADE. Which attribute is not part of the composite PK?

ENROLL_GRADE.

Scenario (Slide 14): ENROLL uses (STU_NUM, CLASS_CODE) as PK. Can the same student/class pair occur twice?

No; the composite PK makes each combination unique.

Scenario (Slide 15): EVENT rows contain DATE, TIME_START, TIME_END, ROOM, EVENT_NAME, and PARTY_OF. Why consider a surrogate PK?

A natural composite key could be long and cumbersome.

Scenario (Slide 15): Two events occur on June 17 at 11 AM in different rooms. Is DATE + TIME_START alone unique?

No; ROOM or another distinguishing field would be needed.

Scenario (Slide 20): An EMPLOYEE manages zero or one DEPARTMENT; each DEPARTMENT has exactly one manager. Where is the FK?

DEPARTMENT.EMP_NUM references EMPLOYEE.EMP_NUM.

Scenario (Slide 20): Each DEPARTMENT must have one manager, but an EMPLOYEE need not manage any. Can DEPARTMENT.EMP_NUM be NULL?

No; it is a mandatory FK.

Scenario (Slide 22): EMPLOYEE stores current JOB_CODE and EMP_SALARY; JOB_HIST stores EMP_NUM, DEPT_ID, DATE_ASSIGN, JOB_CODE, EMP_SALARY. Why create JOB_HIST?

To preserve historical assignments, job codes, and salaries.

Scenario (Slide 22): JOB_HIST uses EMP_NUM, DEPT_ID, DATE_ASSIGN as key fields. Why include DATE_ASSIGN?

To distinguish assignment records across time.

Scenario (Slide 24): DIVISION has many TEAMs and many PLAYERs, but no TEAM–PLAYER relationship. What design trap exists?

Fan trap.

Scenario (Slide 24): DIVISION connects independently to TEAM and PLAYER. What question cannot be answered?

Which player belongs to which team.

Scenario (Slide 25): DIVISION → TEAM → PLAYER is used instead. How do you determine a player’s division?

Follow PLAYER to TEAM and TEAM to DIVISION.

Scenario (Slide 25): TEAM has DIV_ID and PLAYER has TEAM_CODE. Which relationship fixes the fan trap?

TEAM 1:M PLAYER.

Scenario (Slide 27): DIVISION → TEAM → PLAYER exists, and DIVISION → PLAYER is also drawn. Which relationship is redundant?

The direct DIVISION–PLAYER relationship.

Scenario (Slide 27): PLAYER stores TEAM_CODE and DIV_ID, while TEAM stores DIV_ID. What risk is introduced?

Conflicting division values and update anomalies.

Scenario (Slide 37): The Smith School has several offices; each office has a unique number, location, floor, square feet, and cubes; staff request sole use of one cube. What entities are needed?

OFFICE, CUBE, and STAFF.

Scenario (Slide 37): Each office has a unique number, location, floor number, and square feet. What are OFFICE attributes?

OFFICE_NUM (PK), LOCATION, FLOOR_NUM, SQUARE_FEET.

Scenario (Slide 37): All offices have cubes marked A, B, C, etc. What key can uniquely identify each CUBE?

Composite key (OFFICE_NUM, CUBE_LETTER), assuming letters repeat across offices.

Scenario (Slide 37): Each office has cubes. What is the OFFICE–CUBE relationship?

1:M; one OFFICE has multiple CUBEs and each CUBE belongs to one OFFICE.

Scenario (Slide 37): Staff members must request sole use of a single cube, and cubes are not shared. What is STAFF–CUBE connectivity?

1:1 assignment (a staff member uses one cube; a cube is used by at most one staff member).

Scenario (Slide 37): Staff have unique number, name, and phone. What are STAFF attributes?

STAFF_NUM (PK), STAFF_NAME, STAFF_PHONE.

Scenario (Slide 37): Each staff member requests one cube, but the slide does not explicitly say every cube is occupied. Is cube occupancy mandatory?

Not necessarily; unused cubes may exist. Confirm this assumption with users.

Scenario (Slide 37): CUBE letters A, B, C may recur in different offices. Why is CUBE_LETTER alone a weak PK?

It may not uniquely identify a cube across all offices.