数据库题库答案

第二套试卷

1. List four signi?cant differences between a ?le-processing system and a DBMS. Answer:

1)两种系统都包含数据收集和一套存取那些数据的程序，DBMS允许物理上的和逻辑上的数据存取，而文件处理系统只能进行物理上的存取。

2 _DBMS能够通过授权所有程序访问一个物理数据块，来减少数据的冗余，而在文件处理系统中，一个程序所写的数据不能被另一个程序读取。

3 )DBMS允许灵活的对数据进行访问，而文件处理系统则只允许预定的数据访问。

4 )DBMS允许多个用户同时访问同一数据，而文件处理系统则只允许一个或多个程序同时访问不同的数据，只有当两个程序对文件进行只读操作时，才允许并发地访问该文件。

2. Why would you choose a database system instead of simply storing data in operating system ?les? When would it make sense not to use a database system? 1。使用DBMS存储数据并通过WEB浏览器浏览数据。通过WEB可存取的表单界面来产生查询请示，并使用诸如HTML的标记语言将查询结果格式化，从而便于在浏览器中显示！2。一个原因为DBMS是一个复杂的软件，并为如处理多个并发请求之类的工作载荷进行了优化，因此，它的性能可能对一些如具有严格实时约束的应用程序或带有一些定义明确的关键操作并且为这些操作必须编写有效的客户代码的应用程序之类的不是很适合。

另一个原因是某些应用程序可能需要以查询语言不能支持的开工来操纵数据。 3.What is logical data independence and why is it important?

逻辑数据独立性是指用户的应用程序与数据库的逻辑结构是相互独立的，即，当数据的逻辑结构改变时，用户程序也可以不变。

数据逻辑结构改变时，不一定要求修改程序，程序对数据使用的改变也不一定要求修改全局数据结构，使进一步实现深层次数据共享成为可能。

4. Which of the following plays an important role in representing information about the real world in a database? Explain brie?y. 1). The data de?nition language. 2). The data manipulation language. 3). The bu?er manager.

4). The data model. Answer:

1). The data de?nition language.

数据定义语言 (Data Definition Language, DDL) 是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集。目前大多数的DBMS都支持对数据库对象的DDL操作，部份数据库 (如 PostgreSQL) 可把DDL放在交易指令中，

也就是它可以被撤回 (Rollback)。较新版本的DBMS会加入DDL专用的触发程序，让数据库管理员可以追踪来自DDL的修改。 2). The data manipulation language.

数据操纵语言DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作。例如，对表中数据的插入、删除和修改。 3). The bu?er manager.

4). The data model. 数据模型

数据（data）是描述事物的符号记录。模型（Model)是现实世界的抽象。数据模型（Data Model）是数据特征的抽象，是数据库管理的教学形式框架。数据库系统中用以提供信息表示和操作手段的形式构架。数据模型包括数据库数据的结构部分、数据库数据的操作部分和数据库数据的约束条件。 5. What are the responsibilities of a DBA?

1文件管理互动 2完整性约束 3安全性约束 4备份和恢复 5并发控制 6 A company database needs to store information about employees (identi?ed by ssn,with salary and phone as attributes), departments (identi?ed by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identi?ed uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. Draw an ER diagram that captures this information.

7 Consider the scenario from Exercise 6, where you designed an ER diagram for a company database. 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.

Answer The following SQL statements create the corresponding relations.

CREATE TABLE Employees ( ssn CHAR(10), sal INTEGER, phone CHAR(13),

PRIMARY KEY (ssn) )

CREATE TABLE Departments ( dno INTEGER, budget INTEGER, dname CHAR(20),

PRIMARY KEY (dno) )

CREATE TABLE Works in ( ssn CHAR(10), dno INTEGER,

PRIMARY KEY (ssn, dno),

FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (dno) REFERENCES Departments) CREATE TABLE Manages ( ssn CHAR(10), dno INTEGER,

PRIMARY KEY (dno),

FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (dno) REFERENCES Departments) CREATE TABLE Dependents (ssn CHAR(10), name CHAR(10), age INTEGER,

PRIMARY KEY (ssn, name),

FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE )

8.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 faculty members for whom the combined enrollment of the courses that they teach is less than ?ve

2). For each level, print the level and the average age of students for that level.

