数据库复习范围 往年试题 - 飘凌大杂烩
飘凌大杂烩电脑网络手机数码计算机科学与技术数据库复习范围 往年试题
    
 
数据库复习范围 往年试题
发起人:piaoling  回复数:3  浏览数:7434  最后更新:2009/8/23 12:03:09 by piaoling

选择查看 搜索更多相关主题  帖子排序:
2009/8/23 12:00:54
piaoling






角  色:管理员
等  级:旅长
发 帖 数:672
经 验 值:2001
注册时间:2008/12/7
数据库复习范围 往年试题
E1: RA πsname(πsid((πpidσcolor = ’red’ Parts) Catalog) Suppliers) SQL SELECT S.sname FROM Suppliers S, Parts P, Catalog C WHERE P.color=’red’ AND C.pid=P.pid AND C.sid=S.sid ----------------------------------------------------------------------------------------------------------------------------- E2: RA πsid(πpid(σcolor=’red’∨color=’green’Parts) catalog) SQL SELECT C.sid FROM Catalog C, Parts P WHERE (P.color =’red’ OR P.color = ‘green’) AND P.pid = C.pid ----------------------------------------------------------------------------------------------------------------------------- E3: RA ρ(R1, πsid((πpidσcolor=’red’Parts) Catalog)) ρ(R2, πsidσaddress=’221PackerStreet’Suppliers) R1 ∪ R2 SQL SELECT S.sid FROM Suppliers S WHERE S.address = ‘221 Packer street’ OR S.sid IN ( SELECT C.sid FROM Parts P, Catalog C WHERE P.color=’red’ AND P.pid = C.pid ) ----------------------------------------------------------------------------------------------------------------------------- E4: RA ρ(R1, πsid((πpidσcolor=’red’Parts) Catalog)) ρ(R2, πsid((πpidσcolor=’green’Parts) Catalog)) R1 ∩ R2 SQL SELECT C.sid FROM Parts P, Catalog C WHERE P.color = ‘red’ AND P.pid = C.pid AND EXISTS ( SELECT P2.pid FROM Parts P2, Catalog C2 WHERE P2.color = ‘green’ AND C2.sid = C.sid AND P2.pid = C2.pid ) E5: RA (πsid,pidCatalog) / (πpidParts) SQL SELECT C.sid FROM Catalog C WHERE NOT EXISTS (SELECT P.pid FROM Parts P WHERE NOT EXISTS (SELECT C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid)) ----------------------------------------------------------------------------------------------------------------------------- E6: RA (πsid,pidCatalog) / (πpidσcolor=’red’Parts) SQL SELECT C.sid FROM Catalog C WHERE NOT EXISTS (SELECT P.pid FROM Parts P WHERE P.color = ‘red’ AND (NOT EXISTS ( SELECT C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid))) ----------------------------------------------------------------------------------------------------------------------------- E7: RA (πsid,pidCatalog)/(πpidσcolor=’red’∨color=’green’Parts) SQL SELECT C.sid FROM Catalog C WHERE NOT EXISTS (SELECT P.pid FROM Parts P WHERE (P.color = ‘red’ OR P.color = ‘green’) AND (NOT EXISTS (SELECT C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid))) E8: RA ρ(R1, ((πsid,pidCatalog)/(πpidσcolor=’red’Parts))) ρ(R2, ((πsid,pidCatalog)/(πpidσcolor=’green’Parts))) R1 ∪ R2 SQL SELECT C.sid FROM Catalog C WHERE (NOT EXISTS (SELECT P.pid FROM Parts P WHERE P.color = ‘red’ AND (NOT EXISTS (SELECT C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid)))) OR ( NOT EXISTS (SELECT P1.pid FROM Parts P1 WHERE P1.color = ‘green’ AND (NOT EXISTS (SELECT C2.sid FROM Catalog C2 WHERE C2.sid = C.sid AND C2.pid = P1.pid)))) ----------------------------------------------------------------------------------------------------------------------------- E9: RA ρ(R1,Catalog) ρ(R2,Catalog) πR1.sid,R2.sid(σR1.pid=R2.pid∧R1.sid≠R2.sid∧R1.cost>R2.cost(R1 × R2)) SQL SELECT C1.sid, C2.sid FROM Catalog C1, Catalog C2 WHERE C1.pid = C2.pid AND C1.sid = C2.sid AND C1.cost > C2.cost ----------------------------------------------------------------------------------------------------------------------------- E10: RA ρ(R1,Catalog) ρ(R2,Catalog) πR1.pidσR1.pid=R2.pid∧R1.sid≠R2.sid(R1 × R2) SQL SELECT C.pid FROM Catalog C WHERE EXISTS (SELECT C1.sid FROM Catalog C1 WHERE C1.pid = C.pid AND C1.sid = C.sid ) E11: RA ρ(R1, πsidσsname=’Y osemiteSham’Suppliers) ρ(R2,R1 Catalog) ρ(R3,R2) ρ(R4(1 → sid, 2 → pid, 3 → cost), σR3.cost
2009/8/23 12:02:30
piaoling






角  色:管理员
等  级:旅长
发 帖 数:672
经 验 值:2001
注册时间:2008/12/7

Create table employees

     (ssn   char(11),

      name char(30),

      lot   integer,

      primary key (ssn))

Create table policies

     (policyid integer,

cost    real,

ssn    char(11) not null,

      primary key (policyid ),

      foreign key (ssn) references employees,

          on delete cascade)

Create table dependents

     (pname char(20),

      ssn   char(11),

      age   integer,

     policyid integer not null,

      primary key (pname,policyid,ssn),

      foreign key (policyid,ssn) references policies,

          on delete cascade)

 

 

1πsnamesid((πpidσcolor = ’red’ Parts) Catalog) Suppliers)

