資料庫作業四

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

TOC

• 資料庫作業四
  • 6.16
  • 6.18
  • 7.16
  • 7.23

6.16

Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 3.6.

用 Latex 打這東西真痛苦，每個文字都要用 \text{} 包住，符號也得額外查詢。

a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.

$$\begin{gathered} \text{ProjectX}\leftarrow {\sigma }_{\text{Pname="ProductX"}}(\text{PROJECT}) \\ \text{WorksOn\_X}\leftarrow \text{ProjectX}{\bowtie }_{\text{Pnumber=Pno}}\text{WORKS\_ON} \\ \text{X\_10\_more}\leftarrow {\sigma }_{\text{Hours>10}}(\text{WorksOn\_X}) \\ \text{Employee\_10\_X}\leftarrow \text{X\_10\_more}{\bowtie }_{\text{Essn=Ssn}}\text{EMPLOYEE} \\ \text{Answer}\leftarrow {\pi }_{\text{Fname, Lname}}(\text{Employee\_10\_X}) \end{gathered}$$

---

b. List the names of all employees who have a dependent with the same first name as themselves.

$$\begin{gathered} \text{Full\_Dependent}\leftarrow \text{EMPLOYEE}{\bowtie }_{\text{Ssn=Essn}}\text{DEPENDENT} \\ \text{Same\_Dependent}\leftarrow {\sigma }_{\text{Fname=Dependent\_name}}(\text{Full\_Dependent}) \\ \text{Answer}\leftarrow {\pi }_{\text{Fname, Lname}}(\text{Same\_Dependent}) \end{gathered}$$

---

c. Find the names of all employees who are directly supervised by ‘Franklin Wong’.

$$\begin{gathered} \text{Full\_Employee\_and\_Supervisor}\leftarrow \text{EMPLOYEE}{\bowtie }_{\text{Ssn=Super\_ssn}}\text{EMPLOYEE} \\ \text{F\_W\_Ssn}\leftarrow {\pi }_{\text{Ssn}}(\sigma \text{Fname="Franklin", Lname="Wong"}(EMPLOYEE)) \\ \text{Answer}\leftarrow {\pi }_{\text{Fname, Lname}}({\sigma }_{\text{Super\_ssn=F\_W\_Ssn}}(\text{Full\_Employee\_and\_Supervisor})) \end{gathered}$$

---

d. For each project, list the project name and the total hours per week (by all employees) spent on that project.

這題第一行的 JOIN 是 Left Outer Join，只是我真的打不出那個字了。

$$\begin{gathered} \text{Project\_and\_Work}\leftarrow \text{PROJECT}{\bowtie }_{Pnumber=Pno}\text{WORKS\_ON} \\ \text{Hours}{\leftarrow }_{Pname}{\mathcal{F}}_{SUMHours}(\text{Project\_and\_Work}) \\ \text{Answer}\leftarrow {\pi }_{\text{Pname, Sum\_hours}}(\text{Hours}) \end{gathered}$$

---

e. Retrieve the names of all employees who work on every project.

$$\begin{gathered} \text{All\_Project\_Number}\leftarrow {\rho }_{\text{Pno}}({\pi }_{Pnumber}(PROJECT)) \\ \text{Ssn\_With\_All\_Project}\leftarrow \text{WORKS\_ON}÷\text{All\_Project\_Number} \\ \text{Selected\_Employee}\leftarrow {\sigma }_{\text{Ssn=Ssn\_With\_All\_Project}}(EMPLOYEE) \\ \text{Answer}\leftarrow {\pi }_{\text{Fname, Lname}}(\text{Selected\_Employee}) \end{gathered}$$

---

f. Retrieve the names of all employees who do not work on any project.

$$\begin{gathered} \text{All\_Ssn}\leftarrow {\pi }_{Ssn}(EMPLOYEE) \\ \text{Ssn\_With\_Project}\leftarrow {\rho }_{Ssn}({\pi }_{Essn}(\text{WORKS\_ON})) \\ \text{Ssn\_Without\_Project}\leftarrow \text{All\_Ssn}-\text{Ssn\_With\_Project} \\ \text{Answer}\leftarrow {\pi }_{\text{Fname, Lname}}({\sigma }_{\text{Ssn=Ssn\_Without\_Project}}(EMPLOYEE)) \end{gathered}$$

---

g. For each department, retrieve the department name and the average salary of all employees working in that department.