3). For all levels except JR, print the level and the average age of students for that level.

4). For each faculty member that has taught classes only in room R128, print the faculty member?s name and the total number of classes she or he has taught. 5). Find the names of students enrolled in the maximum number of classes 6). Find the names of students not enrolled in any class. Anwser:

1. SELECT DISTINCT F.fname

FROM Faculty F

WHERE 5 > (SELECT COUNT (E.snum) FROM Class C, Enrolled E WHERE C.name = E.cname AND C.?d = F.?d)

2. SELECT S.level, AVG(S.age) FROM Student S GROUP BY S.level

3. SELECT S.level, AVG(S.age) FROM Student S

WHERE S.level <> ?JR? GROUP BY S.level

4. SELECT F.fname, COUNT(*) AS CourseCount FROM Faculty F, Class C WHERE F.?d = C.?d

GROUP BY F.?d, F.fname

HAVING EVERY ( C.room = ?R128? ) 5. SELECT DISTINCT S.sname FROM Student S

WHERE S.snum IN (SELECT E.snum FROM Enrolled E GROUP BY E.snum

HAVING COUNT (*) >= ALL (SELECT COUNT (*) FROM Enrolled E2 GROUP BY E2.snum ))

6. SELECT DISTINCT S.sname FROM Student S

WHERE S.snum NOT IN (SELECT E.snum FROM Enrolled E )

第三套试卷

1. Explain the following terms brie?y: attribute, domain, entity, relationship, one-to-many relationship, many-to-many relationship. 1．属性是实体集中每个成员具有的描述性性质；

2.域在文件系统中，有时也称做“字段”，是指数据中不可再分的基本单元。一个域包含一个值。

3.实体是现实世界中可区别于其他对象的“事件”或“物体”。每个实体都有一组属性，其中一部分属性的取值可以唯一标识一个实体； 4.联系是多个实体间的相互关联。

5.一对多来关系，A中的一个实体可以和B中的任意数目实体相联系，而B中的一个实体至多同A中的一个实体相联系。

6.多对多关系，A中的一个实体可以和B中的任意数目实体相联系，而B中的一个实体也可以同A中的任意数目实体相联系。

2. Given two relations R1and R2, where R1 contains N1 tuples, R2contains 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),

3. Notown Records has decided to store information about musicians who（资料） 4.Consider the Notown database from Exercise 3. You have decided

to recommend that Notown use a relational database system to store company data. Show the SQL statements for creating relations corresponding to the entity sets and relationship sets in your design. Identify any constraints in the ER diagram that you are unable to capture in the SQL statements and brie?y explain why you could not express them.

Answer The following SQL statements create the corresponding relations. 1. CREATE TABLE Musicians ( ssn CHAR(10), name CHAR(30),

PRIMARY KEY (ssn))

2. CREATE TABLE Instruments ( instrId CHAR(10), dname CHAR(30), key CHAR(5),

PRIMARY KEY (instrId))

3. CREATE TABLE Plays ( ssn CHAR(10), instrId INTEGER,

PRIMARY KEY (ssn, instrId),

FOREIGN KEY (ssn) REFERENCES Musicians,

FOREIGN KEY (instrId) REFERENCES Instruments )

FROM Catalog C1

WHERE C1.pid = P.pid) 2. SELECT DISTINCT C.sid FROM Catalog C

WHERE NOT EXISTS ( SELECT * FROM Parts P

WHERE P.pid = C.pid AND P.color <> ?Red? ) 3. SELECT DISTINCT C.sid FROM Catalog C, Parts P

WHERE C.pid = P.pid AND P.color = ?Red? INTERSECT

SELECT DISTINCT C1.sid FROM Catalog C1, Parts P1

WHERE C1.pid = P1.pid AND P1.color = ?Green? 4. SELECT DISTINCT C.sid FROM Catalog C, Parts P

