Database course
Download
1 / 48

Database Course - PowerPoint PPT Presentation


  • 212 Views
  • Updated On :

Database Course. General Information. TAs : Jonathan Mamou Gideon Rothschild Course Email: [email protected] Moderated Newsgroup: local.course.db.ta Students Newsgroup: local.course.db.stud Course Homepage: http://www.cs.huji.ac.il/~db. Assignments (1). About 10-12 assignments

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 'Database Course' - sherlock_clovis


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

General information l.jpg
General Information

  • TAs:

    • Jonathan Mamou

    • Gideon Rothschild

  • Course Email: [email protected]

  • Moderated Newsgroup: local.course.db.ta

  • Students Newsgroup: local.course.db.stud

  • Course Homepage: http://www.cs.huji.ac.il/~db


Assignments 1 l.jpg
Assignments (1)

  • About10-12 assignments

  • Weight is between 25 percent of final grade

  • All assignments must be handed in!

  • Assignments are done alone!


Assignments 2 l.jpg
Assignments (2)

  • Submission of Assignments:

    • Theoretical assignments - in the box in Ross -2

    • Programming assignments - electronic submission

  • Assignments are returned in Ross –2, grades available via the internet

  • Make sure that you have a grade when the exercise is returned!


Appeals and extensions l.jpg
Appeals and Extensions

  • Appeals are submitted in the box in Ross -2

  • Appeal form available via the internet

  • Appeals should be submitted not later than 1 week after assignments are returned

  • Extensions are possible in special cases (I.e., miluim, childbirth, etc.).

  • Ask for the extension before the due date


Course books l.jpg
Course Books

  • Database Management Systems, by Raghu Ramakrishnan

  • Principles of Database and Knowledge-Base Systems, Volumes I and II, by Jeffery Ullman (essentially chapter 7 concerning Design Theory)

  • Oracle 8i: The Complete Reference, by Kevin Loney and George Koch


Entity relationship diagrams l.jpg

Entity-Relationship Diagrams

Database Course, Fall 2004


Scenario l.jpg
Scenario

  • http://www.imdb.com wants to store information about movies

  • One of the steps:

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


Example requirements l.jpg
Example Requirements

  • http://www.imdb.com wants to store information about films

  • For actors and directors, we want to store their name, a unique identification number, address and birthday

  • For actors, we also want to store a photography

  • For films, we want to store the title, year of production and type

  • 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

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


Slide10 l.jpg

address

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


Entities entity sets l.jpg
Entities, Entity Sets

  • Entity (ישות): An object in the world that can be distinguished from other objects

  • 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


Attributes 2 l.jpg
Attributes (2)

  • Examples of 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

birthday

id

Actor

name

address

Another Option for a Key?


Relationships relationship sets l.jpg
Relationships, Relationship Sets

  • Relationship (קשר): Association among two or more entities

    • Relationships may have attributes

  • Relationship Set (קבוצת קשרים): Set of similar relationships

    • Examples of Relationship sets:

       Relationship sets are drawn using diamonds


Example17 l.jpg
Example

title

birthday

id

Film

Actor

year

Acted In

name

type

address

Where does the salary attribute belong?

salary


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
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 E1, …, en En

  • Formally, R E1x …x En

Director

id

name

id

Actor

Film

Produced

title

name


Another option remember recursive relationships l.jpg

id

director

actor

produced

Person

Film

title

name

Another Option: Remember Recursive Relationships


Binary vs trinary relationship l.jpg
Binary vs. Trinary Relationship

שם מוסד

מוסד

לימד

קורס

מרצה

שם קורס

מספר קטלוגי

מס זהות

שם מרצה

שם מוסד

לימד ב

ניתן ב

מוסד

לימד את

קורס

מרצה

שם קורס

מספר קטלוגי

מס זהות

שם מרצה


Important note l.jpg

id

Actor

Film

Acted In

title

name

Important Note

  • The entities in a relationship set identify the relationship

  • Suppose we wanted to store the role of an actor in a film.

  • How would we store information about a person who acted in one film in several roles?


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


Another example28 l.jpg
Another Example

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?

A film has at most one actor and one director


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 least 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

A film has exactly one director.

A director manages at least one film.


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


Example 135 l.jpg
Example (1)

phone

number

name

Organization

Gives

Won

Award

name

year


2 reasons why not l.jpg
2 Reasons Why Not:

org_

name

Won

Award

phone

number

name

year

  • other relationships with the entity set organization

  • 2 phone numbers for the same organization!


Example37 l.jpg
Example

author

title

Book

id

isbn

Person

Copy Of

Borrowed

Copy

copy number

condition


What if we store information about many libraries l.jpg
What if We Store Information About Many 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


Example43 l.jpg
Example

parent

woman

child

ParentOf

Married

man

Person

name

id

Is this good?

A man can be married to a man and a woman to a woman!


Example44 l.jpg

Parent

ParentOf

Child

Person

ISA

Married

Man

Woman

Example

name

id


Aggregation l.jpg
Aggregation

  • Aggregation: Allows us to indicate that a relationship set participates in a relationship set


Example46 l.jpg
Example

picture

Actor

Won

salary

Acted In

Award

year

Film

title

type


2 reason why not l.jpg

picture

Actor

salary

Acted In

Award

year

Film

title

type

2 Reason Why Not:

  • Each film has received at least one award

  • Each actor has received at least one award


1 reason why not l.jpg

picture

Actor

salary

Acted In

Won

Award

year

Film

title

type

1 Reason Why Not:

The same award can be granted to an actor A and to a film F although the actor A has not acted in the film F.


ad