--- tags: DBMS disqus: HackMD --- 資料庫系統 Homework #1 === 3.11. Suppose that each of the following Update operations is applied directly to the database state shown in Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints. --- ![](https://i.imgur.com/qxsMC3e.png) ![](https://i.imgur.com/u7Kky1R.png) a. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE. :::success Accepted ::: b. Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT. :::danger Rejected ( ∵ Dnum = 2 ) ::: c. Insert <‘Production’, 4, ‘943775543’, ‘2007-10-01’> into DEPARTMENT. :::danger Rejected ( ∵ Dnumber exists ) ::: d. Insert <‘677678989’, NULL, ‘40.0’> into WORKS_ON. :::danger Rejected ( ∵ Essn = ‘677678989’ and Pno is NULL ) ::: e. Insert <‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘spouse’> into DEPENDENT. :::success Accepted ::: f. Delete the WORKS_ON tuples with Essn = ‘333445555’. :::success Accepted ::: g. Delete the EMPLOYEE tuple with Ssn = ‘987654321’. :::danger Rejected ( ∵ referenced by EMPLOYEE, DEPARTMENT, WORKS_NO, DEPENDENT tuples ) ::: h. Delete the PROJECT tuple with Pname = ‘ProductX’. :::danger Rejected ( ∵ referenced by WORKS_ON tuples ) ::: i. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively. :::success Accepted ::: j. Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = ‘999887777’ to ‘943775543’. :::danger Rejected ( ∵ referenced by WORKS_ON tuples ) ::: k. Modify the Hours attribute of the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10 to ‘5.0’. :::success Accepted ::: --- 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. :::info TRIP[Ssn] = SALESPERSON[Ssn] EXPENSE[Trip_id] = TRIP[Trip_id] ![](https://i.imgur.com/HIr0kzH.jpg) ::: --- 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. :::info OPTION[Serial_no] = CAR[Serial_no] SALE[Salesperson_id] = SALESPERSON[Salesperson_id] SALE[Serial_no] = CAR[Serial_no] ![](https://i.imgur.com/eIuK2An.jpg) ::: --- 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 possible tuple of the relation is shown below: --- | Name Ssn | Column 2 | Local_phone | Address | Cell_phone | Age | Gpa | |:---------------------------:|:-----------:|:-----------:|:------------------------------------------------:|:----------:|:---:|:----:| | George Shaw William Edwards | 123-45-6789 | 555-1234 | 123 Main St., 555-4321 19 3.75 Anytown, CA 94539 | 555-4321 | 19 | 3.75 | 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?) :::success 假設不會出現兩個同名學生共用家用電話，則姓氏、名字與家用電話的組合可能可以解決問題。 假設每個學生都有家用電話，另一個解決方法是使用姓氏、名字與郵遞區號。 最後一個解決方法是使用姓氏、主修科目與門牌號碼等組合。 ::: b. Would you store this additional information in the Local_phone and Cell_phone attributes or add new attributes to the schema for STUDENT? :::success 如果我們在Primary Key使用Name並且Name發生變化，則Primary Key也會發生變化。更改Primary Key是可以接受的，但是效率很低，因為需要對資料庫更新有參照Primary Key的所有欄位，而在大型資料庫可能需要很長時間更新。同樣，新的Primary Key必須保持唯一。 ::: 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)? :::success 我們可以使用代理鍵作為資料庫中的Primary Key，該Primary Key是按建立tuples的順序產生的，一般會建議由資料庫自動產生Primary Key，在設計上有優缺點考量，優點是可以自動產生唯一的Primary Key，缺點是欄位沒有意義，且效率較低。 ::: d. What general guideline world you recommend for deciding when to store information in a single attribute and when to split the information? :::success 如果欄位可能是包含地區、縣市等欄位資料，建議增加欄位存放，可提高查詢效能。若是非查詢條件的欄位，則可以使用單一欄位存放。 ::: e. Suppose the student can have between 0 and 5 phones. Suggest two different designs that allow this type of information. :::success 1. 可以新增一個table存放Ssn及電話，並將兩個欄位設為Primary Key，且Ssn參照到STUDENT[Ssn] 2. 可以新增一個table存放流水號及電話，將兩個欄位設為Primary Key，而STUDENT則新增電話流水號欄位並參照到流水號。(STUDENT[電話流水號] = Phones[流水號]) :::