Assignment 2: Relational Queries, SQL

CMPT 354

Database Systems

Simon Fraser University

Summer 2011

Instructor: Oliver Schulte

Assignment 2: Relational Queries, SQL.

Total Marks: 300 + 10 bonus

Due Date: Friday, June 17, 11 pm.

Instructions: Check the instructions in the syllabus. The university policy on academic dishonesty and plagiarism (cheating) will be taken very seriously in this course. Everything submitted should be your own writing or coding. You must not let other students copy your work. Discussions of the assignment is okay, for example to understand the concepts involved. If you work in a group, put down the name of all members of your group. On your assignment, put down your name, the number of the assignment and the number of the course. Spelling and grammar count.

For parts that require a database system, please use SQL Server. You can use either your own home installation or the set-up in CSIL. You will need to import databases from MS Access into MS SQL Server.

Handing in the Assignment. Please post your assignment on our course management server . You should post a pdf document.

We also need a printout. Please hand in the printout to the assignment box in CSIL (Computing Science Instructional Lab). You need an access card for CSIL. You should put the printout in the assignment box on the Monday after the due date.

Part I. Total Marks: 100 + 10 bonus.

A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managedby an employee (exactly one); a child must be identified uniquely by name when the parent (who is an employee) is known. We are not interested in information about a child once the parent leaves the company.
Create a Relational Schema. Create one table for each of the relations required to represent this information. You may want to first draw an ER diagram to clarify the requirements for yourself. But we don’t require you to hand in an ER diagram, just the SQL commands for creating the tables. Note that this scenario is similar to the one considered in the textbook and the lectures.
Impose Integrity Constraints. Impose the constraints implied or stated in the description above, such as domains (e.g., integer for dno), key constraints, foreign key constraints, and participation constraints. You may use the Database Diagram feature to specify foreign key constraints in a graphical way. In addition, impose the following constraints.
Every department has exactly one manager.
Each employee has at most 4 dependents.
The total number of dependents of employees is at most 1000.

If you find it necessary to use assertions or triggers to impose these constraints, you can and should do so. The assertion construct has not been implemented in SQL Server, so if you use that, we will accept plain text SQL code as long as it is correct according to the SQL standard.

Bonus Question. 10 marks.

Consider the following slight change to the design. Suppose that we store with each employee a pointer to their spouse if they have one who’s also an employee (e.g., the ssn of the spouse).

Impose the following constraint in SQL: If two employees are spouses, then at most one of them has children covered under his/her policy. A paper-and-pencil answer is sufficient; you may use the assertion construct if you wish.

Marking Criteria, Part I. Total Marks: 100.

Technical Correctness (85 marks)

Presentation, Style (15 marks)

What to Submit. To report your solution of this exercise, you may use the Database Scripting Tool in the Enterprise Manager. The exact location depends on your Management Studio Version, e.g., “generate scripts” may be under a right-click menue. The tool allows you to print out the schemas, assertions, triggers, … of your database. This tool, however, may generate a huge amount of printout when used without care! Make sure that you just create the required information or remove the unnecessary stuff from the output files in a word processor before printing. SQL Server versions 2008 and up allow you to select objects to script from your database before you generate the script. If you don’t like the built-in scripting tools, you can use alternative reports, as you long as you convince us that you did manage to create the schema and the constraints in SQL Server. (With the exception of Assertions, see above.)

Part II. Total Marks: 100.

Introduction

In this assignment we use a Star Wars Trilogy database for science fiction fans. Your task will be to create SQL queries that answer some of the often asked questions. The Star Wars Database is posted as StarWars.mdb. This database is in Access format; you will need to import it into SQL Server.

Tables

Characters: contains information about the character's Name (primary key), Race (if known), Homeworld (if known) and Affiliation (rebels/empire/neutral/free-lancer).

Planets: contains information about the planet's Name (primary key), its Type (gas/swamp/forest/handmade/ice/desert), and its Affiliation (rebels/empire/neutral)

TimeTable: contains Character's Name, Planet's Name, Movie in which the character visited the planet and the time of arrival and departure from the planet. The primary key is Character's Name, Movie, Time of Arrival, Time of Departure. Movie 1 represents The Star Wars, Movie 2 represents Empire Strikes Back, and Movie 3 represents Return of the Jedi. Each movie has been divided into 10 time chunks and these chunks are used to define time of arrival and departure. So if Darth Vader visited Bespin (Cloud City) in Empire Strikes Back from the middle of the movie till its end, the record of it will look like this:

Character's Name / Planet's Name / Movie / Time of Arrival / Time of Departure
Darth Vader / Bespin / 2 / 5 / 10

Find all characters that have been on all neutral planets.
Find distinct names of the planets visited by humans affiliated with the empire.
For each character and for each neutral planet, how much time total did the character spend on the planet?
On which planets and in which movies has Luke been at the same time on the planet as Darth Vader?
Find humans that visited desert planets and droids that visited swampy planets. List the movies when it happened and the names of the characters.

If it is possible to write a query in relational algebra, you should write the query in relational algebra as well as in SQL. Note that relational algebra doesn’t have expressions for aggregate operations such as sum, average or max. You may have to use quotation marks for column names with spaces like ‘Character’s Name’. Alternatively, you can rename the column (e.g., CharName).

What to submit

1. A hard copy with results of the queries.

2. A copy of the SQL queries.

May the force be with you.

Marking Criteria, Part II.

Technical Correctness (80 marks)

Correctness of Results from Queries (10 marks)

Presentation, Style (10 marks)

Part III. (100 marks)

Consider the following relational schema. An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department.

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct_time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)

Use the Access file company.mdb for data to execute your queries. The file is posted on-line. Import the company.mdb database into Microsoft SQL server, then execute the queries above in the SQL server.

Write the following queries in SQL, and where possible, in relational algebra. Note that relational algebra and the domain calculus doesn’t have expressions for aggregate operations such as sum, average or max.
Print the names and ages of each employee who works in both the Hardware department and the Software department.
Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.
Find the managerids of managers who manage only departments with budgets greater than $1 million.
Find the enames of managers who manage the departments with the largest budgets.
If a manager manages one or more departments, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $4 million.