浙江大学 2019–2020 学年 春夏 学期
《数据库系统》课程期末考试试卷
课程号: 21121350 , 开课学院:_计算机学院______
考试试卷:√A 卷、B 卷(请在选定项上打√)
考试形式:√闭、开卷(请在选定项上打√),允许带一张 A4 纸笔记入场
考试日期: 2020 年 9 月 5 日,考试时间: 120 分钟
诚信考试,沉着应考,杜绝违纪。
考生姓名: 学号: 所属院系: _
Problem 1:Relational Model and SQL(18 points)
A software development company develops software projects for different clients. It has
the following relational schemas for its internal management system:
client (cId, cName, cCity)
project (pId, pName, cId, startTime, endTime, budget, paid)
employee (eId, eName, eAddress, eSalary, eBonus)
participate (pId, eId, role)
The underlined are primary keys. “cId” in “project”, “pId” and “eId” in “participate” are
foreign keys. “paid” is the cost that the client has paid for the project. One employee can
participate in different projects with a specific role for each project. Only three different
roles are permitted: “project manager”, “developer”, and “tester”. Based on the above
schemas, answer the following questions:
(1) Write a relational algebra expression to find the project names that the client “X Bank”
has, where “X Bank” is the client name. (3 points)
(2) Write a SQL statement to create table participate with all the necessary constraints. (5
points)
(3) Write a SQL statement to find the employee names who participate in different projects
with all the three different roles. (3 points)
评论0