Assignment #3 Database Systems _SOLUTION

Problem 1. [36pts] Consider the following relational 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. Each department has exactly one manager.Emp(eid int, ename varchar(30), age int, salary float) Works(eid int, did int, pct time float) Dept(did int, budget float, managerid int) A. Write SQL to create the above relations together specifying necessary primary key and foreign keys. Note that primary key has been underlined in the above schema. B. Write SQL to express the following integrity constraints (domain, key, foreign key, CHECK or assertion constraints, choose the one that you think is the most appropriate and you may rewrite the CREATE TABLE SQL from Part A if necessary). 1. Employees must make a minimum salary of $2,000. 2. Every manager must also be an employee. 3. A manager must always have a higher salary than any employee that he or she manages. 4. The total percentage of appointments for an employee must be under 100%. 5. No employee can be a manager for more than 2 departments. 6. No department can have more than 50 employees. Problem 2. [64pts] Consider the following schemas, that is available from our database server in the cs5530db database. student(sid, sname, sex, age, year, gpa) dept(dname, numphds) prof(pname, dname) course(cno, dname, cname) major(dname, sid) section(dname, cno, sectno, pname) enroll(sid, dname, cno, sectno, grade) We showed how to connect to the database server in class (and in the class website). You can use either MySQL Workbench or connect via linux/unix from one of the CADE machines or shell.cs.utah.edu by “mysql –u cs5530 –p -h georgia.eng.utah.edu cs5530db”. (password has been posted on canvas). Once connected, you can “show tables”, “describe tablename”, etc. Answer the followings using SQL queries. Submit both your SQL queries and the query results from the database server. Note that you can store all your SQL queries in a txt file (e.g., query.txt, end each SQL with a semicolon and separate each SQL query with an empty line), and execute them all in once by “source query.txt”. An example of query.txt with 3 queries is given below: Select * from student; Select dname from dept; Select * from major; To capture the query results from the database server in Unix/Linux, you can use the “script” command. In particular, when you are ready to execute all your queries from query.txt. Do the followings: 1) script output.txt 2) connect to the DB server and “source query.txt;” 3) quit the server by “quit” or “exit” 4) type ctrl+d to end the scripting process. 5) all screen printout will be captured in output.txt. Questions: 1. What is the age of the oldest student. 2. Find the names and gpas of the students who have enrolled in course 302. 3. Find the names and majors of students who are taking one of the Geometry courses (i.e., the course title contains a keyword “Geometry” somewhere). 4. Find the names of students who have enrolled in both a course offered by the “Computer Sciences” department and a course offered by the “Mathematics” department. 5. For each department, find the average age of the students majoring in that department along with the age difference between the oldest and youngest students. 6. Find the names of students being taught by professor ”Smith, S.” (i.e. pname = ”Smith, S.” in section table). 7. How many students have more than one major? 8. Find the name(s) of the student(s) who have the most number of majors. 9. Find the name(s) of the oldest first year student(s) (year = 1). 10. Print the ids, names, and gpas of the students who are currently taking all of the Civil Engineering courses. 11. For those departments that have no majors (i.e., students who major in that department) taking a “Computer Sciences” course, print the department name and the number of PhD students in the department. 12. Find the student names for each age group with the maximum average grade calculated from courses they have taken from the Computer Science and Math departments. 13. Find the student names for each age group with the maximum gpa. 14. Find the name(s) of the student(s) who has (have) the highest average grade (computed by the grades from all courses he/she has enrolled into; NOT to simply use the gpa value of a student!). 15. Find the names of students who have enrolled in all courses. 16. Find the name, gpa, the average grade of all courses he/she has enrolled into, and the average CS grade (from all CS courses he/she has taken) for students whose gpa≥3.0 AND who also have an average grade of at least 3.2 from all Computer Science courses he/she has taken.