数据库考前复习题
1.Make a list of curity concerns for a bank. For each item on your list, state whether this
concern relates to physical curity, human curity, operating system curity, or databa curity.
制定⼀个银⾏的安全问题列表。每个项⽬在您的名单，国家是否关注涉及物理安全，⼈⾝安全，操作系统安全、数据库安全。
2. Let the following relation schemas be given
让下⾯的关系模式是
3. R =(A,B,C)S =(D,E,F)Let relations r(R)and s(S) be given. Give an expression in SQL that is equivalent to each of the following queries.
让关系R（R）和S（S）be given。给年的表达在SQL 这是相当于下列查询。
a. ΠA(r)
b. σB=17 (r)
c. r × s
d. ΠA,F(σC=D(r × s))
2.What are five main functions of a databa administrator?
数据库管理员的五⼤主要功能是什么？
4. Consider the following information about a university databa：考虑以下信息⼀个的⼤学数据库：Professors have an SSN, a name, an age, a rank, and a rearch specialty.
教授有⼀个SSN，姓名，年龄，等级，和⼀个研究专业。
Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.
项⽬有项⽬编号，名称（例如，NSF）赞助，开始⽇期，结束⽇期年，与预算
Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.).
研究⽣有⼀个SSN，姓名，年龄，和⼀个学位课程（例如，硕⼠或博⼠）
Each project is managed by one professor (known as the project’s principal investigator).
每个项⽬都是由⼀个教授管理（称为项⽬的⾸席研究员）。
Each project is worked on by one or more professors (known as the pr oject’s co-investigators). Professors can manage and/or work on multiple projects.
每个项⽬的⼯作由⼀个或多个教授（称为项⽬的合作者）。
Each project is worked on by one or more graduate students (known as the project’s rearch assistants)
每个项⽬的⼯作由⼀个或多个研究⽣（称为项⽬的研究助理）
When graduate students work on a project, a professor must supervi their work on the project. Graduate students can work on multiple projects, in which ca they will have a (potentially di?erent) supervisor for each one.
当研究⽣⼯作的⼀个项⽬，⼀个教授必须监督该项⽬的⼯作。研究⽣可以⼯作在多个项⽬，在这种情况下，他们将有⼀个（可能不同地?）为每⼀个主管。
Departments have a department number, a department name, and a main o?ce.
Departments have a professor (known as the chairman) who runs the department.
部门有⼀个教授（称为主席）谁运⾏部。
Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job.教授在⼀个或多个部门的⼯作，并为每个部门，他们的⼯作时间的百分⽐，是与他们的⼯作相关的。
Graduate students have one major department in which they are working on their degree.
研究⽣都有⼀个主要的部门，他们的⼯作对他们的学位
Each graduate student has another, more nior graduate student (known as a student advisor) who advis him or her on what cours to take.
每个研究⽣的另⼀个，更⾼级的研究⽣（称为学⽣顾问）谁劝他或她选择的课程
Design and draw an ER diagram that captures the information about the university.
画⼀个图，捕获的关于⼤学的信息设计。
U only the basic ER model here; that is, entities, relationships, and attributes. Be sure to indicate any key and participation constraints.
这⾥只使⽤基本的ER模型；这是实体，属性，关系，和。⼀定要指出任何的关键与参与约束。
3.Consider the university databa from Exerci 6 and the ER diagram you designed. Write
SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.。
从练习6和ER图你考虑⼤学的数据库设计。编写SQL语句创建的对应关系和捕捉尽可能多的约束。如果你⽆法捕捉到⼀些制约因素，解释为什么。
6.Consider the following relations:
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, meets at: string, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
The meaning of the relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.
这些关系的意义是显⽽易见的；例如，每班学⽣对就读，学⽣参加⼀级记录
Write the following queries in SQL. No duplicates should be printed in any of the answers.
写出下⾯的SQL查询。没有重复，应该在任何的答案
1.find the names of all Juniors (level = JR) who are enrolled in a class taught by I. Teach.
找到所有学⽣的名字（⽔平= Jr）就读于教⼀班教。
2.find the age of the oldest student who is either a History major or enrolled in a cour taught by
I. Teach.
发现的最古⽼的学⽣谁是历史专业，或是参加课程教⼀教的年龄。
3.find the names of all class that either meet in room R128 or have five or more students enrolled.
找到所有类别的名称，可以满⾜房间r128或fi或更多的学⽣参加了
4.find the names of all students who are enrolled in two class that meet at the same time.
发现所有的学⽣就读于两类，同时满⾜的名字。
1．What are the responsibilities of a DBA?
⼀个DBA的职责是什么？
Answer :The DBA is responsible for:
Designing the logical and physical schemas, as well as widely-ud portions of the external schema.
逻辑和物理架构设计，以及⼴泛的外部架构的部分
Security and authorization. 安全和授权
Data availability and recovery from failures. 数据的可⽤性和故障恢复。
Databa tuning: The DBA is responsible for evolving the databa, in particular the conceptual and physical schemas, to ensure adequate performance as ur requirements change.
数据库调优DBA负责进化的数据库，特别是概念和物理模式，以保证⾜够的性能，⽤户需求的变化。2．List four significant differences between a file-processing system and a DBMS.
清单四中的fi斜⾯⼀fi乐处理系统和数据库管理系统之间的差异
Answer: Some main differences between a databa management system and
a file-processing system are:
答：⼀些主要的数据库管理系统之间的差异⽂件处理系统
Both systems contain a collection of data and a t of programs which access that data. A databa management system coordinates both the physical and the logical access to the data, whereas a file-processing system coordinates only the physical access.
这两种系统都包含⼀个收集数据和⼀组访问的程序的数据。⼀个数据库管理系统的物理和坐标数据的逻辑访问，⽽fi乐处理系统坐标的物理访问。
A databa management system reduces the amount of data duplication by
ensuring that a physical piece of data is available to all programs authorized
to have access to it, whereas data written by one program in a file-processing
system may not be readable by another program.
⼀个数据库管理系统，降低了数据复制的数量确保数据的物理块是可⽤的所有程序的授权拥有它，⽽在⽂件处理程序写⼊的数据系统不会被另⼀个程序的可读性
A databa management system is designed to allow flexible access to data
(i.e., queries), whereas a file-processing system is designed to allow predetermined access to data (i.e., compiled programs).
⼀个数据库管理系统的设计允许灵活的数据访问（即，查询），⽽⼀个⽂件处理系统被设计为允许预定访问数据（例如，编译程序）
A databa management system is designed to coordinate multiple urs accessing the same data
at the same time. A file-processing system is usually designed to allow one or more programs to access different data files at the same time. In a file-processing system, a file can be accesd by two programs concurrently only if both programs have read-only access to the file.
⼀个数据库管理系统的⽬的是协调多个⽤户访问相同的数据在同⼀时间。⽂件处理系统通常设计为允许⼀个或多个程序同时访问不同的数据⽂件。在⽂件处理系统，可以将⼀个⽂件同时只有两个程序必须⽂件的只读访问两个程序访问。
3.What is logical data independence and why is it important?
什么是逻辑数据独⽴性和为什么它是重要的
Answer: Logical data independence means that urs are shielded from changes in the logical structure of the data, i.e., changes in the choice of relations to be stored.
逻辑数据独⽴性是指⽤户屏蔽的数据，即逻辑结构的变化，在选择要存储的变化关系
For example, if a relation Students(sid, sname, gpa) is replaced by Studentnames(sid,sname) and Studentgpas(sid, gpa) for some reason, application programs that operate on the Students relation can be shielded from this change by defining a view Stu-dents(sid, sname, gpa) (as the natural join o
f Studentnames and Studentgpas). Thus, application programs that refer to Students need not be changed when the relation Students is replaced by the other two relations. The only change is that instead of storing Students tuples, the tuples are computed as needed by using the view definition; this is transparent to the application program.
例如，如果⼀个关系的学⽣（SID，SNAME，GPA）是由studentnames（SID，SNAME）和studentgpas
（SID，GPA）的⼀些原因，应⽤程序关系对学⽣操作可以避开这种变化的fi宁期学⽣（SID，SNAME，（GPA）作为studentnames和studentgpas⾃然加⼊）。因此，应⽤程序，是指学⽣不需要改变的关系时，学⽣是由其他两个关系取代。唯⼀的变化是，⽽不是存储学⽣这些元组元组，⽤视图的定义需要fi计算；这对于应⽤程序是透明的。
4.What is a transaction? 什么是交易
Answer:. A transaction is any one execution of a ur program in a DBMS. This is the basic unit of change in a DBMS.
任何⼀个交易是执⾏⽤户程序在数据库管理系统这是数据库管理系统的变化的基本单位。
5.
6 The statements to create tables corresponding to entity ts Customer,
Group, and Artist are straightforward and omitted. The other required tables can be created as follows：创造相应的实体集客户表的语句，组，和艺术家是简单的和略。其他所需的表可以创建如下：(1). CREATE TABLE Classify ( title CHAR(20),
name CHAR(20),
PRIMARY KEY (title, name),
FOREIGN KEY (title) REFERENCES Artwork Paints,
FOREIGN KEY (name) REFERENCES Group )
(2). CREATE TABLE Like Group (name CHAR(20),
cust name CHAR(20),
PRIMARY KEY (name, cust name),
FOREIGN KEY (name) REFERENCES Group,
FOREIGN KEY (cust name) REFERENCES Customer)
(3). CREATE TABLE Like Artist (name CHAR(20),
cust name CHAR(20),
PRIMARY KEY (name, cust name),
FOREIGN KEY (name) REFERENCES Artist,
FOREIGN KEY (cust name) REFERENCES Customer)
(4). CREATE TABLE Artwork Paints ( title CHAR(20),
artist name CHAR(20),
type CHAR(20),
price INTEGER,
year INTEGER,
PRIMARY KEY (title),
FOREIGN KEY (artist name)
References Artist)
7
(1). ame, E.age
FROM Emp E, Works W1, Works W2, Dept D1, Dept D2
WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = ‘Hardwa re’ and
E.eid = W2.eid AND W2.did = D2.did AND D2.dname = ‘Software
(2). SELECT W.did, COUNT (W.eid)
FROM Works W
GROUP BY W.did
HA VING 2000 < ( SELECT SUM (W1.pct time)
FROM Works W1
WHERE W1.did = W.did )
(3). ame
FROM Emp E
WHERE E.salary > ALL (SELECT D.budget
FROM Dept D, Works W
WHERE E.eid = W.eid AND D.did = W.did)
(4). SELECT DISTINCT D.managerid
FROM Dept D
WHERE 1000000 < ALL (SELECT D2.budget
FROM Dept D2
WHERE D2.managerid = D.managerid )
(5). ame
FROM Emp E
WHERE E.eid IN (SELECT D.managerid
FROM Dept D
WHERE D.budget = (SELECT MAX (D2.budget)
FROM Dept D2))
(6). SELECT D.managerid
FROM Dept D
WHERE 5000000 < (SELECT SUM (D2.budget)
FROM Dept D2
WHERE D2.managerid = D.managerid )
1. 1. Explain the following terms briefly: attribute, domain, entity, relationship, one-to-many relationship, many-to-many relationship.解释下列术语作了：属性，域，实体，关系，⼀对多的关系，多对多的关系
Answer 2.1 Term explanations:
Attribute - a property or description of an entity. A toy department employee
entity could have attributes describing the employee’s name, salary, and years of
Service
属性：⼀个实体的属性或描述。玩具部门的员⼯实体可以描述员⼯的名字，属性的⼯资，和年
服务。
Domain - a t of possible values for an attribute.域-设置⼀个属性的可能值
