Solutions

Chapter4

4、1、1

4、1、2

a)

b)

c)

Incweassumethataphoneandaddresscanonlybelongtoasinglecustomer(1-mrelationshiprepresentedbyarrowintocustomer)、

d)

Indweassumethatanaddresscanonlybelongtoonecustomerandaphonecanexistatonlyoneaddress、

Ifthemultiplicityofaboverelationshipswerem-to-n,theentitysetbeesweakandthekeyssNoofcustomerswillbeneededaspartofthepositekeyoftheentityset、

Inc&d,weconvertattributesphonesandaddressestoentitysets、Sinceentitysetsoftenbeerelationsinrelationaldesign,

wemustconsidermoreefficientalternatives、

Insteadofqueryingmultipletableswherekeyvaluesareduplicated,wecanalsomodifyattributes:

(i)PhonesattributecanbeconvertedintoHomePhone,OfficePhoneandCellPhone、

(ii)Amultivaluedattributesuchasaliascanbekeptasanattributewhereasinglecolumncanbeusedinrelationaldesigni、e、concatenateallvalues、SQLallowsaquery"like'%Junius%'"tosearchthemultiplevaluesinacolumnalias、

4、1、3

4、1、4

a)

b)

c)

Therelationship"played"betweenTeamsandPlayersissimilartorelationship"plays"betweenTeamsandPlayers、

4、1、5

4、1、6TheinformationaboutchildrencanbeascertainedfrommotherOfandfatherOfrelationships、AttributessNoisrequiredsincenamesarenotunique、

4、1、7

4、1、8

a)

(b)

4、1、9

Assumptions

AProfessoronlyworksinatmostonedepartment、

AcoursehasatmostoneTA、

Acourseisonlytaughtbyoneprofessorandofferedbyonedepartment、

Studentsandprofessorshavebeenassigneduniqueemailids、

Acourseisuniquelyidentifiedbythecourseno,sectionno,andsemester(e、g、cs157-3spring09)、

4、1、10

Giventhatforeachmovie,auniquestudioexiststhatproducesthemovie、Eachstariscontractedtoatmostonestudio、

Butstarscouldbeunemployedatagiventime、Thusthefour-wayrelationshipinfig4、6canbeeasilyintoconvertedequivalentrelationships、

4、2、1

Redundancy:TheowneraddressisrepeatedinAccSetsandAddressesentitysets、

Simplicity:AccSetsdoesnotserveanyusefulpurposeandthedesigncanbemoresimplyrepresentedbycreatingmany-to-manyrelationshipbetweenCustomersandAccounts、

Rightkindofelement:TheentitysetAddresseshasasingleattributeaddress、Acustomercannothavemorethanoneaddress、

HenceaddressshouldbeanattributeofentitysetCustomers、

Faithfulness:Customerscannotbeuniquelyidentifiedbytheirnames、InrealworldCustomerswouldhaveauniqueattributesuchasssNoorcustomerNo

4、2、2

StudiosandPresidentscanbebinedintooneentitysetStudioswithPresidentsbeinganattributeofStudiosunderfollowingcircumstances:

1、ThePresidentsentitysetonlycontainsasimpleattributeviz、presidentName、AdditionalattributesspecifictoPresidentsmightjustifymakingPresidentsintoanentityset、

4、2、3

4、2、4Theentitysetsshouldhavesingleattribute、

a)Stars:starName

b)Movies:movieName

c)Studios:studioName、Howeverthereexistsamany-to-manyrelationshipbetweenStudiosandContracts、Hence,inaddition,weneedmoreinformationaboutstudiosinvolved、Ifacontractalwaysinvolvestwostudios,twoattributessuchasproducingStudioandstarStudiocanreplacethe

Studiosentityset、Ifacontactcanbeassociatedwithatmostfivestudios,itmaybepossibletoreplacetheStudiosentitysetbyfiveattributesviz、studio1,studio2,studio3,studio4,andstudio5、Alternately,apositeattributecontainingconcatenationofallstudionamesinacontactcanbeconsidered、Aseparatorcharactersuchas"$"canbeused、SQLallowssearchingofsuchanattributeusingquerylike'%keyword%'

4、2、5

FromAugmentationruleofFunctionalDependency,

given

B->M(B=Baby,M=Mother)

then

BND->M(N=Nurse,D=Doctor)

Hencewecanjustputanarrowenteringmother、

a)PutanarrowenteringentitysetMothersforthesimplestsolution(Asinfig、4、4,whereamulti-wayrelationshipwasallowed,eventhoughMoviesalonecouldidentifytheStudio)、However,wecandisplaymoreaccurateinformationwithbelowfigure、

