UCLA Fall 04 CS143 Project 1
Defining and Querying Databases in SQL
Using IBM DB2
Due: 12:00pm Noon, Tuesday, Nov 2
SEAS ACCOUNT
Every student enrolled in the class is assigned a new seas account for the project. Please note that your login/passwd for this project account will be explicitly included in your codes. So please:
Change your passwd to anything else except your private passwd;
Change your home directory pemissions properly, e.g., "chmod
go-rwx your_home_directory". Use "pwd" to show your home directory after
you login.
You can only login to one of the following machines: landfair.seas.ucla.edu, lindbrook.seas.ucla.edu, westholme.seas.ucla.edu. To decide
which machine you should login, please check here.
Your project has to run on your SEAS project account against the DB2 Server installed on SEAS.
You can do your project on your personal machine,but you have to copy them to the SEAS account and make sure it works.
There is no exception for that.
For detailed information on running DB2 on SEAS, please check DB2 on SEAS.
You need to use JDBC(Java Database Connectivity), a standard interface to access SQL database. Please refer to
JDBC at SEASNET for detailed information.
The CLASSPATH for DB2 JDBC driver library(/u/cs/class/cs143v/cs143vta/sqllib/java/db2java.zip) is already setup when you
setup DB2 environment.
The Project
In this project, you will use IBM DB2 to create a database,
populate it and develop the queries in SQL and execute the queries on DB2, through DB2 command line and JDBC.
You should work individually on this assignment.
Data Set
The data sets are located at: /w/class.01/cs/cs143v/cs143vta/proj1/: album.csv, label.csv, song.csv, concert.csv, and musican.csv.
You need to create tables first and use DB2 import or load utility to load the data into the tables.
You must initially create the following tables in the database and populate them with the sample data set.
Relations
MUSICIAN(musicianName, income, address, labelNumber)
LABEL(labelNumber, labelName, city, revenue)
ALBUM(albumName, musicianName, labelNumber, price, year)
SONG(songName, albumName, length, genre)
CONCERT(musicianName, date, place, city, price, capacity)
Queries
The following are the queries you need to write:
Find names of musicians and their albums, for those who are giving concerts in ¡°Washington DC¡±.
Find all musician names and album names with a song containing the word ¡°Happy¡± in the song name.
Retrieve the names and income of every musician who has released exactly two albums since 1993 (inclusive).
Retrieve the album names and musicians of every album released in 1995, provided that none of the songs in the album are of genre "pop".
For each year, calculate the average price, the highest price and the lowest price of the albums. List the results by ascending year.
Find the musicians who are listed to perform in two different places on the same date: print the name of the musician and the date of conflict (one row per conflict).
List the musicians who have concerts in all the cities where "Lush" ever gave a concert.
List the musicians who do not have any song from the genres from which ¡°Lush¡± have songs.
Find the albums with the highest cost per minute of combined length of songs in the album. Print the album name and also the cost per minute.
For each musician, find the genre of the majority of the songs in his/her albums.
Find the names of the two top labels in terms of the number of songs produced. (Assume that there is no tie and do not use OLAP functions.)
Delete from the database all Michael Jackson¡¯s concerts that are scheduled for a future date.
Move all concerts by 'Dead Can Dance' in and after 1997 to 'UCLA' in 'Los Angeles'.
Insert your favorite musician into the database. For the musician, insert his/her label information, at least one album, and at least two songs from each album. Fabricate any information you need. (Do that using insert statements).
Queries will be basically graded on correctness. But a few points may be deducted for bad style, like an unnecessarily complicated query which is difficult to understand.
Tasks
The following are the tasks for the project:
PART I: Queries in DB2 SQL scripts
Create tables for the relations;
Use DB2 load utility to load data into the tables;
Write queries for Query 1-14;
Please put all the above queries in a DB2 SQL script file.
Please refresh the tables (clear tables all reload all the data) for PART II.
PART II: Queries in JDBC
After the tables are refreshed, please rewrite Query 10-14 with JDBC.
Submission(Deadline: 12:00 Noon, Tuesday, Nov 2)
What to Submit
PART I: please put all SQL scripts in a single file, e.g., query.sql, and the result in another file query.rst;
PART II: please submit your Java code for JDBC queries, e.g., Query.java, and the result file, jdbc.rst.
Late Submission Policy
Late submissions will be penalized by 50 percent for each additional day (by noon), so if you submit 2 days late, you will have no credit at all. Late submissions should be emailed to the TA because submit command won't work after the deadline.
Electronic Submission of Projects
The submit program under SEAS account allows the students to electronically turn in any type of file to the instructor.
Please put the following content in a file readme.txt:
Class Name: CS143, LEC.1
Name(Last, First):
Student SID:
Project login account:
Database you use:
(STUDBL, STUDBW, or STUDBN)
and submitted as follows:
submit cs143 query.sql query.rst Query.java jdbc.rst readme.txt
Academic Honesty
Each of you is expected to submit your own original work.
On many occasions it is useful to ask others (the instructor, the TA's, or other students) for hints or debugging help,
or to talk generally about programming strategies.
Such activity is both acceptable and encouraged, but you must indicate any assistance (human or otherwise) that you received.
Any assistance received that is not given proper citation will be considered plagiarism. In any event,
you are responsible for coding, understanding, and being able to explain on your own all project work that you submit.
We will pursue aggressively all suspected cases of plagiarism, and they will be handled through official University channels.
If you have any questions about this policy or about the degree to which we will pursue Honor Code violations,
please discuss your concerns with the course staff immediately.