INSY 3010 Fall 2017 HOMEWORK 6 Due: 12/01/2017 11:00 PM
Homework 6 Deliverables
• A word document containing the following:
• Problem 1
• Desired Queries from Problem 2, Problem 3, and Problem 4 (You do not need to include screenshots
of the results of these queries).
• Screenshot/Scan of ER Diagram from Problem 3
• Desired Screenshots from Problem 2 and Problem 3
• problem2.sql
• problem3.sql
Notes
• You may work individually or in groups of two students – your choice.
• If you work in a group, make sure that both of you submit the exact same zip file on Canvas and indicate your
partner in a comment on the assignment.
• All files should be zipped into a single file for submission on Canvas (named
Homework6_LastName1FirstName1_LastName2FirstName2).
• You don’t need to submit any files already on Canvas. (Assume that the TAs have every file provided on
Canvas)
• We will test your results by running your SQL (.sql) file. Points will be deducted for non-sql files.
• ** On problem 2, it is near impossible for two groups to have exactly the same files, since you will be using
music of your choice. If found, we may question the authenticity of your work. Please work in complete honesty.
1. (5 pts) List the main steps in database design. What is the goal of each step? In which step is the ER model mainly
used?
2. (30 pts) Using the music_with_data.sql file on CANVAS as a template, drop the existing tables and create new
tables (preferably with identical names – albums, artists, played, tracks) using music of your choice and implement
the new tables in your existing database on acadmysql.duc.auburn.edu. Your database should include at least 25
songs from at least 4 artists and at least 5 albums. Call the file that implements your new music database tables
problem2.sql. Include a screenshot of a “select * from tracks” query in your word document. In addition, develop
the following SQL queries:
• List all tracks in your music database. For each track, display the track name, album name, and artist
name fields.
• List all albums, along with a count of the total number of tracks on each album.
• List all artists, along with a count of the total number of albums associated with each artist.
INSY 3010 Fall 2017 HOMEWORK 6 Due: 12/01/2017 11:00 PM
3. The Office of Provost at Auburn University has offered to give you a free lifetime supply of football tickets to home
games if you design a database for the employees. Given the rising cost of the tickets, you agree. Here's the
information that you gather:
• Professors have a bannerID, a name, an age, a rank, and a research specialty.
• Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.
• Graduate students have a bannerID, a name, an age, and a degree program (e.g., M.S. or Ph.D.).
• Each project is managed by one professor (known as the project's principal investigator).
• Each project is worked on by one or more professors (known as the project's co-investigators).
• Professors can manage and/or work on multiple projects.
• Each project is worked on by one or more graduate students (known as the project's research assistants).
• When graduate students work on a project, a professor must supervise their work on the project.
• Graduate students can work on multiple projects, in which case they will have a (potentially different)
supervisor for each one
• Departments have a department number, a department name, and a main office.
• Departments have a professor (known as the chair) who runs the department.
• Professors work in one or more departments, and for each department that they work in, a time
percentage is associated with their job.
a) (20 pts) Design and draw an ER diagram that captures the information about the university. Use only the basic
ER model here; that is, entities, relationships, and attributes. Be sure to indicate any keys and participation
constraints.
b) (10 pts) Develop a SQL script. that creates the tables. Call this file problem3.sql.
c) (10 pts) Implement the tables in your database on acadmysql.duc.auburn.edu using sufficient data of your
own (i.e. add at least 5 records to each table). Provide a screenshot of a “select * from departments” query.
4. Consider the following schema/database with the following format - table_name(column1, column2, column3,..)
Employee(employee-name, street, city)
Works(employee-name, company-name, salary)
Company(company-name, city)
Manages(employee-name, manager-name)
Develop SQL queries that:
a) (7.5 pts) finds the names of all employees who work for Auburn Bank.
b) (7.5 pts) finds the names of all employees who live in the same city as the company for which they work.
c) (10 pts) finds the names, street address, and cities of residence of all employees who work for Auburn Bank
and earn more than $40,000 per year.