# 資料庫作業二
* 姓名：張議隆
* 學號：F74082125

:::spoiler TOC
[TOC]
:::

## 4.7

### BOOK table
* 刪除 `BOOK` 中的一個 `tuple` 時
* `BOOK_AUTHOR`、`BOOK_COPIES`、`BOOK_LOANS` 都需要進行 `cascade` (同時刪除這本書的資料)
* 修改 `BOOK` 中的 `Book_id` 時
* `BOOK_AUTHOR`、`BOOK_COPIES`、`BOOK_LOANS` 都需要進行 `cascade` (同時修改這本書的 `ID`)

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

### LIBRARY_BRANCH table
* 刪除 `LIBRARY_BRANCH` 中的一個 `tuple` 時
* `BOOK_COPIES`、`BOOK_LOANS` 需要進行 `set to default` (我是認為書不進行分類跟放一個預設值分類本質上相同，但由於 `BOOK_COPIES` 中的 `Branch_id` 屬於 `Key`，只能設為某個預設值)
* 修改 `LIBRARY_BRANCH` 中的 `Branch_id` 時
* `BOOK_COPIES`、`BOOK_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));

:::warning
我把全部的程式碼放在同一個 `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

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