《數(shù)據(jù)庫(kù)系統(tǒng)》英文教學(xué)課件

《數(shù)據(jù)庫(kù)系統(tǒng)》英文教學(xué)課件,數(shù)據(jù)庫(kù)系統(tǒng),數(shù)據(jù)庫(kù),系統(tǒng),英文,教學(xué),課件 Database SystemsLecture#2Guifeng ZhengSchool of Software,SYSUGuifeng Zheng,DBMS,SS/SYSU2AgendanLast time:intronThis time:E/R model1.Identify entity sets,relations and attributes2.One-one,one-many,many-many relations3.Simple ER diagrams to model a situation4.3-way relationships,multiple roles,subclassesnDesign issues1.Simplicity2.Redundancy3.Replacing a relationships with entity setsGuifeng Zheng,DBMS,SS/SYSU3DB development paththeWorldE/RdesignRelationalschemaRelationalDBGuifeng Zheng,DBMS,SS/SYSU4Data ModelsnDBMS models real worldnData Model is link between users view of the world and bits stored in computernMany models existnWe will ground ourselves in the Relational Modelqclean and commonnBut use the Entity-Relationship model as a middle ground for design1010111101Student(sid:string,name:string,login:string,age:integer,gpa:real)Guifeng Zheng,DBMS,SS/SYSU5Entity Relationship(E/R)ModelnA popular data model useful to database designersqGraphical representation of miniworldnE/R design translated to a relational designqthen implemented in an RDBMSnElements of modelqEntitiesqEntity SetsqAttributesqRelationships(!=relations!)Guifeng Zheng,DBMS,SS/SYSU6E/R Model:Entity SetsnEntity:like an objectqParticular instance of a conceptnEntity set:set of one sort of entities or a conceptqAll with same attributesnRepresented by a rectangle:nA“good”entity setqCommon propertiesqCorrespond to class of phys.or bus.objects nE.g.,Employees,products,accounts,grades,campaigns,etc.World LeaderGuifeng Zheng,DBMS,SS/SYSU7E/R Model:AttributesnProperties of entities in entity setqLike fields in a structqLike columns in a table/spreadsheetqLike data members in an objectnValues in some domain(e.g.,ints,strings)nRepresented by ovals:nAssumed atomicqBut could have limited structureqints,strings,etc.nEach entity set has a key(underlined attribute).IDNameStudentstruct student int id;char*name;Guifeng Zheng,DBMS,SS/SYSU8nA set of attributes that can uniquely identify an entityEmployeeEmpNoNameEmpNoName.123456John Wong.456789.146777.John WongMary CheungERDtabularKey AttributesGuifeng Zheng,DBMS,SS/SYSU9nComposite key:Name or Address alone cannot uniquely identify an employee,but together they can!EmployeeNameAddressKey AttributesGuifeng Zheng,DBMS,SS/SYSU10nAn entity may have more than one keyqe.g.,EmpNo,(Name,Address)qonly one is selected as the key.(sometimes called the Primary key)EmployeeEmpNoNameAddressIn many cases,a key is artificially introduced(e.g.,EmpNo)to make applications more efficient.Question:does a desk has a key?Key AttributesGuifeng Zheng,DBMS,SS/SYSU11E/R Model:RelationshipsnConnect two or more entity setsqe.g.students enroll in coursesqBinary relationships:connect two entity setsnmost commonqMultiway relationships:connect several ESsnRepresented by diamonds:StudentsEnrollCoursesGuifeng Zheng,DBMS,SS/SYSU12E/R Model:RelationshipsnStudents Enroll in coursesnCourses are Held in roomsnThe E/R data model:StudentsEnrollCoursesHeldRoomsNameIDGuifeng Zheng,DBMS,SS/SYSU13Set TheorynInvented by Georg CantorqGreat 19th-C German mathematiciannBig set theory results in 1870s-1890snControversial at the timeqKronecker:“humbug”qFirst rigorous math of the“actual infinite”nwell mostly deal with finite setsGuifeng Zheng,DBMS,SS/SYSU14A little set theorynA mathematical set is a collection of membersnA set is defined by its membersq“Are you in or are you out?”qNo other structure,no order,no duplicates allowednSets specified by listing:q1,2,3,=Nq1,2,George Bush (tho usually homogeneous sets in DBMS)nOr by“set-builder”notation:q x in N:2 divides x=?q x in Presidents|reelected(x)=?q2x:x in N=?Guifeng Zheng,DBMS,SS/SYSU15A little set theorynOne set can be a subset of another(which is a superset of it)qReelectedPresidents is a subset of PresidentsqAlso,RP is a proper subset(真子集)of Pres some lost reelectionnGiven two sets X and Y,the cross productor Cartesian product isX x Y=(x,y):x in X,y in Y=the set of all ordered pairsnImportant:(x,y)!=x,ynIn an order pair or tupleqOrder matters;duplicates are allowedGuifeng Zheng,DBMS,SS/SYSU16A little set theorynMathematically,a relation between X and Y is just a subset of X x Y=all those pairs(x,y)s.t.x is related to ynExample:owner-of O on People,CatsqO(MPJ,Gdel the Cat)holdsnThe equals relation E on N,N:qE(3,3)holds because 3=3qE(3,4)does not holdqE is still a set:E=(1,1),(2,2),(3,3),nFather-of relation F on People,People:qF(GHWB,GWB)holdsqF(GWB,GHWB)does not holdq Relations arent necessarily symmetricGuifeng Zheng,DBMS,SS/SYSU17Many-manyMultiplicity of RelationshipsMany-oneOne-oneRepresentation of relationshipsnNo arrow:many-to-manynSharp arrow:many-to-onenRounded arrow:“exactly one”q“key constraint”nOne-one:Guifeng Zheng,DBMS,SS/SYSU18Multiplicity of RelationshipsStudentsEnrollsCoursesMany-to-many:StudentLiveResidence hallMany-to-one:a student living in a residence hallMany-to-exactly-one:a student must live in a residence hallStudentLiveResidence hallGuifeng Zheng,DBMS,SS/SYSU19Multiplicity,set-theoreticallynAssume no vars below are equalnMany-one means:qif(x1,y1)in R then(x1,y2)cannot be in RnOne-many means:q(Y,X)is many-onenOne-one means:qif(x1,y1)in R,then neither(x2,y1)nor(x1,y2)can be in RnNotice:one-one is stronger than many-onenOne-one implies both many-one and one-manyGuifeng Zheng,DBMS,SS/SYSU20ReviewnMultiplicity review:qSquare-of?(e.g.?)qCube-of?(e.g.?)Guifeng Zheng,DBMS,SS/SYSU21E/R DiagramStudentsCoursesEnrollsIDNameIDNameAssistingTAIDNameGuifeng Zheng,DBMS,SS/SYSU22E/R DiagramsnOK if each TA is a TA of all studentsqStudent and TA connected only through CoursenBut what if students were divided among multiple TAs?qThen a student in SE-304 would be related to only one of the TAs for SE-304which one?qSchema doesnt store enough infon3-way relationship is helpful hereGuifeng Zheng,DBMS,SS/SYSU23Multiway RelationshipsStudentsCoursesTAsEnrollsStudentsCoursesTAsCondiSE-304 DonaldGeorgeSE-304DickAlbertoSE-304 ColinEnrolls entries:NB:Enrolls determines TA:(student,course)at most one TAGuifeng Zheng,DBMS,SS/SYSU24Converting multiway relships to binarynBinary relationships are as strong as multiwaynReplace relationship with connecting entity set and multiple binary relationshipsStudentsCoursesTAsEnrollsStudent-ofCourse-ofTA-ofNB:Enrolls has no attributes!Guifeng Zheng,DBMS,SS/SYSU25Second multiway e.g.:renting moviesnScenario:a Customer Rents a Movie from a VideoStore on a certain datendate should belong to the fact of the rentingqRelationship attributeRentalVideoStoreCustomerMoviedateGuifeng Zheng,DBMS,SS/SYSU26Second multiway e.g.:renting moviesnWhere can we draw arrows?n(store,video,customer)date?n(store,video,date)customer?n(store,date,customer)video?n(video,date,customer)store?RentalVideoStoreCustomerMoviedateGuifeng Zheng,DBMS,SS/SYSU27Q:Why does it matter?nRound arrow benefit:qObvious:one item takes less space than manyqLess obvious:easier to access one item x than set of one item xnIn programming:an int v.a linked list with just one intnRegular arrow benefit:qMapping to a set of either one elm or none seems badqBut not implemented this wayqAlways one element,which may be NULLGuifeng Zheng,DBMS,SS/SYSU28Second multiway e.g.:renting moviesnConvert to binary?RentalVideoStoreCustomerMoviedateRentalCustomerStoreMovieStoreOfMovieOfBuyerOfdateGuifeng Zheng,DBMS,SS/SYSU29Roles in relationshipsnEntity set appears more than once in a relshipqGenerally distinct entitiesnEach appearance is in a different rolenEdges labeled by rolesPre-reqPrereqSuccessorCourseCourse(Pre-req)Course(Successor)AccountingFinance-IFinance-IDerivativesFinance-IFinance-IICalculusDerivativesGuifeng Zheng,DBMS,SS/SYSU30Subclasses in the E/R modelnSome ESs are special cases of othersnConversely:some are generalizationsqMammals,humans,students,grad studentsqNB:These arent members but subclassesnSubclass A isa BqRepresented by a triangleqRoot is more generalGuifeng Zheng,DBMS,SS/SYSU31SubclassesMoviesCartoonsisaVoicesstarslengthtitleyearGuifeng Zheng,DBMS,SS/SYSU32New topic:Design IssuesnFaithfulness(如實(shí)、正確)nAvoiding redundancy(避免冗余)nSimplicity(簡(jiǎn)單性)nChoice of relationships nPicking elementsGuifeng Zheng,DBMS,SS/SYSU33FaithfulnessnIs the relationship many-many or many-one?nAre the attributes appropriate?nAre the relationships applicable to the entities?nExamples:qCourses&instructorsnmaybe many-one,maybe many-manyqBosses&subordinatesnmaybe one-many,maybe many-manyGuifeng Zheng,DBMS,SS/SYSU34Avoiding redundancynSay everything once and only onceqMinimize database storage requirementsqMore important:prevent possible update errorsnOne danger:modifying data one place but not the othernExample:spot the redundancyStudiosMoviesOwnStudioNameNameLengthNameAddressRedundancy:Movies“knows”the studio two waysPhoneGuifeng Zheng,DBMS,SS/SYSU35Spot more redundancyDifferent redundancy:studio info listed for every movie!MoviesStudioNameNameLengthSAddressSPhoneName Length Studio SAddress SPhonePulp Fiction Miramax NYC 212-Sylvia Miramax NYC 212-Jay&Sil.Bob Miramax NYC 212-Guifeng Zheng,DBMS,SS/SYSU36Dont add relships that are impliedStudentsCoursesTAsEnrollsTA-ofAssistSuppose each course again has=1 TAQ:Is this good design?A:probably notunless the Assist relship is not implied by Enrolls+TA-ofGuifeng Zheng,DBMS,SS/SYSU37Still more redundancyqRepeating TA names&IDs redundantqTA is not TAing any course now lose TAs data!qTA should get its own ESStudentsCoursesEnrollsQ:Whats wrong with this design?A:TA-NameTA-IDTA-EmailCourse-IDCNameGuifeng Zheng,DBMS,SS/SYSU38Related issue:entity or attribute?nSome E/Rs improved by removing entitiesnCan convert Entity E into attributes of F if1.R:FE is many-one(or 1-1)2.Attributes for E are mutually independentnknowing one att val doesnt tell us another att valnThenqremove Eqadd all attributes of E to FGuifeng Zheng,DBMS,SS/SYSU39StudentsCoursesEnrollsTA-NameAssistsTAEntity attributeCNameRoomStudentsCoursesEnrollsCNameRoomTA-NameCourse-IDCourse-IDGuifeng Zheng,DBMS,SS/SYSU40Convert TA entity again?nNo!Multiple TAs allowed redundant course datanViolates condition(1)StudentsCoursesEnrollsAssistsTACName CID Room TA-NameDBMS 46 123 HowardDBMS 46 123 WesleyCNameRoomCourse-IDTA-NameGuifeng Zheng,DBMS,SS/SYSU41Convert TA entity again?StudentsCoursesEnrollsAssistsTACNameRoomCourse-IDTA-IDTA-Favorite-ColornNo!TA has dependent fields redundant TA datanViolates condition(2)qHow can it tell?CName TA-Name TA-ID TA-ColorDBMS Ralph 678 GreenA.Soft.Ralph 678 GreenTA-NameGuifeng Zheng,DBMS,SS/SYSU42A case StudyA primary school student writes a composition about a picnic:Today is Sep 9,the weather is fine.My classmates,John,Mary and I go to a picnic in Sai Kung.Our teacher is Ms WongPicnicweatherdestinationdateStudentsNameTeacherNameMy Initial Design:Guifeng Zheng,DBMS,SS/SYSU43Questions?nWhy“John”,“Mary”,“Miss Wong”are not in the ER diagram?nWhat do these names tell us?nWhat are the keys of Student,Picnic&Teacher?nWhat are the cardinalities of the relationships?Guifeng Zheng,DBMS,SS/SYSU44nEvery student has an ID number,it is better to keep it in the database and use it as a key nI bet that there wont be teachers with the same name;otherwise,Ill add employee number and use it as a keyngoes is N:M,why?A picnic has more than one student participating;also,a student can go to more than 1 picnic.However,this N:M relationship allows a student to go to more than one picnic on the same datenleading is N:1,why?Depends on your assumptionsqI assume a teacher can only lead 1 picnic on a certain date,so given the teacher name and the date,I can identify a picnicnPicnic is made a weak entity.I could have added a PicnicNo,but it would be very awkward.My solutionStudentStudentNoNameweatherdatedestinationPicnicgoesleadingTeacherNameQuestion:How to record number of students in a picnic?