Relationele Databases 2002/2003 Hoorcollege 4 8 mei 2003 Jaap Kamps & Maarten de Rijke April – Juli 2003
1
Plan voor Vandaag • Praktische dingen • Huiswerk ? 3.1, 3.2, 3.3, 3.4, 3.5. • SQL Aantekeningen 2 ? Tabellen. • Theorie ? Silberschatz et al: hoofdstuk 3: Relationele Model (§3.3–§3.5). ? Silberschatz et al: hoofdstuk 4: SQL (§4.1–§4.4).
2
Huishoudelijke Zaken • Docenten: 1. Jaap Kamps, Email:
[email protected] 2. Maarten de Rijke, Email:
[email protected] • URL voor de cursus: ? http://www.science.uva.nl/~kamps/RDB/, of ? http://www.science.uva.nl/~mdr/Teaching/RDB/0203/ • Practicum ? Eerste inleveropdracht wordt nagekeken ? http://www.science.uva.nl/~borkur/teaching/RDB0203/ ? Deadline tweede inleveropdracht: 6 juni 2002
3
Huiswerk 3.1 Design a database for a university’s registrar’s office. The office maintains data about each class, including the instructor, the number of students enrolled, and the time and place of the class meetings. For each student-class pair, a grade is recorded • Oplossing (onderstreepte attributen staan voor de primary key): student(ss#, name) class(dept,courseno,time,place,enrollment) instructor(i ss#,name) takes(ss#,dept,courseno,grade) teaches(dept,courseno,i ss#)
4
Huiswerk 3.2 Describe the differences in meaning between the terms relation and relation schema. Illustrate your answer by referring to your solution to Exercise 3.1. ? Oplossing: Een relatie schema is een type definitie, en een relatie is een instantie van dat schema. ? Een voorbeeld van een relatie schema is: Student-schema = (ss#,name) ? Een voorbeeld van relatie gebaseerd op dat schema is: ss# name 123-45-6789 Tom Jones 456-78-9123 Mary Brown
5
Huiswerk 3.3 Design a relational database corresponding to the E-R diagram of Figure 3.38.
6
Huiswerk 3.3 Design a relational database corresponding to the E-R diagram of Figure 3.38. • Oplossing: De relationele database is als volgt. ? ? ? ? ?
person(driver-id,name,address) car (license,year,model) accident(report-number,location,date) owns(driver-id,license) participated(report-number,driver-id,license,damage-amount)
We nemen aan dat het report-nummer een unieke persoon en auto identificeren. Indien er meerdere autos en personen bij het ongeluk betrokken zijn, krijgen die een ander report-number.
7
Huiswerk 3.4 In Chapter 2, we showed how to represent many-to-many, manyto-one, one-to-many, and one-to-one relationship sets. Explain how primary keys help us to represent such relationship sets in the relational model. • Oplossing: Stel de primary key van relatie-schema R is {Ai1 , Ai2 . . . , Ain } en de primary key van relatie-schema S is {Bi1 , Bi2 , . . . , Bim }. Dan kan een relatie tussen beide worden gerepresenteerd als een tuple (Ai1 , Ai2 , . . . , Ain , Bi1 , Bi2 , . . . , Bim ). ? In een one-to-one relatie zal iedere waarde {Ai1 , Ai2 , . . . , Ain } in precies 1 tuple voorkomen, en hetzelfde geldt voor iedere {Bi1 , Bi2 , . . . , Bim }.
8
Huiswerk 3.4 (vervolg) . . . ? In een many-to-one relatie (e.g., many A - one B) zal iedere waarde {Ai1 , Ai2 , . . . , Ain } maar 1 keer voorkomen, en iedere waarde {Bi1 , Bi2 , . . . , Bim } kan meerdere malen voorkomen. ? In een many-to-many relatie zullen waarden {Ai1 , Ai2 , . . . , Ain } en {Bi1 , Bi2 , . . . , Bim } beiden meerdere keren kunnen voorkomen. Echter, in alle drie gevallen zal een tuple (Ai1 , Ai2 , . . . , Ain , Bi1 , Bi2 , . . . , Bim ) niet meer dan 1 keer voorkomen, dat is, de verzameling {Aj1 , Aj2 , . . . , Ajn , Bk1 , Bk2 , . . . , Bkm } is een superkey.
9
Huiswerk 3.5 Consider the relational database of Figure 3.39. For each of the following queries, given an expression in the relational algebra. • Figure 3.39: employee(person-name,street,city) works(person-name, company-name, salary) company(company-name, city) manages(person-name, manager-name)
10
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5a. Find the names of all employees who work for First Bank Corporation. Oplossing: Πperson -name (σcompany -name =“First Bank Corporation”(works))
11
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5b. Find the names and cities of residence of all employees who work for First Bank Corporation. Oplossing: Πperson -name ,city (employee ./ (σcompany -name =“First Bank Corporation”(works)))
12
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5c. Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum. Oplossing: Πperson -name ,street ,city (σcompany -name =“First Bank Corporation”
∧ salary >10000
(works ./ employee)) • We nemen aan dat iedere persoon slechts voor 1 bedrijf werkt. Als personen voor meerdere bedrijven werken, dan krijgen we alleen de personen die meer dan $10,000 per jaar verdienen bij alleen de “First Bank Corporation”.
13
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5d. Find the names of all employees who live in the same city as the company for which they work. Oplossing: Πperson -name (employee ./ works ./ company)
14
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5e. Find the names of all employees who live in the same city and on the same street as do their managers. Oplossing: Πperson -name ((employee ./ manages) ./(manager -name =employee2 .person -name ∧ employee .street =employee2 .street ∧ employee .city =employee2 .city )(ρemployee2 (employee)))
15
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5f. Find the names of all employees in this database who do not work for First Bank Corporation. Oplossing: Als we aannemen dat alle personen voor precies 1 bedrijf werken: Πperson -name (σcompany -name 6=“First Bank Corporation”(works)) • Als we toestaan dat personen in de database mogen voorkomen (e.g., in employee) zonder werknemer te zijn (e.g, in works), of als personen een baan bij meerdere bedrijven mogen hebben, is het probleem gecompliceerder: Πperson -name (employee) − Πperson -name (σcompany -name =“First Bank Corporation”(works))
16
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5g. Find the names of all employees who earn more than every employee of Small Bank Corporation. Oplossing: The following solution assumes that all people work for at most one company. Πperson -name (works) − (Πworks .person -name (works ./(works .salary ≤works2 .salary ∧ works2 .company -name =“Small Bank Corporation”) (ρworks2 (works))))
17
Huiswerk employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
• 3.5h. Assume the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located. Oplossing: Πcompany -name (company ÷ (Πcity (σcompany -name =“Small Bank Corporation”(company)))) • De “Small Bank Corporation” zal 1 van de antwoorden zijn.
18
Database System Concepts — Hoofdstuk 3 Vervolg van het relationele model Vorige keer: • 6 fundamentele operatoren: select, project, union, set difference, Cartesian product, en rename. • 4 gedefinieerde operatoren: set intersectie, natural join, division, en assignment. Vandaag secties §3.3, §3.4 en §3.5
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Database System Concepts — Hoofdstuk 4 Over Structured Query Language of SQL • Meest gebruikte query taal. • Een relationele taal. • Een niet-procedurele taal. Ruwweg secties §4.1 tot en met §4.4
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Overzicht Vandaag • Database System Concepts. ? Hoofdstuk 3: Relationele model (§3.3–§3.5). ? Hoofdstuk 4: SQL (§4.1–§4.4). • Opgaven: ? 3.1, 3.2, 3.3, 3.4, 3.5. • SQL Aantekeningen 2. ? Tabellen.
68
Volgende Keer bij Relationele Databases • Opgaven voor volgende keer (22 mei): ? 3.8, 3.9, 3.10 (alleen voor relationele algebra). ? 4.1, 4.4, 4.5 • SQL Aantekeningen 3. ? Meer Queries. • Rest SQL (Hoofdstuk 4).
69
End!
