Entity-Relationship Modeling & Cardinality – Review Flashcards

44 question-and-answer flashcards reviewing entities, relationships, cardinalities, notation choices, special annotations, and example attributes for common database scenarios.

What are the three key entities in the described library lending scenario?

BOOK, MEMBER, and BORROWING_TRANSACTION

In the library system, what is the relationship between MEMBER and BOOK?

MEMBER borrows BOOK

In the library system, what is the relationship between BOOK and BORROWING_TRANSACTION?

BOOK is borrowed in BORROWING_TRANSACTION

In the library system, what is the relationship between MEMBER and BORROWING_TRANSACTION?

MEMBER is involved in BORROWING_TRANSACTION

For the rule "A CUSTOMER can place multiple ORDERS," what is the minimum cardinality from CUSTOMER to ORDER?

0 (zero) – a customer may place no orders

For the same rule, what is the maximum cardinality from CUSTOMER to ORDER?

• (many) – a customer may place many orders

For the rule "each ORDER is placed by exactly one CUSTOMER," what is the minimum cardinality from ORDER to CUSTOMER?

1 – every order must have a customer

For that rule, what is the maximum cardinality from ORDER to CUSTOMER?

1 – each order is linked to a single customer

For the rule "Each ORDER must include at least one PRODUCT," what is the minimum cardinality from ORDER to PRODUCT?

1 – every order contains at least one product

For that rule, what is the maximum cardinality from ORDER to PRODUCT?

• – an order can contain many products

In the same ORDER–PRODUCT rule, what is the minimum cardinality from PRODUCT to ORDER?

0 – a product might not appear in any order

What is the maximum cardinality from PRODUCT to ORDER?

• – a product can appear in many orders

Which two entities are involved in the rule "A SUPPLIER may supply multiple ITEMS, but each ITEM must be supplied by at least one SUPPLIER"?

SUPPLIER and ITEM

What is the relationship between SUPPLIER and ITEM?

SUPPLIER supplies ITEM

What is the cardinality from SUPPLIER to ITEM?

0..* (a supplier may supply zero or many items)

What is the cardinality from ITEM to SUPPLIER?

1..* (each item must have at least one supplier, possibly many)

Which E-R notation correctly shows the SUPPLIER–ITEM relationship and cardinalities?

SUPPLIER (0..) — supplies — (1..) ITEM

In CUSTOMER (0..) — submits — ORDER, what does CUSTOMER (0..) mean?

A customer may submit zero or more orders

What does ORDER (1..1) mean in the same context?

Each order must be submitted by exactly one customer

Given CUSTOMER (0..*) and ORDER (1..1), what is the correct business rule?

Each ORDER must be submitted by exactly one CUSTOMER

What does PRODUCT (1..*) indicate?

Each product must include at least one item and may include many

What does ITEM (1..*) indicate?

Each item must belong to at least one product and may belong to many

State the full business rule for PRODUCT (1..) includes ITEM (1..).

Each product has one or more items; each item appears in one or more products

In CUSTOMER (0..) submits ORDER (0..), what does ORDER (0..*) indicate?

An order may exist without being submitted by any customer

What is a potential issue with CUSTOMER (0..) submits ORDER (0..)?

An ORDER could exist in the database with no associated CUSTOMER

How can you correct the ORDER–CUSTOMER cardinality to ensure every order has a customer?

Change ORDER to CUSTOMER from (0..*) to (1..1)

When modeling, what is the first step in choosing an E-R notation?

Identify the complexity of the organizational situation

Which notation is most appropriate for a small library tracking books and borrowers?

A simpler E-R notation

Which scenario most likely requires a more detailed E-R notation?

A large hospital management system with complex relationships

What should be considered when choosing between simple and detailed notation?

The level of detail required to accurately model the situation

What is the role of special annotations in E-R diagrams?

To represent business rules and constraints not captured by standard notation

Why are special annotations important?

They ensure all rules governing relationships and entities are accurately represented

Give an example business rule that might require special annotation.

"A student can enroll in a maximum of five courses per semester"

What is the first step when solving a problem that needs special annotations?

Identify the business rules and constraints that must be represented

What should you do if a business rule cannot be captured by standard notation?

Use special annotations to represent the rule

Which attributes belong in the Books entity of a bookstore database?

BookID, Title, Genre, Price, PublisherID, AuthorID

Which attributes belong in the Authors entity?

AuthorID, Name, Bio

Which attributes belong in the Publishers entity?

PublisherID, Name, Address

What is the relationship between Books and Authors?

Many-to-One (many books are written by one author)

What is the relationship between Books and Publishers?

Many-to-One (many books are published by one publisher)

Which attributes define the Students entity?

StudentID (PK), Name, Major

Which attributes define the Courses entity?

CourseID (PK), Title, Credits

Which attributes define the Enrollments entity?

EnrollmentID (PK), StudentID (FK), CourseID (FK), Grade