2πsidpidcolor=’red’color=’green’Parts) catalog)

3ρ(R1, πsid((πpidσcolor=’red’Parts) Catalog))

ρ(R2, πsidσaddress=’221Packer Street’Suppliers)

R1 R2

4ρ(R1, πsid((πpidσcolor=’red’Parts) Catalog))

ρ(R2, πsid((πpidσcolor=’green’Parts) Catalog))

R1 ∩ R2

5sid,pidCatalog) / (πpidParts)

6sid,pidCatalog) / (πpidσcolor=’red’Parts)

7sid,pidCatalog)/(πpidσcolor=’red’color=’green’Parts)

8ρ(R1, ((πsid,pidCatalog)/(πpidσcolor=’red’Parts)))

ρ(R2, ((πsid,pidCatalog)/(πpidσcolor=’green’Parts)))

R1 R2

9ρ(R1,Catalog)

ρ(R2,Catalog)

πR1.sid,R2.sidR1.pid=R2.pidR1.sid≠R2.sidR1.cost>R2.cost (R1 × R2))

10ρ(R1,Catalog)

ρ(R2,Catalog)

πR1.pidσR1.pid=R2.pidR1.sid≠R2.sid (R1 × R2)

11ρ(R1, πsidσsname=’YosemiteSham’Suppliers)

ρ(R2,R1 Catalog)

ρ(R3,R2)

ρ(R4(1→ sid, 2→ pid, 3→ cost), σR3.cost<R2.cost (R3 × R2))

πpid(R2 − πsid,pid,costR4)

12(πsid,pidσcost<200 Catalog)/ πsid Suppliers)

 

 

 

 

 

 

 

 

2009/8/23 12:02:43
piaoling






角  色:管理员
等  级:旅长
发 帖 数:672
经 验 值:2001
注册时间:2008/12/7

1SELECT S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE P.color=’red’ AND C.pid=P.pid AND C.sid=S.sid

2SELECT C.sid

FROM Catalog C, Parts P

WHERE (P.color =’red’ OR P.color = ‘green’)

AND P.pid = C.pid

