What is a Database Management System

1
What is a Database Management System?
Spring 2008

• Lecture slides by Dr. Sara Cohen

2
What and Why

• A database management system is a program used
  to
• Store large amounts of data
• Allow easy access to the data (using a query
  language)
• Separate the physical schema from the logical
  schema

3
What and Why

• Protect the data from corruption or security
  leaks
• Allow multiple users to access the data
  simultaneously
• Provide crash recovery
• And more

4
Entity-Relationship Diagrams
Spring 2008
5
Scenario

• http//www.imdb.com wants to store information
  about movies and has chosen you to help them
• Three steps
• Requirements Analysis Discover what information
  needs to be stored, how the stored information
  will be used, etc. Taught in course on system
  analysis and design
• Conceptual Database Design High level
  description of data to be stored (ER model)
• Logical Database Design Translation of ER
  diagram to a relational database schema
  (description of tables)
• Physical Database Design Done by the DB system

6
Requirements (1)

• For actors and directors, we want to store their
  name, a unique identification number, address and
  birthday (why not age?)
• For actors, we also want to store a photograph
• For films, we want to store the title, year of
  production and type (thriller, comedy, etc.)
• We want to know who directed and who acted in
  each film. Every film has one director. We store
  the salary of each actor for each film

7
Requirements (2)

• An actor can receive an award for his part in a
  film. We store information about who got which
  award for which film, along with the name of the
  award and year.
• We also store the name and telephone number of
  the organization who gave the award. Two
  different organizations can give an award with
  the same name. A single organization does not
  give more than one award with a particular name
  per year.

8
address
id
birthday
Movie Person
name
phone number
name
In the rest of this lesson we explain this diagram
ISA
Organization
Gives
picture
Actor
Director
Won
salary
Acted In
Directed
Award
year
Film
name
year
title
type
9
ER-Diagrams General Information

• ER-diagrams are a formalism to model real-world
  scenarios
• There are many versions of ER-diagrams that
  differ both in their appearance and in their
  meaning
• We will use the version appearing in the course
  book (Database Management Systems by
  Ramakrishnan)
• ER-diagrams have a formal semantics (meaning)
  that must be thoroughly understood, in order to
  create correct diagrams

10
Entities, Entity Sets

• Entity (????) An object in the world that can be
  distinguished from other objects
• Examples of entities
• Examples of things that are not entities
• Entity set (????? ??????) A set of similar
  entities
• Examples of entity sets
• ? Entity sets are drawn as rectangles

11
Attributes

• Attributes (??????) Used to describe entities
• All entities in the set have the same attributes
• A minimal set of attributes that uniquely
  identify an entity is called a key
• An attribute contains a single piece of
  information (and not a list of data)

12
Attributes (2)

• Examples of attributes
• Examples of things that cannot be attributes
• ? Attributes are drawn using ovals
• ? The names of the attributes which make up a key
  are underlined

13
Example
birthday
id
Actor
name
address
14
Another Option for a Key?
birthday
id
Actor
name
address
15
Another Option for a Key?
birthday
id
Actor
name
address
16
Relationships, Relationship Sets

• Relationship (???) Association among two or more
  entities
• Relationships may have attributes
• Examples of Relationships
• Relationship Set (????? ?????) Set of similar
  relationships
• Examples of Relationship sets
• ? Relationship sets are drawn using diamonds

17
Example
title
birthday
id
Film
Actor
year
Acted In
name
type
address
18
Recursive Relationships

• An entity set can participate more than once in a
  relationship
• In this case, we add a description of the role to
  the ER-diagram

phone number
manager
id
Employee
Manages
worker
name
address
19
n-ary Relationship

• An n-ary relationship R set involves exactly n
  entity sets E1, , En.
• Each relationship in R involves exactly n
  entities e1 in E1, , en in En
• Formally, R? E1x x En

20
Example

• Suppose that there are
• Actors Mickey Mouse, Donald Duck
• Directors Big Bird, Kermit
• Films Mickeys Club

How many triplets can be in the relationship set
Produced?
How many pairs can be in the relationship set
Produced?
21
Another Option Remember Recursive Relationships
22
Important Note

• The entities in a relationship set must identify
  the relationship
• Attributes of the relationship set cannot be used
  for identification!
• Suppose we wanted to store the role of an actor
  in a film.
• How should we store the role of the actor?
• How would we store information about a person who
  acted in one film in several roles?

id
Actor
Film
Acted In
title
name
23
Key Constraints (?????? ????)

• Key constraints specify whether an entity can
  participate in one, or more than one,
  relationships in a relationship set
• When there is no key constraint an entity can
  participate any number of times
• When there is a key constraint, the entity can
  participate at most one time
• ? Key constraints are drawn using an arrow from
  the entity set to the relationship set

24
One-to-Many

• A film is directed by at most one director
• A director can direct any number of films

id
Director
Film
Directed
title
name
Director
Directed
Film
25
Many-to-Many

• A film is directed by any number of directors
• A director can direct any number of films

id
Director
Film
Directed
title
name
Director
Directed
Film
26
One-to-One

• A film is directed by at most one director
• A director can direct at most one film

id
Director
Film
Directed
title
name
Director
Directed
Film
27
Another Example
Where would you put the arrow?
age
father
id
Person
FatherOf
child
name
28
Key Constraints in Ternary Relationships
Actor
id
name
id
Director
Film
produced
title
name
What does this mean?
29
Participation Constraints ?????? ???????))

• Participation constraints specify whether or not
  an entity must participate in a relationship set
• When there is no participation constraint, it is
  possible that an entity will not participate in a
  relationship set
• When there is a participation constraint, the
  entity must participate at least once
• ? Participation constraints are drawn using a
  thick line from the entity set to the
  relationship set

