1 database course. 2 general information tas: –sara cohen –jonathan mamou course email:...
TRANSCRIPT
![Page 1: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/1.jpg)
1
Database CourseDatabase Course
![Page 2: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/2.jpg)
2
General InformationGeneral Information
• TAs:
– Sara Cohen
– Jonathan Mamou
• 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
![Page 3: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/3.jpg)
3
Assignments (1)Assignments (1)
•About 10 assignments• Weight is between 15-30
percent of final grade•All assignments must be
handed in!•Assignments are done
alone!
![Page 4: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/4.jpg)
4
Assignments (2)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!
![Page 5: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/5.jpg)
5
Appeals and ExtensionsAppeals 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
![Page 6: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/6.jpg)
6
NotesNotes
• Jonathan Mamou will deal with most
problems that are related to the assignments
• Sara Cohen will deal with most problems that
are related to the tirgul material
• Tirgulim will usually be taught without slides.
However, incomplete lecture notes (in slide
format) will usually be available via the
internet
![Page 7: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/7.jpg)
7
Entity-Relationship DiagramsEntity-Relationship Diagrams
Database Course, Fall 2003
![Page 8: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/8.jpg)
8
ScenarioScenario
• http://www.imdb.com wants to store information about movies
• Three steps:– Requirements Analysis: Discover what information needs
to be stored, how the stored information will be used, etc. Taught in "System Analysis and Design" (Offer Drori)
– 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)
![Page 9: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/9.jpg)
9
Example RequirementsExample 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 (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
• 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.
![Page 10: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/10.jpg)
10
Movie Person
ISA
Actor
id
name
address
birthday
picture Director
Filmyear
typetitle
Acted In Directedsalary
Award
Organization
Gives
year
name
name
phonenumber
Won
![Page 11: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/11.jpg)
11
Entities, Entity SetsEntities, 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
![Page 12: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/12.jpg)
12
AttributesAttributes
• 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)
![Page 13: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/13.jpg)
13
Attributes (2)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
![Page 14: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/14.jpg)
14
ExampleExample
Actorid
name address
birthday
![Page 15: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/15.jpg)
15
Another Option for a Key?Another Option for a Key?
Actorid
name address
birthday
![Page 16: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/16.jpg)
16
Another Option for a Key?Another Option for a Key?
Actorid
name address
birthday
![Page 17: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/17.jpg)
17
Relationships, Relationship SetsRelationships, 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
![Page 18: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/18.jpg)
18
ExampleExample
Actorid
name
address
birthday
Acted In Film
title
type
year
salaryWhere does the salary
attribute belong?
![Page 19: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/19.jpg)
19
Recursive RelationshipsRecursive Relationships
Employeeid
name
address
phone number
Manages
• 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
manager
worker
![Page 20: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/20.jpg)
20
nn-ary Relationship-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
Actorid
name
Produced Film title
Directorid name
![Page 21: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/21.jpg)
21
Another Option: Another Option: Remember Recursive Remember Recursive
RelationshipsRelationships
![Page 22: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/22.jpg)
22
Important NoteImportant Note
Actorid
name
Acted In Film title
• 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 would we store information about a person who acted in one film in several roles?
![Page 23: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/23.jpg)
23
Key Constraints (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
![Page 24: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/24.jpg)
24
One-to-ManyOne-to-Many
A film is directed at most one director
A director can direct any number of films
Directorid
name
Directed Film title
Director Directed Film
![Page 25: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/25.jpg)
25
Many-to-ManyMany-to-Many
A film is directed by any number of directors
A director can direct any number of films
Directorid
name
Directed Film title
Director Directed Film
![Page 26: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/26.jpg)
26
One-to-OneOne-to-One
A film is directed by at most one director
A director can direct at most one film
Directorid
name
Directed Film title
Director Directed Film
![Page 27: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/27.jpg)
27
Another ExampleAnother Example
Personid
name
age
FatherOf
Where would you put the arrow?
father
child
![Page 28: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/28.jpg)
28
Key Constraints in Key Constraints in Ternary RelationshipsTernary Relationships
Directorid
name
produced Film title
Actorid name
What does this mean?
![Page 29: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/29.jpg)
29
Participation Constraints 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
![Page 30: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/30.jpg)
30
Example (1)Example (1)
• A film has at lease one director
• A director can direct any number of films
Directorid
name
Directed Film title
Director Directed Film
Do you think that there
should be a participation constraint
from Director to Directed?
![Page 31: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/31.jpg)
31
Example (2)Example (2)
• We can combine key and participation constraints.
• What does this diagram mean?
Directorid
name
Directed Film title
![Page 32: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/32.jpg)
32
Weak Entity SetsWeak 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
![Page 33: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/33.jpg)
33
Weak Entity SetsWeak 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
![Page 34: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/34.jpg)
34
Example (1)Example (1)
Award
Organization
Gives
year
name
name
phonenumber
Won
![Page 35: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/35.jpg)
35
2 Reasons Why Not:2 Reasons Why Not:
Award
year
org_name
name
phonenumber
Won
![Page 36: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/36.jpg)
36
ExampleExample
Booktitle
author
isbn
Copy
copy number condition
Copy Of
Borrowed
Person
id
![Page 37: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/37.jpg)
37
What if We Store What if We Store Information About Many Information About Many
Libraries?Libraries?
Booktitle
author
isbn
Copy
copy number condition
Copy Of
Borrowed
Person
id
Owned ByLibraryname
![Page 38: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/38.jpg)
38
ISA HierarchiesISA 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
![Page 39: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/39.jpg)
39
ExampleExample
What are the keys of:
1. Movie Person
2. Actor
3. Director
Movie Person
ISA
Actor
id
name
address
birthday
picture Director
![Page 40: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/40.jpg)
40
Overlap ConstraintsOverlap 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
![Page 41: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/41.jpg)
41
Covering ConstraintsCovering 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
![Page 42: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/42.jpg)
42
child Person Married
name id
ParentOf
parent woman
man
ExampleExample
Is this good?
![Page 43: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/43.jpg)
43
AggregationAggregation
• Aggregation: Allows us to indicate
that a relationship set participates in a
relationship set
![Page 44: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/44.jpg)
44
ExampleExample
Actorpicture
Filmyear
typetitle
Acted Insalary
Award
Won
![Page 45: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/45.jpg)
45
1 Reason Why Not:1 Reason Why Not:
Actorpicture
Filmyear
typetitle
Acted Insalary
Award
![Page 46: 1 Database Course. 2 General Information TAs: –Sara Cohen –Jonathan Mamou Course Email: db@cs.huji.ac.il Moderated Newsgroup: local.course.db.ta Students](https://reader035.vdocuments.us/reader035/viewer/2022081514/56649f455503460f94c66e45/html5/thumbnails/46.jpg)
46
1 Reason Why Not:1 Reason Why Not:
Actorpicture
Filmyear
typetitle
Acted Insalary
AwardWon