8.10
CREATE TABLE Publisher
(
    Name  CHAR(30),
    Address CHAR(50),
    Phone CHAR(12),

    CONSTRAINT NameIsPK PRIMARY KEY (Name),
);

CREATE TABLE BOOK
(  BookID    INTEGER,
   Title  CHAR(50),
   PublishName  CHAR(30),

   CONSTRAINT BookidIsPK PRIMARY KEY (BookID),
   CONSTRAINT PublishNameIsFK FOREIGN KEY REFERENCES PUBLISHER(Name) ON DELETE SET NULL ON UPDATE CASCADE,
);

CREATE TABLE LIBRARY_BRANCH
(
    BranchID  INTEGER,
    BranchName CHAR(20),
    Address  CHAR(50),

   CONSTRAINT BranchIdIsPK PRIMARY KEY (BranchID),
);

CREATE TABLE BOOK_COPIES
(
    BookID  INTEGER,
    BranchID  INTEGER,
    No_Of_Copies  INTEGER,

    CONSTRAINT B_B_IdIsPK PRIMARY KEY (BookID, BranchID),
    CONSTRAINT BookIDIsFK FOREIGN KEY REFERENCES BOOK(BookID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT BranchIDIsFK FOREIGN KEY REFERENCES LIBRARY_BRANCH(BranchID) ON DELETE CASCADE ON UPDATE CASCADE,
);

CREATE TABLE BORROWER
(
    CardNo  INTEGER,
    Name  CHAR(20),
    Address CHAR(50),
    Phone CHAR(12),

    CONSTRAINT CardNoIsPK PRIMARY KEY (CardNo),
);

CREATE TABLE BOOK_LOANS
(
    BookID  INTEGER,
    BranchID  INTEGER,
    CardNo  INTEGER,
    DateOut  DATE,
    DueDate  DATE,

    CONSTRAINT B_B_C_IsPK PRIMARY KEY (BookID, BranchID, CardNo),
    CONSTRAINT BookIDIsFK FOREIGN KEY REFERENCES BOOK(BookID) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT BranchIDIsFK FOREIGN KEY REFERENCES LIBRARY_BRANCH(BranchID) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT CardNoIsFK FOREIGN KEY REFERENCES BORROWER(BranchID) ON DELETE RESTRICT ON UPDATE RESTRICT,
);
 

CREATE BOOK_AUTHORS
(
    BookID  INTEGER,
    AuthorName  CHAR(20),

    CONSTRAINT B_A_IsPK PRIMARY KEY (BookID, AuthorName),
    CONSTRAINT BookIDIsFK FOREIGN KEY REFERENCES BOOK(BookID) ON DELETE CASCADE ON UPDATE CASCADE,

);

8.16

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

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

c)  SELECT CourseNumber, Semester, Year, COUNT(StudentNumber)
     FROM SECTION AS S, GRADE_REPORT AS G
     WHERE S.SectionIdentifier = G.SectionIdentifier AND Instructor = 'King'
     GROUP BY CourseNumber, Semester, Year

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

e)  SELECT Name, Major
     FROM STUDENT
     WHERE StudentNumber NOT IN
                                      ( SELECT StudentNumber
                                        FROM GRADE_REPORT
                                        WHERE Grade <> 'A' )
                                AND
                    StudentNumber IN
                                      (SELECT StudentNumber
                                       FROM GRADE_REPORT
                                       WHERE Grade = 'A');
 

f)  SELECT Name, Major
    FROM STUDENT
    WHERE StudentNumber NOT IN
           ( SELECT  StudentNumber
             FROM GRADE_REPORT
             WHERE Grade = 'A'
          );
 

8.17
a)  INSERT INTO STUDENT(Name, StudentNumber, Class, Major) VALUES ( 'Johnson', 25, 1, 'CS');

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

c) INSERT INTO COURSE(CourseName, CourseNumber, CreditHours, Department)
    VALUES ( 'Knowledge Engineering', 'CS4390', 3, 'CS');
 

8.18
a) CREATE VIEW DeptInfo(DeptName, ManagerFName,ManagerLName ManagerSalary) AS
        SELECT DNAME, FNAME, LNAME, SALARY
        FROM EMPLOYEE, DEPARTMENT
        WHERE MGRSSN = SSN;

b)  CREATE VIEW ResearchEmp(EmpFName, EmpLName, SupervisorFName, SupervisorLName, Salary) AS
       SELECT E1.FNAME, E1.LNAME, E2.FNAME, E2.LNAME, S1.SALARY
       FROM EMPLOYEE AS E1, EMPLOYEE AS E2, DEPARTMENT AS D
       WHERE E1.SUPERSSN = E2.SSN AND E1.DNO = D.DNUMBER AND D.DNAME = 'Research';

c)  CREATE VIEW ProjectInfo(ProjectName, DeptName, No_Of_Emp, Total_Hours) AS
        SELECT P.PNAME, D.DNAME, COUNT(W.ESSN), SUM(W.HOURS)
        FROM PROJECT AS P, WORKS_ON AS W, DEPARTMENT AS D
        WHERE P.PNUMBER = W.PNO AND P.DNUM = D.DNUMBER
        GROUP BY P.PNAME, D.DNAME;