WHERE C.pid = P.pid AND P.color = ?Red? UNION

SELECT DISTINCT C1.sid FROM Catalog C1, Parts P1

WHERE C1.pid = P1.pid AND P1.color = ?Green? 5. SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid

HAVING EVERY (P.color=?Green?)

6. SELECT S.sname, MAX(C.cost) as MaxCost FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid

HAVING ANY ( P.color=?green? ) AND ANY ( P.color = ?red? )

第五套试卷

1. 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.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.

Answer: Let us consider the problem of protecting our sample bank database. Some security measures at each of the four levels are mentioned below - a. Physical level - The system from which the relations can be accessed and modi?ed should be placed in a locked,well-guarded, and impregnable room. b. Human level - A proper key transfer policy should be enforced for restrict- ing access to the “system room” mentioned above. Passwords for gaining access to the database should be known only to trusted users.

c. Operating System level - Login passwords should be dif?cult to guess and they should be changed regularly. No user should be able to gain unautho- rized access to the system due to a software bug in the operating system.

d. Database System level - The users should be authorized access only to rele- vant parts of the database. For example, a bank teller should be allowed to modify values for the customer?s balance, but not for her own salary.

3.The Prescriptions-R-X chain of pharmacies has o?ered to give you a（资料） Answer 1. The ER diagram is shown in Figure 2.11.

2. If the drug is to be sold at a ?xed price we can add the price attribute to the Drug entity set and eliminate the price from the Sell relationship set.

3. The date information can no longer be modeled as an attribute of Prescription. We have to create a new entity set called Prescription date and make Prescription a 4-way relationship set that involves this additional entity set.

4. Consider the ER diagram that you designed for the Prescriptions-R-X

chain of pharmacies in Exercise3. De?ne relations corresponding to the entity sets and relationship sets in your design using SQL.

Answer The statements to create tables corresponding to entity sets Doctor,

Pharmacy, and Pharm co are straightforward and omitted. The other required tables can be created as follows:

1. CREATE TABLE Pri Phy Patient ( ssn CHAR(11), name CHAR(20), age INTEGER, address CHAR(20), phy ssn CHAR(11), PRIMARY KEY (ssn),

FOREIGN KEY (phy ssn) REFERENCES Doctor ) 2. CREATE TABLE Prescription ( ssn CHAR(11), phy ssn CHAR(11), date CHAR(11), quantity INTEGER, trade name CHAR(20), pharm id CHAR(11),

PRIMARY KEY (ssn, phy ssn),

FOREIGN KEY (ssn) REFERENCES Patient, FOREIGN KEY (phy ssn) REFERENCES Doctor, FOREIGN KEY (trade name, pharm id) References Make Drug)

3. CREATE TABLE Make Drug (trade name CHAR(20), pharm id CHAR(11),

PRIMARY KEY (trade name, pharm id),

FOREIGN KEY (trade name) REFERENCES Drug, FOREIGN KEY (pharm id) REFERENCES Pharm co) 4. CREATE TABLE Sell ( price INTEGER, name CHAR(10),

trade name CHAR(10),

PRIMARY KEY (name, trade name),

FOREIGN KEY (name) REFERENCES Pharmacy, FOREIGN KEY (trade name) REFERENCES Drug) 5. CREATE TABLE Contract ( name CHAR(20), pharm id CHAR(11), start date CHAR(11), end date CHAR(11), text CHAR(10000), supervisor CHAR(20),

PRIMARY KEY (name, pharm id),

FOREIGN KEY (name) REFERENCES Pharmacy, FOREIGN KEY (pharm id) REFERENCES Pharm co)

5. The following relations keep track of airline ?ight information: Flights(?no: integer, from: string, to: string, distance: integer, departs: time, arrives: time, price: real)

Aircraft(aid: integer, aname: string, cruisingrange: integer) Certi?ed(eid: integer, aid: integer)

Employees(eid: integer, ename: string, salary: integer)

Note that the Employees relation describes pilots and other kinds of employees as well;