$$\begin{gathered} \text{Salary\_And\_Dno}\leftarrow {\pi }_{Salary,Dno}(EMPLOYEE) \\ \text{Average\_Salary\_per\_Dno}{\leftarrow }_{Dno}{\mathcal{F}}_{AVERAGESalary}(\text{Salary\_And\_Dno}) \\ \text{Department\_And\_AVG\_Salary}\leftarrow \text{DEPARTMENT}{\bowtie }_{Dnumber=Dno}\text{Average\_Salary\_per\_Dno} \\ \text{Answer}\leftarrow {\pi }_{\text{Dname, Average\_salary}}(\text{Department\_And\_AVG\_Salary}) \end{gathered}$$

---

h. Retrieve the average salary of all female employees.

$$\begin{gathered} \text{Female\_Employee}\leftarrow {\sigma }_{\text{Sex="F"}}(EMPLOYEE) \\ \text{Answer}{\leftarrow }_{Sex}{\mathcal{F}}_{AVERAGESalary}(\text{Female\_Employee}) \end{gathered}$$

---

i. Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston.

$$\begin{gathered} \text{Project\_In\_Houston}\leftarrow {\sigma }_{\text{Plocation="Houston"}}(\text{PROJECT}) \\ \text{Department\_Not\_In\_Houston}\leftarrow {\sigma }_{\text{Dlocation!="Houston"}}(\text{DEPARTMENT}) \\ \text{Join}\leftarrow \text{Project\_In\_Houston}{\bowtie }_{Plocation=Dlocation}\text{Department\_Not\_In\_Houston} \\ \text{Selected\_Pno}\leftarrow {\pi }_{\text{Pnumber}}(\text{Join}) \\ \text{Selected\_Ssn}\leftarrow {\sigma }_{\text{Pno=Pnumber}}(\text{Selected\_Pno}) \\ \text{Answer}\leftarrow {\pi }_{Fname,Lname,Address}({\sigma }_{\text{Ssn=Selected\_Ssn}}(\text{EMPLOYEE})) \end{gathered}$$

---

j. List the last names of all department managers who have no dependents.

$$\begin{gathered} \text{Manager\_Ssn}\leftarrow {\rho }_{Ssn}({\pi }_{\text{Mgr\_ssn}}(\text{DEPARTMENT})) \\ \text{Ssn\_With\_Dependent}\leftarrow {\rho }_{Ssn}({\pi }_{Essn}(\text{DEPENTDENT})) \\ \text{Manager\_Ssn\_Without\_Dependent}\leftarrow \text{Manager\_Ssn}-\text{Ssn\_With\_Dependent} \\ \text{Answer}\leftarrow {\pi }_{\text{Lname}}({\sigma }_{\text{Ssn=Manager\_Ssn\_Without\_Dependent}}(EMPLOYEE)) \end{gathered}$$

6.18

Consider the LIBRARY relational database schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14. Write down relational expressions for the following queries:

a. How many copies of the book titles The Lost Tribe are owned by the library branch whose name is ‘Sharpstown’?

$$\begin{gathered} \text{That\_Book\_ID}\leftarrow {\sigma }_{\text{Title="The Lost Tribe"}}(\text{BOOK}) \\ \text{That\_Branch\_ID}\leftarrow {\sigma }_{\text{Branch\_name="Sharpstown"}}(\text{LIBRARY\_BRANCH}) \\ \text{Answer}\leftarrow {\pi }_{\text{No\_of\_copies}}({\sigma }_{\text{Book\_id=That\_Book\_ID, Branch\_id=That\_Branch\_ID}}(\text{BOOK\_COPIES})) \end{gathered}$$

---

b. How many copies of the book titles The Lost Tribe are owned by each library branch?

$$\begin{gathered} \text{That\_Book\_ID}\leftarrow {\sigma }_{\text{Title="The Lost Tribe"}}(\text{BOOK}) \\ \text{Answer}\leftarrow {\pi }_{\text{No\_of\_copies}}({\sigma }_{\text{Book\_id=That\_Book\_ID}}(\text{BOOK\_COPIES})) \end{gathered}$$

---

c. Retrieve the names of all borrowers who do not have any books checked out.