30
Example (1)

• A film has at lease one director
• A director can direct any number of films

id
Director
Film
Directed
title
name
Do you think that there should be a participation
constraint from Director to Directed?
Director
Directed
Film
31
Example (2)

• We can combine key and participation constraints.
• What does this diagram mean?

id
Director
Film
Directed
title
name
32
Storing Award Information

• What do you think of this?
• To model this correctly we need weak entity sets

org_ name
Won
Award
phone number
name
year
33
Weak Entity Sets

• Weak entity sets are entity sets that are not
  uniquely identified by their attributes
• A weak entity set has an "identifying
  relationship" with an entity set that is the
  "identifying owner" of the weak entity set

34
Weak Entity Sets

• A weak entity set must
• participate fully in the identifying relationship
  (? a thick line)
• participate in a one to many relationship with
  the identifying owner (? an arrow)
• ? Weak entity sets have a thick rectangle, their
  keys are underlined with a broken line, and the
  identifying relationship has a thick diamond

35
Example
phone number
name
Organization
What would be the meaning if this was not a thick
line?
Gives
Won
Award
name
year
36
Example
How are the entity sets identified?
37
Example

• Suppose that you were storing information about
  books.
• Should books be modeled as a weak entity set or a
  regular entity set?
• Does ISBN identify a book
• The answer it depends what type of data you are
  interested in storing!

38
Copies of Books in Libraries
Owned By
name
Library
author
Book
title
id
isbn
Person
Copy Of
Borrowed
Copy
copy number
condition
39
ISA Hierarchies

• ISA Relationships Define a hierarchy between
  entity sets
• ISA is similar to inheritance
• ? ISA relationships are drawn as a triangle with
  the word ISA inside it. The "super entity-set" is
  above the triangle and the "sub entity-sets" are
  below

40
Example

• What are the keys of
• Movie Person
• Actor
• Director

address
id
birthday
Movie Person
name
ISA
picture
Actor
Director
41
Overlap Constraints

• Overlap constraints Determine whether two
  sub-entity sets can contain the same entity
• Example Can an Actor be a Director?
• ? Write "Actor OVERLAPS Director". If not
  written, assume no overlap

42
Covering Constraints

• Covering constraints Determine whether every
  entity in the super-entity set is also in at
  least one of the sub-entity sets
• Example Is every movie person either an Actor or
  a Director?
• ? Write "Actor AND Director COVER Movie Person".
  If not written, assume no covering

43
Aggregation

• Aggregation Allows us to indicate that a
  relationship set participates in a relationship
  set
• Remember, we want to store information about
  Actors, Films and their award. We will see why
  aggregation is needed for this

44
Whats Wrong?
All the actors for all their participation in all
the films must get an award!
picture
Actor
salary
Acted In
Award
year
Film
title
type
45
Whats Wrong?
An actor may get an award for a film in which he
has never acted in!
picture
Actor
salary
Acted In
Won
Award
year
Film
title
type
46
The Solution
Suppose that there are 3 actors 2 films 4
awards How many pairs can there be in
ActedIn? How many pairs can there be in Won? Note
that the pairs of Won are of a special type
picture
Actor
Won
salary
Acted In
Award
year
Film
title
type
47
Final Diagram
address
id
birthday
Movie Person
name
phone number
name
ISA
Organization
Gives
picture
Actor
Director
Won
salary
Acted In
Directed
Award
year
Film
name
year
title
type
48
References

• Database Management Systems, by Raghu
  Ramakrishnan Johannes Gehrke,
• third edition, McGraw-Hill, 2003.
• Chapters 2

49
Assignment 2

• In the Ramakrishnan book, exercises 2.2 and 2.3
  (Chapter 2). page 52.
• Exercise 2.2
• A university DB contains information about
  professors (identified by social security number,
  or SSN) and courses (identified by courseid).
  Professors teach courses each of the following
  situations concerns the Teachers relationship
  set. For each situation, draw an ER diagram that
  describes it (assuming no further constraints
  hold).
• Professors can teach the same course in several
  semesters, and each ofering must be recorded.
• Professors can teach the same course in several
  semesters, and only the most recent such offering
  needs to be recorded. (Assume this condition
  applies in all subsequent questions.)
• Every professor must teach some course.
• Every professor teaches exactly one course.
• Every professor teaches exactly one course, and
  every course must be taught by some professor.
• Now suppose that certain courses can be taught by
  a team of professors jointly, but it is possible
  that no one professor in a team can teach the
  course.

50
Assignment 2

• Exercise 2.3
• Design and draw ER diagram capturing all the
  following constraints regarding an university DB
• Professors have an SSN, a name, an age, a rank,
  and a research specialty.
• Projects have a project number, a sponsor name, a
  starting date, an ending date, and a budget.
• Graduate students have an SSN, a name, an age,
  and a degree program (M.S. or Ph.D.)
• Each project is managed by one professor (known
  as the projects principal investigator).
• Each project is worked on by one or more
  professors (known as the projects
  co-investigators).
• Professors can manage and/or work on multiple
  projects.
• Each project is worked on by one or more graduate
  students (the projects research assistants).

51
Assignment 2

• Exercise 2.3 cont.
• When grad. students work on a project, a
  professor must supervise their work on the
  project. Grad. Students may work on many projects
  (in this case they may have more than one
  supervisor).
• Departments have a department number, name, and a
  main office.
• Departments have a professor, who runs the
  department.
• Professors work in one or more departments, and
  for each department that they work in, a time
  percentage is associated with their job.
• Grad. students have one major department in which
  they are working on their degree.
• Each grad. Student has another, more senior grad.
  student (a student advisor) who advises him/her
  on what courses to take.