System Concepts and ArchitectureData ModelCategories of Data ModelsDatabase SchemaDatabase StateThree-Schema ArchitectureThe Three SchemasData IndependenceDatabase System ArchitecturesTwo Tier Client-ServerThree Tier Client-ServerEntity-Relationship ModelSlide 13Entity SetsEntity Set NotationRelationshipsValuesMulti-way RelationshipsRelationship ConstraintsSlide 20Slide 21Slide 22Attributes on RelationshipsRecursive RelationshipsSubclassesKeysShowing KeysWeak Entity SetsExample Weak Entity SetPractice with E-R DiagramsSystem Concepts and ArchitectureRose-Hulman Institute of TechnologyCurt CliftonData ModelA set of concepts to describeDatabase structureBasic operations on the dataCategories of Data ModelsConceptualClosest to users’ viewsImplementationIntermediate level for programmersPhysicalActual hardware levelDatabase SchemaA description of the databaseNot the actual data in itTends to change seldomShown with a Schema DiagramDatabase StateActual content at an instant in timeEvery change results in a new stateDBMS tries to ensure only valid states occurThree-Schema ArchitectureGoals:Support program-data independenceRepresent multiple views of dataThe Three SchemasInternal schemaDescribes storage with physical data modelConceptual schemaDescribes entire database structurewith conceptual or implementation data modelExternal schemasDescribe user viewstypically with same data modelData IndependenceTwo kinds:Logical: change conceptual schema without changing external schemasPhysical: change internal schema without changing conceptualJust update mappingsDatabase System ArchitecturesCentralized All processing on one machineMainframe + dumb terminalsClient-ServerSpecialized server machines for each functionSmart client machines provide interfacesConnected via some sort of networkTwo Tier Client-ServerClient runs UI and application programsUses API to connect directly to DBMSPerhaps multiple DBMSThree Tier Client-ServerIntermediate layerApplication Server or Web ServerAdvantagesSecurityScalabilityDisadvantageComplexityEntity-Relationship ModelRose-Hulman Institute of TechnologyCurt CliftonEntity-Relationship ModelLets us sketch database designsSketches called ER DiagramsSimple enough share with customersCan convert sketches into implementationsConversion is easy (with practice)Entity SetsEntity: a “thing” that database tracksEntity set: a collection of similar entitiesAttribute: property of an entitySimple values, like integers or stringsAll entities in set have same properties(though different values)Entity Set NotationEntity Set NameAttribute 1Attribute 2Attribute 3Entity set names areusually singular, i.e. “Employee”not “Employees”RelationshipsConnect two (or more) entity setsNotation:Try to make verbs read left-to-right, top-to-bottomEntity Set 1 Entity Set 2VerbsValuesEntity set value:The set of entities in itRelationship value:A set of pairs (or triples, …) withone element from each related entity setMulti-way RelationshipsConnect more than two entity setsUseful for more complex relationshipsRelationship ConstraintsOne-One:Entity of first set can connect to just one entity in second set, and vice versaEntity Set 1 Entity Set 2Verbs11Relationship ConstraintsOne-Many:Entity of first set can connect to just one entity in second setEntity of second set can connect to many in firstUse N for arbitrary number greater than 1,or put specific numberEntity Set 1 Entity Set 2VerbsN1Relationship ConstraintsMany-Many:An entity of either set can connect to many entities in the other setUse N and M for arbitrary number greater than 1,or put specific number (or omit)Entity Set 1 Entity Set 2VerbsNMRelationship ConstraintsNumbers on lines indicate maximumsCan also show that every entity must participateEvery entity of first set must be related to at least one entity of the second setEntity Set 1 Entity Set 2VerbsMNAttributes on RelationshipsSometimes attribute is property of relationship instead of either entityEntity Set 1 Entity Set 2VerbsAttributeRecursive RelationshipsWhen an entity set is related to itselfLabel edges with rolesConsider “Cousin Of”SymmetricalNo clear role namesPersonMarrieswifehusbandSubclassesSubclass = fewer entitiesHave more propertiesEntity of subclass set is alsoin superclass setHas all attributes of both setsSuperclassEntity SetSubclassEntity SetisaKeysLet us tell entities apartThe key for an entity set is a subset of the attributes for that entity set, such that no two entities agree on all the attributesShowing KeysEach entity must have a keyShown by underlining names of key attributesFor subclass hierarchies:Only the root entity set has a keyAll entities in hierarchy use that keyWeak Entity SetsWhen even all the attributes aren’t enough for a key…Use a many-one relationship to “borrow” an additional attribute for the keyExample Weak Entity SetConsider football players in a fantasy leagueIs Name a key?Is Number a key?Need Number + Team Played OnPlayer TeamPlaysonname namenumber1Practice with E-R DiagramsIn groups of 2–3 work on HW Problem 3.21On back of
View Full Document