b)

c)

AgainfromAugmentationruleofFunctionalDependency,

given

BM->D

then

BMN->D

ThuswecanjustaddanarrowenteringDoctorstofig4、15、Belowfigurerepresentsmoreaccurateinformationhowever、

4、2、6

a)

b)TransitivityandAugmentationrulesofFunctionalDependencyallowarrowenteringMothersfromBirths、However,anewrelationshipinbelowfigurerepresentsmoreaccurateinformation、

c)

Designflawsinabcabove1、Assuggestedabove,usingTransitivityandAugmentationrulesofFunctionalDependency,muchsimplerdesignispossible、

4、2、7

Inbelowfigurethereexistsamany-to-onerelationshipbetweenBabiesandBirthsandanothermany-to-onerelationshipbetweenBirthsandMothers、Fromtransitivityofrelationships,thereisamany-to-onerelationshipbetweenBabiesandMothers、Henceababyhasauniquemotherwhileabirthcanallowmorethanonebaby、

4、3、1

a)

b)

Acaptaincannotexistwithoutateam、Howeveraplayercan(freeagent)、Arecentlyformed(ordefunct)teamcanexistwithoutplayersorcolors、

c)

Childrencanexistwithoutmotherandfather(unknown)、

4、3、2

a)

ThekeysofbothE1andE2arerequiredforuniquelyidentifyingtuplesinR

b)

ThekeyofE1

c)

ThekeyofE2

d)

ThekeyofeitherE1orE2

4、3、3

SpecialCase:Allentitysetshavearrowsgoingintothemi、e、allrelationshipsare1-to-1

AnyKi

Otherwise:binationofallKi'swheretheredoesnotexistanarrowgoingfromRtoEi、

4、4、1

No,gradeisnotpartofthekeyforenrollments、ThekeysofStudentsandCoursesbeekeysoftheweakentitysetEnrollments、

4、4、2

ItispossibletomakeassignmentnumberaweakkeyofEnrollmentsbutthisisnotgooddesign(redundancysincemultipleassignmentscorrespondtoacourse)、AnewentitysetAssignmentiscreatedanditisalsoaweakentityset、HencethekeyattributesofAssignmentwillefromthestrongentitysetstowhichEnrollmentsisconnectedi、e、studentID,dept,andCourseNo、

4、4、3

a)

b)

c)

4、4、4

a)

b)

4、5、1

Customers(SSNo,name,addr,phone)

Flights(number,day,aircraft)

Bookings(custSSNo,flightNo,flightDay,row,seat)

RelationsfortoCustandtoFltrelationshipsarenotrequiredsincetheweakentitysetBookingsalreadycontainsthekeysofCustomersandFlights、

4、5、2

(a)

(b)

Schemaischanged、SincetoCustisnolongeranidentifyingrelationship,SSNoisnolongerapartofBookingsrelation、

Bookings(flightNo,flightDay,row,seat)

ToCust(custSSNO,flightNo,flightDay,row,seat)

Theaboverelationsaremergedinto

Bookings(flightNo,flightDay,row,seat,custSSNo)

HowevercustSSNoisnolongerakeyofBookingsrelation、Itbeesaforeignkeyinstead、

4、5、3

Ships(name,yearLaunched)

SisterOf(name,sisterName)

4、5、4

(a)

Stars(name,addr)

Studios(name,addr)

Movies(title,year,length,genre)

Contracts(starName,movieTitle,movieYear,studioName,salary)

DependingonotherrelationshipsnotshowninERdiagram,studioNamemaynotberequiredasakeyofContracts(ornotevenrequiredasanattributeofContracts)、

(b)

Students(studentID)

Courses(dept,courseNo)

Enrollments(studentID,dept,courseNo,grade)

(c)

Departments(name)

Courses(deptName,number)

(d)

Leagues(name)

Teams(leagueName,teamName)

Players(leagueName,teamName,playerName)

4、6、1

TheweakrelationCourseshasthekeyfromDeptsalongwithnumber、HencethereisnorelationforGivenByrelationship、

(a)

Depts(name,chair)

Courses(number,deptName,room)

LabCourses(number,deptName,allocation)

(b)LabCourseshasalltheattributesofCourses、

Depts(name,chair)

Courses(number,deptName,room)

LabCourses(number,deptName,room,allocation)

(c)CoursesandLabCoursesarebinedintoonerelation、

Depts(name,chair)

Courses(number,deptName,room,allocation)

4、6、2

