数据库系统基础教程第二章答案解析.docx
- 文档编号:25119085
- 上传时间:2023-06-05
- 格式:DOCX
- 页数:32
- 大小:141.97KB
数据库系统基础教程第二章答案解析.docx
《数据库系统基础教程第二章答案解析.docx》由会员分享,可在线阅读,更多相关《数据库系统基础教程第二章答案解析.docx(32页珍藏版)》请在冰豆网上搜索。
数据库系统基础教程第二章答案解析
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:
123456acctNo
savingstype
12000balance
ForrelationCustomersandthefirsttuple,thecomponentsare:
RobbiefirstName
BankslastName
901-222idNo
12345account
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:
acctNoInteger
typeString
balanceInteger
firstNameString
lastNameString
idNoString(becausethereisahyphenwecannotuseInteger)
accountInteger
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(
makerCHAR(30),
modelCHAR(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(Product
PC)
R2:
=σmaker=B(Product
Laptop)
R3:
=σmaker=B(Product
Printer)
R4:
=πmodel,price(R1)
R5:
=πmodel,price(R2)
R6:
=πmodel,price(R3)
R7:
=R4
R5
R6
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(R1 Product) 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: =R1 R2 R4: =ρR4(model2,speed2)(R3) R5: =πmodel,speed(R3 (speed R6: =R3–R5 R7: =πmaker(R6 Product) maker B Exercise2.4.1j R1: =πmaker,speed(Product PC) 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(Product PC) 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: =Classes Ships 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: =Classes R2 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: =R1 R3 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,wewillgetallofthetuplesoftheoriginalresultandtheprojectionoftheaddedtuple.Theprojectionoperatoronlyselectscolumnsfromtherelationanddoesnotaffecttherowsthatareselected.Thustheprojectionoperatorismonotone. Selection
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础教程 第二 答案 解析
![提示](https://static.bdocx.com/images/bang_tan.gif)