(a) Design an E-R diagrams for this situation, indicating the data attributes and stating clearly any assumptions that you make.
(b) Construct relational schemas for the above E-R diagram.
(15 marks each)
2
2. Consider the database schema:
STUDENT (Name, Student_number, Class, Major),
COURSE (Course_name, Course_number, Credit_hours, Department),
PREREQUISITE (Course_number, Prerequisite_number),
where example values of Department are: CS, MATH, MUSIC, and example values for Course_number are: CS3130, MATH1003, MUSIC2343 (i.e. each Course_number has the Department as prefix).
a. If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer Science and Software Engineering) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be changed.
b. The kind of changes in (a) above can occur from time to time. Can you restructure the columns in the database schema to reduce the impact of the above change?
(10 marks each)
3. Design a generalization–specialization hierarchy for a motor-vehicle sales company. The company sells motorcycles, passenger cars, vans, and buses. Assume that there are the two categories of vehicles: commercial and non-commercial. Note that each vehicle would attract a general sales tax, as well as an additional tax applicable to its category. You should determine the attributes of each entity type and indicate these attributes using appropriate notations. You should state any assumptions you make.
(22 marks)
The following are related to Questions 4-10. Consider the following database schema, where the primary keys are underlined.
EMPLOYEE (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date),
DEPT_LOCATIONS (Dnumber, Dlocation),
WORKS_ON (Essn, Pnumber, Hours),
PROJECT (Pname, Pnumber, Plocation, Dnum)
DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
where Fname signifies first name; Minit, middle initial; Lname, last name; Ssn, Essn are the social security numbers; Super_ssn is the social security number of the supervisor; Dname, Dnum, Dno, Dlocation are department name, number and location (similarly for projects); while other attributes have an obvious interpretation.
(4 marks each for the following seven questions)
3
4. Retrieve the name and address of all employees who work for the “Research” department.
6. Make a list of project numbers for projects that involve an employee whose last name is “Smith”, either as a worker or as a manager of the department that controls the project.
7. Retrieve the names of employees who have no dependents.
8. List the names of managers who have at least one dependent.
9. Find all employees directly supervised by “James Borg”.
10. Find all employees directly supervised by those directly supervised by “James Borg”. Would it be possible to find all employees supervised by a given employee at all levels?
