数据库系统原理期中试题解答

一

给出下列关系代数操作对应的 SQL 语句

1. \(\sigma_{p = 233}(r)\)

   SELECT * FROM r WHERE p = '233';

2. \(\Pi_{A_1, A_2, \cdots, A_m}(r)\)

   SELECT A_1, A_2, \cdots, A_m FROM r;

3. \(\Pi_{A_1, A_2}(\sigma_{p = 114}(r))\)

   SELECT A_1, A_2 FROM r WHERE p = '114';

4. \(\Pi_{A, B}(\sigma_{B = 114}(r \Join S))\)，假设 \(r(A, B, C)\)，\(s(C, E, F)\)。

   SELECT r.A, r.B FROM r JOIN s USING (C) WHERE r.B = '114';

二

给出下列 SQL 语句对应的关系代数表达式

1. SELECT * FROM student WHERE SNO = '10086';

   \[ \sigma_{\text{SNO} = 10086}(\text{Student}) \]

2. SELECT Name, Class FROM Student WHERE SNO = '10086';

   \[ \Pi_{\text{Name}, \text{Class}}(\sigma_{\text{SNO} = 10086}(\text{Student})) \]

3. SELECT Name FROM Student, SC WHERE SC.SNO = Student.SNO AND SC.CNO = '1';

   \[ \Pi_{\text{Name}}(\sigma_{\text{Student.SNO} = \text{SC.SNO} \land \text{SC.CNO = 1}}(\text{Student} \times \text{SC})) \]

4. SELECT R.*, S.C FROM R , S WHERE R.A = S.A;，假设 \(R(A, B)\)，\(S(A, C)\)

   \[ \Pi_{R.A, R.B, S.C}(\sigma_{R.A = S.A}(R \times S)) \]

三

某银行企业数据库关系如下，划线部分为主键

表	属性
branch	\(\underline{\text{branch-name}}\), branch-city, assets
loan	\(\underline{\text{loan-number}}\), branch-name, amount
borrower	\(\underline{\text{customer-name}}\), \(\underline{\text{loan-number}}\)
customer	\(\underline{\text{customer-name}}\), customer-street, customer-city
account	\(\underline{\text{account-number}}\), branch-name, balance
depositor	\(\underline{\text{customer-name}}\), account-number
employee	\(\underline{\text{employee-ID}}\), employee-name, branch-name, job-title

1. 使用 SQL 语句定义关系表 employee，其中 employee-ID 是主键，employee-name 是候选键并且不为空；表 employee 和 branch 之间存在参照完整性；员工的职位必须是 manager，teller，officer，secretary 之一。

   CREATE TABLE employee (
     employee_ID VARCHAR(5) NOT NULL,
     employee_name VARCHAR(10) NOT NULL,
     branch_name VARCHAR(10) NOT NULL,
     job_title VARCHAR(9) NOT NULL,
     PRIMARY KEY (employee_ID),
     UNIQUE (employee_name),
     CHECK (job_title in ('manager', 'teller', 'officer', 'secretary')),
     FOREIGN KEY (branch_name) REFERENCES branch(branch_name)
   );

2. 使用 SQL 语句查找在 Raccoon 市有一个或多个账户，并且这些账户的余额总额超过 10000 元的客户信息。列出客户的姓名和他在 Raccoon 市分支机构的账户总余额，客户姓名的字母降序排列。

   SELECT d.customer_name, SUM(a.balance) AS balance_in_Raccoon
   FROM depositor d
   JOIN account a on d.account_number = a.account_number
   JOIN branch b on a.branch_name = b.branch_name
   WHERE b.branch_city = 'Raccoon'
   GROUP BY d.customer_name
   HAVING SUM(a.balance) > 10000
   ORDER BY d.customer_name DESC;

四

1. 每位老师有教师代码（唯一）、教师姓名、办公地址、职级、部门。
2. 每门课程有课程名称、课程编号（唯一）、课程学分。课程包含章节，每个章节有一个编号（唯一）和章节名，同一门课程可能有多个章节。
3. 每个学生有学生编号（唯一）、姓名、专业、出生日期。
4. 一位老师最多可以教四门课程，每门课程由两位老师教授。
5. 一个学生可以选择多门课程，每门课程可以由多个学生选择。

E-R Diagram in Question 4

五

