資料庫作業一

• 姓名：張議隆
• 學號：F74082125

TOC

• 資料庫作業一
  • 3.11
  • 3.15
  • 3.17
  • 3.19

3.11

• 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

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

我不知道 EXPENSE 的 Account# 代表意義是什麼，我唯一能想到的是類似台灣的統編，但又感覺不太對。

3.17

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

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