1 of 17

Database Design Part 3

Mark Fontenot, PhD

Northeastern University

2 of 17

Relationships and FKs

Products

Categories

R

productID

categoryID

name

name

3 of 17

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).

4 of 17

Weak Entity

4

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.

5 of 17

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.

6 of 17

Full Example

Note: This is only one way to go from description to conceptual ER Model.

7 of 17

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.

8 of 17

Generate HuskyWorks ER Diagram

HuskyWorks is organized into departments. Each department has a unique name a unique 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 unique name, unique 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.

9 of 17

Department

Employee

dept_emp

(houses)

dept_mgr

(manages)

1…1

1…N

1…1

0…1

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

Note a new strategy for naming relationships.

10 of 17

Generate HuskyWorks ER Diagram

HuskyWorks is organized into departments. Each department has a unique name a unique 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 unique name, unique 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.

11 of 17

Department

Employee

Project

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

emp_dep

(depends on)

Dependent

emp_dep

(depends on)

1…1

0…N

Dependent

12 of 17

Generate HuskyWorks ER Diagram

HuskyWorks is organized into departments. Each department has a unique name a unique 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 unique name, unique 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.

13 of 17

Department

Employee

Project

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

emp_dep

(depends on)

Dependent

emp_dep

(depends on)

1…1

0…N

Dependent

locations

locations

number

name

startDate

name

fName

midInitial

lName

bDate

IDNum

address

salary

14 of 17

Generate HuskyWorks ER Diagram

HuskyWorks is organized into departments. Each department has a unique name a unique 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 unique name, unique 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.

15 of 17

Department

Employee

Project

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

emp_dep

(depends on)

Dependent

emp_dep

(depends on)

1…1

0…N

Dependent

locations

locations

number

name

startDate

name

fName

midInitial

lName

bDate

IDNum

address

salary

location

number

name

hours

16 of 17

Generate HuskyWorks ER Diagram

HuskyWorks is organized into departments. Each department has a unique name a unique 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 unique name, unique 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.

17 of 17

dep_id

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

number

name

location

number

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent