Database Design Part 3

Mark Fontenot, PhD

Northeastern University

1:1 Examples

• Assume Acme Corp has many locations…
• Each location has 1 manager (an employee) and that manager can only be manager of 1 location at a time.

​

​

• University Example
• Each academic department has 1 professor as department chair and a professor can only be chair of one department at a time.

1:M Examples

• Acme Corp. sells numerous products, but each product comes from only 1 supplier. Each supplier may supply multiple products.

​

​

• Each manager of an Acme Corp. location supervises many employees, but each employee only has 1 supervisor/manager.

M:N Examples

• Acme Corp allows multiple products to be ordered at one time by a customer. Each product may be part of many orders as well.

​

​

​

• A professor teaches many students and each student may be taking classes from multiple professors.

Participation Constraints

• Indicates whether some or all entity instances must participate in the relationship
• Mandatory (total) participation: All entity instances must participate in the relationship
• Example 1: Every employee must have a supervisor
• Example 2: Every class must have an instructor.
• Optional (partial) participation: only some entity instances will participate in the relationship
• Example 1: Only some employees supervise other employees
• Example 2: A subset of professors serve as department chairs.

Mandatory Participation Constraint

• Mandatory (total) participation
• Example 1: Every employee must have a supervisor

​

​

​

• Example 2: Every class must have an instructor.

​

Optional Participation Constraint

• Optional (partial) participation
• Example 1: Only some employees supervise other employees

​

​

​

• Example 2: A subset of professors serve as department chairs.

More Examples: #1

You’re designing a DB for an After-School Tutoring Program for Elementary students.

​

Every tutor is assigned to one student and every student has exactly one tutor. A tutor is hired specifically to tutor one child. Children must have a tutor.

8

More Examples: #2

You’re designing a DB for an After-School Tutoring Program for Elementary students.

​

Every child must be assigned exactly one tutor, but there are some tutors who are not currently assigned to a child. A tutor will never tutor more than one child at a time.

9

More Examples: #2B

You’re designing a DB for an After-School Tutoring Program for Elementary students.

​

Every child must be assigned a tutor (and may have multiple tutors), but there are some tutors who are not currently assigned to a child. A tutor will never tutor more than one child at a time.

10

More Examples: #3

You’re designing a DB for an After-School Tutoring Program for Elementary students.

​

Every tutor must be assigned to a child, but there are some children for whom tutors have not been found yet.

11

More Examples: #4

You’re designing a DB for an After-School Tutoring Program for Elementary students.

​

Multiple tutors help each child and each child can be helped by multiple tutors. At any point in time, a child may not be associated with any tutors, and a tutor may not be helping any children.

12

Weak Entity

• Weak Entity - the existence of an instance of a weak entity is dependent on a corresponding strong entity.
• The weak entity instances are uniquely identified by the PK of the corresponding strong entity plus some attribute(s) of its own.
• A weak entity must have total participation in a 1:M relationship with its associated strong entity (the 1 side).

Weak Entity

14

Section

course_section

Section

Course

course_section

Dept

Num

Sec Number

Book

Prof

1

N

Note, double edged

diamond and rectangle and dashed underline

Each class section is associated with some particular course, which is identified by the department and number. Each section is identified by the associated course’s department and number plus the section number.

Weak Entity Examples

• Hotel chain with multiple locations
• Each hotel location is a strong entity instance
• Each room is a weak entity instance instance
• Room 101 is meaningless until we associate it with some particular hotel location.
• Sports League with multiple Teams
• Each Team is a strong entity instance
• Each player is a weak entity instance
• Player w/ jersey #10 is meaningless unless we know what team the player is on.

Generate HuskyWorks ER Diagram

HuskyWorks is organized into departments. Each department has a name, a unique ID number, and could have several locations. Each department of HuskyWorks has a particular manager who manages it. We keep track of the start date when that employee began as manager.

​

A department controls a number of projects. Each HuskyProject has a name, unique project id number, and a single location.

​

The HuskyWorks Database will store each employee’s name, ID number, address, salary, and birth date. Each employee is assigned to one department, but they may work on several projects which may be controlled by other departments. HuskyWorks DB must keep track of the number of hours that an employee is assigned to work on each project they are assigned to (note… different than number of hours actually worked, which we are not tracking). Lastly, each employee has a direct supervisor, who is another employee.

​

HuskyWorks DB must also track the dependents of each employee for insurance purposes. The info that needs to be tracked is the dependent’s first name, birth date, and relationship to the employee.