Overview of Database SystemsProjectDatabase SystemsCreating a DatabaseQuerying DatabasesOther Database TopicsEntity-Relationship ModelEntities and AttributesEntity Sets and AttributesRelationshipsValues of RelationshipsMulti-Way RelationshipsRelationship TypesSlide 14Slide 15Diagrams of RelationshipsAttributes on RelationshipsSlide 18Slide 19RolesSubclassSlide 22KeysKey for multiple attributesSlide 25Slide 26Weak entity setsDesign TechniquesSlide 29Slide 30Slide 31Slide 32Overview of Database SystemsCPSC 315 – Programming StudioSpring 2009Team Project 1, Lecture 1ProjectYour first project (next week) will involve putting together a very basic database systemThere will be a few lectures to give you an overview of database systemsThis is nowhere close to what you would get in a full database courseSlides adapted from Jennifer Welch (some of hers were from Jeffrey Ullman)Database SystemsSystems designed to manage very large amounts of data, and to query that data to pull out useful informationOften, key considerations include:EfficiencyReliability Ease of access (querying, distributed)Creating a DatabaseA database schema determines what will be represented in the databaseThis should be tightly controlled by a database managerSpecified through a data definition languageQuerying DatabasesOnce database has been populated, users can query the dataA data manipulation language controls how the user can specify queries, (and thus what types of queries are allowed)SQL is probably the most well-knownOther Database Topics“Real” database courses include lots of other things that we’ll be ignoring hereMore complete theory behind designQuery optimizationEfficient storageProcessing Transactions – grouped queries that provide atomic operationsScheduling, logging, recoveryEntity-Relationship ModelWay of expressing (in diagrammatic form) a database designKinds of data and how they connectEasy first way to think about databasesLater, relational model describedEntities and AttributesEntities are thingsEntity sets are collections of those thingsAttributes are properties of entity setsEntity Sets and AttributesSenatorNameStatePartyYearsNameTextBillRelationshipsConnect two or more entity setsSenatorNameStatePartyYearsName TextBillSponsoredNameOrganizationLobbyistWroteContributedValues of RelationshipsThe “value” of an entity set is the entities it containsThe “value” of a relationship is a list of currently related entities (one from each entity set)Senator BillSmith Tax BillSmith Defense BillJones Tax BillMulti-Way RelationshipsE.g. Lobbyist lobbied Senator about BillSenatorNameStatePartyYearsName TextBillNameOrganizationLobbyistLobbiedRelationship TypesConsider binary relationships (two entity groups in a relationship)One-to-oneEach entity can have at most one in the other categorye.g. entity groups: Baseball player, Team relationship: Team MVPA team can only have one MVP, and a player can only be MVP for one team.Relationship TypesConsider binary relationships (two entity groups in a relationship)One-to-oneMany-to-oneEach entity of first set can go to at most one of the second sete.g. entity groups: Person, Town relationship: BornInA person can is born in only one town, but a town can have many people born thereRelationship TypesConsider binary relationships (two entity groups in a relationship)One-to-oneMany-to-oneMany-to-manyAny number from one set to the othere.g. Senators can sponsor many bills, and each bill can be sponsored by many SenatorsDiagrams of RelationshipsArrow shows “to one”Person TownBaseballPlayerTeamBorn InMVPLived InAttributes on RelationshipsCan be converted to multi-way diagramsPerson TownBorn InHospitalAttributes on RelationshipsCan be converted to multi-way diagramsPerson TownBorn InHospitalHospitalsAttributes on RelationshipsNote arrowsPerson DateInjuredHospitalHospitalsProgrammerRolesIf multiple references to same entity set, label edges by rolesStudentsTeamTesterTeam LeadSubclassFewer entities, more propertiesU.S.RepresentativeStateElected OfficialNamePartyisaDistrictSubclassEntity in multiple subclasses U.S.RepresentativeDistrictElected OfficialNameisaRepublican…isa…isaDemocratStateKeysA key is a set of attributes for an entity set such that no two entities agree on all the attributes.We must have a key for every entity setU.S.RepresentativeDistrictElected OfficialNamePartyisaFor an isahierarchy,only root canhave a key.Key for multiple attributesMust choose one set of attributesBaseball PlayerFirstNameLastNameNumberPositionNationality SalaryBirthdateTeamKey for multiple attributesMust choose one set of attributesBaseball PlayerFirstNameLastNameNumberPositionNationality SalaryBirthdateTeamKey for multiple attributesMust choose one set of attributesBaseball PlayerFirstNameLastNameNumberPositionNationality SalaryBirthdateTeamWeak entity setsNeed “help” to determine keyBaseball PlayerFirstNameLastNameNumberPositionNationality SalaryBirthdateNameTeamPlaysOnCityNote arrrow:indicates manyto one.Design TechniquesAvoid redundancySay the same thing two waysBaseball PlayerFirstNameLastNameNumberPositionTeamNameSalaryBirthdateNameTeamPlaysOnCityDesign TechniquesAvoid redundancySay the same thing two waysBaseball PlayerFirstNameLastNameNumberPositionTeamNameTeamTownBirthdateDesign TechniquesDon’t use entity set if attribute will doEntity lists should eitherHave some non-key attributeBe the “many” in a many-one/many-many relationshipBaseball PlayerNamePlaysOnNameTeamCityDesign TechniquesDon’t use entity set if attribute will doEntity lists should eitherHave some non-key attributeBe the “many” in a many-one/many-many relationshipBaseball PlayerNameTeamDesign TechniquesDon’t overuse weak entity setsUsually use unique key for each entity set (e.g. UIN, SSN, VIN)Not always possible,