1 / 26

Chapter 13

Chapter 13. Database Design for Student Loan Limited. Outline. Case description Conceptual data modeling Logical database design Physical database design. Case Overview. Guaranteed Student Loans Environment Student Lender Service Provider Guarantor Department of Education

teryl
Download Presentation

Chapter 13

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 13 Database Design for Student Loan Limited

  2. Outline • Case description • Conceptual data modeling • Logical database design • Physical database design

  3. Case Overview • Guaranteed Student Loans • Environment • Student • Lender • Service Provider • Guarantor • Department of Education • Replace existing information system

  4. Loan Processing Workflow Originate loan Approve loan Separate from school Apply Send bill Make payment Miss payments Claim

  5. Major Documents • Loan origination form • Disclosure letter • Statement of account • Loan activity report

  6. Loan Origination Form

  7. Loan Origination ERD

  8. Disclosure Letter Structure

  9. Disclosure Letter ERD

  10. Statement Structure

  11. Statement ERD

  12. Loan Activity Structure

  13. Loan Activity ERD

  14. Schema Conversion Rules • Entity type rule • 1-M relationship rule • M-N relationship rule • Identification dependency rule

  15. Schema Conversion Result

  16. Normalization • Student not in BCNF because of Zip FD • Zip  State • Loan not in BCNF because of RouteNo FD • RouteNo  DisBank • Institution not in BCNF because of Zip FDs • Zip  City, State

  17. Normalized Table Design

  18. Physical Database Design • Application profiles: tables, conditions, parameter values, and frequencies • Table profiles: estimated number of rows and distribution of values • Index selection: clustering and non clustering indexes • Derived data and denomalization • Other implementation considerations

  19. Application Profiles

  20. Application Frequencies

  21. Table Profiles

  22. Index Selections

  23. Derived Data and Denormalization Decisions • Derived data • Loan.NoteValue • DiscLetter and LoanActivity tables have derived data in the image columns. • Denormalization • LenderNo and Lender.Name in the Loan table violates BCNF, but it may reduce joins between the Loan and the Lender tables

  24. Other Implementation Issues • Processing volumes in a new system can be much larger than in the old system • Poor quality of old data may cause many rejections in the conversion process • Size of image data

  25. Application Development Notes • Provides cross check on quality of database design • Data requirements for forms and reports • Loan origination form • Loan activity report • Derived data maintenance: AFTER ROW trigger for Loan.Balance

  26. Summary • Case includes a significant subset of student loan processing. • Solution depicts models for database development phases. • Next step: database development for a real organization • Open-ended, unclear, and changing requirements are challenges.

More Related