(a)

Person(name,address)

ChildOf(personName,personAddress,childName,childAddress)

Child(name,address,fatherName,fatherAddress,motherName,motherAddresss)

Father(name,address,wifeName,wifeAddresss)

Mother(name,address)

SinceFatherOfandMotherOfaremany-onerelationshipsfromChild,thereisnoneedforaseparaterelationforthem、Similarlytheone-onerelationshipMarriedcanbeincludedinFather(orMother)、ChildOfisamany-manyrelationshipandneedsaseparaterelation、

HowevertheChildOfrelationisnotrequiredsincetherelationshipcanbededucedfromFatherOfandMotherOfrelationshipscontainedinChildrelation、

(b)

ApersoncannotbebothMotherandFather、

Person(name,address)

PersonChild(name,address)

PersonChildFather(name,address)

PersonChildMother(name,address)

PersonFather(name,address)

PersonMother(name,address)

ChildOf(personName,personAddress,childName,childAddress)

FatherOf(childName,childAddress,fatherName,fatherAddress)

MotherOf(childName,childAddress,motherName,motherAddress)

Married(husbandName,husbandAddress,wifeName,wifeAddress)

Themany-manyChildOfrelationshipagainrequiresarelation、

Anentitybelongstooneandonlyoneclasswhenusingobject-orientedapproach、Hence,themany-onerelationsMotherOfandFatherOfcouldbeaddedasattributestoPersonChild,PersonChildFather,andPersonChildMotherrelations、

SimilarlytheMarriedrelationcanbeaddedasattributestoPersonChildMotherandPersonMother(orthecorrespondingfatherrelations)、

(c)ForthePersonrelationatleastoneofhusbandandwifeattributeswillbenull、

Person(personName,personAddress,fatherName,fatherAddress,motherName,motherAddresss,wifeName,wifeAddresss,husbandName,husbandAddress)

ChildOf(personName,personAddress,childName,childAddress)

4、6、3

(a)

People(name,fatherName,motherName)

Males(name)

Females(name)

Fathers(name)

Mothers(name)

ChildOf(personName,childName)

(b)

People(name)

PeopleMale(name)

PeopleMaleFathers(name)

PeopleFemale(name)

PeopleFemaleMothers(name)

ChildOf(personName,childName)

FatherOf(childName,fatherName)

MotherOf(childName,motherName)

PeoplecannotbelongtobothmaleandfemalebranchoftheERdiagram、

Moreoversinceanentitybelongstooneandonlyoneclasswhenusingobject-orientedapproach,noentitybelongstoPeoplerelation、

AgainwecouldreplaceMotherOfandFatherOfrelationsbyaddingasattributestoPeopleMale,PeopleMaleFathers,PeopleFemale,andPeopleFemaleMothersrelations、

(c)

People(name,fatherName,motherName)

ChildOf(personName,childName)

4、6、4

(a)

Eachentitysetresultsinonerelation、Thusboththeminimumandmaximumnumberofrelationsise、

Therootrelationhasaattributesincludingkkeys、Thustheminimumnumberofattributesisa、Allotherrelationsincludethekkeysfromrootalongwiththeiraattributes、Thusthemaximumnumberofattributesisa+k、

(b)

Therelationforrootwillhaveaattributes、Therelationrepresentingthewholetreewillhavee*aattributes、

Thenumberofrelationswilldependontheshapeofthetree、Atreeofeentitieswhereonlyonechildexists(sayleftchildonly)wouldhavetheminimumnumberofrelations、Thusbelowfigurewillonlycontain4subtreesthatcontainrootE1,E1E2,E1E2E3,andE1E2E3E4、Witheentitysets,minimumerelationsarepossible、

Themaximumnumberofsubtreesresultwhenalltheentities(exceptroot)areatdepth1、Thusbelowfigurewillcontain8subtreesthatcontainrootE1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,andE1E2E3E4、Witheentitysets,maximum2^(e-1)relationsarepossible、

(c)

Thenullsmethodalwaysresultsinonerelationandcontainsattributesfromalleentitiesi、e、e*aattributes、

Summarizingfora,b,andcabove;

#ponents#Relations

MinMaxMinMax

Method

straight-E/Raaee

object-orientedae*ae2^(e-1)

nullse*ae*a11

4、7、1

4、7、2

a)

b)

c)

d)

4、7、3

4、7、4

4、7、5

MalesandFemalessubclassesareplete、MothersandFathersarepartial、Allsubclassesaredisjoint、

4、7、6

4、7、7

4、7、8

