**$ 20.00**

# TEST BANK FOR DATABASE SYSTEMS AN APPLICATION-ORIENTED APPROACH 2ND ED BY Michael Kifer, Arthur

- GradeMaster1
- Rating : 0
- Grade :
**No Rating** - Questions : 0
- Solutions : 922
- Blog : 0
- Earned : $180.60

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

111111111 CSE515

111111111 CSE505

111111111 CSE532

666666666 CSE532

666666666 CSE541

111223344 CSE504

987654321 CSE504

023456789 CSE515

123454321 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

111111111 CSE501 A

6 CHAPTER 2 The Big Picture

111111111 CSE533 B+

666666666 CSE505 A-

666666666 CSE541 C

111223344 CSE533 B-

987654321 CSE515 B+

023456789 CSE505 A

123454321 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 = 123456789 from the course CS305 for the fall of

2001

Solution:

DELETE

FROM Transcript

WHERE StudId = ’123456789’

AND CrsCode = ’CS305’ AND Semester = ’F2001’

b. Changes to an A the grade assigned to the student with Id = 123456789 for the

course CS305 taken in the fall of 2000

Solution:

UPDATE Transcript

SET Grade = ’A’

WHERE StudId = ’123456789’

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

## [Solved] TEST BANK FOR DATABASE SYSTEMS AN APPLICATION-ORIENTED APPROACH 2ND ED BY Michael Kifer, Arthur

- This solution is not purchased yet.
- Submitted On 12 Nov, 2021 05:54:56

- GradeMaster1
- Rating : 0
- Grade :
**No Rating** - Questions : 0
- Solutions : 922
- Blog : 0
- Earned : $180.60