数据库考前复习题

1. Make a list of security concerns for a bank. For each item on your list, state whether this

concern relates to physical security, human security, operating system security, or database security.

2. Let the following relation schemas be given

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.

a. ΠA(r)

b. σB=17 (r)

c. r × s

d. ΠA,F (σC=D(r × s))

2. What are ?ve main functions of a database administrator?

4. Consider the following information about a university database:

Professors have an SSN, a name, an age, a rank, and a research specialty.

Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.

Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.). 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 project’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 research assistants).

When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case 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 senior graduate student (known as a student advisor) who advises him or her on what courses to take.

Design and draw an ER diagram that captures the information about the university.

Use only the basic ER model here; that is, entities, relationships, and attributes. Be sure to indicate any key and participation constraints.

5. Consider the university database from Exercise 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. Consider the following relations:

Student(snum: integer, sname: string, major: string, level: string, age: integer)

Class(name: string, meets at: string, room: string, ?d: integer)

Enrolled(snum: integer, cname: string)

Faculty(?d: integer, fname: string, deptid: integer)

The meaning of these 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.

1. Find the names of all Juniors (level = JR) who are enrolled in a class taught by I. Teach.

2. Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.

3. Find the names of all classes that either meet in room R128 or have ?ve or more students enrolled.

4. Find the names of all students who are enrolled in two classes that meet at the same time.

1．What are the responsibilities of a DBA?

Answer :The DBA is responsible for:

Designing the logical and physical schemas, as well as widely-used portions of the external schema.

Security and authorization.

Data availability and recovery from failures.

Database tuning: The DBA is responsible for evolving the database, in particular the conceptual and physical schemas, to ensure adequate performance as user requirements change.

2．List four signi?cant differences between a ?le-processing system and a DBMS.

Answer: Some main differences between a database management system and

a ?le-processing system are:

? Both systems contain a collection of data and a set of programs which access that data. A database management system coordinates both the physical and the logical access to the data, whereas a ?le-processing system coordinates only the physical access.

? A database 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 ?le-processing

system may not be readable by another program.

? A database management system is designed to allow ?exible access to data

(i.e., queries), whereas a ?le-processing system is designed to allow predetermined access to data (i.e., compiled programs).

? A database management system is designed to coordinate multiple users

accessing the same data at the same time. A ?le-processing systemis usually

designed to allow one or more programs to access different data ?les at

the same time. In a ?le-processing system, a ?le can be accessed by two

programs concurrently only if both programs have read-only access to the

?le.

3. What is logical data independence and why is it important?

Answer: Logical data independence means that users 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 de?ning a view Stu-dents(sid, sname, gpa) (as the natural join of 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, these tuples are computed as needed by using the view de?nition; this is transparent to the application program.

4.What is a transaction?

Answer:. A transaction is any one execution of a user program in a DBMS. This is the basic unit of change in a DBMS.

5.

6 The statements to create tables corresponding to entity sets 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). SELECT E.ename, 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 = ‘Hardware’ 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

HAVING 2000 < ( SELECT SUM (W1.pct time)

FROM Works W1

WHERE W1.did = W.did )

(3). SELECT E.ename

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). SELECT E.ename

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 brie?y: 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 set of possible values for an attribute.

Entity - an object in the real world that is distinguishable from other objects such as the green dragon toy.

Relationship - an association among two or more entities.

Entity set - a collection of similar entities such as all of the toys in the toy depart- ment.

Relationship set - a collection of similar relationships

One-to-many relationship - a key constraint that indicates that one entity can be associated with many of another entity. An example of a one-to-many relationship is when an employee can work for only one department, and a department can have many employees.

Many-to-many relationship - a key constraint that indicates that many of one entity can be associated with many of another entity. An example of a many- to-many relationship is employees and their hobbies: a person can have many di?erent hobbies, and many people can have the same hobby.

2. Exercise 4.2 Given two relations R1and R2, where R1 contains N1 tuples, R2con- tains N2 tuples, and N2 > N1 > 0, give the minimum and maximum possible sizes (in tuples) for the resulting relation produced by each of the following relational algebra expressions. In each case, state any assumptions about the schemas for R1and R2 needed to make the expression meaningful:

(1) R1∪R2, (2) R1∩R2, (3) R1?R2, (4) R1×R2, (5) σa=5(R1), (6) πa(R1), and (7) R1/R2

Aanswer: