版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、ExerciseFor relation Accounts, the attributes are:acctNo, type, balanceFor relation Customers, the attributes are:firstName, lastName, idNo, accountExerciseFor relation Accounts, the tuples are:(12345, savings, 12000),(23456, checking, 1000),(34567, savings, 25)For relation Customers, the tuples are
2、:(Robbie, Banks, 901-222, 12345),(Lena, Hand, 805-333, 12345),(Lena, Hand, 805-333, 23456)ExerciseFor relation Accounts and the first tuple, the components are:123456 acctNosavings type12000 balanceFor relation Customers and the first tuple, the components are:Robbie firstNameBanks lastName901-222 i
3、dNo12345 accountExerciseFor relation Accounts, a relation schema is:Accounts(acctNo, type, balance)For relation Customers, a relation schema is:Customers(firstName, lastName, idNo, account)ExerciseAn example database schema is:Accounts (acctNo,type,balance)Customers (firstName,lastName,idNo,account)
4、ExerciseA suitable domain for each attribute:acctNo Integertype Stringbalance IntegerfirstName StringlastName StringidNo String (because there is a hyphen we cannot use Integer)account IntegerExerciseAnother equivalent way to present the Account relation:acctNobalancetype3456725savings234561000check
5、ing1234512000savingsAnother equivalent way to present the Customers relation:idNofirstNamelastNameaccount805-333LenaHand23456805-333LenaHand12345901-222RobbieBanks12345Examples of attributes that are created for primarily serving as keys in a relation:Universal Product Code (UPC) used widely in Unit
6、ed States and Canada to track products in stores.Serial Numbers on a wide variety of products to allow the manufacturer to individually track each product.Vehicle Identification Numbers (VIN), a unique serial number used by the automotive industry to identify vehicles.We can order the three tuples i
7、n any of 3! = 6 ways. Also, the columns can be ordered in any of 3! = 6 ways. Thus, the number of presentations is 6*6 = 36.We can order the three tuples in any of 5! = 120 ways. Also, the columns can be ordered in any of 4! = 24 ways. Thus, the number of presentations is 120*24 = 2880We can order t
8、he three tuples in any of m! ways. Also, the columns can be ordered in any of n! ways. Thus, the number of presentations is n!m!CREATE TABLE Product (makerCHAR(30),modelCHAR(10) PRIMARY KEY,type CHAR(15);CREATE TABLE PC (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,price DECIMAL(7,2);CRE
9、ATE TABLE Laptop (model CHAR(30),speed DECIMAL(4,2),ram INTEGER,hd INTEGER,screen DECIMAL(3,1),price DECIMAL(7,2);CREATE TABLE Printer (model CHAR(30),color BOOLEAN,type CHAR (10),price DECIMAL(7,2);ALTER TABLE Printer DROP color;ALTER TABLE Laptop ADD od CHAR (10) DEFAULT none;CREATE TABLE Classes
10、(class CHAR(20),type CHAR(5),country CHAR(20),numGuns INTEGER,bore DECIMAL(3,1),displacement INTEGER);CREATE TABLE Ships (name CHAR(30),class CHAR(20),launched INTEGER);CREATE TABLE Battles (name CHAR(30),date DATE);CREATE TABLE Outcomes (ship CHAR(30),battle CHAR(30),result CHAR(10);ALTER TABLE Cla
11、sses DROP bore;ALTER TABLE Ships ADD yard CHAR(30);R1 := speed 3.00 (PC)R2 := model(R1)model100510061013R1 := hd 100 (Laptop)R2 := Product (R1)R3 := maker (R2)makerEABFGR1 := maker=B (Product PC)R2 := maker=B (Product Laptop)R3 := maker=B (Product Printer)R4 := model,price (R1)R5 := model,price (R2)
12、R6: = model,price (R3)R7 := R4 R5 R6modelprice100464910056301006104920071429R1 := color = true AND type = laser (Printer)R2 := model (R1)model30033007R1 := type=laptop (Product)R2 := type=PC(Product)R3 := maker(R1)R4 := maker(R2)R5 := R3 R4makerFGR1 := PC1(PC)R2 := PC2(PC)R3 := R1 (PC1.hd = PC2.hd A
13、ND PC1.model PC2.model) R2R4 := hd(R3)hd25080160R1 := PC1(PC)R2 := PC2(PC)R3 := R1 (PC1.speed = PC2.speed AND PC1.ram = PC2.ram AND PC1.model PC2.model) R2R4 := PC1.model,PC2.model(R3)PC1.modelPC2.model10041012R1 := model(speed 2.80(PC) model(speed 2.80(Laptop)R2 := maker,model(R1 Product)R3 := R3(m
14、aker2,model2)(R2)R4 := R2 (maker = maker2 AND model model2) R3R5 := maker(R4)makerBER1 := model,speed(PC)R2 := model,speed(Laptop)R3 := R1 R2R4 := R4(model2,speed2)(R3)R5 := model,speed (R3 (speed speed2 ) R4)R6 := R3 R5R7 := maker(R6 Product)makerBR1 := maker,speed(Product PC)R2 := R2(maker2,speed2
15、)(R1)R3 := R3(maker3,speed3)(R1)R4 := R1 (maker = maker2 AND speed speed2) R2R5 := R4 (maker3 = maker AND speed3 speed2 AND speed3 speed) R3R6 := maker(R5)makerADER1 := maker,model(Product PC)R2 := R2(maker2,model2)(R1)R3 := R3(maker3,model3)(R1)R4 := R4(maker4,model4)(R1)R5 := R1 (maker = maker2 AN
16、D model model2) R2R6 := R3 (maker3 = maker AND model3 model2 AND model3 model) R5R7 := R4 (maker4 = maker AND (model4=model OR model4=model2 OR model4=model3) R6R8 := maker(R7)makerABDER1 := bore 16 (Classes)R2 := class,country (R1)classcountryIowaUSANorth CarolinaUSAYamatoJapanR1 := launched 1921 A
17、ND displacement 35000 (R1)R3 := name (R2)nameIowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamatoR1 := battle=Guadalcanal(Outcomes)R2 := Ships (ship=name) R1R3 := Classes R2R4 := name,displacement,numGuns(R3)namedisplacementnumGunsKirishima320008Washington370009R1 := name(Ships)R2 :=
18、 ship(Outcomes)R3 := R3(name)(R2)R4 := R1 R3nameCaliforniaHarunaHieiIowaKirishimaKongoMissouriMusashiNew JerseyNorth CarolinaRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal SovereignTennesseeWashingtonWisconsinYamatoArizonaBismarckDuke of YorkFusoHoodKing George VPrince of WalesRodneyScharnhor
19、stSouth DakotaWest VirginiaYamashiroFrom 2.3.2, assuming that every class has one ship named after the class.R1 := class(Classes)R2 := class(name class(Ships)R3 := R1 R2classBismarckR1 := country(type=bb(Classes)R2 := country(type=bc(Classes)R3 := R1 R2countryJapanGt. BritainR1 := ship,result,date(B
20、attles (battle=name) Outcomes)R2 := R2(ship2,result2,date2)(R1)R3 := R1 (ship=ship2 AND result=damaged AND date date2) R2R4 := ship(R3)No results from sample data.The result of the natural join has only one attribute from each pair of equated attributes. On the other hand, the result of the theta-jo
21、in has both columns of the attributes and their values are identical.UnionIf we add a tuple to the arguments of the union operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple is a duplicate tuple, then the set behavior will eliminate that tuple
22、. Thus the union operator is monotone.IntersectionIf we add a tuple to the arguments of the intersection operator, we will get all of the tuples of the original result and maybe the added tuple. If the added tuple does not exist in the relation that it is added but does exist in the other relation,
23、then the result set will include the added tuple. Thus the intersection operator is monotone.DifferenceIf we add a tuple to the arguments of the difference operator, we may not get all of the tuples of the original result. Suppose we have relations R and S and we are computing R S. Suppose also that
24、 tuple t is in R but not in S. The result of R S would include tuple t. However, if we add tuple t to S, then the new result will not have tuple t. Thus the difference operator is not monotone.ProjectionIf we add a tuple to the arguments of the projection operator, we will get all of the tuples of t
25、he original result and the projection of the added tuple. The projection operator only selects columns from the relation and does not affect the rows that are selected. Thus the projection operator is monotone.SelectionIf we add a tuple to the arguments of the selection operator, we will get all of
26、the tuples of the original result and maybe the added tuple. If the added tuple satisfies the select condition, then it will be added to the new result. The original tuples are included in the new result because they still satisfy the select condition. Thus the selection operator is monotone.Cartesi
27、an ProductIf we add a tuple to the arguments of the Cartesian product operator, we will get all of the tuples of the original result and possibly additional tuples. The Cartesian product pairs the tuples of one relation with the tuples of another relation. Suppose that we are calculating R x S where
28、 R has m tuples and S has n tuples. If we add a tuple to R that is not already in R, then we expect the result of R x S to have (m + 1) * n tuples. Thus the Cartesian product operator is monotone.Natural JoinsIf we add a tuple to the arguments of a natural join operator, we will get all of the tuple
29、s of the original result and possibly additional tuples. The new tuple can only create additional successful joins, not less. If, however, the added tuple cannot successfully join with any of the existing tuples, then we will have zero additional successful joins. Thus the natural join operator is m
30、onotone.Theta JoinsIf we add a tuple to the arguments of a theta join operator, we will get all of the tuples of the original result and possibly additional tuples. The theta join can be modeled by a Cartesian product followed by a selection on some condition. The new tuple can only create additiona
31、l tuples in the result, not less. If, however, the added tuple does not satisfy the select condition, then no additional tuples will be added to the result. Thus the theta join operator is monotone.RenamingIf we add a tuple to the arguments of a renaming operator, we will get all of the tuples of th
32、e original result and the added tuple. The renaming operator does not have any effect on whether a tuple is selected or not. In fact, the renaming operator will always return as many tuples as its argument. Thus the renaming operator is monotone.If all the tuples of R and S are different, then the u
33、nion has n + m tuples, and this number is the maximum possible.The minimum number of tuples that can appear in the result occurs if every tuple of one relation also appears in the other. Then the union has max(m , n) tuples. If all the tuples in one relation can pair successfully with all the tuples
34、 in the other relation, then the natural join has n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if none of the tuples of one relation can pair successfully with all the tuples in the other relation. Then the natural join has
35、 zero tuples.If the condition C brings back all the tuples of R, then the cross product will contain n * m tuples. This number would be the maximum possible.The minimum number of tuples that can appear in the result occurs if the condition C brings back none of the tuples of R. Then the cross produc
36、t has zero tuples.Assuming that the list of attributes L makes the resulting relation L(R) and relation S schema compatible, then the maximum possible tuples is n. This happens when all of the tuples of L(R) are not in S.The minimum number of tuples that can appear in the result occurs when all of t
37、he tuples in L(R) appear in S. Then the difference has max(n m , 0) tuples. Defining r as the schema of R and s as the schema of S:1. r(R S)2. R (rs(S)where is the duplicate-elimination operator in Section 5.2 pg. 2133. R (R r(R S)Defining r as the schema of R1. R - r(R S)A1,A2An(R S)speed 500(PC) =
38、 Model 1011 violates this constraint.screen 15.4 AND hd PC.ram AND Laptop.price PC.price(PC Laptop) = Models 2002,2006,2008 violate the constraint.class(bore 16(Classes) = The Yamato class violates the constraint.class(numGuns 9 AND bore 14(Classes) = No violations to the constraint.This complex exp
39、ression is best seen as a sequence of steps in which we define temporary relations R1 through R5 that stand for nodes of expression trees. Here is the sequence: R1(class,name) := class,name(Classes Ships) R2(class2,name2) := R2(class2,name2)(R1) R3(class3,name3) := R3(class3,name3)(R1) R4(class,name,class2,name2) := R1 (class = class2 AND name name2) R2 R5(class,name,class2,na
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 《妈妈的爱》教案设计
- 人教版五年级数学上册【全册教案】
- 河流清淤疏浚服务合同
- 人教版二年级语文下册教学计划教案
- 墙面施工合同:美术馆内部装修
- 网络综艺策划副导演聘用协议
- 宾馆水暖设施更新工程协议
- 工业用水施工合同
- 建筑楼内硅芯管道安装合同
- 残障人士保姆合同模板
- 九年级英语《Unit 13 Were trying to save the earth!》教学设计
- 交通警察培训课件
- ISTA-2A-包装运输测试报告-审核通过
- 魁北克腰痛障碍评分表(Quebec-Baclain-Disability-Scale-QBPDS)
- 政府采购网上商城服务规范第1部分:交易流程
- 3D打印技术与应用
- 餐饮原料的采购与采购课件
- 卤味官方直播话术
- 第八章采购管理发展新趋势
- 高压气瓶的安全知识
- 林业林权流转与经营主体培育
评论
0/150
提交评论