HW4 Solutions Page

Return to homework page.

7.19 We are supposed to discuss all the integrity contraints on Figure 7.6 violated by the given update operation. (Note: the book says Figure 7.7 , but this is a typo. It is correct in the 2nd edition of the text.)

a. No constraints are violated by this first insert. Might argue that the semantic constraint that this employee's manager is making less than him violated.

b. Foreign key constraint violated as DNUM doesn't match anything in DEPARTMENT. Ask user to correct this number.

c. Primary key constraint violated as DNUMBER 4 is already used by Administration. Inform user of this. Here I am assuming the insert in a. worked or the foreign key constraint for MGRSSN would be violated as well.

d. PNO cannot be null as part of key. So primary key constraint violated. If there was a default value could use this.

e. No constraints are violated.

f. No constraints are violated. Might want to cascade this delete to people who work under this manager.

g. If delete this employee the referential constraint that all employees have a legit manager might be violated. Similar constraints violated for DEPARTMENT and WORKS_ON. To resolve could cascade delete.

h. This violates the referential constraint with WORKS_ON as the PNO 1 still occurs there. Could cascade the delete.

i. Although there is no direct referential constrint violated, it appears to violate the semantic constraint that the SUPERSSN of an employee is the same as the MGRSSN of the department an employee works for. Might want to inform person performing operation of this.

j. This would be okay if (a) above was done first. Otherwise, the referential constraint that the SUPERSSN must refer to an EMPLOYEE SSN would be violated. Should inform user of this.

k. This operation is okay.

7.25 We are first asked to list the foreign key constraints among the given tables. SSN in TRIP is a foreign key referencing SALESPERSON. Trip_ID in EXPENSE is a foreign key referencing TRIP. ACCOUNT# in EXPENSE also presumably references some not listed (don't lose anything if say or don't say this last one.) Next we are supposed to give relational algebra queries for the given English request. My answers are in pseudo LaTeX.

a. \pi_{each attribute of TRIP}(\sigma_{Amount > 2000}(TRIP*EXPENSE))
Another way to do this would be:
T1 >-- \pi_{TRIP_ID}(\sigma_{Amount >2000}(EXPENSE))
RESULT >-- TRIP * T1

b. \pi_{SSN}(\sigma_{To_City='Honolulu'}(SALESPERSON * TRIP))

c. \GothicF SUM Amount (\sigma_{SSN='234-56-7890'}(TRIP*EXPENSE))

The solutions to the remaining problems are modified from student YL's 
homework which I verified was correct:

CREATE TABLE STUDENT
    (Name             VARCHAR(30),
     StudentNumber    INT NOT NULL,
     Class            INT,
     Major            VARCHAR(15),
 PRIMARY KEY (StudentNumber));

CREATE TABLE COURSE
    (CourseName        VARCHAR(30),
     CourseNumber      VARCHAR(15),
     CreditHours       INT,
     Department        VARCHAR(10),     
 PRIMARY KEY (CourseNumber));

CREATE TABLE SECTION
    (SectionIdentifier   INT NOT NULL,
     CourseNumber        VARCHAR(15),
     Semester            VARCHAR(10),
     Year                VARCHAR(4),
     Instructor          VARCHAR(30),
 PRIMARY KEY (SectionIdentifier),
 FOREIGN KEY (CourseNumber) REFERENCES COURSE(CourseNumber)
     ON DELETE SET NULL);

CREATE TABLE GRADE_REPORT
    (StudentNumber       INT,
     SectionIdentifier   INT NOT NULL,     
     Grade               VARCHAR(2), 
 FOREIGN KEY (StudentNumber) REFERENCES STUDENT(StudentNumber)
     ON DELETE SET NULL,
 FOREIGN KEY (SectionIdentifier) REFERENCES SECTION(SectionIdentifier)
     ON DELETE SET NULL);

CREATE TABLE PREREQUISITE
    (CourseNumber         VARCHAR(15),
     PrerequisiteNumber   VARCHAR(15),       
 FOREIGN KEY (CourseNumber) REFERENCES COURSE(CourseNumber)
     ON DELETE SET NULL,
 FOREIGN KEY (PrerequisiteNumber) REFERENCES COURSE(CourseNumber)
     ON DELETE SET NULL);

INSERT INTO STUDENT VALUES('Smith', 17, 1, 'CS');
INSERT INTO STUDENT VALUES('Brown', 8, 2, 'CS');