将下面的 E-R 图转换为相应的关系模型。对于每个模型，用 R(\(\underline{\text{A1, A2}}\), \(\cdots\), An) 的形式写出来，用下划线标出主键，并用 R.A1(FK) References S.A2(PK) 的形式说明外键。

E-R Diagram in Question 5

Customer(\(\underline{\text{Customer-no}}\), name, e-mail), Order(\(\underline{\text{order-no}}\), date, cost, Customer-no), Product(\(\underline{\text{product-no}}\), name, price) includes(\(\underline{\text{order-no}}\), \(\underline{\text{product-no}}\), quantity), Order.Customer-no(FK) References Customer.Customer-no(PK), includes.order-no(FK) References Order.order-no(PK), includes.product-no(FK) References Product.product-no(PK).

六·

A university student database needs to store information about \(\underline{\mathit{students}}\), \(\underline{\mathit{professors}}\), \(\underline{\mathit{projects}}\), and \(\underline{\pmb{departments}}\). Consider the following information:

• Each student has an SNo, a name, an age, and a degree program (e.g. M.S. or Ph.D.).
• Each professor has a PNo, a name, an age, and a research specialty.
• Each project has a project number, a starting date, an ending date, and a budget.
• Each department has a department number, a department name, and a main office.
• integrity constraints:
  1. A student studies in one (and only one) department.
  2. A Professor works in one (and only one) department.
  3. Each project must be managed by one and only one professor, and each professor must manage at least one project.
  4. Each project is worked on by some students, more than one student can participate(or work on) the same project, and some students may work on no projects.
  5. When a student work on a project, the professor managing this project must supervise the student’s work. One student may work on several projects, so he may have several supervisors.

1. Design and draw an E/R diagram for this database that captures the information above. > Note: mapping cardinality of each relationship and participation of each entity to the relationship should be described in the diagram.

   

   E-R Diagram in Question 6(1)

2. Convert the E-R diagram to the proper relational schema, and give the primary key of each relation schema by underlines.

   student(\(\underline{\text{SNo}}\), stu_name, stu_age, stu_degree, dept_number), professor(\(\underline{\text{PNo}}\), pro_name, pro_age, research_specialty, dept_number), project(\(\underline{\text{project\_number}}\), start_date, end_date, budget, PNo), department(\(\underline{\text{dept\_number}}\), dept_name, main_office), works_on(\(\underline{\text{student.SNo}}\), \(\underline{\text{project.project\_number}}\)), supervise(\(\underline{\text{student.SNo}}\), \(\underline{\text{professor.PNo}}\), project_number).

七

Consider the following relation schema \(R\) and the functional dependency \(F\) hold on \(R = \{X, Y, Z, W, Q\}\), \(F = \{XY \to ZQ, Q \to XY, Z \to W, Q \to Z\}\)

1. Compute \((XZ)+\).

   由 \(Z\to W\) 可得 \(W\)；无法由 \(XZ\) 推出 \(Q\) 或 \(Y\)（需要 \(Q\) 或 \(XY\) 才能触发 \(Q\to XY\) 或 \(XY\to ZQ\)），因此闭包为 \(\{X,Z,W\}\)。

2. Is the decomposition \(\rho = \{R1(X, Y, Z), R2(Z, W, Q)\}\) on \(R\) lossless-join? Why?

   交集为 \(R_1\cap R_2=\{Z\}\)。检查 \(Z^+=\{Z,W\}\)，既不是 \(R_1(X,Y,Z)\) 的超码，也不是 \(R_2(Z,W,Q)\) 的超码；不满足二元分解无损充要条件“交集函数决定其中一个模式”，故分解有损。

八

The functional dependency set \(F = \{A \to C, C \to A, B \to A, D \to AC, B \to E\}\) holds on the relation schema \(R = (A, B, C, D, E)\).

1. Compute \((AB)+\).

   由 \(B\to A\)、\(A\to C\)、\(B\to E\) 依次可得 \(A,C,E\)；无规则推出 \(D\)。

2. List all the candidate keys of \(R\).

   \(D\to AC\)，配合 \(B\to A, B\to E\) 得 \(BD\to ABCDE\)，且 \(B\) 与 \(D\) 分别不能由其它属性函数确定（右部无 \(B\)、\(D\)），因此 \(BD\) 最小且为唯一候选键。