WeconverttheternaryrelationshipContractsintothreebinaryrelationshipsbetweenanewentitysetContractsandexistingentitysets、

4、7、9

a)

b)

c)

4、7、10

Aself-associationParentOfforentitysetpeoplehasmultiplicity0、、2atparentroleend、

InaLibrarydatabase,ifapatroncanloanatmost12books,themmultiplicityis0、、12、

ForaFullTimeStudentsentityset,arelationshipofmultiplicity5、、*mustexistwithCourses(Astudentmusttakeatleast

5coursestobeclassifiedFullTime、

4、8、1

Customers(SSNo,name,addr,phone)

Flights(number,day,aircraft)

Bookings(row,seat,custSSNo,FlightNumber,FlightDay)

Customers("SSNo",name,addr,phone)

Flights("number","day",aircraft)

Bookings(row,seat,"custSSNo","FlightNumber","FlightDay")

4、8、2

a)

Movies(title,year,length,genre)

Studios(name,address)

Presidents(cert#,name,address)

Owns(movieTitle,movieYear,studioName)

Runs(studioName,presCert#)

Movies("title","year",length,genre)

Studios("name",address)

Presidents("cert#",name,address)

Owns("movieTitle","movieYear",studioName)

Runs("studioName",presCert#)

b)

Sincethesubclassesaredisjoint,ObjectOrientedApproachisused、

Thehierarchyisnotplete、Hencefourrelationsarerequired

Movies(title,year,length,genre)

MurderMysteries(title,year,length,genre,weapon)

Cartoons(title,year,length,genre)

Cartoon-MurderMysteries(title,year,length,genre,weapon)

Movies("title","year",length,genre)

MurderMysteries("title","year",length,genre,weapon)

Cartoons("title","year",length,genre)

Cartoon-MurderMysteries("title","year",length,genre,weapon)

c)

Customers(ssNo,name,phone,address)

Accounts(number,balance,type)

Owns(custSSNo,accountNumber)

Customers("ssNo",name,phone,address)

Accounts("number",balance,type)

Owns("custSSNo","accountNumber")

d)

Teams(name,captainName)

Players(name,teamName)

Fans(name,favoriteColor)

Colors(colorname)

ForDisplaysassociation,

TeamColors(teamName,colorname)

RootsFor(fanName,teamName)

Admires(fanName,playerName)

Teams("name",captainName)

Players("name",teamName)

Fans("name",favoriteColor)

Colors("colorname")

ForDisplaysassociation,

TeamColors("teamName","colorname")

RootsFor("fanName","teamName")

Admires("fanName","playerName")

e)

People(ssNo,name,fatherSSNo,motherSSNo)

People("ssNo",name,fatherssNo,motherssNo)

f)

Students(email,name)

Courses(no,section,semester,professorEmail)

Departments(name)

Professors(email,name,worksDeptName)

Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)

Students("email",name)

Courses("no","section","semester",professorEmail)

Departments("name")

Professors("email",name,worksDeptName)

Takes(letterGrade,"studentEmail","courseNo","courseSection","courseSemester")

4、8、3

a)

Eachandeveryobjectisamemberofexactlyonesubclassatleaflevel、Wehavenineclassesattheleafofhierarchy、Henceweneedninerelations、

b)

Allobjectsonlybelongtoonesubclassanditsancestors、Hence,weneednotconsidereverypossiblesubtreebutratherthetotalnumberofnodesintree、

Henceweneedthirteenrelations、

c)

Weneedallpossiblesubtrees、Hence218relationsarerequired、

4、9、1

classCustomer(key(ssNo)){

attributeintegerssNo;

attributestringname;

attributestringaddr;

attributestringphone;

relationshipSet<Account>ownsAccts

inverseAccount::ownedBy;

};

classAccount(key(number)){

attributeintegernumber;

attributestringtype;

attributerealbalance;

relationshipSet<Customer>ownedBy

inverseCustomer::ownsAccts;

};

4、9、2

a)

ModifyclassAccounttocontainrelationshipCustomerownedBy(noSet)

b)

AlsoremovesetinrelationshipownsAcctsofclassCustomer、

c)

ODLallowsacollectionofprimitivetypesaswellasstructures、ToclassCustomeraddfollowingattributesinplaceofsimpleattributesaddrandphone:

Set<stringphone>

Set<Structaddr{stringstreet,stringcity,stringstate}>

d)

ODLallowsstructuresandcollectionsrecursively、

Set<Structaddr{stringstreet,stringcity,stringstate},Set<stringphone>>

4、9、3