every pilot is certi?ed for some aircraft, and only pilots are certi?ed to ?y. Write each of the following queries in SQL. (Additional queries using the same schema are listed in the exercises for Chapter 4.)

1. Find the names of aircraft such that all pilots certi?ed to operate them have salaries more than $80,000.

2. For each pilot who is certi?ed for more than three aircraft, ?nd the eid and the maximum cruisingrange of the aircraft for which she or he is certi?ed.

3. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

4. For all aircraft with cruisingrange over 1000 miles, ?nd the name of the aircraft and the average salary of all pilots certi?ed for this aircraft. 5. Find the names of pilots certi?ed for some Boeing aircraft.

6. Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

Answer The answers are given below: 1. SELECT DISTINCT A.aname FROM Aircraft A

WHERE A.Aid IN (SELECT C.aid FROM Certi?ed C, Employees E WHERE C.eid = E.eid AND NOT EXISTS ( SELECT * FROM Employees E1

WHERE E1.eid = E.eid AND E1.salary < 80000 )) 2. SELECT C.eid, MAX (A.cruisingrange) FROM Certi?ed C, Aircraft A WHERE C.aid = A.aid GROUP BY C.eid

HAVING COUNT (*) > 3

3. SELECT DISTINCT E.ename FROM Employees E

WHERE E.salary < ( SELECT MIN (F.price) FROM Flights F

WHERE F.from = ?Los Angeles? AND F.to = ?Honolulu? )

4. Observe that aid is the key for Aircraft, but the question asks for aircraft names; we deal with this complication by using an intermediate relation Temp: SELECT Temp.name, Temp.AvgSalary

FROM ( SELECT A.aid, A.aname AS name, AVG (E.salary) AS AvgSalary

FROM Aircraft A, Certi?ed C, Employees E WHERE A.aid = C.aid AND

C.eid = E.eid AND A.cruisingrange > 1000 GROUP BY A.aid, A.aname ) AS Temp 5. SELECT DISTINCT E.ename

FROM Employees E, Certi?ed C, Aircraft A WHERE E.eid = C.eid AND C.aid = A.aid AND

A.aname LIKE ?Boeing%? 6. SELECT A.aid FROM Aircraft A

WHERE A.cruisingrange > ( SELECT MIN (F.distance) FROM Flights F

WHERE F.from = ?Los Angeles? AND F.to = ?Chicago? )

第六套试卷

1. 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.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.

Answer: Let us consider the problem of protecting our sample bank database. Some security measures at each of the four levels are mentioned below - a. Physical level - The system from which the relations can be accessed and modi?ed should be placed in a locked,well-guarded, and impregnable room. b. Human level - A proper key transfer policy should be enforced for restrict- ing access to the “system room” mentioned above. Passwords for gaining access to the database should be known only to trusted users.

c. Operating System level - Login passwords should be dif?cult to guess and they should be changed regularly. No user should be able to gain unautho- rized access to the system due to a software bug in the operating system.

d. Database System level - The users should be authorized access only to rele- vant parts of the database. For example, a bank teller should be allowed to modify values for the customer?s balance, but not for her own salary.

3.The Prescriptions-R-X chain of pharmacies has o?ered to give you a（资料） Answer 1. The ER diagram is shown in Figure 2.11.

2. If the drug is to be sold at a ?xed price we can add the price attribute to the Drug entity set and eliminate the price from the Sell relationship set.

3. The date information can no longer be modeled as an attribute of Prescription. We have to create a new entity set called Prescription date and make Prescription a 4-way relationship set that involves this additional entity set.

4. Consider the ER diagram that you designed for the Prescriptions-R-X

chain of pharmacies in Exercise3. De?ne relations corresponding to the entity sets and relationship sets in your design using SQL.

Answer The statements to create tables corresponding to entity sets Doctor,

Pharmacy, and Pharm co are straightforward and omitted. The other required tables can be created as follows:

