1Roadmap for OO Design•RDBMS principles•Implementing OO models in RDBMS–Normalization and good models•More OO design–polymorphism, enhancing inheritance•Architecture–Alternate Data management–External control–ReuseWhat is a Relational Database?GSJones301WMSmith256KSFrench123IDFaculty Name StudentID StudentDatabase = collection of tablesTable = collections of rowsRow = set of attributes(fields)attributesStudentsDomains and KeysEach attribute defines a Domain: Set of values for attributeStudent ID={x | x is a valid student ID}Student Name={x | x is a student name}Faculty ID={x | x is faculty ID initials}A Key is a is a set of attributes that identifies a row.Key NomenclatureCandidate Key: Uniquely identifies a row.Primary Key: a Candidate Key used for main accessForeign Key: An attribute from one table that is a Primary Key in another table.Keys Example4444-355IzzoPHY1234-432LeheayCEP1212-353RoverEEC1111-355StockmanCSEFax#ChairDept001-45CEPCreweC00321-12CSEStirewaltS563200EECPotterP013100CSEMcUmberM21OfficeDeptNameID ProfForeign keyPrimary keyPrimary keyNote: referential integrityWhy is it called Relational?Recall:CBARrelationCBACcBbAacbaCBACBA××⊆××∈∈∈=××:product cross the of subset a is A Product Cross a called is that such sets Given..,),,(,,,The sets A,B,C are the domains (attribute values)bcRcaRbaBcbAaBAR=⇒∈∧∈∈∈×⊂),(),(,, if function a is relationA Potter=)01(Pfname2Operations on TablesProject operator = all rows, but selected columnsAll the RDBMS operators act on tables to produce tables.Join A, B over column x from A, y from B = (two steps):BAC×= Form 1.2. Keep only rows where x=ySelect operator = select rows based on criteria (such as key match)Join ExampleCJones301BSmith256AFrench124IDFaculty NameID Student304 EB001C100 BH201B201 EB101ALocationSectionIDFaculty 304 EB001CCJones100 BH201BBSmith201 EB101AAFrenchB.LocationB.SectionB.FacultyA.FacultyA.NameID A.Student301256124Table ATable BSupport for Existence-BasedIdentity•RDBMS generates unique ID (usuallynumber)•MS-Access uses type counter (really a longvalue)•Oracle uses type sequence•Other possibilities–Date/Time (if fine grained)–pointer connected with DB (Ingres)Implementing a Class Model•Objects have identity–Value based (keys are domain values)–Existence based (keys are made-up Ids)•New Domains–Identifier: contains existence Ids–Enumeration: discrete set of values•Class table•Assoc & Generalization table or relationImplementing 1-1ABA-Id B-Id ……. B-Id ……. Foreign key can be on either sideImplementing Many to ManyABA-Id ……….B-Id ……. A-Id B-Id3Example with Foreign KeysCityAirportserves1..*1..*Lansing01009Albany01007Atlanta01006Houston01005cityNamecityIDATL01006LAN01009TEW01008HOU01005IAH01005eairportCodcityIDLansingLANHartsfieldATLHobbyHOUnentialIntercontiIAHeairportNameairportCodCity TableServes TableAirport TableImplementing 1 to ManyABA-Id B-Id ……. B-Id ……. B-Id A-Id ……. A-Id ……. In the “0-1” case, flipping these requiresa possible null keyorExercisePersonbirthDatedeathDatenameCitationtitlenoteauthorsedits1. Use existence-based identity to design tables for classes Person and Citation.2. Design tables for associations authors and edits.Review: Implementing 1-1ABA-Id B-Id ……. B-Id ……. Foreign key can be on either sideReview: Implementing Manyto ManyABA-Id ……….B-Id ……. A-Id B-IdReview: Implementing 1 toManyABA-Id B-Id ……. B-Id ……. B-Id A-Id ……. A-Id ……. In the “0-1” case, flipping these requiresa possible null keyor4Normal Forms and OOImplementations (1)3TTh 20:10C2231 CSE3MTF 10:9Intro3101 CSE310MWF:4Math2260 CSE430MWF:11Software1470 CSE3MTF 00:8Math1260 CSECreditsTimeDescSectionCourse++If the description of CSE260 changes, have to change itin multiple places. Called modification anomaly.Primary keyScheduleNormal Forms•“Normal Form” old RDBMS concept•Concerned with redundancies anddependencies between attributes.•With Object Models we can (almost)ignore normalization•Requires good model and goodimplementation.Normal Forms and OOImplementations (2)3TTh 20:10C2231 CSE3MTF 10:9Intro3101 CSE310MWF:4Math2260 CSE430MWF:11Software1470 CSE3MTF 00:8Math1260 CSECreditsTimeDescSectionCourse++If I want to add CSE870, and have a description and credits,I still need a section and time before it can be added.This is an insertion anomaly.ScheduleNormal Forms and OOImplementations (3)3TTh 20:10C2231 CSE3MTF 10:9Intro3101 CSE310MWF:4Math2260 CSE430MWF:11Software1470 CSE3MTF 00:8Math1260 CSECreditsTimeDescSectionCourse++If no sections of CSE101 are offered, and I delete it, I losethe description and credits. This is a deletion anomaly.ScheduleNormal Forms &Correct Models3TTh 20:10C2231 CSE3MTF 10:9Intro3101 CSE310MWF:4Math2260 CSE430MWF:11Software1470 CSE3MTF 00:8Math1260 CSECreditsTimeDescSectionCourse++This table has problems because it has partial dependencies, and isnot in in second normal form. It’s also the implementation of an incorrectobject model. Description and Credits only depend on Course.ScheduleSecond Normal FormSecond normal form means there are no partial dependencies.CourseSchedule0..*Better Model3C231 CSE3Intro101 CSE4Software470 CSE3Math260 CSECreditsDescCourse++TTh 20:102231 CSEMTF 10:93101 CSEMWF 30:111470 CSEMTF 00:81260 CSETimeSectionCourseCourseSchedule5Third Normal Form (I)0012-505Freud257PSYWestW47441-555Stockman127CSEMorrisM37318-555Fui241EECJonesJ27441-555Stockman263CSEBrownB1FAXChairOfficeDeptNameID ProfThis “professor” class is modeled incorrectly. The table is insecond normal form, but has anomalies:Modification: Changing dept chair requires multiple modificationsInsertion: A new Dept requires at least one prof even if we have a chair and FAX.Why? Chair and FAX depend on only Dept. Called TransitiveDependencyKeyProfThird Normal Form (II)DeptProfessor0..*7318-555FuiEEC0012-505FreudPSY7441-555StockmanCSEFAXChairDept257PSYWestW4127CSEMorrisM3241EECJonesJ2263CSEBrownB1OfficeDeptNameID ProfNow there are no partial or transitive dependencies.DeptProfessorThings NOT To DoCompanyNameFoundedBoardboardSizeName Founded boardSizeDon’t combine optional classesABA-Id B-IdB-Id A-Id Don’t double up on foreign keysConclusions So Far….•Classes are tables•Associations are relations or tables andrelations•Proper class model produces