Database Systems{week 04b}

Rensselaer Polytechnic InstituteCSCI-4380 – Database SystemsDavid Goldschmidt, Ph.D.

Entity/Relationship (E/R) models Modeling a database begins by

identifying the information to be stored Need to also define how information

elements are related to one another Further, define constraints

on the information,including defining keys,referential integrity, etc.

Entities and entity sets An entity is a building block of our database,

abstracting an object of some sort e.g. movie, faculty member, student

An entity set is a collection of similar entities e.g. all movies, all faculty members, all students

This is similar to the object/class conceptsof OOP, but there are no methods/functions

Keys and attributes

Each entity set has a keyand (usually) other attributes The key consists of one or more attributes The key should functionally determine

all other attributes! Entity sets should be in BCNF or 3NF Entity sets should not have attributes that

relate to other entity sets, unless they are foreign keys

E/R diagram

An E/R diagram is a graph representing: Entity sets (rectangles)

Attributes (ovals)

Relationships (diamonds)▪ i.e. connections between two or more entity sets

Students

Courses

id

name

enroll-inE/R diagrams are a notation

for describing database schemas

keys are underlined

Many-to-many relationships

Each course has many enrolled students Each students enrolls in many classes Note that many implies zero or more

Students Coursesenroll-in

One-to-many relationships

Each department has many faculty Each faculty member belongs to at most

one department (so zero or one)

Faculty Departmentsin

“at most one”

One-to-one relationships

Each department has at most one chair Each faculty member can be chair of at

most one department

Faculty Departmentschair-of

Three-way relationships

For a particular major and student, there is at most one faculty member who is the advisor

A faculty member may advise many students in many majors

Students Facultyadvised-by

Majors

Three-way relationships

A student enrolls in a course with at most one department code (e.g. CSCI)

Is this the correct relationship?

Students Coursesenroll-in

DeptCodes (e.g. CSCI)

Revising three-way relationships

The department code is actuallyfunctionally determined by thecourse itself (e.g. by CRN)

Note that this does not account for cross-listed courses....

Students Coursesenroll-in

DeptCodes (e.g. CSCI)

has

Revising three-way relationships

Is this the correct relationship? It is correct (and necessary) if a faculty

member can advise students across multiple majors

Otherwise, each faculty should functionally determine the major s/he advises

Students Facultyadvised-by

Majors

Recursive relationships (and roles)

A movie may have many sequels For each sequel, there is at most one

original movie

Movies

sequel-of

original

sequel

exactly one in this case...

role

Relationship attributes

The grade attribute is associated withthe given relationship▪ And involves both a student and a course

This is merely a shortcut....

Students Coursesenroll-in

grade

Removing relationship attributes

The grade attribute of the Grades entity set (relation) is functionally determined by the entire tuple involving Students and Courses

i.e. a student enrolled in a course has a grade

Students Coursesenroll-in

Gradesgrad

e

Exercises

Go back to the relations we’veworked with in class and createE/R diagrams for each e.g. MusicGroup, Company

Also create a detailed E/R diagram for the celebrities relations (and relationships!) see next slide....

Exercises