Exam (elaborations) TEST BANK FOR DATABASE SYSTEMS AN APPLICATION-ORIENTED APPROACH 2ND EDITION BY Michael Kifer, Arthur Bernstein, Philip M. Lewis (SOLUTION MANUAL)-Converted

Design the following two tables (in addition to that in Figure 2.1) that might be used in the Student Registration System. Note that the same student Id might appear in many rows of each of these tables. a. A table implementing the relation CoursesRegisteredFor, relating a student’s Id and the identifying numbers of the courses for which she is registered Solution: Id CrsCode CSE515 CSE505 CSE532 CSE532 CSE541 CSE504 CSE504 CSE515 CSE505 b. A table implementing the relation CoursesTaken, relating a student’s Id, the identifying numbers of the courses he has taken, and the grade received in each course Solution: Id CrsCode Grade CSE501 A 6 CHAPTER 2 The Big Picture CSE533 B+ CSE505 A- CSE541 C CSE533 B- CSE515 B+ CSE505 A CSE532 B+ Specify the predicate corresponding to each of these tables. Solution: For the first table: Student X is registered for Course Y For the second table: Student X has taken Course Y and gotten Grade Z 2.2 Write an SQL statement that a. Returns the Ids of all seniors in the table Student Solution: SELECT S.Id FROM Student WHERE S.Status = ’senior’ b. Deletes all seniors from Student Solution: DELETE FROM Student S WHERE S.Status = ’senior’ c. Promotes all juniors in the table Student to seniors Solution: UPDATE Student S SET S.Status = ’senior’ WHERE S.Status = ’junior’ 2.3 Write an SQL statement that creates the Transcript table. Solution: Exercises 7 CREATE TABLE Transcript ( StudId INTEGER, CrsCode CHAR(6), Semester CHAR(6), Grade CHAR(1), PRIMARY KEY (StudId, CrsCode, Semester) ) 2.4 Using the Transcript table, write an SQL statement that a. Deregisters the student with Id = from the course CS305 for the fall of 2001 Solution: DELETE FROM Transcript WHERE StudId = ’’ AND CrsCode = ’CS305’ AND Semester = ’F2001’ b. Changes to an A the grade assigned to the student with Id = for the course CS305 taken in the fall of 2000 Solution: UPDATE Transcript SET Grade = ’A’ WHERE StudId = ’’ AND CrsCode = ’CS305’ AND Semester =’F2000’ c. Returns the Id of all students who took CS305 in the fall of 2000 Solution: SELECT StudId FROM Transcript WHERE CrsCode = ’CS305’ AND Semester = ’F2000’
2.5 Given the relation Married that consists of tuples of the form
a, b, w here a is the husband and b is the wife, the relation Brother that has tuples of the form
c, d, where c is the brother of d, and the relation Sibling, which has tuples of the form
e, f , w here e and f are siblings, use SQL to define the relation Brother-In-Law, where tuples have the form
x, y with x being the brother-in-lawof y. (Hint: This query can be represented as a union of three separate SQL queries. SQL provides the operator UNION to achieve this effect.) 8 CHAPTER 2 The Big Picture Solution: The first SQL query, below, describes the situation where someone is the brother of the wife and hence the brother-in-law