Chapter 13. Outline. Case Overview. Database Design for Student Loan Limited

McGraw-Hill/Irwin Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.

Chapter 13

Database Design for Student Loan Limited

13-2

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

13-4

Loan Processing Workflow

Apply

Approve loan

Originate

loan _{Separate from}

school Send bill Make payment Miss payments Claim 13-5

Major Documents

Loan origination form

Disclosure letter

Statement of account

Loan activity report

Loan Origination Form

Parent Node LoanNo

ProcDate, DisbMeth, DisbBank, RouteNo, AcctNo, DateAuth NoteValue, Subsidized, Rate, StdNo Name, Address, City, State Zip, DOB, ExpGradMonth, ExpGradYear, Phone, GuarantorNo, Guarantor, Name, LenderNo, Lender Name, InstID, Institution Name, Address, City, State, Zip

Child Node Date Amount OrigFee GuarFee

13-7

Loan Origination ERD

Student StdNo Loan LoanNo Lender LenderNo Guarantor GuarantorNo Institution InstitutionNo DisburseLine Date LoanNo GivenTo Authorizes Guarantees Uses Sent 13-8

Disclosure Letter Structure

Parent Node LoanNo, DateSent StdName, RepayDate, AmtBorrowed, NumPayments, IntRate, EstFinCharge FirstPayDate, MonthPayment, LastPayDate

Disclosure Letter ERD

Student StdNo Loan LoanNo Lender LenderNo Guarantor GuarantorNo Institution InstitutionNo DisburseLine Date LoanNo GivenTo Authorizes Guarantees Uses Sent DiscLetter LetterNo Includes

13-10

Statement Structure

Parent Node

StatementNo Date, StudentNo, Name, Address, City, State Zip, DueDate, AmountEnclosed, PayMethod, AmountDue Child Node LoanNo Balance Rate 13-11

Statement ERD

Student StdNo Loan LoanNo Lender LenderNo Guarantor GuarantorNo Institution InstitutionNo DisburseLine Date LoanNo GivenTo Authorizes Guarantees Uses Sent DiscLetter LetterNo Includes Statement StatementNo StatementLoan StatementNo LoanNo AppliedTo StatementsApplied

Loan Activity Structure

Parent Node

StudentNo Date, Name, Address, City,

State, Zip

Child Node

LoanNo BegBalance, EndBalance,

13-13

Loan Activity ERD

Student StdNo Loan LoanNo Lender LenderNo Guarantor GuarantorNo Institution InstitutionNo DisburseLine Date LoanNo GivenTo Authorizes Guarantees Uses Sent DiscLetter LetterNo Includes Statement StatementNo StatementLoan StatementNo LoanNo AppliedTo StatementsApplied LoanActivity ReportNo MailedTo 13-14

Schema Conversion Rules

Entity type rule

1-M relationship rule

M-N relationship rule

Identification dependency rule

Schema Conversion Result

Student StdNo Statement StatementNo StdNo LoanActivity ReportNo StdNo DiscLetter LetterNo LoanNo Guarantor GuarantorNo Lender LenderNo Institution InstID DisburseLine DateSent LoanNo Applied StatementNo LoanNo Loan LoanNo StdNo GuarantorNo InstID LenderNo 1 1 1 1 1 1 1 1 1 8 8 8 8 8 8 8 8 8 1 8

13-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

13-17

Normalized Table Design

Student StdNo Zip Statement StatementNo StdNo LoanActivity ReportNo StdNo Discletter LetterNo LoanNo Guarantor GuarantorNo Lender LenderNo Bank RouteNo DisburseLine DateSent LoanNo Applied StatementNo LoanNo Loan LoanNo StdNo GuarantorNo InstID LenderNo RouteNo 1 1 1 1 1 1 1 1 1 8 8 8 8 8 8 8 8 8 1 8 Institution InstID Zip ZipCode Zip 1 8 8 1 1 8

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

13-19

Application Profiles

A p plica tion T a bles C o nd ition s

V e rify da ta (for loa n o rigina tio n) Stu de nt, Len de r, Institution, G u aran to r S td N o = $ X ; L en de rN o = $Y ; InstID = $ Z ; G u aran torN o = $W C re ate lo an (fo r loa n o rigina tio n) Lo an , D isb urseLin e 1 ro w inserted in L oa n; m u ltiple ro ws inse rted in D isbu rseL in e C re ate stud en t (fo r loa n o rigina tio n) Stu de nt 1 ro w inserted 13-20

Application Frequencies

Application Frequency Comments

Verify data 100,000/year Most activity at

beginning of term

Create loan 100,000/year Most activity at

beginning of term

Create student 20,000/year Most students are repeat

Create disclosure letter

50,000/year Spread evenly throughout year Display disclosure

letter

5,000/year Spread evenly

throughout year

Table Profiles

T ab le N u m b e r of R ow s C o lu m n (N u m b er of U n iq u e V alu e s) S tu de nt 100 ,00 0 S tdN o (P K ), N a m e (9 9,0 00), A d dress (90,0 00), C ity(1,000 ), Z ip (1 ,00 0), D O B (365 ), E xpG rad M onth (12 ), E xpG ra dY ea r (10) L oa n 300 ,00 0 Lo anN o (P K ), P roc D a te (3 50),

D isbM e thod (3), D isb B a nk (3 ,00 0), R o ute N o (3,0 00), A c ctN o (90,000), D a teA uth (350 ), N ote V a lue (1,000), S ubsidize d (2), R ate (1,000 ), B ala nc e (10,0 00), S tdN o (100 ,000 ), InstID (2,00 0), G u aran torN o (10 0), Le nde rN o (2,00 0)

13-22

Index Selections

Column Index Kind Rule

Student.StdNo Clustering 1 Student.Name Nonclustering 3 Statement.StatementNo Clustering 1 DiscLetter.LetterNo Clustering 1 Loan.LoanNo Clustering 1 Institution.InstID Clustering 1 13-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

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

13-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

13-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.