$$\begin{gathered} \text{All\_Borrowers}\leftarrow {\pi }_{\text{Card\_no}}(\text{BORROWER}) \\ \text{All\_Borrowers\_Has\_Books}\leftarrow {\pi }_{\text{Card\_no}}(\text{BOOK\_LOANS}) \\ \text{Borrowers\_No\_Book}\leftarrow \text{All\_Borrowers}-\text{All\_Borrowers\_Has\_Books} \\ \text{Answer}\leftarrow {\pi }_{\text{Name}}({\sigma }_{\text{Card\_no=Borrowers\_No\_Book}}(BORROWER)) \end{gathered}$$

---

d. For each book that is loaned out from the Sharpstown branch and whose Due_date is today, retrieve the book title, the borrower’s name, and the borrower’s address.

我不知道要怎麼定義 today 是哪一天，因此請允許我直接用 "today" 代指應該要選的那天。

$$\begin{gathered} \text{That\_Branch\_ID}\leftarrow {\sigma }_{\text{Branch\_name="Sharpstown"}}(\text{LIBRARY\_BRANCH}) \\ \text{Due\_Today}\leftarrow {\sigma }_{\text{Branch\_id=That\_Branch\_ID, Due\_date="today"}}(\text{BOOK\_LOANS}) \\ \text{Due\_Book\_id}\leftarrow {\pi }_{\text{Book\_id}}(\text{Due\_Today}) \\ \text{Due\_Book\_Title}\leftarrow {\pi }_{\text{Title}}({\sigma }_{\text{Book\_id=Due\_Book\_id}}(BOOK)) \\ \text{Borrowers\_no}\leftarrow {\pi }_{\text{Card\_no}}(\text{Due\_Today}) \\ \text{Borrower\_Info}\leftarrow {\pi }_{\text{Name, Address}}({\sigma }_{\text{Card\_no=Boorrowers\_no}}(BORROWER)) \\ \text{Answer}\leftarrow \text{Due\_Book\_Title}\cup \text{Borrower\_Info} \end{gathered}$$

---

e. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

$$\begin{gathered} \text{Loan\_Count\_by\_Branch\_id}{\leftarrow }_{\text{Branch\_id}}{\mathcal{F}}_{COUNT(\text{Branch\_id})}(\text{BOOK\_LOANS}) \\ \text{Answer}\leftarrow {\pi }_{\text{Branch\_name, Count\_branch\_id}}(\text{LIBRARY\_BRANCH}\cup \text{Loan\_Count\_by\_Branch\_id}) \end{gathered}$$

---

f. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.

$$\begin{gathered} \text{Group\_by\_Loan\_Count}{\leftarrow }_{\text{Card\_no}}{\mathcal{F}}_{COUNT(\text{Card\_no})}(\text{BOOK\_LOANS}) \\ \text{Borrower\_Satisfying}\leftarrow {\sigma }_{\text{Count\_card\_no > 5}}(\text{Group\_by\_Loan\_Count}) \\ \text{Answer}\leftarrow {\pi }_{\text{Name, Address, Count\_card\_no}}(\text{BORROWER}\cup \text{Borrower\_Satisfying}) \end{gathered}$$

---

g. For each book authored (or coauthored) by Stephen King, retrieve the title and the number of copies owned by the library branch whose name is Central.

我不知道在這個資料庫內怎麼尋找共同作者，因此我就只尋找作者名。

$$\begin{gathered} \text{Those\_Book\_ID}\leftarrow {\sigma }_{\text{Author\_name="Stephen King"}}(\text{BOOK\_AUTHORS}) \\ \text{Those\_Books}\leftarrow {\sigma }_{\text{Book\_id=Those\_Book\_ID}}(\text{BOOK}) \\ \text{That\_Branch\_ID}\leftarrow {\sigma }_{\text{Branch\_name="Central"}}(\text{LIBRARY\_BRANCH}) \\ \text{Books\_Satisfying}\leftarrow {\sigma }_{\text{Branch\_id=That\_Branch\_ID}}(\text{Those\_Books}) \\ \text{Book\_id\_Satisfying}\leftarrow {\pi }_{Boo{k}_{i}d}(\text{Books\_Satisfying}) \\ \text{Copies\_Satisfying}\leftarrow {\sigma }_{\text{Book\_id=Book\_id\_Satisfying}}(\text{BOOK\_COPIES}) \\ \text{Answer}\leftarrow {\pi }_{\text{Title, No\_of\_copies}}(\text{Books\_Satisfying}\cup \text{Copies\_Satisfying}) \end{gathered}$$

7.16

Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students’ transcripts. This is similar but not identical to the database shown in Figure 1.2:

