In Datenbanken 1 erlernten wir die Grundlagen der Relationalen Datenbanksysteme. Als Übung haben wir eine Schema entworfen und zu diesem Abfragen konstruiert.
Musicdb
Wir versuchten uns an einer Musik Datenbank mit Simplen Tabellen, die in Verbindung zueinander stehen:
%CODE{"plsql"}%
/* musicdb.sql
This is a dump of a simple music database created
for a summer lecture at the TU Graz
-
- authors
- Stephan Oberauer , Paul Rudolf Seebacher
- created
- 2008-06-03
*/
/* Database schema of 4 relations. One of primary keys should be compound,
i.e. should consists of more than one attribute. Additionally, an
explanation that the relations are in the Third Normal Form must be provided. */
DROP TABLE IF EXISTS track;
DROP TABLE IF EXISTS label;
DROP TABLE IF EXISTS genre;
DROP TABLE IF EXISTS artist;
DROP TABLE IF EXISTS album_has_track;
DROP TABLE IF EXISTS album;
CREATE TABLE album (
al_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
artist_ar_id INTEGER UNSIGNED NOT NULL,
al_title VARCHAR(255) NULL,
al_release_year YEAR NULL,
PRIMARY KEY(al_id),
INDEX album_FKIndex1(artist_ar_id)
);
CREATE TABLE album_has_track (
album_al_id INTEGER UNSIGNED NOT NULL,
track_t_id INTEGER UNSIGNED NOT NULL,
track_nr INTEGER UNSIGNED NULL,
PRIMARY KEY(album_al_id, track_t_id), /* compound primary key ! */
INDEX album_has_track_FKIndex1(album_al_id),
INDEX album_has_track_FKIndex2(track_t_id)
);
CREATE TABLE artist (
ar_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
ar_name VARCHAR(255) NULL,
ar_origin VARCHAR(255) NULL,
ar_founding_year YEAR NULL,
PRIMARY KEY(ar_id)
);
CREATE TABLE genre (
g_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
artist_ar_id INTEGER UNSIGNED NOT NULL,
g_name VARCHAR(255) NULL,
PRIMARY KEY(g_id),
INDEX genre_FKIndex1(artist_ar_id)
);
CREATE TABLE label (
l_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
album_al_id INTEGER UNSIGNED NOT NULL,
l_name VARCHAR(255) NULL,
l_country VARCHAR(255) NULL,
PRIMARY KEY(l_id),
INDEX label_FKIndex1(album_al_id)
);
CREATE TABLE track (
t_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
t_title VARCHAR(255) NULL,
t_comment VARCHAR(255) NULL,
PRIMARY KEY(t_id)
);
/* Three queries to the database implemented in terms of the
Relational Algebra, The queries must illustrate the
Select, Join, Project, Divide and one of Set operations. */
/* artists:
+-------+---------------+-----------+------------------+
| ar_id |
ar_name |
ar_origin |
ar_founding_year |
+-------+---------------+-----------+------------------+
| 1 |
Yonderboi |
Hungary |
1996 |
| 2 |
Kings of Leon |
USA |
2000 |
| 3 |
Ratatat |
USA |
2001 |
| 4 |
Radiohead |
UK |
1991 |
+-------+---------------+-----------+------------------+ */
INSERT INTO artist (ar_name, ar_origin, ar_founding_year) VALUES('Yonderboi', 'Hungary', '1996');
INSERT INTO artist (ar_name, ar_origin, ar_founding_year) VALUES('Kings of Leon', 'USA', '2000');
INSERT INTO artist (ar_name, ar_origin, ar_founding_year) VALUES('Ratatat', 'USA', '2001');
INSERT INTO artist (ar_name, ar_origin, ar_founding_year) VALUES('Radiohead', 'UK', '1991');
/* albums:
+-------+--------------+-------------------------+-----------------+
| al_id |
artist_ar_id |
al_title |
al_release_year |
+-------+--------------+-------------------------+-----------------+
| 1 |
1 |
Splendid Isolation |
2005 |
| 2 |
2 |
Aha Shake Heartbrake |
2004 |
| 3 |
2 |
Youth and Young Manhood |
2003 |
| 4 |
3 |
Classics |
2006 |
| 5 |
4 |
OK Computer |
1997 |
| 6 |
4 |
Pablo Honey |
1993 |
+-------+--------------+-------------------------+-----------------+*/
INSERT INTO album (artist_ar_id, al_title, al_release_year) VALUES('1', 'Splendid Isolation', '2005');
INSERT INTO album (artist_ar_id, al_title, al_release_year) VALUES('2', 'Aha Shake Heartbrake', '2004');
INSERT INTO album (artist_ar_id, al_title, al_release_year) VALUES('2', 'Youth and Young Manhood', '2003');
INSERT INTO album (artist_ar_id, al_title, al_release_year) VALUES('3', 'Classics', '2006');
INSERT INTO album (artist_ar_id, al_title, al_release_year) VALUES('4', 'OK Computer', '1997');
INSERT INTO album (artist_ar_id, al_title, al_release_year) VALUES('4', 'Pablo Honey', '1993');
/* tracks:
+------+--------------------------------------+-----------+
+------+--------------------------------------+-----------+
| 1 |
Climbing Up The Walls |
|
| 2 |
Airbag |
|
| 3 |
Paranoid Android |
|
| 4 |
Lucky |
|
| 5 |
The Tourist |
|
| 6 |
Karma Police |
|
| 7 |
Let Down |
|
| 8 |
Electioneering |
|
| 9 |
No Surprises |
|
| 10 |
Subterranean Homesick Alien |
|
| 11 |
Fitter Happier |
|
| 12 |
Exit Music (For A Film) |
|
| 13 |
Badly broken butterflies |
|
| 14 |
Follow me home |
|
| 15 |
Soulbitch |
|
| 16 |
Trains in the night |
|
| 17 |
Amor |
|
| 18 |
Eyes for you |
|
| 19 |
Were you thinking of me? |
|
| 20 |
People always talk about the weather |
|
| 21 |
Before you snap |
|
| 22 |
Love hides |
|
| 23 |
All we go to hell |
|
| 24 |
Motor |
|
| 25 |
Even if you are victorious |
|
| 26 |
Montanita |
|
| 27 |
Lex |
|
| 28 |
Wildcat |
|
| 29 |
Swisha |
|
| 30 |
Loud Pipes |
|
| 31 |
Nostrand |
|
| 32 |
Gettysburg |
|
| 33 |
Kennedy |
|
| 34 |
Tacobel Canon |
|
| 35 |
Tropicana |
|
| 36 |
Taper Jean Girl |
|
| 37 |
Slow Night, So Long |
|
| 38 |
King of the Rodeo |
|
| 39 |
The Bucket |
|
| 40 |
Where Nobody Knows |
|
| 41 |
Soft |
|
| 42 |
Rememo |
|
| 43 |
Day Old Blue |
|
| 44 |
Velvet Snow |
|
| 45 |
Pistol of Fire |
|
| 46 |
Four Kicks |
|
| 47 |
Razz |
|
| 48 |
Milk |
|
| 49 |
O Dusty |
|
| 50 |
Genius |
|
| 51 |
Holy Roller Novocaine |
|
| 52 |
Red Morning Light |
|
| 53 |
Wicker Chair |
|
| 54 |
Happy Alone |
|
| 55 |
Mollys Chambers |
|
| 56 |
Tranny |
|
| 57 |
Joes Head |
|
| 58 |
Talihina Sky |
|
| 59 |
California Waiting |
|
| 60 |
Wasted Time |
|
| 61 |
Spiral Staircase |
|
| 62 |
Vegetable |
|
| 63 |
Stop Whispering |
|
| 64 |
Creep |
|
| 65 |
Thinking About You |
|
| 66 |
Anyone Can Play Guitar |
|
| 67 |
Blow Out |
|
| 68 |
You |
|
| 69 |
Prove Yourself |
|
| 70 |
I Cant |
|
| 71 |
Ripcord |
|
| 72 |
How Do you? |
|
| 73 |
Lurgee |
|
| 74 |
Creep |
|
+------+--------------------------------------+-----------+*/
/* Radiohead - OK Computer */
INSERT INTO track (t_title, t_comment) VALUES('Climbing Up The Walls', '');
INSERT INTO track (t_title, t_comment) VALUES('Airbag', '');
INSERT INTO track (t_title, t_comment) VALUES('Paranoid Android', '');
INSERT INTO track (t_title, t_comment) VALUES('Lucky', '');
INSERT INTO track (t_title, t_comment) VALUES('The Tourist', '');
INSERT INTO track (t_title, t_comment) VALUES('Karma Police', '');
INSERT INTO track (t_title, t_comment) VALUES('Let Down', '');
INSERT INTO track (t_title, t_comment) VALUES('Electioneering', '');
INSERT INTO track (t_title, t_comment) VALUES('No Surprises', '');
INSERT INTO track (t_title, t_comment) VALUES('Subterranean Homesick Alien', '');
INSERT INTO track (t_title, t_comment) VALUES('Fitter Happier', '');
INSERT INTO track (t_title, t_comment) VALUES('Exit Music (For A Film)', '');
/* Yonderboi */
INSERT INTO track (t_title, t_comment) VALUES('Badly broken butterflies', '');
INSERT INTO track (t_title, t_comment) VALUES('Follow me home', '');
INSERT INTO track (t_title, t_comment) VALUES('Soulbitch', '');
INSERT INTO track (t_title, t_comment) VALUES('Trains in the night', '');
INSERT INTO track (t_title, t_comment) VALUES('Amor', '');
INSERT INTO track (t_title, t_comment) VALUES('Eyes for you', '');
INSERT INTO track (t_title, t_comment) VALUES('Were you thinking of me?', '');
INSERT INTO track (t_title, t_comment) VALUES('People always talk about the weather', '');
INSERT INTO track (t_title, t_comment) VALUES('Before you snap', '');
INSERT INTO track (t_title, t_comment) VALUES('Love hides', '');
INSERT INTO track (t_title, t_comment) VALUES('All we go to hell', '');
INSERT INTO track (t_title, t_comment) VALUES('Motor', '');
INSERT INTO track (t_title, t_comment) VALUES('Even if you are victorious', '');
/* Ratatat */
INSERT INTO track (t_title, t_comment) VALUES('Montanita', '');
INSERT INTO track (t_title, t_comment) VALUES('Lex', '');
INSERT INTO track (t_title, t_comment) VALUES('Wildcat', '');
INSERT INTO track (t_title, t_comment) VALUES('Swisha', '');
INSERT INTO track (t_title, t_comment) VALUES('Loud Pipes', '');
INSERT INTO track (t_title, t_comment) VALUES('Nostrand', '');
INSERT INTO track (t_title, t_comment) VALUES('Gettysburg', '');
INSERT INTO track (t_title, t_comment) VALUES('Kennedy', '');
INSERT INTO track (t_title, t_comment) VALUES('Tacobel Canon', '');
INSERT INTO track (t_title, t_comment) VALUES('Tropicana', '');
/* Kings of Leon - Aha Shake Heartbreak */
INSERT INTO track (t_title, t_comment) VALUES('Taper Jean Girl', '');
INSERT INTO track (t_title, t_comment) VALUES('Slow Night, So Long', '');
INSERT INTO track (t_title, t_comment) VALUES('King of the Rodeo', '');
INSERT INTO track (t_title, t_comment) VALUES('The Bucket', '');
INSERT INTO track (t_title, t_comment) VALUES('Where Nobody Knows', '');
INSERT INTO track (t_title, t_comment) VALUES('Soft', '');
INSERT INTO track (t_title, t_comment) VALUES('Rememo', '');
INSERT INTO track (t_title, t_comment) VALUES('Day Old Blue', '');
INSERT INTO track (t_title, t_comment) VALUES('Velvet Snow', '');
INSERT INTO track (t_title, t_comment) VALUES('Pistol of Fire', '');
INSERT INTO track (t_title, t_comment) VALUES('Four Kicks', '');
INSERT INTO track (t_title, t_comment) VALUES('Razz', '');
INSERT INTO track (t_title, t_comment) VALUES('Milk', '');
/* Kings of Leon - Youth and Young Manhood */
INSERT INTO track (t_title, t_comment) VALUES('O Dusty', '');
INSERT INTO track (t_title, t_comment) VALUES('Genius', '');
INSERT INTO track (t_title, t_comment) VALUES('Holy Roller Novocaine', '');
INSERT INTO track (t_title, t_comment) VALUES('Red Morning Light', '');
INSERT INTO track (t_title, t_comment) VALUES('Wicker Chair', '');
INSERT INTO track (t_title, t_comment) VALUES('Happy Alone', '');
INSERT INTO track (t_title, t_comment) VALUES('Mollys Chambers', '');
INSERT INTO track (t_title, t_comment) VALUES('Tranny', '');
INSERT INTO track (t_title, t_comment) VALUES('Joes Head', '');
INSERT INTO track (t_title, t_comment) VALUES('Talihina Sky', '');
INSERT INTO track (t_title, t_comment) VALUES('California Waiting', '');
INSERT INTO track (t_title, t_comment) VALUES('Wasted Time', '');
INSERT INTO track (t_title, t_comment) VALUES('Spiral Staircase', '');
/* Radiohead - Pablo Honey */
INSERT INTO track (t_title, t_comment) VALUES('Vegetable', '');
INSERT INTO track (t_title, t_comment) VALUES('Stop Whispering', '');
INSERT INTO track (t_title, t_comment) VALUES('Creep', '');
INSERT INTO track (t_title, t_comment) VALUES('Thinking About You', '');
INSERT INTO track (t_title, t_comment) VALUES('Anyone Can Play Guitar', '');
INSERT INTO track (t_title, t_comment) VALUES('Blow Out', '');
INSERT INTO track (t_title, t_comment) VALUES('You', '');
INSERT INTO track (t_title, t_comment) VALUES('Prove Yourself', '');
INSERT INTO track (t_title, t_comment) VALUES('I Cant', '');
INSERT INTO track (t_title, t_comment) VALUES('Ripcord', '');
INSERT INTO track (t_title, t_comment) VALUES('How Do you?', '');
INSERT INTO track (t_title, t_comment) VALUES('Lurgee', '');
INSERT INTO track (t_title, t_comment) VALUES('Creep', '');
/* labels
+------+-------------+-----------------------+-----------+
| l_id |
album_al_id |
l_name |
l_country |
+------+-------------+-----------------------+-----------+
| 1 |
1 |
Mole Listening Pearls |
Hungary |
| 2 |
2 |
Sony BMG |
USA |
| 3 |
3 |
RCA |
USA |
| 4 |
4 |
XL Recordings |
USA |
| 5 |
5 |
Parlophone |
UK |
| 6 |
6 |
Parlophone |
UK |
+------+-------------+-----------------------+-----------+*/
INSERT INTO label (album_al_id, l_name, l_country) VALUES('1', 'Mole Listening Pearls', 'Hungary');
INSERT INTO label (album_al_id, l_name, l_country) VALUES('2', 'Sony BMG', 'USA');
INSERT INTO label (album_al_id, l_name, l_country) VALUES('3', 'RCA', 'USA');
INSERT INTO label (album_al_id, l_name, l_country) VALUES('4', 'XL Recordings', 'USA');
INSERT INTO label (album_al_id, l_name, l_country) VALUES('5', 'Parlophone', 'UK');
INSERT INTO label (album_al_id, l_name, l_country) VALUES('6', 'Parlophone', 'UK');
/* genres
+------+--------------+------------------+
+------+--------------+------------------+
| 1 |
1 |
Downtempo |
| 2 |
2 |
Indie Rock |
| 3 |
3 |
Electronic |
| 4 |
4 |
Alternative Rock |
+------+--------------+------------------+*/
INSERT INTO genre (artist_ar_id, g_name) VALUES('1', 'Downtempo');
INSERT INTO genre (artist_ar_id, g_name) VALUES('2', 'Indie Rock');
INSERT INTO genre (artist_ar_id, g_name) VALUES('3', 'Electronic');
INSERT INTO genre (artist_ar_id, g_name) VALUES('4', 'Alternative Rock');
/* album_has_track
+-------------+------------+----------+
| album_al_id |
track_t_id |
track_nr |
+-------------+------------+----------+
| 1 |
23 |
1 |
| 1 |
17 |
2 |
| 1 |
18 |
3 |
| 1 |
13 |
4 |
| 1 |
14 |
5 |
| 1 |
19 |
6 |
| 1 |
20 |
7 |
| 1 |
22 |
8 |
| 1 |
24 |
9 |
| 1 |
16 |
10 |
| 1 |
15 |
11 |
| 1 |
21 |
12 |
| 1 |
25 |
13 |
| 2 |
37 |
1 |
| 2 |
38 |
2 |
| 2 |
36 |
3 |
| 2 |
45 |
4 |
| 2 |
48 |
5 |
| 2 |
39 |
6 |
| 2 |
41 |
7 |
| 2 |
47 |
8 |
| 2 |
43 |
9 |
| 2 |
46 |
10 |
| 2 |
44 |
11 |
| 2 |
42 |
12 |
| 2 |
40 |
13 |
| 3 |
52 |
1 |
| 3 |
54 |
2 |
| 3 |
60 |
3 |
| 3 |
57 |
4 |
| 3 |
53 |
5 |
| 3 |
56 |
6 |
| 3 |
59 |
7 |
| 3 |
61 |
8 |
| 3 |
55 |
9 |
| 3 |
50 |
10 |
| 3 |
49 |
11 |
| 3 |
51 |
12 |
| 3 |
58 |
13 |
| 4 |
26 |
1 |
| 4 |
27 |
2 |
| 4 |
32 |
3 |
| 4 |
28 |
4 |
| 4 |
35 |
5 |
| 4 |
30 |
6 |
| 4 |
33 |
7 |
| 4 |
29 |
8 |
| 4 |
31 |
9 |
| 4 |
34 |
10 |
| 5 |
2 |
1 |
| 5 |
3 |
2 |
| 5 |
10 |
3 |
| 5 |
12 |
4 |
| 5 |
7 |
5 |
| 5 |
6 |
6 |
| 5 |
11 |
7 |
| 5 |
8 |
8 |
| 5 |
1 |
9 |
| 5 |
9 |
10 |
| 5 |
4 |
11 |
| 5 |
5 |
12 |
| 6 |
68 |
1 |
| 6 |
64 |
2 |
| 6 |
72 |
3 |
| 6 |
63 |
4 |
| 6 |
65 |
5 |
| 6 |
66 |
6 |
| 6 |
71 |
7 |
| 6 |
62 |
8 |
| 6 |
69 |
9 |
| 6 |
70 |
10 |
| 6 |
73 |
11 |
| 6 |
67 |
12 |
+-------------+------------+----------+*/
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '23', '1');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '17', '2');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '18', '3');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '13', '4');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '14', '5');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '19', '6');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '20', '7');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '22', '8');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '24', '9');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '16', '10');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '15', '11');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '21', '12');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('1', '25', '13');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '37', '1');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '38', '2');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '36', '3');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '45', '4');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '48', '5');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '39', '6');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '41', '7');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '47', '8');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '43', '9');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '46', '10');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '44', '11');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '42', '12');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('2', '40', '13');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '52', '1');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '54', '2');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '60', '3');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '57', '4');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '53', '5');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '56', '6');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '59', '7');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '61', '8');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '55', '9');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '50', '10');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '49', '11');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '51', '12');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('3', '58', '13');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '26', '1');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '27', '2');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '32', '3');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '28', '4');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '35', '5');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '30', '6');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '33', '7');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '29', '8');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '31', '9');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('4', '34', '10');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '2', '1');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '3', '2');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '10', '3');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '12', '4');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '7', '5');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '6', '6');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '11', '7');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '8', '8');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '1', '9');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '9', '10');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '4', '11');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('5', '5', '12');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '68', '1');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '64', '2');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '72', '3');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '63', '4');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '65', '5');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '66', '6');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '71', '7');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '62', '8');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '69', '9');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '70', '10');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '73', '11');
INSERT INTO album_has_track (album_al_id, track_t_id, track_nr) VALUES('6', '67', '12');
/* explanation that the relations are in the Third Normal Form:
A relation is in 3rd Normal Form if Every data item outside the primary key is
identifiable
o by the primary key
o by the whole primary key
o only by the primary key
Referring to the section “Functional Dependencies” it is quite obvious, that all
relations of the database are in 3rd Normalform.
*/
/*
* Three queries to the database implemented in terms of the Relational Algebra, The queries must illustrate the Select, Join, Project, Divide and one of Set operations.
* Three queries in terms of the Relational Calculus. Each query should operate with 2-3 relations (at least) simultaneously.
You can choose either Relational Calculus with Domain Variables or Relational Calculus with Tuple variables.
Note, that the queries must utilize existential and universal quantifiers in both cases.
* Three queries in terms of SQL. Each query should operate with 2-3 relations (at least) simultaneously. Illustration of "Nested SQL blocks", "Group By" and "Having" Clauses is compulsory.
Important! You have to build up a working database application by means of the
MySQL? database management system which includes:
* Installing
MySQL? Database Management System
* Definition of the database schema by means of the system
* Sample database (just put some tuples into the previously defined relations)
* Implementation of the three SQL queries mentioned above via mySQL client (queries should be presented in "selection from multiple relations" syntax, i.e. should be defined without nested SQL blocks.
* E x a m p l e ( report.doc report.pdf)
Creating a working group
You can develop the example in collaboration with colleagues. In other words, the example may be presented on behalf of a working group consisting of maximum 5 students (see Instructions).
Packaging and presenting the sample database (see uploading check list)
Thus, your results should consist of
* written report
* a sample database running on your computer. The mySQL directory containing your database should be compressed into an archive.
Both files (report and compressed database) should be uploaded into your personal locker on the internet server.
Deadline for uploading is June 10th 23:59
*/
%ENDCODE%
--
PaulRudolfSeebacher - 30 Jul 2008
Main
Education Web
If not mentioned otherwise content is licensed under CC Attribution Non-Commercial Share Alike 3.0 Austria