Data Modelling Quiz ITEC3200

What two concepts are ER modelling based on?

Entities and Relationships

What is an entity?

an object in the real world with an independent existence that can be differentiated from other objects (physical or conceptual)

Define relationships

associations or interactions between entities

Attributes

Defines an entity (EMPLOYEE = (Name, Address, Birthdate (age), Salary)

E/R diagrams consist mainly of what 3 things?

Entities, Attributes, and Relationships between entities. 

Define Business Rules

used to determine the cardinality and connectivity of relationships

What is Cardinality?

describes the relationship between two tables by expressing the min/max number of entity occurrences associated

Maximum Cardinality

The max number of entity instances that may participate in a relationship instance — one, many, or some other fixed number

Describe the Maximum Cardinality between a Department and an Employee.

One to Many (1:M)

Describe the Maximum Cardinality between a Person and a Passport.

One to One (1:1)

Describe the Maximum Cardinality between a customer and a property

Many to Many (M:M)

Identify the maximum cardinality type:

Student → Student Record is One

Student Record → Student is One

One to One (1:1)

Identify the maximum cardinality:

Mentor → Student

One to Many (1:M)

Identify the maximum cardinality:

Course → Student

Many to Many (M:N)

What is Minimum Cardinality?

the minimum number of entities that need to participate in a relationship (mandatory or optional)

Describe the minimum cardinality from Student → Course:

BR: A student doesn’t need to take a course to exist as a student, but a course needs to have a student to exist

Optional

Describe the minimum cardinality from Course → Student:

BR: A student doesn’t need to take a course to exist as a student, but a course needs to have a student to exist

Mandatory

Describe the minimum cardinality from:

Person → Passport

BR: a person needs to have a passport to exist as an entity and a passport needs to have a person to exist as an entity

Mandatory

Describe the minimum cardinality from:

Passport → Person

BR: a person needs to have a passport to exist as an entity and a passport needs to have a person to exist as an entity

Mandatory

Describe the minimum cardinality from:

Person → Dentist

Business Rule: Person does not need to have a dentist

Optional

Describe the minimum cardinality from:

Dentist → Person

Business Rule: Dentist needs to have a patient to exist

Mandatory

What cardinality is represented in this image?

multiplicity of one

What cardinality is represented in this image?

multiplicity of many

What cardinality is represented in this image?

mandatory

What cardinality is represented in this image?

optional

Which cardinality is represented by a Crow’s foot drawn on the inside edge?

minimum cardinality

Which cardinality is represented by a Crow’s foot drawn on the outside edge?

maximum cardinality

What is the maximum cardinality of this model?

One to One (1:1)

What is the minimum cardinality of this model from Student → Seat? What is the minimum cardinality from Seat → Student?

mandatory, mandatory

What is the maximum cardinality of this model?

One to Many (1:M)

What is the minimum cardinality of this model from Course → Lecturer?

mandatory

What is the minimum cardinality of this model from Lecturer → Course?

optional

What is the maximum cardinality of this model?

Many to Many (M:M)

What is the minimum cardinality of this model from Author → Book?

required

What is the minimum cardinality of this model from Book → Author?

required

Where is the foreign key placed when the maximum cardinality is One to One?

one of the two entities. Table choice doesn’t matter.

Where is the foreign key placed when the maximum cardinality is One to Many?

in the many side of the entity

Where is the foreign key placed when the maximum cardinality is Many to Many?

need to create a new associative entity

Why is the FK in the Employee table?

In a 1:M relationship, the FK is placed in the many side of the entity

Why does this M:M table have a composite key?

BR: Student can enroll in a course only once.

A new associative entity must be created for a M:M cardinality (Student → Course).

Why is there an Enrollment table in this E/R model?

Students → Class is a M:M relationship, meaning we must create a new associative entity with a composite key (EnrollmentID, StudentID, ClassID)