學(xué)生會期末考試備考-05、大三上數(shù)據(jù)庫原理

DatabaseSystemsConceptualModelingofDatabaseswithEntity-RelationshipDiagramsContent4.1DatabaseDesignGoalSpecificationofdatabaseschemaInvolvesrelations、attributes、constraintsAtwo-stageprocessDescribingahigh-levelgraphicalviewofessentialcomponentsofenterpriseandhowtheyarerelated(introducedinthischapter)2methodologiesEntity-Relation(E-R)modelUMLclassdiagramsRefinethefirststepbyusingtherelationalnormalizationtheory(introducedinChapter6)4.1DatabaseDesign(Cont’d)E-RModel:enterpriseviewedassetofEntities:modeltheobjectsRelationships:modeltheconnectionsamongentitiesIntegrityconstraintsE-Rdiagram:providesagraphicalsummaryofthedatabasestructure4.2EntitiesandEntityTypesEntity(EntityInstance):anobjectthatisinvolvedintheenterpriseE.g.John,CSE305EntityType:setofsimilarobjectsE.g.students,coursesCorrespondstoarelationinRelationalModeldescribedbyasetofattributesE.g.Student:Id,Name,Address,HobbiesAttribute:describesoneaspectofanentitytypeE.,maximumenrollmentThedifferententitiesofthesametypecanhaveidenticalvaluesinalloftheirattributesSingle-valuedorset-valued(differentwithrelationalmodel)4.2EntitiesandEntityTypes(Cont’d)Key:minimumsetofattributesthatuniquelyidentifiesanentity(candidatekey)EntitySchemaconsistofThenameoftheentitytypeThecollectionofitsattributesThekeyconstraintsE-RdiagramrepresentationEntitytype–rectangleAttribute–ovalSet-valuedattribute–doubleovalKey–underlinedattributePersonNameAddressSSNHobbies4.3RelationshipsandRelationshipTypesRelationship:relatestwoormoreentities(instances)E.g.JohnmajorsinComputerScienceRelationshipType:setofsimilarrelationshipsE.g.Student(entitytype)relatedtoDepartment(entitytype)byMajorsIn(relationshiptype).Distinction-relation(relationalmodel)-setoftuplesrelationship(E-RModel)–describesrelationshipbetweenentitiesofanenterpriseBothentitytypesandrelationshiptypes(E-Rmodel)aremappedtorelations(relationalmodel)4.3.1AttributesandRolesAttributeofarelationshiptypespecifiessomepropertyofarelationshipE.g.AmyMarriedToTomin2000TomandAmyarerelatedentity2000isthevalueofDateattributeofMarriedTorelationshiptype4.3.1AttributesandRoles(Cont’d)RoleofarelationshiptypespecifiesinwhatwayanentitytypeparticipatesintherelationshipE.g.AmyisthevalueofWiferoleandTomisthevalueofHusbandroleofMarriedTorelationshiptype4.3.1AttributesandRoles(Cont’d)Role(cont’d)Ifallofentitiesaredrawfromdistinctentitytypes,rolesarenotrequiredIfallofentitiesaredrawfromthesameentitytypes,rolesarerequiredforaparticularparticipantentitytype,eachentityparticipatesinatmostonerelationship,correspondingroleisakeyofrelationshiptype.Thisissingle-rolekey4.3.1AttributesandRoles(Cont’d)SchemaofaRelationshipTypeIncludesAttributeswithdomains:single-valuedorset-valuedRoleswithentitytypes:single-valuedDegreeofthetypeisthenumberofrolesengagedinarelationshiptypeConstraintsSyntax:RelationshiptypeRhasadegreen.Risdefinedinthisform<e1,…en;a1,…ak>eiisanentity,avaluefromRi’s(role)entitysetajisasetofattributevalueswithelementsfromdomainofAj(attribute)E.g.MarriedTohasschema<Wife,Husband;Date> instance<Amy,Tom;2000>4.3.2E-RDiagramRepresentationRelationshiptype–diamondRole–edgesthatconnectrelationshiptypeswithentitytypes4.3.3KeysKeyofarelationshiptypeR:minimumsetofrolesandattributesofRwhosevaluesuniquelyidentifytherelationshipinstancesTypesSingle-rolekeyExpressedbyanarrowpointinginthedirectionoftherelationship’sdiamondE.g.relationshipMarriedToMulti-roleskeyRepresentedtextually.{roles;attribute}E.g.relationshipSOLDAllrolesformakeyThekeywillnotbespecifiedinthediagram4.5FromE-RDiagramtoRelationalDatabaseSchemaInthefollowingpart,wewilltalkabouthowtotranslatetheseentity-relationshipapproachintotherelationalmodelandSQL4.5.1RepresentationofEntitiesEntitytypecorrespondstoarelationRelation’sattributes=entitytype’sattributesProblem:entitytypecanhavesetvaluedattributes.(111111,John,123MainSt,{stamps,coins})Solution:Useseveralrowstorepresentasingleentity(111111,John,123MainSt,stamps)(111111,John,123MainSt,coins)Problemswithsolution:RedundancyKeyofentitytypenotkeyofrelation=>resultingrelationmustbefurthertransformed.ThisonewillbediscussedinChapter64.5.2RepresentationofRelationshipsAttributesofcorrespondingrelationareAttributesofrelationshiptypeForeachrole,theprimarykeyoftheentitytypeassociatedwiththatroleE.g.S2000Courses(CrsCode,SectNo,Enroll)Professor(Id,DeptId,Name)Teaching(CrsCode,SecNo,Id,RoomNo)TeachingS2000CoursesProfessorDeptIdNameRoomNoCrsCodeEnrollSectNoId4.5.2RepresentationofRelationships(Cont’d)CandidatekeysofcorrespondingrelationareCandidatekeyofrelationExceptwhentherearesetvaluedattributes.SolutionissimilarwithEntity’sForeign-keysofcorrespondingrelationareTheprimarykeyofentityreferredbyaroleisaforeignkeyincorrespondingrelation4.5.2RepresentationofRelationships(Example1)CREATETABLEWorksIn(SinceDATE,--attributeStatusCHAR(10),--attributeProfIdINTEGER,--role(keyofProfessor)DeptIdCHAR(4),--role(keyofDepartm