Übungsblatt 6 - Lösung - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, F. Wenzel, M. Endres
WS 2009/2010
04. Dez. 2009
Lösungsblatt 6
Datenbanksysteme I
Aufgabe 1: Datendefinition und Integritätsbedingungen
Lösung:
a) Erstellen der gewünschten Relation:
CREATE TABLE programmer
(EmpNo VARCHAR(3),
Last_Name VARCHAR(25) NOT NULL,
First_Name VARCHAR(25),
Hire_Date Date,
Project NUMBER(3),
Language VARCHAR(15),
TaskNo NUMBER(2),
Clearance VARCHAR(25),
CONSTRAINT programmer_pk PRIMARY KEY(EmpNo)
);
b) Die project Relation wird folgendermaßen erstellt:
CREATE TABLE project
(project_id NUMBER(3),
project_name VARCHAR(50),
CONSTRAINT project_pk PRIMARY KEY(project_id)
);
c) Anschließend wird ein Foreign-Key in der programmer Relation hinzugefügt:
ALTER TABLE programmer ADD CONSTRAINT programmer_fk FOREIGN KEY(Project)
REFERENCES project(project_id) ON DELETE CASCADE;
Wird ein Projekt Tupel aus project gelöscht, so werden über die Foreign-Key Bedingung ON DELETE CASCADE
auch verbundene Tupel in programmer gelöscht.
d) Erweitern der project Relation:
ALTER TABLE project ADD(manager_id INTEGER);
Dezimieren der programmer Relation:
ALTER TABLE programmer DROP(clearance);
e) Einfügen von Tupeln in die project Relation:
INSERT INTO project VALUES(1, ’projekt1’, 22);
INSERT INTO project VALUES(2, ’projekt2’, 21);
INSERT INTO project VALUES(3, ’projekt3’, 22);
Einfügen von Tupeln in die programmer Relation:
INSERT INTO programmer VALUES(’1’, ’Maier’ , ’Hans’,
TO_DATE(’04-03-1983’, ’MM-DD-YYYY’), 1, ’Java’, 44);
INSERT INTO programmer VALUES(’2’, ’Huber’ , ’Paul’,
TO_DATE(’11-23-2004’, ’MM-DD-YYYY’), 2, ’C++’, 43);
1
Löschen des Eintrags von Projekt 1:
DELETE FROM project WHERE project_id=1;
Beim Löschen von Projekt 1 werden alle Programmierer aus programmer gelöscht, die über den Fremdschlüssel
mit Projekt 1 verbunden sind. Dies war in Teilaufgabe c) bereits explizit so vorgesehen.
Aufgabe 2: SQL: DDL und DML
a) DDL:
CREATE TABLE Bundeslaender
(Name VARCHAR(20) PRIMARY KEY, Einwohner INTEGER NOT NULL CHECK (Einwohner >
0));
CREATE TABLE Wahlkreise
(Nr INTEGER PRIMARY KEY, Bezeichnung VARCHAR(20),
Bundesland VARCHAR(20) REFERENCES Bundeslaender);
CREATE TABLE Wahlbezirke
(Nr INTEGER PRIMARY KEY,
Wahlberechtigte INTEGER NOT NULL CHECK (Wahlberechtigte > 0),
Wahllokal VARCHAR(20),
Wahlkreis INTEGER REFERENCES Wahlkreise ON DELETE CASCADE);
CREATE TABLE Parteien
(Name VARCHAR(20) PRIMARY KEY, Mitglieder INTEGER NOT NULL CHECK (Mitglieder >
0));
CREATE TABLE Direktkandidaten
(SozialVNr VARCHAR(20) PRIMARY KEY, Name VARCHAR(20),
Partei VARCHAR(10) REFERENCES Parteien ON DELETE SET NULL,
Wahlkreis INTEGER REFERENCES Wahlkreise ON DELETE CASCADE);
CREATE TABLE Erststimmen
(Wahlbezirk INTEGER NOT NULL REFERENCES Wahlbezirke ON DELETE CASCADE,
Jahr INTEGER NOT NULL,
Kandidat VARCHAR(20) NOT NULL REFERENCES Direktkandidaten ON DELETE CASCADE,
Stimmen INTEGER NOT NULL CHECK (Stimmen >=0), PRIMARY KEY (Wahlbezirk, Jahr,
Kandidat));
CREATE TABLE Zweitstimmen
(Wahlbezirk INTEGER NOT NULL REFERENCES Wahlbezirke ON DELETE CASCADE,
Jahr INTEGER NOT NULL,
Partei VARCHAR(10) NOT NULL REFERENCES parteien ON DELETE CASCADE,
Stimmen INTEGER NOT NULL CHECK (Stimmen >=0), PRIMARY KEY(Wahlbezirk, Jahr,
Partei));
b) DML:
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Bundeslaender VALUES(’Bayern’, 12000000);
Wahlkreise VALUES(153, ’Oberbach’, ’Bayern’);
Wahlbezirke VALUES(21921, 6700, ’Buergerhaus’, 153);
Parteien VALUES(’P1’, 580000);
Direktkandidaten VALUES(’2005-DK02’, ’Huhn’, ’P1’, 153);
Erststimmen VALUES(21921, 2005, ’2005-DK02’, 750);
Zweitstimmen VALUES(21921, 2005, ’P1’, 535);
2
Aufgabe 3: Deklarative Änderungsoperationen
Lösung:
a) Erstellen der s candidates Relation:
CREATE TABLE s_candidates(
id VARCHAR2(3) PRIMARY KEY,
last_name VARCHAR2(20),
first_name VARCHAR2(20),
salary NUMBER (11,2));
b) Einfügen aller Mitarbeiter aus s emp, die nicht bereits Manager in der Hauptgesellschaft sind:
INSERT INTO s_candidates
SELECT e.id, e.last_name, e.first_name, e.salary
FROM s_emp e
WHERE e.id NOT IN
(SELECT f.id FROM s_emp f, s_emp m WHERE f.id= m.manager_id);
c) Gehaltserhöhung für alle Kandidaten, die bereits Kunden in der Hauptgesellschaft betreuten:
UPDATE s_candidates
SET salary = 1.2*salary
WHERE id IN
(SELECT e.id FROM s_emp e, s_customer c WHERE c.sales_rep_id=e.id);
d) Löschen aller Kandidaten, deren Gehalt 30 Prozent über dem Durchschnittsgehalt in s candidates liegt:
DELETE FROM s_candidates
WHERE salary >(SELECT 1.3*AVG(salary) FROM s_candidates);
e) Erstellen einer View mit Nachnamen und Vornamen der Kandidaten:
CREATE VIEW cand_list (last_name,first_name) AS
SELECT last_name, first_name FROM s_candidates;
3