1.
Database Scheme & Description
Let us assume a database that contains information about bands, their members and
their releases over the years. The following schema has been proposed for this
database:
Band (bid, name, country,
webpage) Release (rid, bid,
title, year, type, rating) Song
(title, rid, cdbonus)
Member (mid, name, stillalive)
MemberOf (mid, bid, startyear, endyear, instrument)
The schema includes the following information, where [type] indicates that the
associated attribute is of type [type]:
Bands, including
o their names [VARCHAR(50)],
o country of origin if known [VARCHAR(20)],
o their official webpage if available [VARCHAR(120)] and
o a unique INTEGER as identifier in the database (i.e. bid).
Different releases of the bands, including
o the title of each release [VARCHAR(120)],
o the year of the release [INTEGER],
o the type of the release (e.g., album, live, mini or single)
[VARCHAR(10)],
o and, if available, a rating by a specialist critic (an INTEGER out of 10).
o Every release has a unique INTEGER as identifier in the database (i.e.
rid) and refers to a band (i.e., bid field).
Songs, including
o their titles [VARCHAR(120)],
o which release they belong to (i.e., rid field),
o and whether they are a CD bonus or not (i.e. Y for Yes, N for No)
[CHAR(1)].
Members, including
o their names [VARCHAR(50)],
o whether they are still alive or not (i.e. Y for Yes, N for No) [CHAR(1)]
o and a unique INTEGER as identifier in the database (i.e., mid).
Relationships between Members and Bands, stating
o whether a particular member served in a specific band,
o with a start year and an end year (INTEGERs),
o and their role in the band (i.e. instrument with values vocals,
guitar, bass, keyboards and drums) [VARCHAR (15)].
In addition, the designer of the database has chosen the following assumptions for
the data Input:
(i)
EndYear will have NULL as value if the corresponding member is still a
member of the band.
(ii)
StartYear will have NULL as value if the corresponding member has been
in the band since its creation.
3.1
Notes
Note 1: When writing your solutions, you must not use views in any of your solutions.
You must not use operators that are not part of the SQL3 standard (e.g. LIMIT, NVL),
and you should not use SQL3 procedures (e.g. COALESCE, CASE, IF ... ELSE
statements); using the standard SQL to the fullest extent possible makes your queries
portable from a DBMS to another.
All your SQL solutions must be readable and must be preceded by a short
description in the form of a comment that briefly explains how your solution works.
Note 2: The table contents from the CSV files found in the file: FIRSTASSIGNMENT-FILES.zip on the Moodle. Each file name matches the table name
and includes tuples with attributes in the exact order given in the provided
database scheme, so please create your tables and import the data properly.
You can also refer to the pgAdminIII introduction sheet to see an example of how to
populate a table from a CSV file. Since some tables (e.g. Song) contain Latin
characters, you have to choose the appropriate encoding types (UTF8).
3.2SQL Statements over the Bands Database
STATEMENT: Provide the CREATE TABLE statements, and justify
the choice of any keys or constraints you declared.
QUERY 1: Find all the members called Tim, who are still alive.
QUERY 2: Get the names of all Iron Maidens current band members.
QUERY 3: Find out if any band has the same name as any member of any
band.
QUERY 4: Show how many members in the database have played bass.
The column should be renamed to as Bass_Player_Count
QUERY 5: Display the discography of each drummer.
Columns should be renamed to as Drummer_Name, Release_Name, Year
All rows for the same drummer should appear together and sorted according to
release year.
QUERY 6: Write an SQL query to display the number of bonus tracks
that each band has recorded, whose corresponding release was rated
over 5 or there is no rating.
Columns should be renamed to as Band_Name, Bonus_Num and sorted by:
Band_Name
QUERY 7: Find the names of the bands whose each releases year is after
1999.
QUERY 8: Find the names of the bands with releases since 2011.
When writing SQL queries, you should only use operators that are part of
the SQL3 standard.
