Due date: 5pm Friday week 9
Late submission is not acceptable
Question 1
(Based on exercise 8.7 in the textbook – Fundamentals of Database Systems)
Consider the following database schema
Student(Name, Student_number, Class, Major)
Course(Course_name, Course_number, Credit, Department)
Prerequisite (Course_number, Prerequisite_number)
Section (Section_identifier, Course_number, Semester, Year, Instructor)
Mark_Report(Student_number, Section_identifier, Mark)
- What are the referential integrity constraints that should hold on the schema?
- Write appropriate SQL Data Definition Language statements to define the database.
Question 2
Consider the following database schema
Student (StudentId, FullName, BDate,Major, Stage)
Course (Code, Name, LecturerName)
Enrol(StudentId, CourseCode, Session, Mark)
Specify the following queries in SQL
- Find the name of all first year (stage 1) students who are enrolled in a course taught by Do Minh
- Find the name of all student who enrolled in course Database System in session S1-07 and had failed mark (mark < 5)
- Find the names of lecturers who have taught more than 5 courses
- For student with id ‘20062020 ‘, find all courses and corresponding mark that student had enrolled in session S1-07
- How many Computer Science student are currently in stage 3?
- For each student in stage 1, print the student id and the average mark.
- Find the names of students who are not enrolled in any courses.
- Find the name of the student(s) who had the highest mark for course Database System in session S2-07
Question 3
Consider the following database 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, ename, age, salary)
Dept(did, budget, managerid)
Works(eid, did, pct_time)
The following SQL DDL statements are used to define the database.
CREATE TABLE Employees (
eid INTEGER,
ename VARCHAR(30),
age INTEGER,
salary REAL,
PRIMARY KEY (eid)
);
CREATE TABLE Departments (
did INTEGER,
dname VARCHAR(20),
budget REAL,
manager INTEGER,
PRIMARY KEY (did),
FOREIGN KEY (manager) REFERENCES Employees(eid)
);
CREATE TABLE WorksIn (
eid INTEGER,
did INTEGER,
pct_time REAL,
PRIMARY KEY (eid,did),
FOREIGN KEY (eid) REFERENCES Employees(eid),
FOREIGN KEY (did) REFERENCES Departments(did)
);
Give the answer for each of the following question in SQL
- Add a constraint to the CREATE TABLE statements above to ensure that every department must have a manager.
- Add a constraint to the CREATE TABLE statements above to ensure that employees’s salary is exceed the low-bar of 15,000
- When an employee is removed from the database, it makes sense to also delete all of the records that show which departments he/she works for. Modify the CREATE TABLE statements above to ensure that this occurs.
- The company has several years of growth and high profits, and considers that the Sales department is primarily responsible for this. Write an SQL statement to give all employees in the Sales department a 10% pay rise.
- A new government initiative to get more young people into work cuts the salary levels of all workers under 25 by 20%. Write an SQL statement to implement this policy change.
- Find the names and ages of each employee who works in both the Hardware and Software department.
- Find the name and pct_time of all employees who works for department Software with less than 50% time effort.
- For each department with more than 20 employees , print the department id together with the number of employees that work in that department.
- If a manager manages more than one department, he/she is said to control the sum of all the budgets for those departments. Find the managerids of managers who control more than 5,000,000.
