Database Management System - Chapter 13: Database Design for Student Loan Limited

Chapter 13Database Design for Student Loan LimitedOutline Case descriptionConceptual data modelingLogical database designPhysical database designCase OverviewGuaranteed Student LoansEnvironmentStudentLenderService ProviderGuarantorDepartment of EducationReplace existing information system Loan Processing WorkflowApplyApproveloanOriginateloanSeparate fromschoolSendbillMakepaymentMisspaymentsClaimMajor DocumentsLoan origination formDisclosure letterStatement of accountLoan activity report Loan Origination Form Loan Origination ERD Disclosure Letter Structure Disclosure Letter ERD Statement Structure Statement ERD Loan Activity Structure Loan Activity ERDSchema Conversion RulesEntity type rule1-M relationship ruleM-N relationship ruleIdentification dependency rule Schema Conversion ResultNormalizationStudent not in BCNF because of Zip FDZip  StateLoan not in BCNF because of RouteNo FDRouteNo  DisBankInstitution not in BCNF because of Zip FDsZip  City, State Normalized Table DesignPhysical Database DesignApplication profiles: tables, conditions, parameter values, and frequenciesTable profiles: estimated number of rows and distribution of valuesIndex selection: clustering and non clustering indexesDerived data and denomalizationOther implementation considerations Application Profiles Application Frequencies Table Profiles Index SelectionsDerived Data and Denormalization DecisionsDerived dataLoan.NoteValueDiscLetter and LoanActivity tables have derived data in the image columns.DenormalizationLenderNo and Lender.Name in the Loan table violates BCNF, but it may reduce joins between the Loan and the Lender tables Other Implementation IssuesProcessing volumes in a new system can be much larger than in the old systemPoor quality of old data may cause many rejections in the conversion processSize of image dataApplication Development NotesProvides cross check on quality of database designData requirements for forms and reportsLoan origination formLoan activity reportDerived data maintenance: AFTER ROW trigger for Loan.BalanceSummaryCase includes a significant subset of student loan processing.Solution depicts models for database development phases.Next step: database development for a real organizationOpen-ended, unclear, and changing requirements are challenges.