1. CREATE TABLE Pri Phy Patient ( ssn CHAR(11), name CHAR(20),

age INTEGER, address CHAR(20), phy ssn CHAR(11), PRIMARY KEY (ssn),

FOREIGN KEY (phy ssn) REFERENCES Doctor ) 2. CREATE TABLE Prescription ( ssn CHAR(11), phy ssn CHAR(11), date CHAR(11), quantity INTEGER, trade name CHAR(20), pharm id CHAR(11),

PRIMARY KEY (ssn, phy ssn),

FOREIGN KEY (ssn) REFERENCES Patient, FOREIGN KEY (phy ssn) REFERENCES Doctor, FOREIGN KEY (trade name, pharm id) References Make Drug)

3. CREATE TABLE Make Drug (trade name CHAR(20), pharm id CHAR(11),

PRIMARY KEY (trade name, pharm id),

FOREIGN KEY (trade name) REFERENCES Drug, FOREIGN KEY (pharm id) REFERENCES Pharm co) 4. CREATE TABLE Sell ( price INTEGER, name CHAR(10),

trade name CHAR(10),

PRIMARY KEY (name, trade name),

FOREIGN KEY (name) REFERENCES Pharmacy, FOREIGN KEY (trade name) REFERENCES Drug) 5. CREATE TABLE Contract ( name CHAR(20), pharm id CHAR(11), start date CHAR(11), end date CHAR(11), text CHAR(10000), supervisor CHAR(20),

PRIMARY KEY (name, pharm id),

FOREIGN KEY (name) REFERENCES Pharmacy, FOREIGN KEY (pharm id) REFERENCES Pharm co)

5. The following relations keep track of airline ?ight information: Flights(?no: integer, from: string, to: string, distance: integer, departs: time, arrives: time, price: real)

Aircraft(aid: integer, aname: string, cruisingrange: integer) Certi?ed(eid: integer, aid: integer)

Employees(eid: integer, ename: string, salary: integer)

Note that the Employees relation describes pilots and other kinds of employees as well;

every pilot is certi?ed for some aircraft, and only pilots are certi?ed to ?y. Write each of the following queries in SQL. (Additional queries using the same schema are listed in the exercises for Chapter 4.)

1. Find the names of aircraft such that all pilots certi?ed to operate them have salaries more than $80,000.

2. For each pilot who is certi?ed for more than three aircraft, ?nd the eid and the maximum cruisingrange of the aircraft for which she or he is certi?ed.

3. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

4. For all aircraft with cruisingrange over 1000 miles, ?nd the name of the aircraft and the average salary of all pilots certi?ed for this aircraft. 5. Find the names of pilots certi?ed for some Boeing aircraft.

6. Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

Answer The answers are given below: 1. SELECT DISTINCT A.aname FROM Aircraft A

WHERE A.Aid IN (SELECT C.aid FROM Certi?ed C, Employees E WHERE C.eid = E.eid AND NOT EXISTS ( SELECT * FROM Employees E1

WHERE E1.eid = E.eid AND E1.salary < 80000 )) 2. SELECT C.eid, MAX (A.cruisingrange) FROM Certi?ed C, Aircraft A WHERE C.aid = A.aid GROUP BY C.eid

HAVING COUNT (*) > 3

3. SELECT DISTINCT E.ename FROM Employees E

WHERE E.salary < ( SELECT MIN (F.price) FROM Flights F

WHERE F.from = ?Los Angeles? AND F.to = ?Honolulu? )

4. Observe that aid is the key for Aircraft, but the question asks for aircraft names; we deal with this complication by using an intermediate relation Temp: SELECT Temp.name, Temp.AvgSalary

FROM ( SELECT A.aid, A.aname AS name, AVG (E.salary) AS AvgSalary

FROM Aircraft A, Certi?ed C, Employees E WHERE A.aid = C.aid AND

C.eid = E.eid AND A.cruisingrange > 1000 GROUP BY A.aid, A.aname ) AS Temp

5. SELECT DISTINCT E.ename

FROM Employees E, Certi?ed C, Aircraft A WHERE E.eid = C.eid AND C.aid = A.aid AND

A.aname LIKE ?Boeing%? 6. SELECT A.aid FROM Aircraft A

