数据库系统基础教程第二章复习资料.docx
《数据库系统基础教程第二章复习资料.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第二章复习资料.docx（29页珍藏版）》请在冰豆网上搜索。
数据库系统基础教程第二章复习资料
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（
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.modelR4:
=π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
（speedR6:
=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=damagedANDdateR4:
=π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
升级会员 