3SELECT S.sid

FROM Suppliers S

WHERE S.address = ‘221 Packer Street’

OR S.sid IN ( SELECT C.sid

FROM Parts P, Catalog C

WHERE P.color=’red’ AND P.pid = C.pid )

4SELECT C.sid

FROM Parts P, Catalog C

WHERE P.color = ‘red’ AND P.pid = C.pid

AND EXISTS ( SELECT P2.pid

FROM Parts P2, Catalog C2

WHERE P2.color = ‘green’ AND C2.sid = C.sid

AND P2.pid = C2.pid )

5SELECT C.sid

FROM Catalog C

WHERE NOT EXISTS (SELECT P.pid

FROM Parts P

WHERE NOT EXISTS (SELECT C1.sid

FROM Catalog C1

WHERE C1.sid = C.sid

AND C1.pid = P.pid))

6SELECT C.sid

FROM Catalog C

WHERE NOT EXISTS (SELECT P.pid

FROM Parts P

WHERE P.color = ‘red’

AND (NOT EXISTS ( SELECT C1.sid

FROM Catalog C1

WHERE C1.sid = C.sid

AND C1.pid = P.pid)))

7SELECT C.sid

FROM Catalog C

WHERE NOT EXISTS (SELECT P.pid

FROM Parts P

WHERE (P.color = ‘red’ OR P.color = ‘green’)

AND (NOT EXISTS (SELECT C1.sid

FROM Catalog C1

WHERE C1.sid = C.sid AND

C1.pid = P.pid)))

8SELECT C.sid

FROM Catalog C

WHERE (NOT EXISTS (SELECT P.pid

FROM Parts P

WHERE P.color = ‘red’ AND

(NOT EXISTS (SELECT C1.sid

FROM Catalog C1

WHERE C1.sid = C.sid AND

C1.pid = P.pid))))

OR ( NOT EXISTS (SELECT P1.pid

FROM Parts P1

WHERE P1.color = ‘green’ AND

(NOT EXISTS (SELECT C2.sid

FROM Catalog C2

WHERE C2.sid = C.sid AND

C2.pid = P1.pid))))

9SELECT C1.sid, C2.sid

FROM Catalog C1, Catalog C2

WHERE C1.pid = C2.pid AND C1.sid ≠ C2.sid

AND C1.cost > C2.cost

10SELECT C.pid

FROM Catalog C

WHERE EXISTS (SELECT C1.sid

FROM Catalog C1

WHERE C1.pid = C.pid AND C1.sid ≠ C.sid )

11SELECT C.pid

FROM Catalog C, Suppliers S

WHERE S.sname = ‘Yosemite Sham’ AND C.sid = S.sid

AND C.cost >= ALL (SELECT C2.cost

FROM Catalog C2, Suppliers S2

WHERE S2.sname = ‘Yosemite Sham’

AND C2.sid = S2.sid)

12SELECT C.pid

FROM Catalog C

WHERE cost<200 AND

NOT EXISTS (SELECT S.pid

FROM Suppliers S

WHERE NOT EXISTS

(SELECT C1.sid

FROM Catalog C1

WHERE C1.pid=C.pid AND C1.sid=C.sid))

2009/8/23 12:03:09
piaoling






角  色:管理员
等  级:旅长
发 帖 数:672
经 验 值:2001
注册时间:2008/12/7

1. DBMS中的数据描述成三级抽象模式:概念模式、物理模式、外模式。

2.数据类型:数据模型是隐藏了许多低级存储细节的高级数据描述结构的集合。

3.关系模式的必要条件

关系模式对表的每个列进行描述。

关系模式给出①关系名(即表名) ②每个字段(称之为列或属性)的名称,③每个字段的域(即域约束)。

4DDL(数据定义语言),用于定义外模式和概念模式。

DDL包括定义库与表、修改表结构、删除库、删除表等操作。

DML 对表中的数据进行操作,insertdeleteupdateselect

