Try   HackMD

資料庫作業二

  • 姓名:張議隆
  • 學號:F74082125
TOC

4.7

BOOK table

  • 刪除 BOOK 中的一個 tuple
    • BOOK_AUTHORBOOK_COPIESBOOK_LOANS 都需要進行 cascade (同時刪除這本書的資料)
  • 修改 BOOK 中的 Book_id
    • BOOK_AUTHORBOOK_COPIESBOOK_LOANS 都需要進行 cascade (同時修改這本書的 ID)

PUBLISHER table

  • 刪除 PUBLISHER 中的一個 tuple
    • BOOK 需要進行 set to NULL (這本書暫時變成沒有出版商,之後再進行設定)
  • 修改 PUBLISHER 中的 Name
    • BOOK 需要進行 cascade (跟著修改這本書的出版商名稱)

LIBRARY_BRANCH table

  • 刪除 LIBRARY_BRANCH 中的一個 tuple
    • BOOK_COPIESBOOK_LOANS 需要進行 set to default (我是認為書不進行分類跟放一個預設值分類本質上相同,但由於 BOOK_COPIES 中的 Branch_id 屬於 Key,只能設為某個預設值)
  • 修改 LIBRARY_BRANCH 中的 Branch_id
    • BOOK_COPIESBOOK_LOANS 都需要進行 cascade(跟著修改這本書的分類)

BORROWER table

  • 刪除 BORROWER 中的一個 tuple
    • BOOK_LOANS 需要進行 cascade(還書了自然可以清理掉借書紀錄,另一種解法可能是新增一個還書日期的註記)
  • 修改 BORROWER 中的 Card_no
    • BOOK_LOANS 需要進行 cascade(跟著更新借書人的圖書證號)

4.8

BOOK table

-- 假設書碼是依照 ISBN-13 
CREATE TABLE BOOK
( Book_id INTEGER(13) NOT NULL,
Title VARCHAR NOT NULL,
Publisher_name VARCHAR,
PRIMARY KEY (Book_id),
FOREIGN KEY (Publisher_name) REFERENCES PUBLISHER(Name)
ON DELETE SET NULL ON UPDATE CASCADE);

BOOK_AUTHOR table

CREATE TABLE BOOK_AUTHOR
( Book_id INTEGER(13) NOT NULL,
Author_name VARCHAR,
PRIMARY KEY (Author_name),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)
ON DELETE CASCADE ON UPDATE CASCADE);

PUBLISHER table

CREATE TABLE PUBLISHER
( Name VARCHAR NOT NULL,
Address VARCHAR,
Name VARCHAR,
PRIMARY KEY (Name));

BOOK_COPIES table

CREATE TABLE BOOK_COPIES
( Book_id INTEGER(13) NOT NULL,
Branch_id INTEGER NOT NULL,
No_of_copies INTEGER 
PRIMARY KEY (Book_id, Branch_id),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id) 
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE);

BOOK_LOANS table

CREATE TABLE BOOK_LOANS
( Book_id INTEGER(13) NOT NULL,
Branch_id INTEGER NOT NULL,
Card_no INTEGER,
Date_out Date,
Due_date Date,
PRIMARY KEY (Book_id, Branch_id, Card_no),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Branch_id) REFERENCES LIBRARY_BRANCH(Book_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (Card_no) REFERENCES BORROWER(Card_no)
ON DELETE SET DEFAULT ON UPDATE CASCADE);

LIBRARY_BRANCH table

CREATE TABLE LIBRARY_BRANCH
( Branch_id INTEGER NOT NULL,
Branch_name VARCHAR,
Address VARCHAR,
PRIMARY KEY (Branch_id));

BORROWER table

CREATE TABLE BORROWER
( Card_no INTEGER,
Name VARCHAR,
Address VARCHAR,
Phone VARCHAR,
PRIMARY KEY (Card_no));

我把全部的程式碼放在同一個 Cell 裡面排版會出現問題,因此將每個部份獨立出來。

4.12

a. Retrieve the names of all senior students majoring in ‘CS’ (computer science).

  • SELECT Name FROM STUDENT WHERE Major='CS' and class = '4'

b. Retrieve the names of all courses taught by Professor King in 2007 and 2008.

  • SELECT COURSE.Course_Name
  • FROM COURSE, SECTION
  • WHERE SECTION.Course_number=COURSE.Course_number and SECTION.Year>='07' and SECTION.Year<='08' and SECTION.Instructor='King'
    • 我稍微做了點排版,直接把所有東西打在一行真的不能讀,實際上這三行要接在一起

c. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section.

  • SELECT SECTION.Course_number, SECTION.Semester, SECTION.Year, COUNT(GRADE_REPORT.Section_identifier)
  • FROM SECTION, GRADE_REPORT
  • WHERE GRADE_REPORT.Section_identifier=SECTION.Section_identifier and SECTION.Instructor='King'

d. Retrieve the name and transcript of each senior student (Class = 4) majoring in CS. A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.

  • SELECT STUDENT.Name, COURSE.Course_name, COURSE.Course_number, COURSE.Credit_hours, SECTION.Semester, SECTION.Year, GRADE_REPORT.Grade
  • FROM STUDENT, COURSE, SECTION, GRADE_REPORT
  • WHERE STUDENT.Student_number=GRADE_REPORT.Student_number and GRADE_REPORT.Section_identifier=SECTION.Section_identifier and SECTION.Course_number=COURSE.Course_number and STUDENT.class='4' and STUDENT.Major='CS'
    • 我知道這一切看起來很糟糕,但我已經盡力排版了

4.13

a. Insert a new student, <‘Johnson’, 25, 1, ‘Math’>, in the database.

  • INSERT INTO STUDENT
  • VALUES (‘Johnson’, 25, 1, ‘Math’);

b. Change the class of student ‘Smith’ to 2.

  • UPDATE STUDENT
  • SET Class=2
  • WHERE Name=‘Smith’;

c. Insert a new course, <‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>

  • INSERT INTO COURSE
  • VALUES (‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’)

d. Delete the record for the student whose name is ‘Smith’ and whose student number is 17.

  • DELETE FROM STUDENT
  • WHERE Name=‘Smith’ and Student_number=17

我其實不理解為何會有 ’ 和 ' 這兩種符號,我不確定 SQL 到底是吃哪一種符號才對,又或者兩種符號都接受。
我在 4.12 題使用的都是 ' 的符號, 4.13 題使用的都是 ’ 符號。
從我的編輯器看得出差異,但是顯示成文字後就完全看不出差異了。

tags: 1112_courses database