数据库系统基础教程第二章答案.docx

数据库系统基础教程第二章答案.docx

《数据库系统基础教程第二章答案.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第二章答案.docx（26页珍藏版）》请在冰豆网上搜索。

数据库系统基础教程第二章答案

ExerciserelationAccounts,theattributesare:

acctNo,type,balance

ForrelationCustomers,theattributesare:

firstName,lastName,idNo,account

ExerciserelationAccounts,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）

ExerciserelationAccountsandthefirsttuple,thecomponentsare:

123456acctNo

savingstype

12000balance

ForrelationCustomersandthefirsttuple,thecomponentsare:

RobbiefirstName

BankslastName

901-222idNo

12345account

ExerciserelationAccounts,arelationschemais:

Accounts（acctNo,type,balance）

ForrelationCustomers,arelationschemais:

Customers（firstName,lastName,idNo,account）

Exerciseexampledatabaseschemais:

Accounts（

acctNo,

type,

balance

）

Customers（

firstName,

lastName,

idNo,

account

）

Exercisesuitabledomainforeachattribute:

acctNoInteger

typeString

balanceInteger

firstNameString

lastNameString

idNoString（becausethereisahyphenwecannotuseInteger）

accountInteger

ExerciseequivalentwaytopresenttheAccountrelation:

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

Exerciseofattributesthatarecreatedforprimarilyservingaskeysinarelation:

UniversalProductCode（UPC）usedwidelyinUnitedStatesandCanadatotrackproductsinstores.

SerialNumbersonawidevarietyofproductstoallowthemanufacturertoindividuallytrackeachproduct.

VehicleIdentificationNumbers（VIN）,auniqueserialnumberusedbytheautomotiveindustrytoidentifyvehicles.

Exercisecanorderthethreetuplesinanyof3!

=6ways.Also,thecolumnscanbeorderedinanyof3!

=6ways.Thus,thenumberofpresentationsis6*6=36.

Exercisecanorderthethreetuplesinanyof5!

=120ways.Also,thecolumnscanbeorderedinanyof4!

=24ways.Thus,thenumberofpresentationsis120*24=2880

Exercisecanorderthethreetuplesinanyofm!

ways.Also,thecolumnscanbeorderedinanyofn!

ways.Thus,thenumberofpresentationsisn!

m!

ExerciseTABLEProduct（

makerCHAR（30）,

modelCHAR（10）PRIMARYKEY,

typeCHAR（15）

）;

ExerciseTABLEPC（

modelCHAR（30）,

speedDECIMAL（4,2）,

ramINTEGER,

hdINTEGER,

priceDECIMAL（7,2）

）;

ExerciseTABLELaptop（

modelCHAR（30）,

speedDECIMAL（4,2）,

ramINTEGER,

hdINTEGER,

screenDECIMAL（3,1）,

priceDECIMAL（7,2）

）;

ExerciseTABLEPrinter（

modelCHAR（30）,

colorBOOLEAN,

typeCHAR（10）,

priceDECIMAL（7,2）

）;

ExerciseTABLEPrinterDROPcolor;

ExerciseTABLELaptopADDodCHAR（10）DEFAULT‘none’;

ExerciseTABLEClasses（

classCHAR（20）,

typeCHAR（5）,

countryCHAR（20）,

numGunsINTEGER,

boreDECIMAL（3,1）,

displacementINTEGER

）;

ExerciseTABLEShips（

nameCHAR（30）,

classCHAR（20）,

launchedINTEGER

）;

ExerciseTABLEBattles（

nameCHAR（30）,

dateDATE

）;

ExerciseTABLEOutcomes（

shipCHAR（30）,

battleCHAR（30）,

resultCHAR（10）

）;

ExerciseTABLEClassesDROPbore;

ExerciseTABLEShipsADDyardCHAR（30）;

Exercise

R1:

=σspeed≥（PC）

R2:

=πmodel（R1）

model

1005

1006

1013

ExerciseR1:

=σhd≥100（Laptop）

R2:

=Product

（R1）

R3:

=πmaker（R2）

maker

E

A

