数据库系统基础教程第二章答案.doc
- 文档编号:1295591
- 上传时间:2023-04-30
- 格式:DOC
- 页数:19
- 大小:1.20MB
数据库系统基础教程第二章答案.doc
《数据库系统基础教程第二章答案.doc》由会员分享,可在线阅读,更多相关《数据库系统基础教程第二章答案.doc(19页珍藏版)》请在冰点文库上搜索。
Exercise2.2.1a
ForrelationAccounts,theattributesare:
acctNo,type,balance
ForrelationCustomers,theattributesare:
firstName,lastName,idNo,account
Exercise2.2.1b
ForrelationAccounts,thetuplesare:
(12345,savings,12000),
(23456,checking,1000),
(34567,savings,25)
ForrelationCustomers,thetuplesare:
(Robbie,Banks,901-222,12345),
(Lena,Hand,805-333,12345),
(Lena,Hand,805-333,23456)
Exercise2.2.1c
ForrelationAccountsandthefirsttuple,thecomponentsare:
123456àacctNo
savingsàtype
12000àbalance
ForrelationCustomersandthefirsttuple,thecomponentsare:
RobbieàfirstName
BanksàlastName
901-222àidNo
12345àaccount
Exercise2.2.1d
ForrelationAccounts,arelationschemais:
Accounts(acctNo,type,balance)
ForrelationCustomers,arelationschemais:
Customers(firstName,lastName,idNo,account)
Exercise2.2.1e
Anexampledatabaseschemais:
Accounts(
acctNo,
type,
balance
)
Customers(
firstName,
lastName,
idNo,
account
)
Exercise2.2.1f
Asuitabledomainforeachattribute:
acctNoàInteger
typeàString
balanceàInteger
firstNameàString
lastNameàString
idNoàString(becausethereisahyphenwecannotuseInteger)
accountàInteger
Exercise2.2.1g
AnotherequivalentwaytopresenttheAccountrelation:
acctNo
balance
type
34567
25
savings
23456
1000
checking
12345
12000
savings
AnotherequivalentwaytopresenttheCustomersrelation:
idNo
firstName
lastName
account
805-333
Lena
Hand
23456
805-333
Lena
Hand
12345
901-222
Robbie
Banks
12345
Exercise2.2.2
Examplesofattributesthatarecreatedforprimarilyservingaskeysinarelation:
UniversalProductCode(UPC)usedwidelyinUnitedStatesandCanadatotrackproductsinstores.
SerialNumbersonawidevarietyofproductstoallowthemanufacturertoindividuallytrackeachproduct.
VehicleIdentificationNumbers(VIN),auniqueserialnumberusedbytheautomotiveindustrytoidentifyvehicles.
Exercise2.2.3a
Wecanorderthethreetuplesinanyof3!
=6ways.Also,thecolumnscanbeorderedinanyof3!
=6ways.Thus,thenumberofpresentationsis6*6=36.
Exercise2.2.3b
Wecanorderthethreetuplesinanyof5!
=120ways.Also,thecolumnscanbeorderedinanyof4!
=24ways.Thus,thenumberofpresentationsis120*24=2880
Exercise2.2.3c
Wecanorderthethreetuplesinanyofm!
ways.Also,thecolumnscanbeorderedinanyofn!
ways.Thus,thenumberofpresentationsisn!
m!
Exercise2.3.1a
CREATETABLEProduct(
maker CHAR(30),
model CHAR(10)PRIMARYKEY,
typeCHAR(15)
);
Exercise2.3.1b
CREATETABLEPC(
modelCHAR(30),
speedDECIMAL(4,2),
ramINTEGER,
hdINTEGER,
priceDECIMAL(7,2)
);
Exercise2.3.1c
CREATETABLELaptop(
modelCHAR(30),
speedDECIMAL(4,2),
ramINTEGER,
hdINTEGER,
screenDECIMAL(3,1),
priceDECIMAL(7,2)
);
Exercise2.3.1d
CREATETABLEPrinter(
modelCHAR(30),
colorBOOLEAN,
typeCHAR(10),
priceDECIMAL(7,2)
);
Exercise2.3.1e
ALTERTABLEPrinterDROPcolor;
Exercise2.3.1f
ALTERTABLELaptopADDodCHAR(10)DEFAULT‘none’;
Exercise2.3.2a
CREATETABLEClasses(
classCHAR(20),
typeCHAR(5),
countryCHAR(20),
numGunsINTEGER,
boreDECIMAL(3,1),
displacementINTEGER
);
Exercise2.3.2b
CREATETABLEShips(
nameCHAR(30),
classCHAR(20),
launchedINTEGER
);
Exercise2.3.2c
CREATETABLEBattles(
nameCHAR(30),
dateDATE
);
Exercise2.3.2d
CREATETABLEOutcomes(
shipCHAR(30),
battleCHAR(30),
resultCHAR(10)
);
Exercise2.3.2e
ALTERTABLEClassesDROPbore;
Exercise2.3.2f
ALTERTABLEShipsADDyardCHAR(30);
Exercise2.4.1a
R1:
=σspeed≥3.00(PC)
R2:
=πmodel(R1)
model
1005
1006
1013
Exercise2.4.1b
R1:
=σhd≥100(Laptop)
R2:
=Product(R1)
R3:
=πmaker(R2)
maker
E
A
B
F
G
Exercise2.4.1c
R1:
=σmaker=B(ProductPC)
R2:
=σmaker=B(ProductLaptop)
R3:
=σmaker=B(ProductPrinter)
R4:
=πmodel,price(R1)
R5:
=πmodel,price(R2)
R6:
=πmodel,price(R3)
R7:
=R4R5R6
model
price
1004
649
1005
630
1006
1049
2007
1429
Exercise2.4.1d
R1:
=σcolor=trueANDtype=laser(Printer)
R2:
=πmodel(R1)
model
3003
3007
Exercise2.4.1e
R1:
=σtype=laptop(Product)
R2:
=σtype=PC(Product)
R3:
=πmaker(R1)
R4:
=πmaker(R2)
R5:
=R3–R4
maker
F
G
Exercise2.4.1f
R1:
=ρPC1(PC)
R2:
=ρPC2(PC)
R3:
=R1(PC1.hd=PC2.hdANDPC1.model<>PC2.model)R2
R4:
=πhd(R3)
hd
250
80
160
Exercise2.4.1g
R1:
=ρPC1(PC)
R2:
=ρPC2(PC)
R3:
=R1(PC1.speed=PC2.speedANDPC1.ram=PC2.ramANDPC1.model R4: =πPC1.model,PC2.model(R3) PC1.model PC2.model 1004 1012 Exercise2.4.1h R1: =πmodel(σspeed≥2.80(PC))πmodel(σspeed≥2.80(Laptop)) R2: =πmaker,model(R1Product) R3: =ρR3(maker2,model2)(R2) R4: =R2(maker=maker2ANDmodel<>model2)R3 R5: =πmaker(R4) maker B E Exercise2.4.1i R1: =πmodel,speed(PC) R2: =πmodel,speed(Laptop) R3: =R1R2 R4: =ρR4(model2,speed2)(R3) R5: =πmodel,speed(R3(speed R6: =R3–R5 R7: =πmaker(R6Product) maker B Exercise2.4.1j R1: =πmaker,speed(ProductPC) R2: =ρR2(maker2,speed2)(R1) R3: =ρR3(maker3,speed3)(R1) R4: =R1(maker=maker2ANDspeed<>speed2)R2 R5: =R4(maker3=makerANDspeed3<>speed2ANDspeed3<>speed)R3 R6: =πmaker(R5) maker A D E Exercise2.4.1k R1: =πmaker,model(ProductPC) R2: =ρR2(maker2,model2)(R1) R3: =ρR3(maker3,model3)(R1) R4: =ρR4(maker4,model4)(R1) R5: =R1(maker=maker2ANDmodel<>model2)R2 R6: =R3(maker3=makerANDmodel3<>model2ANDmodel3<>model)R5 R7: =R4(maker4=makerAND(model4=modelORmodel4=model2ORmodel4=model3))R6 R8: =πmaker(R7) maker A B D E Exercise2.4.2a Exercise2.4.2b Exercise2.4.2c Exercise2.4.2d Exercise2.4.2e Exercise2.4.2f Exercise2.4.2g Exercise2.4.2h Exercise2.4.2i Exercise2.4.2j Exercise2.4.2k Exercise2.4.3a R1: =σbore≥16(Classes) R2: =πclass,country(R1) class country Iowa USA NorthCarolina USA Yamato Japan Exercise2.4.3b R1: =σlaunched<1921(Ships) R2: =πname(R1) name Haruna Hiei Kirishima Kongo Ramillies Renown Repulse Resolution Revenge RoyalOak RoyalSovereign Tennessee Exercise2.4.3c R1: =σbattle=DenmarkStraitANDresult=sunk(Outcomes) R2: =πship(R1) ship Bismarck Hood Exercise2.4.3d R1: =ClassesShips R2: =σlaunched>1921ANDdisplacement>35000(R1) R3: =πname(R2) name Iowa Missouri Musashi NewJersey NorthCarolina Washington Wisconsin Yamato Exercise2.4.3e R1: =σbattle=Guadalcanal(Outcomes) R2: =Ships(ship=name)R1 R3: =ClassesR2 R4: =πname,displacement,numGuns(R3) name displacement numGuns Kirishima 32000 8 Washington 37000 9 Exercise2.4.3f R1: =πname(Ships) R2: =πship(Outcomes) R3: =ρR3(name)(R2) R4: =R1R3 name California Haruna Hiei Iowa Kirishima Kongo Missouri Musashi NewJersey NorthCarolina Ramillies Renown Repulse Resolution Revenge RoyalOak RoyalSovereign Tennessee Washington Wisconsin Yamato Arizona Bismarck DukeofYork Fuso Hood KingGeorgeV PrinceofWales Rodney Scharnhorst SouthDakota WestVirginia Yamashiro Exercise2.4.3g From2.3.2,assumingthateveryclasshasoneshipnamedaftertheclass. R1: =πclass(Classes) R2: =πclass(σname<>class(Ships)) R3: =R1–R2 class Bismarck Exercise2.4.3h R1: =πcountry(σtype=bb(Classes)) R2: =πcountry(σtype=bc(Classes)) R3: =R1∩R2 country Japan Gt.Britain Exercise2.4.3i R1: =πship,result,date(Battles(battle=name)Outcomes) R2: =ρR2(ship2,result2,date2)(R1) R3: =R1(ship=ship2ANDresult=damagedANDdate R4: =πship(R3) Noresultsfromsampledata. Exercise2.4.4a Exercise2.4.4b Exercise2.4.4c Exercise2.4.4d Exercise2.4.4e Exercise2.4.4f Exercise2.4.4g Exercise2.4.4h Exercise2.4.4i Exercise2.4.5 Theresultofthenaturaljoinhasonlyoneattributefromeachpairofequatedattributes.Ontheotherhand,theresultofthetheta-joinhasbothcolumnsoftheattributesandtheirvaluesareidentical. Exercise2.4.6 Union Ifweaddatupletotheargumentsoftheunionoperator,wewillgetallofthetuplesoftheoriginalresultandmaybetheaddedtuple.Iftheaddedtupleisaduplicatetuple,thenthesetbehaviorwilleliminatethattuple.Thustheunionoperatorismonotone. Intersection Ifweaddatupletotheargumentsoftheintersectionoperator,wewillgetallofthetuplesoftheoriginalresultandmaybetheaddedtuple.Iftheaddedtupledoesnotexistintherelationthatitisaddedbutdoesexistintheotherrelation,thentheresultsetwillincludetheaddedtuple.Thustheintersectionoperatorismonotone. Difference Ifweaddatupletotheargumentsofthedifferenceoperator,wemaynotgetallofthetuplesoftheoriginalresult.SupposewehaverelationsRandSandwearecomputingR–S.SupposealsothattupletisinRbutnotinS.TheresultofR–Swouldincludetuplet.However,ifweaddtuplettoS,thenthenewresultwillnothavetuplet.Thusthedifferenceoperatorisnotmonotone. Projection Ifweaddatupletotheargumentsoftheprojectionoperator,wewillgetallofthetuples
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础教程 第二 答案