W hat is a database management system
Download
1 / 51

What is a Database Management System - PowerPoint PPT Presentation


  • 241 Views
  • Updated On :

W hat is a Database Management System?. Spring 2008. Lecture slides by Dr. Sara Cohen. 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'What is a Database Management System' - Roberta


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
W hat is a database management system l.jpg

What is a Database Management System?

Spring 2008

Lecture slides by Dr. Sara Cohen


What and why l.jpg
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


What and why3 l.jpg
What and Why

  • Protect the data from corruption or security leaks

  • Allow multiple users to access the data simultaneously

  • Provide crash recovery

  • And more…



Scenario l.jpg
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


Requirements 1 l.jpg
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


Requirements 2 l.jpg
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.


Slide8 l.jpg

address

id

birthday

Movie Person

name

phone

number

name

In the rest of this lesson we explain this diagram

ISA

Organization

Gives

picture

Director

Actor

Won

salary

Acted In

Directed

Award

year

name

Film

year

title

type


Er diagrams general information l.jpg
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


Entities entity sets l.jpg
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


Attributes l.jpg
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)


Attributes 2 l.jpg
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


Example l.jpg
Example

birthday

id

Actor

name

address


Another option for a key l.jpg
Another Option for a Key?

birthday

id

Actor

name

address


Another option for a key15 l.jpg
Another Option for a Key?

birthday

id

Actor

name

address


Relationships relationship sets l.jpg
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


Example17 l.jpg

Where does the salary attribute belong?

salary

Example

title

birthday

id

Film

Actor

year

Acted In

name

type

address


Recursive relationships l.jpg
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


N ary relationship l.jpg

Director

id

name

id

Actor

Film

Produced

title

name

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, …, enin En

  • Formally, R E1x …x En


Example20 l.jpg

Director

id

name

id

Actor

Film

Produced

title

name

Example

  • Suppose that there are:

    • Actors: Mickey Mouse, Donald Duck

    • Directors: Big Bird, Kermit

    • Films: Mickey’s Club

How many triplets can be in the relationship set “Produced”?

How many pairs can be in the relationship set “Produced”?



Important note l.jpg
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


Key constraints l.jpg
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


One to many l.jpg
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


Many to many l.jpg
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


One to one l.jpg
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


Another example l.jpg
Another Example

Where would you put the arrow?

age

father

id

Person

FatherOf

child

name


Key constraints in ternary relationships l.jpg
Key Constraints in Ternary Relationships

Actor

id

name

id

Director

Film

produced

title

name

What does this mean?


Participation constraints l.jpg
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


Example 1 l.jpg
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


Example 2 l.jpg
Example (2)

  • We can combine key and participation constraints.

  • What does this diagram mean?

id

Director

Film

Directed

title

name


Storing award information l.jpg
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


Weak entity sets l.jpg
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


Weak entity sets34 l.jpg
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


Example35 l.jpg
Example

phone

number

name

Organization

What would be the meaning if this was not a thick line?

Gives

Won

Award

name

year


E xample l.jpg

גדוד

מספר גדוד

שייכת ל

פלוגה

אות פלוגה

שייכת ל

מחלקה

מספר מחלקה

Example

How are the entity sets identified?


Example37 l.jpg
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!


Copies of books in libraries l.jpg
Copies of Books in Libraries

Owned By

name

Library

author

Book

title

id

isbn

Person

Copy Of

Borrowed

Copy

copy number

condition


Isa hierarchies l.jpg
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


Example40 l.jpg
Example

  • What are the keys of:

  • Movie Person

  • Actor

  • Director

address

id

birthday

Movie Person

name

ISA

picture

Director

Actor


Overlap constraints l.jpg
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


Covering constraints l.jpg
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


Aggregation l.jpg
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…


What s wrong l.jpg
What’s 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


What s wrong45 l.jpg
What’s 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


The solution l.jpg

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

The Solution

picture

Actor

Won

salary

Acted In

Award

year

Film

title

type


Final diagram l.jpg

address

Final Diagram

id

birthday

Movie Person

name

phone

number

name

ISA

Organization

Gives

picture

Director

Actor

Won

salary

Acted In

Directed

Award

year

name

Film

year

title

type


References l.jpg
References

  • “Database Management Systems”, by Raghu Ramakrishnan & Johannes Gehrke,

    third edition, McGraw-Hill, 2003.

    Chapters: 2


Assignment 2 l.jpg
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.


Assignment 250 l.jpg
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 project’s principal investigator).

      • Each project is worked on by one or more professors (known as the project’s co-investigators).

      • Professors can manage and/or work on multiple projects.

      • Each project is worked on by one or more graduate students (the project’s research assistants).


Assignment 251 l.jpg
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.


ad