CS 34800: Information Systems

Project 1: SQL Queries

Due 11:59pm EDT Saturday, 17 September 2016

Introduction

You are going to use Oracle to perform some queries and create views for a database. The schema and sample data of the database are provided. The project should be run on CS data.cs.purdue.edu workstations. Information about getting your Oracle account and general initial configuration is available in: https://www.cs.purdue.edu/resources/facilities/oracle.html

Description of the database

MusicalInfo is a database used by a music company to keep track of its albums. MusicalInfo keeps track of the musicians, albums and songs including details about the recordings. The back-end database of MusicalInfo consists of the relations defined as below:

MUSICIAN (MNO, NAME, ADDRESS, PHONE)
ALBUM (ANO, ATITLE, COPYRIGHT_DATE, MNO, COPY_SOLD)
SONG (SNO, STITLE, ANO, LYRICIST)
PLAY (MNO, INSTRUMENT)
PERFORM (MNO, SNO, INSTRUMENT)

  • The MUSICIAN relation contains information about the musicians in the system. The MNO attribute uniquely identifies each musician.
  • The ALBUM relation contains information about the albums. The ANO attribute acts as the unique identifier of an album; MNO refers to a musician already existing in the MUSICIAN relation and acts as the producer of the album. Other information is: title, copyright date, and the number of copies that have been sold. An album contains many songs. An album may have many songs performed by various musicians, but it has only one musician as its producer.
  • The SONG relation has SNO as the unique identifier. ANO refers to the album in which the song appears. LYRICIST is the name of the lyricist who wrote the lyrics of the song.
  • The PLAY relation contains information about the instruments that a musician can play.
  • The PERFORM relation contains information about the instrument that a musician plays in each song. Note that a musician can play many instruments, but in a particular song, he/she plays only one instrument.

    • Accessing the database

    In this project, you will have READ-ONLY access to the aforementioned database (which is hosted on one of the TAs' account). READ-ONLY means you can only run SELECT queries on the database and will not be able to modify the data. The database has some initial data to help you test your queries and get started on the project. In order to grade the project, the TAs will be testing your queries on a different dataset. So please DO NOT hardcode the results or the queries.

    You can access the relations in the database just by name of a relation e.g., to access records in the relation SONG, you would type SONG, the relation MUSICIAN can be accessed by typing MUSICIAN, etc.

    As an example, for the query "Find the distinct names of all Musicians", you would type:
      SELECT DISTINCT name
      FROM Musician;
    Note: issue with permissions and priviliges in Oracle server has been resolved. It means that you can use table names without adding username dulybysh. in front of the table's name.

    Your Assignment

    Based on the above database, your task is to write SQL queries and programs in PL/SQL as specified in Part 1 and Part 2.

      PART 1 (70 points)

      Queries

      Write SQL queries for the following questions and run them on the Oracle system. If you are making any assumptions, state them clearly and document your queries.
      1. Find the names of musicians who cannot play 'Piano'.
      2. Find the names of musicians who only use 'Piano' or 'Guitar' to perform.
      3. Find the phone numbers of all musicians who had used 'Guitar' to perform.
      4. Find the ANOs of the albums which have a song performed by 'Guitar'.
      5. Find the ANOs of the albums which only have songs performed by 'Guitar'.
      6. Find the MNO of the musician who produces the largest number of albums.
      7. Find the names of the musicians who can play all instruments.
      8. Find the names of the musicians who can play only one instrument.
      9. Find the names of the musicians who have never performed a song produced by other musicians.
      10. Find the ANOs of the albums that have a song performed by other musicians (not the producer).
      11. Find the names of the musicians who have performed for albums that have a copyright before '03-Sep-1999'.
      12. Find the name of the musician who has the album with the maximum number of sold copies.
      13. Find the names of (distinct) musicians who can play 2 or more instruments.
      14. For each musician except 'Sid Vicious', print the musician's name, phone and average number of sold copies of albums produced by the musician. If the musician has not produced any album, print 0 for the average number of sold copies.

      What to submit

      The result of your work is a file named p1_your_career_login.sql which contains all the SQL statements you used in this assignment. Not naming your file as your p1_your_career_login.sql may result in penalty points.

      Add the following lines in the beginning of your .sql file (mandatory step):

      rem CS348 SQL Project 11
      rem your_first_name your_last_name
      rem your_Purdue_email_address

      PART 2 (30 points)

      Writing PL/SQL programs to process data.
      You are going to use PL/SQL (Oracle's procedural extension to SQL) to write a few programs to process data. The result of this part of the project should be a file named p2_your_career_login.sql that will contain all the PL/SQL statements you develop to handle the data processing tasks described below. Your grade depends on how you use the PL/SQL statements and the result of running the .sql file. You will use the same database used in Part 1 of this project. It is required that you perform the following tasks:

      1. Write a program that generates information about songs aggregated by their albums, print the output of your program in the following format:

        Album   NumOfSongs   NumOfMusicians   NumOfLyricists
        --------------------------------------------------------
        Parachutes    10    5    2

        This program returns a list of the albums and information about songs in the album. NumOfSongs indicates the number of songs in a certain album. NumOfMusicians indicates the number of musicians that performed in an album (if a musician performed for more than one song, count him/her once). NumOfLyricists indicates the number of distinct lyricists for an album who wrote the lyrics for at least one song of the album.

      2. Write a program that generates for each musician, say M, the top two musicians that performed with M in most of their albums. Print the output of your program in the following format:

        Musician MNO: 1
        Musician Name: Rihanna

        MNO   MusicianName   NumOfAlbums
        ----------------------------------------------------
        5    Justin   4
        2    Michael  1

        This program returns the top two musicians who performed with a certain musician in most of their albums. MusicianName indicates the name of the musician and NumOfAlbums indicates the number of albums in which this musician performed with musician M.

      Note: Please, do not create stored procedures since we don't have privilege to grant you permission to create stored procedures.

      What to submit

      You are required to submit ONLY ONE file for this part, p2_your_career_login.sql. Not naming your file as your p2_your_career_login.sql may result in penalty points. This file should contain the PL/SQL program for each query.

    Add the following lines in the beginning of your .sql file (mandatory step):

    rem CS348 SQL Project 12
    rem your_first_name your_last_name
    rem your_Purdue_email_address

    A useful strategy

    Here is a useful approach for doing the project:
    1. Follow the introduction about the environment setup, connect to the Oracle server with your assigned Oracle account.
    2. Try a few simple SQL statements until you are comfortable interacting with sqlplus.
    3. Workout the SQL statements you need to solve the above queries.
    4. Use a text editor you are familiar with to create a .sql file that contains the necessary SQL statements for this project.
    5. Test your .sql file.
    6. For Part 2, work through the examples in Chapter 3 of “Oracle 10g Programming: A Primer, Rajshekhar Sunderrman” to become familiar with the PL/SQL syntax.
    7. In sqlplus command mode, type “SET SERVEROUTPUT ON SIZE 32000”

    Turning in your .sql files for Part 1 and Part 2

    Please create a single zip file that contains both the .sql files for Part 1 and Part 2. Please turn in the project by uploading the zip file in Blackboard (click on Project 1 and Attach File).

    Valid XHTML 1.1