cs243: Datenbanken FS 2013 - Universität Basel | Informatik

UNIVERSITÄT BASEL
Prof. Dr. Heiko Schuldt
Ihab Al Kabary, MSc
Ilir Fetai, MSc
Nenad Stojni¢, MSc
cs243: Datenbanken
Übung 2
FS 2013
Deadline: 28.03.2012 (23:59 Uhr)
Aufgabe 1:
(5 Punkte)
a) Convert the specied data model depicted in the Figure 1 into a relational database
schema, and provide a corresponding executable SQL script. Determine the keys,
and introduce articial keys if necessary. Choose one of the possible variants for the
implementation of the inheritance given in the specied data model. Name the variant
you have selected and briey describe why you have chosen it.
(5 Punkte)
Abbildung 1: The database schema in ER
Aufgabe 2:
(25 Punkte)
a) Complete the object-relational database schema given below. This incomplete schema
implements parts of the data model given in task 1 using object-relational constructs.
1
Notice that we have added a new type `teachingAssistant_obj'. For the syntax you
may use the Oracle documentation:
http://download-west.oracle.com
/docs/cd/B14117_01/server.101/b10759/statements_8001.htm#i2083561
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
DROP
TABLE teachingAssistant;
TABLE lecture;
TYPE professor_obj;
TYPE phdStudent_obj;
TYPE address_obj;
TYPE hobbies_varray;
TYPE person_obj;
TYPE teachingAssistant_obj;
TYPE lecture_obj;
CREATE TYPE person_obj AS object(
firstName varchar(30),
lastName varchar(30),
hobbies hobbies_varray,
address address_obj) NOT FINAL NOT INSTANTIABLE;
CREATE TYPE teachingAssistant_obj UNDER universityStaff_obj(
institute varchar(30)
) NOT FINAL;
CREATE TYPE lecture_obj AS object(
title varchar(30),
[date] date,
location varchar(30)) NOT FINAL;
(10 Punkte)
b) Insert sample data into all tables of both the relational and the object-relational
schemas. An example of the insertion in the teachingAssistant table in the objectrelational schema is given as follows:
INSERT INTO teachingAssistant VALUES (
teachingAssistant_obj('John', 'Doe', hobbies_varray('soccer', 'music'),
address_obj('street in Basel', 11, 'Basel', 12345), 123,
'Informatics'));
(8 Punkte)
c) For both the relational and object-relational schemas, write SQL to display all the
`persons' in the database. (Hint: You should display all extensions of the person).
Furthermore, display the persons that are only Phd students, and the persons that
are only professors.
2
(7 Punkte)
Extra credit (5 points)
Write a function for the type `Professor', that computes the amount of lectures the professor is holding, and show how you can execute the function.
You can refer to the syntax in the lecture slides or you can use the following link:
http://download-west.oracle.com
/docs/cd/B14117_01/server.101/b10759/statements_8002.htm#i2064997
3