1
Plan voor Vandaag
Relationele Databases 2002/2003 • Praktische dingen Hoorcollege 4 8 mei 2003
• Huiswerk ? 3.1, 3.2, 3.3, 3.4, 3.5.
Jaap Kamps & Maarten de Rijke April – Juli 2003
• 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
3
Huiswerk
• Docenten: 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
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/
• 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#)
• Practicum ? Eerste inleveropdracht wordt nagekeken ? http://www.science.uva.nl/~borkur/teaching/RDB0203/ ? Deadline tweede inleveropdracht: 6 juni 2002
4
Huiswerk
5
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.
3.3 Design a relational database corresponding to the E-R diagram of Figure 3.38.
? 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
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
9
Huiswerk
Huiswerk
3.4 (vervolg) . . .
3.5 Consider the relational database of Figure 3.39. For each of the following queries, given an expression in the relational algebra.
? 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.
• Figure 3.39: employee(person-name,street,city) works(person-name, company-name, salary) company(company-name, city) manages(person-name, manager-name)
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.
10
11
Huiswerk
Huiswerk
company(company-name,city) employee(person-name,street,city) works(person-name,company-name,salary) manages(person-name,manager-name)
company(company-name,city) employee(person-name,street,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.
• 3.5b. Find the names and cities of residence of all employees who work for First Bank Corporation.
Oplossing:
Oplossing:
Πperson -name (σcompany -name =“First Bank Corporation”(works))
Πperson -name ,city (employee ./ (σcompany -name =“First Bank Corporation”(works)))
12
13
Huiswerk
Huiswerk
employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
company(company-name,city) employee(person-name,street,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.
• 3.5d. Find the names of all employees who live in the same city as the company for which they work. Oplossing:
Oplossing: Πperson -name ,street ,city (σcompany -name =“First Bank Corporation”
Πperson -name (employee ./ works ./ company)
∧ 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”.
14
Huiswerk
15
Huiswerk
employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
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.
• 3.5f. Find the names of all employees in this database who do not work for First Bank Corporation.
Oplossing: Πperson -name ((employee ./ manages) ./(manager -name =employee2 .person -name ∧ employee .street =employee2 .street ∧ employee .city =employee2 .city )(ρemployee2 (employee)))
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
17
Huiswerk
Huiswerk
employee(person-name,street,city) company(company-name,city) works(person-name,company-name,salary) manages(person-name,manager-name)
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.
• 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: The following solution assumes that all people work for at most one company.
Oplossing:
Πperson -name (works) − (Πworks .person -name (works ./(works .salary ≤works2 .salary ∧ works2 .company -name =“Small Bank Corporation”) (ρworks2 (works))))
Πcompany -name (company ÷ (Πcity (σcompany -name =“Small Bank Corporation”(company)))) • De “Small Bank Corporation” zal 1 van de antwoorden zijn.
18
19
20
21
22
23
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
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!
