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

《數(shù)據(jù)庫(kù)系統(tǒng)》英文教學(xué)課件,數(shù)據(jù)庫(kù)系統(tǒng),數(shù)據(jù)庫(kù),系統(tǒng),英文,教學(xué),課件 Guifeng Zheng,DBMS,SS/SYSU1Database SystemsLecture#3Guifeng ZhengSchool of Software,SYSUGuifeng Zheng,DBMS,SS/SYSU2AgendanLast time:qA little on designq(nearly)finished E/R modelsnThis time:qFinish E/RqConstraints(some review)qRelational modelqConverting E/R to relationsnNext time:Functional dependenciesGuifeng Zheng,DBMS,SS/SYSU3Quick topic:Weak entity setsnDef:some or all key attributes belong to another ESnPlays role in a connecting relationshipnThe key consists of:qPossibly its own attributes andqAll key attributes of entity sets from supporting relationshipsGuifeng Zheng,DBMS,SS/SYSU4Conditions for supporting relationshipsnSupporting relationship R:EFqR is many-one(or 1-1)EFqWith referential integrity(rounded arrow)qR is binaryqE receives key attributes of FnF itself may be weak qAnother entity set G,and so on recursively A1A2REFGuifeng Zheng,DBMS,SS/SYSU5nFor several supporting relships from E to FqKeys of each F role appear as foreign key of EnOther,non-supporting many-one relationships are not affectedConditions for weak entity setsBrideGroomWeddingA1A2PeopleWitness Guifeng Zheng,DBMS,SS/SYSU6Weak entity set e.g.nExample:Hierarchy species&genusnIdea:species name unique per genus onlynExercise:email addresses&loginsqaddress=username hostqzhenggf qPassword table stores just usernamenDraw E/R diagram with weak entity set Username supported by entity set HostSpeciesBelongs-toGenus Guifeng Zheng,DBMS,SS/SYSU7Next topic:Constraints(約束)nReview:programmer-defined rules stating what should always be true about consistent databasesnRestrictions on data(egs?):qKeysqSingle value constraintsqReferential Integrity(參照完整性)qDomain constraintsqGeneral constraintsnCant infer(推斷)constraints from dataqmay hold“accidentally”qbut they are a part of the schemaGuifeng Zheng,DBMS,SS/SYSU8E/R keysnUniquely identify entity in ESnAttribute or set of attributesqTwo entities cannot agree on all key attributesqThese attributes determine all othersnEvery ES should have a keyqpossibly including all attributesnPrimary key attributes underlinednMore than one possible key:qCandidate keys,primary keynPractical tip:create art key attributeqE.g.SSN,course-id,employee-id,etc.qSSN shorter than(name,address)addressnamessnPersonGuifeng Zheng,DBMS,SS/SYSU9Single-valued constraintsn“at most one”valueqAlready saw sharp arrows for relationshipsnAttributes have this automaticallyqcould be null or one valueqCan think of key atts as(non-null)single-valuedTACourseAssistsGuifeng Zheng,DBMS,SS/SYSU10Referential integrityn“Exactly one value”qNOT NULL&foreign keys in SQLnRelationshipsqNon-null value refers to entity that existsqRefer to entity with foreign keyqHTML analogy:no broken linksqProgramming analogy:no dangling pointersqMultiple ways of handling violationsInstructorCourseTaughtGuifeng Zheng,DBMS,SS/SYSU11Referential integrity E/R e.g.nInsertion must refer to existing entitynSuppose need to add:qcourse:DBMSqinstructor:ZGFnQ:Which order?nQ:What if relship were exactly-exactly,say,M(Hs,Ws)?qi.e.,referential integrity in both directions?nA:Put both inserts in one xact laterStudentsCoursesEnrollsInstructorTaughtGuifeng Zheng,DBMS,SS/SYSU12Other kinds of constraintsnDomain constraintsqE.g.date:must be after 1980qEnumerated type:grades A through F,no EqNo special E/R notation just write near linenGeneral constraints:qA class may have no more than 100 students;a student may not have more than 6 courses:StudentsCoursesEnroll=6=100Guifeng Zheng,DBMS,SS/SYSU13Next topic:the Relational Data ModelnInvented by Ted CoddqResearcher at IBMqWell see his name againnRelated work at BerkeleynIntroduced in a paper a paper published in June,1970Guifeng Zheng,DBMS,SS/SYSU14DB development paththeWorldE/RdesignRelationalschemaRelationalDBGuifeng Zheng,DBMS,SS/SYSU15Relations as tablesName Price Category Manufacturergizmo$19.99 gadgets GizmoWorksPower gizmo$29.99 gadgets GizmoWorksSingleTouch$149.99 photography CanonMultiTouch$203.99 household Hitachituples/rows/records/entitiesAttribute namesProduct table/relationGuifeng Zheng,DBMS,SS/SYSU16Relational terminologynRelation is composed of tuplesnTuple=sequence of attribute valuesqAttribute has atomic typesnRelation schema:relation name+attribute names+attribute typesnDatabase schema:set of relation schemasGuifeng Zheng,DBMS,SS/SYSU17Relations as setsnRecall:math relation is a subset of the cross-product of the attribute value setsqR subset-of S x TqProduct subset-of Name x Price x Cat x MftnOne member of Product relation:q(gizmo,$19.99,gadgets,GizmoWorks)in ProductqProduct(gizmo,$19.99,gadgets,GizmoWorks)nUsual updates:add/delete/change a tuple in this setnUpdates to the schema are rare,painful(why?)Guifeng Zheng,DBMS,SS/SYSU18From E/R models to relationsnRecall justification:qdesign is easier in E/Rqbut implementation is easier/faster in RnAnalogy to program compilation:qdesign is easier in C/Java/whateverqimplemen.is easier/faster in machine/byte codenStrategy:1.apply semi-mechanical conversion rules2.improve by combining some relations3.improve by normalizationninvolves finding functional dependenciesGuifeng Zheng,DBMS,SS/SYSU19E/R conversion rulesnRelationship relationqattributes:keys of entity-sets/rolesqkey:depends on multiplicitynEntity set relationqattributes:attributes of entity setqkey:key of ESnNB:mapping of types is not one-oneqWell see:mapping one tokens is also not one-onenSpecial treatment:qWeak entity setsqIsa relations&subclassesGuifeng Zheng,DBMS,SS/SYSU20Entity SetsnEntity set Students ssnnameaddressStudentsJohnHowardNameSouth Carolina444-555-6666Park Avenue111-222-3333AddressSSNRel:StudentsGuifeng Zheng,DBMS,SS/SYSU21nKey:keys of both entitiesBinary many-to-many relationshipsC30.0046444-555-6666C20.0056111-222-3333SE-304111-222-3333CourseIDssnRelation:EnrollsEnrollsS_addrS_NameStudentsCourseCourse-NameCourseIDssnGuifeng Zheng,DBMS,SS/SYSU22Many-to-one relationshipsnKey:keys of many entitiyMoviesStudiosowns2003SyliaM2021999Mr.Ripley.M101YearTitleMovieIDMoviesOrlandoDisneyS73NYCMiramaxS35AddressNameStudioIDStudiosS35S73StudioIDCN22222CN11111CopyrightNoM202M101MovieIDOwnsCopyrightNoMovieIDTitleYearStudioIDNameAddressGuifeng Zheng,DBMS,SS/SYSU23Many-to-one:a better designnQ:What if a movies Owns row were missing?2003SyliaM2021999Mr.Ripley.M101YearTitleMovieIDMoviesS35S73StudioIDCN22222CN11111CopyrightNoM202M101MovieIDOwnsCN22222CN11111CopyrightNoS35S73StudioID20031999YearSyliaM202Talent Mr.RipleyM101TitleMovieIDMoviesGuifeng Zheng,DBMS,SS/SYSU24Many-to-many relationships againnNB:Wont work for many-many relationshipsactsMovieIDTitleYearM101Mr.Ripley1999M202Sylia2003M303P.D.Love2002StarIDNameAddressT400Gwyneth P.Bev.HillsT401P.S.HoffmanHollywoodT402Jude LawPalm SpringsMovieIDStarIDM101T400M202T400M101T401M101T402M303T401MoviesStarsActsMoviesStarsGuifeng Zheng,DBMS,SS/SYSU25Many-to-many relationships againMovieIDTitleYearStarIDM101Talented Mr.Ripley1999T400M101Talented Mr.Ripley1999T401M101Talented Mr.Ripley1999T402M202Syvlia2003T400M303Punch Drunk Love2003T401And heres why:Guifeng Zheng,DBMS,SS/SYSU26Multiway relationships&rolesnDifferent roles treated as different entity setsnKey:keys of the many entitiesStudentsCoursesTAstutorsgradersenrollsTA_SSNNameSSNCourseIDNameNameGuifeng Zheng,DBMS,SS/SYSU27Multiway relationships&rolesEnrolls(S_SSN,Course_ID,Tutor_SSN,Grader_SSN)SSNName111-11-1111George222-22-2222DickTA_SSNName333-33-3333Wesley 444-44-4444Howard555-55-5555JohnStudentsTAsCourseIDNameSE-304DatabasesC20.0056SoftwareCoursesS_SSNCourseIDTutor_SSNGrader_SSN111-11-1111SE-304333-33-3333444-44-4444222-22-2222SE-304444-44-4444555-55-5555Guifeng Zheng,DBMS,SS/SYSU28Converting weak ESs differencesnAtts of Jersey Rel.are:qattributes of Jerseyqkey attributes of supporting ESs,ClubJersey球衣OwnClubClubNameNoaddress9巴薩10皇馬10巴薩JerseyNoClubNameJerseynSupporting relships are omitted(why?)Guifeng Zheng,DBMS,SS/SYSU29Weak entity sets-relationshipsPlayerPNameC羅梅西PNamePlayerAssignJersey球衣OwnClubClubNameNoaddressGuifeng Zheng,DBMS,SS/SYSU30Weak entity sets-relationshipsnNon-supporting relationships for weak ESs are convertedqkeys include entire weak ES key71030JerseyNoC羅梅西梅西PName皇馬巴薩巴薩ClubNameAssignGuifeng Zheng,DBMS,SS/SYSU31Conversion examplenVideo store rental example,plus some attsnQ:Conversion to relations?RentalVideoStoreCustomerMoviedateyearMNameaddressCnameMIDGuifeng Zheng,DBMS,SS/SYSU32Next weeknFor next week:qReview/skim Ch.3 section 5(from today)qRead Ch.19 sections 1-3