数据库作业及答案

作业及参考答案1.1关系模型:1.试述数据库系统的组成和DBA的职责.答：组成:数据库、数据库管理系统、应用系统、数据库管理员、用户。DBA负责全面管理和控制数据库系统，主要职责：设计和定义数据库系统帮助用户使用数据库系统监督与控制数据库系统的使用和运行改进和重组数据库，调优数据库性能转储与恢复数据库重构数据库2.a)找出下面表的三个候选键.ABCDa1b1c1D1a2b3c1D2a3b4c2D2a4b2c2D1答：三个候选键：A,B,(C,D)b)给出一个四行四列的表,要求只有一个由前三列组成的候选键.答：ABCDa1b1c1D1a2b1c1D1a1b2c1D1a1b1c2D13.根据您的认识设计一个人事管理数据库,要求说明设计的背景及理由.答：要点：首先划定该数据库的应用范围，要解决哪些问题（不一定包罗人事事务的方方面面）；在问题范围内确定数据项；研究数据项间关系形成一个由相互关联的若干张表组成的数据库。注：列出各个表的数据项，表与表的参照关系。几个独立的表并不是关系数据库的主要目的，不要有冗余。1.2关系代数:1.如果关系R和S没有共同的列属性,根据定义说明表R×S等于表R∞S。答：广义笛卡尔积(ExtendedCartesianProduct):两个分别为n目和m目的关系R和S的广义笛卡尔积是一个(n+m)列的元组的集合。元组的前n列是关系R的一个元组，后m列是关系S的一个元组。若R有k1个元组，S有k2个元组，则关系R和关系S的广义笛卡尔积有k1×k2个元组。记作：R×S={trts|tr∈R∧ts∈S}连接：连接也称为θ连接。它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。记作：R∞S={trts|tr∈R∧ts∈S∧tr[A]=ts[B]}AθB其中A和B分别为R和S上度数相等且可比的属性组。θ是比较运算符。连接运算的结果是从R和S的广义笛卡尔积R×S中选取R关系在A属性组上的值与S关系在B属性组上值满足比较关系θ的元组。连接的定义中如果R和S中没有相同的列A、B，则在确定元组t在不在表R∞S中时，应忽略对列A和列B的引用，那么连接的定义就退化为广义笛卡尔积R×S，于是有R×S=R∞S。2.对CAP数据库，用关系代数完成下列查询。1）找出顾客、代理商和商品都在同一个城市的三元组（cid,aid,pid）。答：πcid,aid,pid(σC.city=A.cityandA.city=P.city((C×A)×P))或πcid,aid,pid(σC.city=A.cityandA.city=P.city(C×A×P))2）找出顾客、代理商和商品两两不在同一个城市的三元组（cid,aid,pid）。答：πcid,aid,pid(σC.cityA.cityandA.cityP.cityandC.cityP.city(C×A×P))注意:XYandYZ并不意味着XZ.3）列出所有在同一个城市代理商的aid对。答：A1:=A,A2:=AπA1.aid,A2.aid(σA1.city=A2.cityandA1.aidA2.aid(A1×A2))4）找出折扣率最大和最小的顾客cid。答：最大:C1:=C,C2:=CX(cid1,cid):=πC1.cid,C2.cid(σC1.discnt=C2.discnt(C1×C2))X÷πC.cid(C)最小:C1:=C,C2:=CX(cid1,cid):=πC1.cid,C2.cid(σC1.discnt=C2.discnt(C1×C2))X÷πC.cid(C)5）取出销售过所有曾被顾客c002订购过的商品的代理商的名字。答：πaname((πaid,pid(O)÷πpid(σcid=’c002’(O)))∞A)6）找出只从一家代理商处订购过商品的顾客cid。答：OY:=Oπcid(O)—πO.cid(σO.aidOY.aidandO.cid=OY.cid(O×OY))1.3SQL:1.检索佣金百分率大于最小百分率的代理商aid.答:selectaidfromagentswherepercentany(selectpercentfromagents);2.求通过住在北京或上海的代理商订货的顾客cid.(使用子查询和不使用子查询两种)答:selectdistinctcidfromorderswhereaidin(selectaidfromagentswherecity=“北京”orcity=”上海”);selectdistinctcidfromorders,agentswhereorders.aid=agents.aidand(agents.city=“北京”oragents.city=”上海”);3.R、S和T具有相同的属性列A1……An,不用高级SQL来实现关系表达式(RUNIONS)MINUST.答:(RUNIONS)MINUST.=(RMINUST)UNION(SMINUST)(select*fromRwherenotexists(select*fromTwhereR.A1=T.A1and…andR.An=T.An))union(select*fromSwherenotexists(select*fromTwhereS.A1=T.A1and…andS.An=T.An));4.求出没有为任何住在北京的顾客订购任何在天津生产的产品的代理商的aid.答:selecta.aidfromagentsawherenotexists(selecto.*fromorerso,customersc,productspwhereo.aid=a.aidando.cid=c.cidando.pid=p.pidandc.city=’北京’andp.city=’天津’);5.没有一个包含GROUPBY子句的查询会返回重复行,这一命题为真吗?如果为真,解释原因;否则,给出反例.答:不真.例:selectcount(*)fromtablegroupbykey;返回表只有一列,有与原表一样多的行,每行值都是1.6.说出你所用到DBMS和其中的SQL语句,他们与讲课时的不一样或者不能用.1.4对象关系数据库:1.对象关系数据库特点.对象-关系数据库系统除了具有原来关系数据库的特点外,还应该有以下特点:(1)扩充数据类型(2)支持复杂对象(3)支持继承的概念(4)提供通用的规则系统2.行对象与列对象有何不同?行对象，一个表可以为包含用户定义类型的多个行对象，即可以违犯第一范式，ORACLE为每个行对象提供一个唯一标识；而一个表的一列（列对象）可定义包含一个用户定义类型的值，而且ORACLE不能为每一列提供一个唯一标识。3.Value(),Ref(),Table()的作用?Value()取属性值Ref()引用对象值Table()将对象转换成表4.student_t类型中有namesex_t类型以及学号id、e-mail和入学年份（整数），其中namesex_t不空。定义student_t类型和表student,学号id。createtypestudent_tasobject(idchar(8),emailchar(20),yearint,enamenamesex_tnotnull);createtablestudentofstudent_t(primarykey(id));5.对对象关系数据库CAP，查找姓李的代理商aid和ananme，这些代理商没有销售过北京的商品。selectA.aid,A.anamefromagentAwhereA.anamelike李%andnotexists(select*fromorderO,agentA,productPwhereO.pid=P.pidandO.aid=A.aidandP.city=北京)6．检索在三个以上矩形中出现的点，显示点和覆盖这些点的矩形数。SelectP.x,P.ycount(*)ascntfrompointsP,rectsRwhereR.inside(Value(P))0groupbyP.x,P.yhavingcount(*)32.1数据存储:1.ORACLE中执行:createtablecustomers(cid…)storage(initial20480,next20480,maxextents8,minextents3,pctincrease0);该文件第一次创建时,会分配多少字节的磁盘空间?该表最大可以容纳多大的空间?答:第一次创建时会分配20K+20K×(3-1)=60K字节,该表最大可以容纳的空间为60K+20K×(8-3)=160K字节.2.职工表emp中有200000行,每行长度100字节.createtableemp(eidintnotnull…)pctfree25;设块中可使用2000个字节,估算emp中行所需的块的个数.createuniqueindexonemp(eid)pctfree20;ROWID占6个字节,eid占4个字节,每个键的列附加一个字节.估算B树索引中每一块的索引项个数,叶子层块数?假设目录层目录项(sepkeyval,np)与叶子层中的项相等,估算每一目录层的块数?eid的值从1到200000连续,每秒可进行80块磁盘操作,估算在没有聚簇和有聚簇情况执行下列查询所需时间?select*fromempwhereeidbetween10000and20000;答:①(200000*100)÷[2000×(1-25%)]=13334(块).故emp中行所需的块的个数为13334;②[2000×(1-20%)]÷(6+4+1)=145每一块的索引项个数为:145200000÷145=1380叶子层块数为:1380③K=CEIL(logn(M))=CELL(log145(200000))=3(层)故,包含叶子层共3层,其中目录有2层根目录:1块第二层目录:145块④没有聚簇情况下所需时间为:(访问索引根+访问第二层目录+访问叶子节点+访问数据行)÷80=(1+145+CELL(10000÷145)+10000)÷80=128(s)有聚簇情况下所需时间为:(访问索引根+访问第二层目录+访问叶子节点)÷80=(1+145+CELL(10000÷145))÷80=2.7(s)3.解释散列簇.答:散列簇(HashCluster)是簇的另一种形式,它同样把若干有共用列的表聚簇在一起,但是散列簇不建索引,簇中的数据要通过散列函数计算(通过HASH函数生成HASH值,由HASH值确定键值对应行的存储位置)得出物理位置,一步到位.它可以极大程度的提高等值查询的效率.2.2数据管理:1.利用非过程性约束和过程性约束完成完整性定义：保证表agents的行的城市值必须是表coutomers中的城市之一。答：非过程性约束是在建表时建立约束createtableAGENT(aidchar(4)notnull,citychar(4),…,primarykey(aid),constraintcityrefforeignkey(city)referencescustomers(city)…);过程性约束是建立触发器能引起一个动作的执行约束createtrggert_cbeforeinsertorupdateofcityonAGENTstatementdeclarev1string;v2string;v3integer;beginv2:=:new.city;v1:=:old.city;selectcount(*)into:v3fromcustomerwherecity=:v2;ifv3=0thendbms_output.put_line(‘city无效’);rollback;endifend;2.CAP数据库中的四张表对应各自的用户，彼此不能相互操作，请给出让彼此之间可以相互查看的措施。答：可以创建角色，该角色可以查看各用户的四张表。把角色授予用户。3.结合数据库恢复的内容谈谈如何保证数据库