Major Issues from LDBD
37 Pages9058 Words269 Views
Added on 2019-10-18
Major Issues from LDBD
Added on 2019-10-18
ShareRelated Documents
M4 Lecture Notes: Logical DB DesignM4 Lecture Notes OrientationThis 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 peruse some of the M4 readings to understand how elaborate it can be in case of UML etc.),b) Specification of constraints (RI and rules), and c) Quality Assurance or Validation techniques. Note that order of topic introduction may be different from the order of a practical LDBD process execution.At the start of this module please review CB Chapter 17 LDBD (and Chapter 16 section 3 again). The scope of logical database design covers specification of constraints (RI and rules) but their importance in practice warrants more elaborate treatment then shown within the CB textbook. The M4 Lecture Notes will provide those 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 and logical data models). Techniques are presented with case studies. Having in mind that traditional QA technique was Design Review (feedback on models and comments in discussions are our replacement for live interactive design review sessions).-Model Validation: see example Movie Mega-TheateroFitness test oSimplified Sharp’s method-Referential Integrity options: see examples Racecourse, Lost Baggage, QSALE , and for self-review of RI option specification Maintenance View, and Crimes case 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) has major 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 available developers a review of the design proceeds in a more or less defined manner with an aim of improving a design by pointing out potential problems, hard to understand elements, and suggesting alternatives. Typical measure used with review is defect density (DD). The defect density is number of defects discovered divided by number of distinct elements in a solution (usually expressed as a % of defect). For example, if we assume that in a simple model for a Used Book case study (below) the only error is using a complete instead of incomplete generalization (to allow for possible extensions with return and other transactions types) 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 under 25%; 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. Design ReviewC2. Design ReviewB2. Normalization1A. NormalizationModelD1. Fitness TestD2. Simplified Sharp’s Step 1 ValidationModelQuestionsModelModelClassification: Traditional, developers driven, alternatives A, B, and C vs. Agile, user driven D and EC1. Preliminary (internal) Design ReviewE1. Preliminary (internal) Design ReviewE2. Fitness TestE3. Simplified Sharp’s Step 1 ValidationModelQuestionsE4. Rules ReviewFigure 2 Model validation process, alternatives and classification2.Model Validation (Agile Validation)Using elements of the Sharp’s Method for In terms of its aim the Sharp’s method is closest to normalization, it addresses attribute placement in any given table and does not depend on modeling notation. Method is based on obtaining experts acceptance for systematic data samples implied by the model- where any case of unaccepted data sample represent a defectto be investigated. The method requires an expert with knowledge of domain semantics but does not require knowledge of the normalization vocabulary. In our experience the method can be sufficiently explained and its understanding tested in one class hour. To illustrate how effective method is examples are simple but could easily 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 covers performance and records a schedule for a small town single mega-theater operation. Initial set of interesting questions by the user (a sample of four) is given as follows:1)Daily gross for all movies shown2)Daily schedule of showings by movie, theater, and starting time for each showing3)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 the Figure 3 was considered feasible (developers claimed answers touser queries reachable, i.e. access path shown on the model for each question). The next step was performed by a user following sentence variation step from the Sharp’s method in a manner shown above. The Expert column was populated by a user. Note that figure 3, for convenience of showing table content clearly uses IDEF1Xnotation. We selected a Showtime entity table for review, as follows:MovieTimeTheaterDateModelExpertm1t1r1x1YYothert1r1x1YN*m1otherr1x1YYm1t1otherx1YYm1t1r1otherYYAfter validation by a user, developer’s job is to analyze N-case(s) and to fix model imperfection(s). The N case above can be read as: different movies could be shown at some starting time <t1> in the theater <r1> on the date <x1>, and that is not allowed. Figure below contrast models before and after the improvement triggered by validation.Figure 3 Discussion Models:MOVIE_COPYCopyNumbername (FK)Added to the model in 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 copies model 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 model in 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 copies model 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 we will review several examples:a)The Racecourse Case Studyw hy not useSET NULL?Discuss RI options as if there is such database 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 appropriate RI options. In the example above the default option for optional foreign keys SET NULL is not useful as it will eliminate ancestors with their winning statistics thus lowering the value of this database for those betting on horses progeny (on delete of a parent horse). Similarly even if a race track is closed we cannot cascade a delete risking loss of participation in formation of horse success in the race on that racetrack (loosing valuable history data), again cascade option needs to be replace with RESTRICT (NO ACTION) one on the path to participation from racetrack. Note that on a direct line between a root and the entity to be preserved it usually suffice to 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 between race_event and participation. 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 another layer of RESTRICT (in red) when at the time of earliest possible deletes on the parent (root) lower level descendants (to be protected) may not yet exists. A good example 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
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Logical Modeling and Design: Assignmentlg...
|25
|3038
|492