ER → Relational Mapping
Mark Fontenot, PhD
Northeastern University
Material bo
owed from Dr. De
insky’s slides for 3200 with permission.
Mandatory vs Optional: Ex 1
Profs:
(1, Alford)
(2, Quicksall)
(3, No
is)
Classes:
(CS, 2500)
(CS, 2510)
(CS, 1800)
(CS, 3200)
Profs
Classes
teach
Description: A professor can teach more than one class, but doesn’t have to be teaching any classes. Each class is taught by 1 professor and must have a professor assigned to it.
Mandatory vs Optional: Ex 1
Profs:
(1, Alford)
(2, Quicksall)
(3, No
is)
Classes:
(CS, 2500)
(CS, 2510)
(CS, 1800)
(CS, 3200)
Profs
Classes
teach
Description: A professor can teach more than one class, but doesn’t have to be teaching any classes. Each class is taught by 1 professor and must have a professor assigned to it.
0…M
Mandatory vs Optional: Ex 1
Profs:
(1, Alford)
(2, Quicksall)
(3, No
is)
Classes:
(CS, 2500)
(CS, 2510)
(CS, 1800)
(CS, 3200)
Profs
Classes
teach
Description: A professor can teach more than one class, but doesn’t have to be teaching any classes. Each class is taught by 1 professor and must have a professor assigned to it.
0…M
1…1
Mandatory vs Optional: Ex 2
Profs:
(1, Alford)
(2, Quicksall)
(3, No
is)
Classes:
(CS, 2500)
(CS, 2510)
(CS, 1800)
(CS, 3200)
Profs
Classes
teach
Description: Every professor must be teaching at least 1 class (but could be more). Each class is taught by 1 professor and must have a professor assigned to it.
Mandatory vs Optional: Ex 2
Profs:
(1, Alford)
(2, Quicksall)
(3, No
is)
Classes:
(CS, 2500)
(CS, 2510)
(CS, 1800)
(CS, 3200)
Profs
Classes
teach
Description: Every professor must be teaching at least 1 class (but could be more). Each class is taught by 1 professor and must have a professor assigned to it.
1…M
1…1
Where are we?
Requirements
Analysis
Conceptual Design
Choose a DBMS and Map Conceptual
Physical Design
System Implementation and Tuning
‹#›
emp_dep
(depends on)
locations
‹#›
Department
Employee
Project
Dependent
name
fName
midInitial
lName
Date
IDNum
address
salary
gende
locations
numbe
name
location
numbe
name
name
gende
Date
elationship
dept_emp
(houses)
dept_mg
(manages)
emp_proj
(works_on)
dept_proj
(controls)
hours
startDate
emp_dep
(depends on)
emp_mg
(supervises)
superviso
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
‹#›
How We Will Describe Relations
‹#›
SSN
FName
LName
Bdate
SSN
SeqNum
Salary
From
To
Employee:
SalaryInfo:
Step 1: Handling Strong Entity Types
For each strong entity type, create a co
esponding relation that includes all the simple attributes
include simple attributes of composite attributes.
Choose one of the candidate keys as the primary key.
primary key may be a composite key.
Note any other candidate keys will be marked as unique attributes when creating tables.
useful in performance tuning / indexing later in physical design
‹#›
emp_dep
(depends on)
locations
‹#›
Department
Employee
Project
Dependent
name
fName
midInitial
lName
Date
IDNum
address
salary
gende
locations
numbe
name
location
numbe
name
name
gende
Date
elationship
dept_emp
(houses)
dept_mg
(manages)
emp_proj
(works_on)
dept_proj
(controls)
hours
startDate
emp_dep
(depends on)
emp_mg
(supervises)
superviso
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
‹#›
Step 1: Result for Example ERD
‹#›
Step 2: Handling Weak Entity Types
For each weak entity type, create a co
esponding relation that includes all simple attributes.
Add as a foreign key all of the primary key attribute(s) from the co
esponding strong entity type
The primary key of the new relation is the combination of the PK attributes of the owner entity type and the partial key (discriminator) of the weak entity
‹#›
Step 2: Result for Example ERD
‹#›
Step 3 - Handling Binary 1:1 Relationships
General Idea: Foreign Key used to map the relationship
Choose one relation as S, the other as T
Better if S has mandatory participation
c it reduces the number of null values.
Add to S all the simple attributes of the relationship
Add as a foreign key in S the primary key attributes of T
‹#›
Step 3: Result for Example ERD
‹#›
Step 4: Handling Binary 1:N Relationships
Assume S is the relation on the many side of the relationship; the other relation is T
Add as a foreign key in S all of the primary key attribute(s) of T.
Add any simple attributes of the relationship to S.
‹#›
Step 4: Result for Example ERD
‹#›
Step 5: Handling Binary M:N Relationships
Create a new relation S (sometimes called the
idge table)
In some ER Diagrams, you may see this indicated with a box around the diamond
Add as foreign keys to S the primary keys of both relations; their combination forms the PK of S.
Add any simple attributes of the M:N relationship to S.
‹#›
Step 5 - Result for Example ERD
‹#›
Step 6 - Handling MultiValued Attributes
If number of instances is unbounded or unknown
Create a new relation S
Add as foreign keys to S the primary keys of the co
esponding relation
Add the attribute to S (if composite, the simple attributes). The combination of all attributes in S forms the primary key of S.
If number of instances is bounded and known
Add additional attributes to relation, one for each potential instance.
‹#›
Step 6 - Result for Example ERD
‹#›
Review
Step 1: Handle Strong Entity Types
Step 2: Handle Weak Entity Types
Step 3: Handle 1:1 Relationships
Step 4: Handle 1:M Relationships
Step 5: Handle M:N Relationships
Step 6: Handle Multivalued Attributes
‹#›
HW05 - Univ ER Diagram
Students
Sections
Departments
Courses
takes
majors
minors
Name
Code
College
Department
Credits
Numbe
Description
Name
elongs
to
Course
Numbe
Numbe
Yea
Semeste
Instructo
Student
ID
First
Yea
Semeste
SSN
Last
Name
Perm.
Address
Unique
Num.
Grade
ID
0…
1
0…
M
0…N
0…
N
1…1
1…
1
0…N 1…1
Email
CS XXXXXXXXXXDatabase Design - Fontenot
Homework 05 - ER Modeling & Relational Mapping
You will submit this assignment as a single PDF to GradeScope. It is vitally important that you complete the submission process on GradeScope wherein you indicate the location of each of your answers for the assignment. Failure to do so will result in a grade of 0 on the assignment.
All ER Diagrams need to be created in a drawing app such as Google Draw, LucidChart, OmniGraffle, or even PowerPoint; no handwritten/drawn diagrams.
All ER Diagrams must use the notation consistent with the examples done in class. Each relationship should have participation and cardinality for all involved entities using the 0…M style notation. Do not use a double line to indicate mandatory participation. Additionally, no crow’s foot or UML-style notation.
Question 1. Below (in italics), you’re given a bulleted, na
ative explanation of a system, and below that, you’re given an ER diagram. Assume that the na
ative is the ground truth for the system under review. Find at least ten mistakes in the ER diagram. You will submit:
1. a
ief English description of each of the 10 e
ors you find, and
2. an updated ER diagram co
ectly reflecting the requirements from the na
ative.
You get equal credit for each co
ected mistake; even if co
ecting a mistake requires several changes to the diagram. For each co
ection, you will receive half of the points for your textual description of the mistake, as well as justification with respect to the na
ative and ERD, and half if you fully co
ect the issue in your diagram. Please number each textual description, and label the associated change(s) in the diagram, such that your submission is clear and understandable. Each e
or is worth 6 points (3 for explanation and 3 for proper diagram update.
Na
ative:
· The university keeps track of each student’s name, student ID, social security number, permanent address, major department, and minor department (if any). Some applications need to refer to the city, state, and ZIP code of the student’s permanent address, as well as the student’s last name. Both SSN and student ID have unique values for each student, but the university has chosen student ID as the unique identifier for students. Every student is allowed to have up to 4 email addresses.
· Each department is described by a unique name, a unique department code, and a college name. The department code serves as the primary key of the Departments table
· Each course has a course name, description, offering department, unique number among all courses in the department offering the course, number of credits.
· Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semeste
year.
· When a student takes a section of a course, they receive a letter grade and a numeric grade. Given a numeric grade from the instructor, the letter grade is assigned based upon the university’s standard grade-conversion table.
Question 1 - Part 1: The 10 e
ors you found.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Question 1 - Part 2: Co
ected Diagram. You can start with the Google Drive Drawing found > here < or recreate the diagram in another tool of your choice. Indicate any changes or additions using a different colored block/text.
Please label the associated change(s) in the diagram with your description from Q1 Part 1 above, such that your submission is clear and understandable.
NO handwritten diagrams will be graded.
Question 2: Convert the co
ected ER diagram from Question 1 into a relational diagram as demonstrated in Lecture Slide Deck 15 (ER → Relational Mapping). Provide the diagram as it would exist after each Step of the mapping process. (In other words, you’ll have 6 diagrams to submit) (40 points total)