CMPT 354 (00-3): Assignment 1
(100 marks)
Submission Requirements
- For the programming part, please hand hard copy of query and result.(you can do print screen)
- The assignment must be ordered correctly and stapled in the
top left corner.
- The hand-in version must include a header page indicating:
student name, student number, user id, course number and assignment
number. If not, 5 mark will be deducted.
Due time and date: 9:30 on October 11, 2000, Wednesday.
Please hand in your assignment to the instructor.
Part 1: Exercise Assignment (70 points)
1. (20 points) p48. Exercise 2.6 (Chapter 2)
2. (20 points) p84. Exercise 3.8 (Chapter 3)
3. (30 points) Consider the following relational database:
person(pname, street, city)
works_for(pname, cname, salary)
company(cname, city)
manages(pname, manager)
Answer each of the following queries in:
- the relational algebra
- the tuple relational calculus
- the domain relational calculus
- SQL
(a) Find the street and city of all employees who work for the Future Shop,
live in Burnaby, and earn more than $40,000.
(b) Find the names and the companies they work for, for all the people who
have a higher salary than their manager.
(c) Assume that companies may be located in several cities. Find all the
companies located in every city in which the Future Shop is located.
(d) For SQL only, find the number of managers who manage more than 5
employees living in Richmond.
Part 2: Program Assignment (30 points, 5 points each)
Please use only SQL query to do all the questions.
Diagram for the DB
Help with the assignment
Preparation:
Open SQL Server. (under cmpt354) and open the Northwind database and you can see the definition and datatype for the five table in that database. get familiar with SQL server.
Problems
- Question one: Create the tables in the above diagram in your own database. The table is empty right now.
- Question two: insert the values from Northwind database into your tables for all the five tables.
- Question three: insert the tuple (4,"TCompany", 1234567) into Shippers table
- Question four: delete all the tuples in the DB with shipVia/ShipperID =3. What happens if you try to delete the tuples in shippers table first then Orders table. Explain why.
- Question five: retrieve distinct CustomerID such that they live in London and their order's ShipVia is 1 and order by asc order.
- Question six: find the average unit price for each order, ordered by customer name and EmployeeID is 5.
Solution to assignment one (question one and two)
Solution to assignment one (question three)
Solution to programming part
Page maintained by: Nancy Liao
Last update: September 23, 2000