B

F

G

ExerciseR1:

=σ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

Exercise

R1:

=σcolor=trueANDtype=laser（Printer）

R2:

=πmodel（R1）

model

3003

3007

Exercise

R1:

=σtype=laptop（Product）

R2:

=σtype=PC（Product）

R3:

=πmaker（R1）

R4:

=πmaker（R2）

R5:

=R3–R4

maker

F

G

ExerciseR1:

=ρPC1（PC）

R2:

=ρPC2（PC）

R3:

=R1

=AND<>R2

R4:

=πhd（R3）

hd

250

80

160

ExerciseR1:

=ρPC1（PC）

R2:

=ρPC2（PC）

R3:

=R1

=AND=AND

R4:

=π,（R3）

1004

1012

ExerciseR1:

=πmodel（σspeed≥（PC））

πmodel（σspeed≥（Laptop））

R2:

=πmaker,model（R1

Product）

R3:

=ρR3（maker2,model2）（R2）

R4:

=R2

（maker=maker2ANDmodel<>model2）R3

R5:

=πmaker（R4）

maker

B

E

Exercise

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

ExerciseR1:

=π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

ExerciseR1:

=π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

Exercise

R1:

=σbore≥16（Classes）

R2:

=πclass,country（R1）

class

country

Iowa

USA

NorthCarolina

USA

Yamato

Japan

ExerciseR1:

=σlaunched<1921（Ships）

R2:

=πname（R1）

name

Haruna

Hiei

Kirishima

Kongo

Ramillies

Renown

Repulse

Resolution

Revenge

RoyalOak

RoyalSovereign

Tennessee

ExerciseR1:

=σbattle=DenmarkStraitANDresult=sunk（Outcomes）

R2:

=πship（R1）

ship

Bismarck

Hood

ExerciseR1:

=Classes

Ships

R2:

=σlaunched>1921ANDdisplacement>35000（R1）

R3:

=πname（R2）

name

Iowa

Missouri

Musashi

NewJersey

NorthCarolina

Washington

Wisconsin

Yamato

ExerciseR1:

=σ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

ExerciseR1:

=π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

ExerciseFromassumingthateveryclasshasoneshipnamedaftertheclass.

R1:

=πclass（Classes）

R2:

=πclass（σname<>class（Ships））

R3:

=R1–R2

class

Bismarck

ExerciseR1:

=πcountry（σtype=bb（Classes））

R2:

=πcountry（σtype=bc（Classes））

R3:

=R1∩R2

country

Japan

Gt.Britain

ExerciseR1:

=πship,result,date（Battles

（battle=name）Outcomes）

R2:

=ρR2（ship2,result2,date2）（R1）

R3:

=R1

（ship=ship2ANDresult=damagedANDdate

R4:

=πship（R3）

Noresultsfromsampledata.

Exercise

resultofthenaturaljoinhasonlyoneattributefromeachpairofequatedattributes.Ontheotherhand,theresultofthetheta-joinhasbothcolumnsoftheattributesandtheirvaluesareidentical.

ExerciseUnionweaddatupletotheargumentsoftheunionoperator,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

Ifweaddatupletotheargumentsoftheselectionoperator,wewillgetallofthetuplesoftheoriginalresultandmaybetheaddedtuple.Iftheaddedtuplesatisfiestheselectcondition,thenitwillbeaddedtothenewresult.Theoriginaltuplesareincludedinthenewresultbecausetheystillsatisfytheselectcondition.Thustheselectionoperatorismonotone.

CartesianProduct

IfweaddatupletotheargumentsoftheCartesianproductoperator,wewillgetallofthetuplesoftheoriginalresultandpossiblyadditionaltuples.TheCartesianproductpairsthetuplesofonerelationwiththetuplesofanotherrelation.SupposethatwearecalculatingRxSwhereRhasmtuplesandShasntuples.IfweaddatupletoRthatisnotalreadyinR,thenweexpecttheresultofRxStohave（m+1）*ntuples.ThustheCartesianproductoperatorismonotone.

NaturalJoins

Ifweaddatupletotheargumentsof