Trusted by +2 million users,
1000+ happy students everyday
1000+ happy students everyday
Showing pages 1 to 8 of 37 pages
M4 Lecture Notes: Logical DB DesignM4 Lecture NotesOrientationThis module deals with the three major issues from the LDBD scope, namely:a) Transforming CDBD into LDBD model as RM (trivial in case of IDEF1x, but perusesome of the M4 readings to understand how elaborate it can be in case of UML etc.),b) Specification of constraints (RI and rules), andc) Quality Assurance or Validation techniques.Note that order of topic introduction may be different from the order of a practicalLDBD process execution.At the start of this module please review CB Chapter 17 LDBD (and Chapter 16section 3 again). The scope of logical database design covers specification ofconstraints (RI and rules) but their importance in practice warrants more elaboratetreatment then shown within the CB textbook. The M4 Lecture Notes will providethose missing details of ‘How to’specify (design) constraints.On the other hand traditional use of Normalization as a LDDB design (technique)option is largely abandoned in practice. The Normalization, see also CB Chapters 14and 15 has its uses (limited as it may be), as a QA technique is treated later on.This lecture also elaborates on interrelatedness of key techniques recommended foruse in quality assurance (for logical data base design and for the conceptual andlogical data models). Techniques are presented with case studies. Having in mindthat traditional QA technique was Design Review (feedback on models andcomments in discussions are our replacement for live interactive design reviewsessions).-Model Validation: see exampleMovie Mega-TheateroFitness testoSimplified Sharp’s method-Referential Integrity options: see examplesRacecourse, Lost Baggage,QSALE, and for self-review of RI option specificationMaintenance View,andCrimescase studies-Rule specification:Widget Case-Logical Database Design: (additional examples in readings)-Translate a Logical Data Model to a RDBMS (Oracle):Used BooksNote that model validation technique using transaction paths (from CB 16.3) hasmajor relevancy with PDBD and will be covered in Module 5.1.Data Model ValidationTraditional Design Reviews
M4 Lecture Notes: Logical DB DesignIn a dialog between developers and involved in design and other availabledevelopers a review of the design proceeds in a more or less defined manner withan aim of improving a design by pointing out potential problems, hard tounderstand elements, and suggesting alternatives. Typical measure used withreview is defect density (DD). The defect density is number of defects discovereddivided by number of distinct elements in a solution (usually expressed as a % ofdefect). For example, if we assume that in a simple model for a Used Book casestudy (below) the only error is using a complete instead of incompletegeneralization (to allow for possible extensions with return and other transactionstypes) ad we ignore attributes the number of concepts/elements in a solution is 12(six entities and six relationships), with one error as stated then DD is 1/12 i.e. 8%.Typical DD for student work are 25-40% and for professional work products under25%; in cases of DD over a threshold say 20% additional reviews may be scheduled.CUSTOMERCust_NumberAddressObservationUsed Book Store- draftlBOOKBook_NumberTitleLanguagePublisherAuthorsFormatCOPYBook_Number (FK)Copy_NumberEst_valueDescriptionTR_typeBUYTransaction_Number (FK)Price_PaidExtra_costSELLTransaction_Number (FK)Sold_forTotalShippingDataalternative_shipping (FK)TRANSACTIONTransaction_NumberBook_Number (FK)Copy_Number (FK)DateTRTR_typeCust_Number (FK)Figure 1 Used Books Case
M4 Lecture Notes: Logical DB DesignB1.DesignReviewC2.DesignReviewB2.Normalization1A.NormalizationModelD1.FitnessTestD2.SimplifiedSharp’s Step1ValidationModelQuestionsModelModelClassification:Traditional,developers driven,alternatives A,B,and C vs.Agile,user driven D and EC1.Preliminary(internal)DesignReviewE1.Preliminary(internal)DesignReviewE2.FitnessTestE3.SimplifiedSharp’s Step1ValidationModelQuestionsE4.RulesReviewFigure 2 Model validation process, alternatives and classification2.Model Validation(Agile Validation)Using elements of the Sharp’s Method forIn terms of its aim the Sharp’s method is closest to normalization, it addressesattributeplacement in any given table and does not depend on modeling notation.Method is based on obtaining experts acceptance for systematic data samplesimplied by the model- whereany case of unaccepted data sample represent a defectto be investigated. The methodrequires an expert with knowledge of domainsemantics but does not require knowledge ofthe normalization vocabulary. In ourexperience the method can be sufficiently explainedand its understanding tested inone class hour. To illustrate how effective method is examples are simple but couldeasily be modeled, let us try one from the Exercises.
M4 Lecture Notes: Logical DB DesignsemesterSemYearvalidation illustrationonlyenrolmentSemYear (FK)Student Id (FK)Section Number (FK)course id (FK)letter gradeinstructorinstructor idNameSECTIONSemYear (FK)Section Numbercourse id (FK)instructor id (FK)coursecourse idCatalog DescriptionSTUDENTStudent IdNameEnrolmentStudentCourseSemesterSectionGradeModelExpert1DBF07S1AYYotherDBF07S1AYY1otherF07S1AYY1DBotherS1AYY1DBF07otherAYN*N* means- that student can’t enroll in the same semester in two sections of the same course. Can this rule berepresented structurally (in a model) or need to be enforced programmatically?Solution is left as an exercise for students (to use data modeling more carefully or trynormalization).The next example is a model that has to pass preliminary (feasibility) review.
M4 Lecture Notes: Logical DB DesignThe model coversperformance and records a schedule for a small town singlemega-theater operation.Initial set of interesting questions by the user (a sampleof four) is given as follows:1)Daily gross for all movies shown2)Daily schedule of showings by movie, theater, and starting time for eachshowing3)Each week, the movies with a bottom three minimal gross revenue total4)Daily number of showings with capacity use under 50%.The first step of the approach is a preliminary review by developers.Model on top of theFigure 3 was considered feasible (developers claimed answers touser queries reachable,i.e. access path shown on the model for each question). Thenext step was performed by auser following sentence variation step from theSharp’s method in a manner shownabove. The Expert column was populated by auser. Note that figure 3, for convenienceof showing table content clearly uses IDEF1Xnotation. We selected aShowtimeentitytable for review, as follows:MovieTimeTheaterDateModelExpertm1t1r1x1YYothert1r1x1YN*m1otherr1x1YYm1t1otherx1YYm1t1r1otherYYAfter validation by a user, developer’s job is to analyze N-case(s) and to fix modelimperfection(s). The N case above can be read as:different moviescould beshown atsome starting time <t1> in the theater <r1> on the date <x1>, and thatis not allowed.Figure below contrast models before and after the improvementtriggered by validation.Figure 3 Discussion Models:MOVIE_COPYCopyNumbername (FK)Added to the modelin the last momentM_COPYCopy-codem# (FK)MOVIEm#namedescriptionstars-ratingrating-restictionDATEDateDayTHEATERtheater#capacitySHOWTIMEtheater# (FK)Date (FK)starting-timetickets-soldCopy-code (FK)After:Before:SHOWTIMEname (FK)theater# (FK)Date (FK)starting-timetickets-soldTHEATERtheater#capacityDATEDateDayMOVIEnamedescriptionstars-ratingrating-restictionWithout copiesmodel will haveto make Movie as FK onlyMOVIEnamedescriptionstars-ratingrating-restictionDATEDateDayTHEATERtheater#capacitySHOWTIMEtheater# (FK)Date (FK)starting-timetickets-soldCopyNumber (FK)
M4 Lecture Notes: Logical DB DesignMOVIE_COPYCopyNumbername (FK)Added to the modelin the last momentM_COPYCopy-codem# (FK)MOVIEm#namedescriptionstars-ratingrating-restictionDATEDateDayTHEATERtheater#capacitySHOWTIMEtheater# (FK)Date (FK)starting-timetickets-soldCopy-code (FK)After:Before:SHOWTIMEname (FK)theater# (FK)Date (FK)starting-timetickets-soldTHEATERtheater#capacityDATEDateDayMOVIEnamedescriptionstars-ratingrating-restictionWithout copiesmodel will haveto make Movie as FK onlyMOVIEnamedescriptionstars-ratingrating-restictionDATEDateDayTHEATERtheater#capacitySHOWTIMEtheater# (FK)Date (FK)starting-timetickets-soldCopyNumber (FK)For additional examples see exercises and additional examples and exercises on a CD.
M4 Lecture Notes: Logical DB Design2.Referential IntegritiesIn the M11 Lecture slides you have seen the syntax and available options, now wewill review several examples:a)The Racecourse Case Studyw hy not useSET NULL?Discuss RI options as if there is suchdatabase for betting in Las VegasPARTICIPATIONRacetrack_Name (FK)Date_Race (FK)Event (FK)Horse_ID (FK)JockeyStatusFinalPositionStarting_NumberHandicapRACE_EVENTEventRacetrack_Name (FK)Date_RaceOffical_ObserverRACETRACKRacetrack_NameRTLocationHORSEHorse_IDHorse_NameBirthdateSIR (FK)DAME (FK)Ow nerStable_LocationAskingPrice_LastThe first case illustrates importance of semantics of the case for using appropriateRI options. In the example above the default option for optional foreign keys SETNULL is not useful as it will eliminate ancestors with their winning statistics thuslowering the value of this database for those betting on horses progeny (on deleteof a parent horse). Similarly even if a race track is closed we cannot cascade adelete risking loss of participation in formation of horse success in the race on thatracetrack (loosing valuable history data), again cascade option needs to be replacewith RESTRICT (NO ACTION) one on the path to participation from racetrack. Notethat on a direct line between a root and the entity to be preserved it usually sufficeto place a one RESTRICT (typically as close as possible in order to minimize impact
M4 Lecture Notes: Logical DB Designon the rest of the DB)– in the above example betweenrace_eventandparticipation. All other FK relationships can be set to CASCADE.b)Lost Baggage Case StudyThe point of this case is to illustrate a need to protect future instances with anotherlayer of RESTRICT (in red) when at the time of earliest possible deletes on theparent (root) lower level descendants (to be protected) may not yet exists. A goodexample is lost baggage that is not yet reported at the time of a canceled flight.contantain /described contentchecked foraccompany ingExample- Selection ofReferential Intergrity OptionsLost_IndicatorREPORTED_LOST_ITEMBagage_Bar_Code_Number (FK)Item_NumberItem_DescriptionLOST_BAGAGEBagage_Bar_Code_Number (FK)Asking_ValueStatus_Not_FoundACTUAL_FLIGHTFLight_NumberFlight_DatetimeAircraf t_AssignedEtcBAGAGEBagage_Bar_Code_NumberTicket_Number (FK)Lost_IndicatorFLight_Number (FK)Flight_Datetime (FK)RESERVATIONTicket_NumberPassenger_NameSeat_NumberPrice_Category