資料庫作業一

# 資料庫作業一 * 姓名：張議隆 * 學號：F74082125 :::spoiler TOC [TOC] ::: ## 3.11 ![](https://i.imgur.com/20RDNrE.png) ![](https://i.imgur.com/yqCAqwz.png) * Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1 > into EMPLOYEE. * 有違反 `Semantic Integrity Constraints`，`Robert` 的上司是 `James`(`888665555`) ，但 `Robert` 的薪水(`58000`)比上司(`55000`)還高。 --- * Insert < ‘ProductA’, 4, ‘Bellaire’, 2 > into PROJECT. * `Bellaire` 的 `Dnum` 應該是 `5` 而不是 `2`。 --- * Insert < ‘Production’, 4, ‘943775543’, ‘2007-10-01’ > into DEPARTMENT. * `Dnumber`(`Key`) 的 `4` 發生了重複。 --- * Insert < ‘677678989’, NULL, ‘40.0’ > into WORKS_ON. * `Pno`(`Key`) 不可為 `NULL`。 --- * Insert < ‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘spouse’ > into DEPENDENT. * 沒有違反，雖然這對姊弟戀差了 `18` 歲。 --- * Delete the WORKS_ON tuples with Essn = ‘333445555’. * 如果可以一次刪除四筆資料，這次刪除是可以通過的。 --- * Delete the EMPLOYEE tuple with Ssn = ‘987654321’ * `DEPARTMENT` 、 `WORKS_ON` 、 `DEPENEDENT` 有 `ssn` 為 `987654321` 的資料在 reference ，不可刪除。 --- * Delete the PROJECT tuple with Pname = ‘ProductX’ * `WORKS_ON` 有 `Pno` 為 `1`(`ProductX`) 的資料在 reference ，不可刪除。 --- * Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively * `research` 這個部門的經理變成 `John`(`123456789`)，然而 `John` 的上司依舊是 `Franklin`(`333445555`)，那這時候 `Franklin` 會屬於哪個部門? 應該不能這樣修改。 --- * Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = ‘999887777’ to ‘943775543’. * 不存在 `ssn` 為 `943775543` 的人，因此不能修改。 --- * Modify the Hours attribute of the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10 to ‘5.0’. * 沒有違反。 ## 3.15 :::info Consider the following relations for a database that keeps track of business trips of salespersons in a sales office: * SALESPERSON(++Ssn++, Name, Start_year, Dept_no) * TRIP(Ssn, From_city, To_city, Departure_date, Return_date, ++Trip_id++) * EXPENSE(++Trip_id++, ++Account#++, Amount) A trip can be charged to one or more accounts. Specify the foreign keys for this schema, stating any assumptions you make. ::: 1. `TRIP` 的 `Ssn` 會 reference 到 `SALESPERSON` 的 `Ssn` 2. `EXPENSE` 的 `Trip_id` 會 reference 到 `TRIP` 的 `Trip_id` :::warning 我不知道 `EXPENSE` 的 `Account#` 代表意義是什麼，我唯一能想到的是類似台灣的統編，但又感覺不太對。 ::: ## 3.17 :::info Consider the following relations for a database that keeps track of automobile sales in a car dealership (OPTION refers to some optional equipment installed on an automobile): * CAR(++Serial_no++, Model, Manufacturer, Price) * OPTION(++Serial_no++, ++Option_name++, Price) * SALE(++Salesperson_id++, ++Serial_no++, Date, Sale_price) * SALESPERSON(++Salesperson_id++, Name, Phone) First, specify the foreign keys for this schema, stating any assumptions you make. Next, populate the relations with a few sample tuples, and then give an example of an insertion in the SALE and SALESPERSON relations that violates the referential integrity constraints and of another insertion that does not ::: 1. `OPTION` 的 `Serial_no` 會 reference 到 `CAR` 的 `Serial_no` 2. `SALE` 的 `Salespersion_id` 會 reference 到 `SALESPERSON` 的 `Salesperson_id` 3. `SALE` 的 `Serial_no` 會 reference 到 `CAR` 的 `Serial_no` 如果 `insert` 一個 `SALE` 的 `tuple` 時輸入了不存在於 `SALESPERSON` 中的 `Salesperson_id`，會違反 `constraints`。直接在 `SALESPERSON` 中新增一個 `tuple` 不會違反。 ## 3.19 :::info Consider a STUDENT relation in a UNIVERSITY database with the following attributes * (Name, Ssn, Local_phone, Address, Cell_phone, Age, Gpa). Note that the cell phone may be from a different city and state (or province) from the local phone. (以下忽略) ::: a. Identify the critical missing information from the Local_phone and Cell_phone attributes. (Hint: How do you call someone who lives in a different state or province?) * 以台灣的狀況來說，缺乏市話中的區碼(如台南的`(06)`) b. Would you store this additional information in the Local_phone and Cell_phone attributes or add new attributes to the schema for STUDENT? * 可以直接多一個 `Table` 來表示不同區域對應的區碼為何 c. Consider the Name attribute. What are the advantages and disadvantages of splitting this field from one attribute into three attributes (first name, middle name, and last name)? * 優點是需要其中的數據時可以直接取得，缺點是占用記憶體及硬碟空間會更大 d. What general guideline world you recommend for deciding when to store information in a single attribute and when to split the information? * 如果資料本身區分後會失去對應的意義就保留成單一的 `attribute`，如果不會就可以考慮 `split` e. Suppose the student can have between 0 and 5 phones. Suggest two different designs that allow this type of information. 1. 製作一個 `STUDENT_PHONE` 的 `table` 記錄學生的電話資訊 2. 直接在原本的 `table` 再新增一個 `tuple` ###### tags: `1112_courses` `database`