INSERT INTO COURSE VALUES('Intro to Computer Science', 'CS1310', 4, 'CS');
INSERT INTO COURSE VALUES('Data Structures', 'CS3320', 4, 'CS');
INSERT INTO COURSE VALUES('Discrete Mathematics', 'MATH2410', 3, 'MATH');
INSERT INTO COURSE VALUES('Database', 'CS3380', 3, 'CS');

INSERT INTO SECTION VALUES(85, 'MATH2410', 'Fall', 98, 'King');
INSERT INTO SECTION VALUES(92, 'CS1310', 'Fall', 98, 'Anderson');
INSERT INTO SECTION VALUES(102, 'CS3320', 'Spring', 99, 'Knuth');
INSERT INTO SECTION VALUES(112, 'MATH2410', 'Fall', 99, 'Chang');
INSERT INTO SECTION VALUES(119, 'CS1310', 'Fall', 99, 'Anderson');
INSERT INTO SECTION VALUES(135, 'CS3380', 'Fall', 99, 'Stone');

INSERT INTO GRADE_REPORT VALUES(17, 112, 'B');
INSERT INTO GRADE_REPORT VALUES(17, 119, 'C');
INSERT INTO GRADE_REPORT VALUES(8, 85, 'A');
INSERT INTO GRADE_REPORT VALUES(8, 92, 'A');
INSERT INTO GRADE_REPORT VALUES(8, 102, 'B');
INSERT INTO GRADE_REPORT VALUES(8, 135, 'A');

INSERT INTO PREREQUISITE VALUES('CS3380', 'CS3320');
INSERT INTO PREREQUISITE VALUES('CS3380', 'MATH2410');
INSERT INTO PREREQUISITE VALUES('CS3320', 'CS1310');

8.16

a.
SELECT Name FROM STUDENT WHERE Class = 4 AND Major = 'CS';


b.
SELECT CourseName FROM COURSE c, SECTION s
    WHERE (c.CourseNumber = s.CourseNumber)
    AND (Year = '98' OR Year = '99')
    AND (Instructor = 'King');

c. 
SELECT SectionIdentifier, CourseNumber, Semester, Year, Count FROM 
    (SELECT SectionIdentifier, s.CourseNumber, Semester, Year 
    FROM COURSE c, SECTION s 
    WHERE Instructor = 'King' AND c.CourseNumber = s.CourseNumber),
    (SELECT SectionIdentifier AS sid, COUNT(*) AS Count FROM GRADE_REPORT
        GROUP BY SectionIdentifier)
WHERE SectionIdentifier = sid;

d.
SELECT Name, CourseName, se.CourseNumber, CreditHours, Semester, Year, Grade
    FROM STUDENT st, COURSE c, SECTION se, GRADE_REPORT g
    WHERE (Class = 5) AND (Major = 'CS')
       AND (st.StudentNumber = g.StudentNumber) 
       AND (g.SectionIdentifier = se.SectionIdentifier);

e.


SELECT DISTINCT Name, Major FROM STUDENT s, GRADE_REPORT g
    WHERE s.StudentNumber = g.StudentNumber
          AND
          (SELECT COUNT(*) FROM GRADE_REPORT WHERE 
StudentNumber = s.StudentNumber AND Grade = 'A')          
          =
          (SELECT COUNT(*) FROM GRADE_REPORT WHERE 
StudentNumber = s.StudentNumber);
    
Note could have done NOT EXISTS in the WHERE clause. For example,

SELECT DISTINCT Name, Major FROM STUDENT s
    WHERE  NOT EXISTS
          (SELECT * FROM GRADE_REPORT g WHERE g.StudentNumber = 
          s.StudentNumber AND        
          Grade != 'A');                                                     
              
f.
SELECT DISTINCT Name, Major FROM STUDENT s, GRADE_REPORT g
    WHERE s.StudentNumber = g.StudentNumber
    AND ( SELECT COUNT(*) FROM GRADE_REPORT WHERE 
StudentNumber = s.StudentNumber AND Grade = 'A' ) = 0;

Also could have used not exists here.


8.17

a.
INSERT INTO STUDENT VALUES('Johnson', 25, 1, 'MATH');

b.
UPDATE STUDENT SET Class = 2 WHERE Name = 'Smith';

c.
INSERT INTO COURSE VALUES('Knowledge Engineering', 'CS4390', 3, 'CS');

d.
DELETE FROM STUDENT WHERE Name = 'Smith' AND StudentNumber = 17;