5.码约束                     

码约束是指具有一个特定的最小字段集合,通过它可以惟一确定每条记录。能够惟一确定每条记录的关系的字段集合称为关系的候选码,简称为码。

6.主码:DBMS为主码建立索引,主码是最小、最快的索引。

7.联系集(P22):联系是两个与多个实体之间的一种关联。

8ER图中的码约束用来构成‘一对一’的联系集。(p24

如果一个实体集E在联系R中有一个码约束,则每个E实例的实体最多只出现在R的一个联系中。为了指出联系集R的实体集E上的约束码,我们画一个从ER的箭头。(p25

9. 关系模式的度:是指字段的数目。p45

10DDL                           

DDL语句用语定义和管理数据库中的对象,包括定义库与表(视图)、修改表结构(视图)、删除库、删除表等操作。如CreateAlterDrop

CREATE TABLE, CREATE VIEW,CREATE TRIGGER, CREATE PROCEDURE, CREATE SEQUENCE,  ALTER TABLE, ALTER VIEW, ALTER PROCEDURE.

11.完整性约束包括                   

① 码约束 ②外码约束 ③取值范围约束 ④一般约束:单表约束、断言 ⑤域约束p47p123

12VIEW                            

视图只存定义,不存数据;

并非所有的视图都可更新。对于定义在一个基本表之上,只具有选择和投影操作,而没有聚集操作的视图,SQL-92标准允许对其进行更新。p6465

13.修改表、修改视图语句                   

修改表:

① 增加字段:ALTER TABLE students ADD COLUMN maiden_name char(10)

② 删除字段:ALTER TABLE students DROP COLUMN maiden_name

修改视图:例,先创建再修改

CREATE VIEW All_authors (au_fname, au_lname, address, city, zip)
AS 
SELECT au_fname, au_lname, address, city, zip
FROM authors
go
ALTER VIEW All_authors (au_fname, au_lname, address, city, zip)
AS 
SELECT au_fname, au_lname, address, city, zip
FROM authors
WHERE state = 'UT'
Go
 
14.关系代数
关系代数是与关系模型相关的查询语言,过程化的
关系演算是非过程化的。
15.关系代数的基本运算符
                                选择行

投影列

R-S={t|t∈R∧t不属于S}
 并,记录的并

× 叉积

16.连接的类型

内连接:条件连接、相等连接、自然连接

条件连接:

相等连接

自然连接

17.外连接                        

①左外连接left outer join: 结果集将包含左边表中的所有行。

select sailors.sid, reserve.bid from sailors left outer join reserve on sailors.sid = reserve.sid  结果中包括所有的水手,而不管水手是否订过船。,

② 右外连接 Right Outer Join结果集将包含右边表中的所有行

③ 全外连接 FULL OUTER JOIN结果集中包括两个表中的所有行。

18‘=’与‘like

‘left’=‘left ’结果为真,使用=进行字符串比较时,系统自动将字符串前后的所有空格删除后比较

leftlike‘left ’结果为假,当使用 LIKE 进行字符串比较时,字符串中的所有字符都有意义,包括起始或尾随空格,系统会按位比较。

19UNKNOW TRUEFALSE

SQL提供了一个特殊的列值,称为空值(null)。当列值是未知的或者是不适用的时候,就使用null.

逻辑运算符

逻辑表达式

结果

and

.T. and U

U

.F. and U

.F.

or

.T. or U

.T.

.F. or U

U

not

not U

U

20.触发器                         

触发器的描述包括3部分

事件:激活触发器的数据库的改变

条件:当触发器被激活时运行的查询或检测

动作:当触发器被激活且条件为真时,DBMS要执行的过程。

返回页首↑


津ICP备09000164号

联系我们 - piaoling Corporation - 论坛存档 - 返回顶端
Powered by BBSXP 2007 ACCESS © 1998-2025
Server Time 2025/1/13 17:30:08
Processed in 0.03 second(s)
飘凌大杂烩