270 likes | 496 Views
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
E N D
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 • Replace existing information system
Loan Processing Workflow Originate loan Approve loan Separate from school Apply Send bill Make payment Miss payments Claim
Major Documents • Loan origination form • Disclosure letter • Statement of account • Loan activity report
Schema Conversion Rules • Entity type rule • 1-M relationship rule • M-N relationship rule • Identification dependency rule
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
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
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
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
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
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.