CollectionsareallowedinODL、Hence,ColorsSetcanbeeanattributeofTeams、

classColors(key(colorname)){

attributestringcolorname;

relationshipSet<Fans>FavoredBy

inverseFans::Favors;

relationshipset<Teams>DisplayedBy

inverseTeams::Displays;

};

classTeams(key(name)){

attributestringname;

relationshipset<Colors>Displays

inverseColors::DisplayedBy;

relationshipset<Players>PlayedBy

inversePlayers::Plays;

relationshipPLayersCaptainedBy

inversePlatyers::Captains;

relationshipset<Fans>RootedBy

inverseFans::Roots;

};

classPlayers(key(name)){

attributestringname;

relationshipSet<Teams>Plays

inverseTeams::PlayedBy;

relationshipTeamsCaptains

inverseTeams::CaptainedBy;

relationshipSet<Fans>AdmiredBy

inverseFans::Admires;

};

classFans(key(name)){

attributestringname;

relationshipColorsFavors

inverseColors::FavoredBy;

relationshipSet<Teams>RootedBy

inverseTeams::Roots;

relationshipSet<Players>Admires

inversePlayers::AdmiredBy;

};

4、9、4

classPerson{

attributestringname;

relationshipPersonmotherOf

inversePerson::childrenOfFemale;

relationshipPersonfatherOf

inversePerson::childrenOfMale;

relationshipSet<Person>children

inversePerson::parentsOf;

relationshipSet<Person>childrenOfFemale

inversePerson::motherOf;

relationshipSet<Person>childrenOfMale

inversePerson::fatherOf;

relationshipSet<Person>parentsOf

inversePerson::children;

};

4、9、5

Thestructeducation{stringdegree,stringschool,stringdate}cannothaveduplication、

HenceuseofSetsdoesnotmakeanydifferentasparedtobags,lists,orarrays、

Listswillallowfasteraccess/queriesduetothealreadysortednature、

4、9、6

a)

classDepartments(key(name)){

attributestringname;

relationshipCoursesoffers

inverseCourses::offeredBy;

};

classCourses(key(number,offeredBy)){

attributestringnumber;

relationshipDepartmentsofferedBy

inverseDepartments::offers;

};

b)

classLeagues(key(name)){

attributename;

relationshipTeamscontains

inverseTeams::belongs;

};

classTeams(key(name,belongs)){

attributename,

relationshipLeaguesbelongs

inverseLeagues::contains;

relationshipPlayersplay

inversePlayers::plays;

};

classPlayers(key(number,plays)){

attributenumber,

relationshipTeamsplays

inverseTeams::play;

};

4、9、7

classStudents(keyemail){

attributestringemail;

attributestringname;

relationshipCoursesisTA

inverseCourses::TA;

relationshipCoursesTakes

inverseCourses::TakenBy;

};

classProfessors(keyemail){

attributestringemail;

attributestringname;

relationshipDepartmentsWorksFor

inverseDepartment::Works;

relationshipCoursesTeaches

inverseCourses::TaughtBy;

};

classCourses(key(no,semester,section)){

attributestringno;

attributestringsemester;

attributestringsection;

relationshipStudentsTA

inverseStudents::isTA;

relationshipStudentsTakenBy

inverseStudents::Takes;

relationshipProfessorsTaughtBy

inverseProfessors::Teaches;

relationshipDepartmentsOfferedBy

inverseDepartments::Offer;

};

classDepartments(keyname){

attributename;

relationshipCoursesOffer

inverseCourses::OfferedBy;

relationshipProfessorsWorks

inverseProfessors::WorksFor;

};

4、9、8

Arelationshipisitsowninversewhenforeveryattributepairintherelationship,theinversepairalsoexists、Arelationwithsucharelationshipiscalledsymmetricinsettheory、e、g、ArelationshipcalledSiblingOfinPersonrelationisitsowninverse、

4、10、1

a)

Customers(ssNo,name,addr,phone)

Account(number,type,balance)

Owns(ssNo,accountNumber)

b)

Accounts(number,balance,type,owningCustomerssNo)

Customers(ssNo,name)

Addresses(ownerssNo,street,state,city)

Phones(ownerssNo,street,state,city,phonearea,phoneno)

WecanremoveAddressesrelationsinceitsattributesareasubsetofrelationPhones、

c)

Fans(name,colors)

RootedBy(fan_name,teamname)

Admires(fan_name,playername)

Players(name,teamname,is_captain)

Teams(name)--removesubsetofteamcolor

Teamcolors(name,colorname)

Colors(colorname)

d)

cl