COMP 7640 Homework Assignment #1
Due: 5:00 pm Oct. 5 (Friday), 2018
- A company database needs to store information about employees (identified by eid, 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). An employee works in one or more departments; some employees may work in pairs as partners (an employee can have at most one partner); each department is managed by an employee; the information for the children will be collected associated with his/her father or mother. A child must be identified uniquely by name when the parent is known. We are not interested in information about a child once the parent leaves the company.
Draw an ER diagram that captures this information. Show the reasons for different constrains briefly. (40 marks)
- Consider the scenario from Question 1, where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. Also state the constraints that you cannot capture, if any.
(40 marks)
- Answer each of the following questions briefly. The questions are based on the following relational schema:
Emp(eid: integer, ename: string, age: integer, salary: real) Dept(did: integer, dname: string, budget: real, managerid: integer) Works(eid: integer, did: integer, pcttime: real)
- a) Assume that an employee works in one or more departments. Write the SQL statement required to create the Works relation, including appropriate versions of primary and foreign key integrity constraints, such that when an employee is deleted all his/her work records are also removed, and the deletion of a department is disallowed if there are one or more employees working in the department.
- b) Write an SQL statement to create the Dept relation such that every department is guaranteed to have a manager (represented by managerid) and the manager must be an existing employee.
- c) Write an SQL statement to add Alice as an employee with eid = 200, age = 20 and salary = 15000.
d) Write an SQL statement to delete the ‘Toy’ department. _________________________________ _________________________________
(20 marks)
Late Penalty: Late homework will be marked down 20% for each day it is late. Any exceptions to the late rule must be made prior to when the assignment is due and the excuse needs to be a good one – just too busy won’t cut it. Individual exceptions are unfair to other students and hence they won’t be made unless the circumstances are truly exceptional.
Plagiarism: All work submitted by you should be your own. Copying or sharing of assignments would constitute plagiarism. Penalty will be given to those involved in plagiarism. This will be no exception once plagiarism is caught – being copied without notice won’t excuse it; you should simply keep your assignment well. The Turnitin system might be used to check the “originality” of your submitted assignment for the detection of plagiarism.
Submission: Please put the hard copy of your assignment to “Zhiwei Zhang (張志威)” mailbox outside the department general office at 7/F of Sir Run Run Shaw Building.