a. The university keeps track of each student’s name, student number, Social Security number, current address and phone number, permanent address and phone number, birth date, sex, class (freshman, sophomore, …, graduate), major department, minor department (if any), and degree program (B.A., B.S., …, Ph.D.). Some user applications need to refer to the city, state, and ZIP Code of the student’s permanent address and to the student’s last name. Both Social Security number and student number have unique values for each student.

---

b. Each department is described by a name, department code, office number, office phone number, and college. Both name and code have unique values for each department.

---

c. Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of the course number is unique for each course.

---

d. Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, …, up to the number of sections taught during each semester.

---

e. A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, or 4).

---

Design an ER schema for this application, and draw an ER diagram for the schema. Specify key attributes of each entity type, and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

每個 Table 的 Key attribute 為：(我不知道怎麼在圖形內加上底線，因此文字敘述)

• STUDENT：Student_id, Ssn
• DEPARTMENT： Department_code
• SECTION： Section_number

另外 CURRENT_ADDRESS 、 PERMANENT_ADDRESS 與 GRADE_REPORT 都屬於 weak entity ，沒畫上去是因為沒有 double diamond 以及 double box 這類型的 shape
其他沒特別提到的限制等等我在圖形內應該都表示了，我盡可能地根據題目要求作圖。

我知道圖很小(物件太多了)，但我已經盡全力將圖形畫好了。
有些 edge 有 overlapping 的問題，但我不知道怎麼移動特定 edge 讓他不用穿圖。
使用的套件是 Graphviz，在Hackmd上作圖用這個還算不錯。

7.23

Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans.

a. List the strong (nonweak) entity types in the ER diagram.

BANK, ACCOUNT, LOAN, CUSTOMER

---

b. Is there a weak entity type? If so, give its name, partial key, and identifying relationship.

• BANK_BRANCH
  • partial key: Branch_no
  • identifying relationship: BRANCHES

---

c. What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram?

在具有 BANK 的 Code 與 BANK_BRANCH 的 Branch_no 情況下才能 uniquely indentify 每個銀行的分部。

---

d. List the names of all relationship types, and specify the (min, max) constraint on each participation of an entity type in a relationship type. Justify your choices.

• BRANCHES: BANK 的 (min, max) 為 (1, 1)；BANK_BRANCH 的 (min, max) 為 (1, n)。
  • 一個銀行名稱對應到許多分部
• ACCTS：BANK_BRANCH 的 (min, max) 為 (1, 1)；ACCOUNT 的 (min, max) 為 (0, n)。
  • 一個分部底下可能沒有帳戶或是有許多帳戶
• LOANS：BANK_BRANCH 的 (min, max) 為 (1, 1)；LOAN 的 (min, max) 為 (0, n)。
  • 一個分部底下可能沒有借貸或是有許多借貸
• A_C：ACCOUNT 的 (min, max) 為 (1, n)；CUSTOMER 的 (min, max) 為 (1, n)。
  • 一個人在一間銀行底下應該只有一個帳戶，但是有 n 個人就應該有 n 個帳戶
• L_C：LOAN 的 (min, max) 為 (0, n)；CUSTOMER 的 (min, max) 為 (1, n)。
  • 一個人在一間銀行底下可以有多次借貸，更別提許多人了

---

e. List concisely the user requirements that led to this ER schema design.

這個設計是從銀行的角度看過去，一個銀行在各地有著許多分部，每個分部底下都有各自的帳戶資料及借貸資料，分別對應到不同人。

---

f. Suppose that every customer must have at least one account but is restricted to at most two loans at a time, and that a bank branch cannot have more than 1,000 loans. How does this show up on the (min, max) constraints?

• 如果每個帳戶都沒有借貸的話，可以得到 CUSTOMER 的 (min, max) 為 (0, Infinity)，而 LOAN 的 (min, max) 為 (0, 0)。
• 如果每個帳戶都借貸一次的話，可以得到 CUSTOMER 的 (min, max) 為 (0, 1000)，而 LOAN 的 (min, max) 為 (0, 1000)。
• 如果每個帳戶都借貸兩次的話，可以得到 CUSTOMER 的 (min, max) 為 (0, 500)，而 LOAN 的 (min, max) 為 (0, 1000)。

從以上的討論可以得出：LOAN 的 (min, max) 為 (0, 1000)，但是對於 CUSTOMER 則是不明，會根據實際的借貸筆數狀況而影響實際 (min, max)。

tags: 1112_courses database