354 CMPT - Database Systems I
Assignment 1 Due date: June 3, 2008 12 marks, 6% of grade J.P. Delgrande May 20, 2008
Important Note: Students must work individually on this, and other CMPT 354, assignments. You may not discuss the specific questions in this assignment, nor their solutions with any other student. You may not provide or use any solution, in whole or in part, to or by another student. You are encouraged to discuss the general concepts involved in the questions in the context of completely different problems. If you are in doubt as to what constitutes acceptable discussion, please ask! 1. (4 marks) Consider the following relational database schema for a company. There are 5 schemas: employee, department, dept location, project and works on; these schemas store the following information, and contain the following attributes: Employees have a social insurance number, name and address, along with salary, supervisor, and department. employee(sin, emp name, emp address, salary, supervisor sin, dept#) Departments have a number, name, location, and manager: department(d num, d name, d location, manager sin) Projects have a name, number, location, and department that they're associated with: project(proj name, proj number, proj location, d number) Employees work on projects for a given number of hours per week: works on(emp sin, project number, hours) Note that some of the attributes have the same domain, but different names. sin, supervisor sin, emp sin all refer to Social Insurance Numbers. dept# and d num refer to department numbers. Specify the following queries using relational algebra. (a) Find the names of employees in department 5 who work more than 10 hours/week on Project X. (b) Find the names of employees who are directly supervised by "Yi Jin". 1
(c) (d) (e) (f)
Find the names of employees who earn more than their supervisor. Find the names of employees who work on every project. Find the names of employees who do not work on any project. Find the names and addresses of employees who work on at least one project located in Vancouver but whose department is not Vancouver. (g) Find the number of projects that "Beth Sproule" is working on.
2. (4 marks) You run a small theatre that puts on a series of plays over the year. The theatre has a single auditorium. You are interested in constructing relational a database to manage ticket sales and seating for customers. The following data is to be stored. A play will have a title, director, and perhaps other information. Associated with a play will be a set of performances, where each performance has associated with it a date and time. There is a set of orders of tickets for performances. The customer name, number of tickets (seats), ticket type and cost will need to be stored. Thus a customer will place an order for a certain number of tickets for a particular performance. Associated with each order entry will be a set of seat numbers reserved for that order. A ticket type will indicate a class of tickets and the cost of a seat. Examples might include front (or middle or back) orchestra, front balcony, etc. (a) Construct an E-R diagram for the booking information, stating any assumptions you have made regarding the real world being modeled by your design. Other attributes can be introduced where you think necessary (for example to provide a primary key), but give (brief) reasons for any added information. (b) Give the relational schemas corresponding to your ER diagram. 3. (4 marks) The editor of the Journal of Database Systems Research wants you to develop a database for handling manuscripts submitted to the journal. The journal publishes research papers in database systems. Authors submit papers to the journal. Submitted paper...