WHERE A.cruisingrange > ( SELECT MIN (F.distance) FROM Flights F

WHERE F.from = ?Los Angeles? AND F.to = ?Chicago? ) 第七套试卷 1. Computer Sciences Department frequent ?iers have been complaining to Dane County Airport o?cials about the poor organization at the airport. As a result, the o?cials decided that all information related to the airport should be organized using a DBMS, and you have been hired to design the database. Your ?rst task is to organize the information about all the airplanes stationed and maintained at the airport. The relevant information is as follows:

Every airplane has a registration number, and each airplane is of a speci?c model. The airport accommodates a number of airplane models, and each model is identi?ed by a model number (e.g., DC-10) and has a capacity and a weight.

A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician.

Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.

Tra?c controllers must have an annual medical examination. For each tra?c controller, you must store the date of the most recent exam.

All airport employees (including technicians) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identi?ed by a social security number.

The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score.

The FAA requires the airport to keep track of each time a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score the airplane received on the test.

1. Draw an ER diagram for the airport database. Be sure to indicate the various attributes of each entity and relationship set; also specify the key and participation

constraints for each relationship set. Specify any necessary overlap and covering constraints as well (in English).

2. The FAA passes a regulation that tests on a plane must be conducted by a technician who is an expert on that model. How would you express this constraint in the ER diagram? If you cannot express it, explain brie?y.

answer:

1. Since all airline employees belong to a union, there is a covering constraint on the Employees ISA hierarchy.

2. You cannot note the expert technician constraint the FAA requires in an ER diagram. There is no notation for equivalence in an ER diagram and this is what is needed: the Expert relation must be equivalent to the Type relation.

2. Translate your ER diagram from Exercise 1 into a relational schema, and show the SQL statements needed to create the relations, using only key and null constraints. If your translation cannot capture any constraints in the ER diagram, explain why.

In Exercise 1, you also modi?ed the ER diagram to include the constraint that tests on a plane must be conducted by a technician who is an expert on that model. Can you modify the SQL statements de?ning the relations obtained by mapping the ER diagram to check this constraint?

Answer The following SQL statements create the corresponding relations. 1. CREATE TABLE Expert ( ssn CHAR(11), model no INTEGER,

PRIMARY KEY (ssn, model no),

FOREIGN KEY (ssn) REFERENCES Technician, FOREIGN KEY (model no) REFERENCES Models )

The participation constraint cannot be captured in the table.

2. CREATE TABLE Models ( model no INTEGER, capacity INTEGER, weight INTEGER,

PRIMARY KEY (model no))

3. CREATE TABLE Employees ( ssn CHAR(11), union mem no INTEGER, PRIMARY KEY (ssn))

4. CREATE TABLE Technician emp ( ssn CHAR(11), name CHAR(20), address CHAR(20), phone no CHAR(14), PRIMARY KEY (ssn), FOREIGN KEY (ssn)

REFERENCES Employees ON DELETE CASCADE)

5. CREATE TABLE Tra?c control emp ( ssn CHAR(11),

exam date DATE,

PRIMARY KEY (ssn), FOREIGN KEY (ssn)

REFERENCES Employees ON DELETE CASCADE)

6. CREATE TABLE Plane Type ( reg no INTEGER, model no INTEGER, PRIMARY KEY (reg no),

FOREIGN KEY (model no) REFERENCES Models) 7. CREATE TABLE Test info ( FFA no INTEGER, ssn CHAR(11), reg no INTEGER, hours INTEGER, date DATE,

score INTEGER,

PRIMARY KEY (ssn, reg no, FFA no),

FOREIGN KEY (reg no) REFERENCES Plane Type, FOREIGN KEY (FAA no) REFERENCES Test, FOREIGN KEY (ssn) REFERENCES Employees )

8. The constraint that tests on a plane must be conducted by a technician who is an expert on that model can be expressed in SQL as follows. CREATE TABLE Test info ( FFA no INTEGER, ssn CHAR(11), reg no INTEGER, hours INTEGER, date DATE,

score INTEGER,

本文来源：https://www.bwwdw.com/article/uvbt.html