The project sponsors of the U.S. Student Aid Data project want you to participate in the project debrief meeting. You are to provide information around the methodology and practices you used to develo

Eric Case

DAT 390

May 13, 2019

U.S. Student Aid Database Schema and Query Recommendations

Introduction

In this paper, we will look at the database tables of the “Student aid database”. We will develop the SQL queries needed and populate the tables with the data provided. We will also look at the appropriate strategy to optimize and incorporate best practices in to the SQL.

Physical Database Tables

School (SchoolCode PK, SchoolName,Address, City, State, Zip, Province, Country, PostCode, SchoolType)

Loans (LoanId PK, LoanType, SchoolCode FK)

DirectLoans (DLId PK, LoanType, LoanId FK)

DirectLoanSubsidized (DLSubsidizedId PK, Recipients, NoOfLoans, AmountOfLoans, NoOfDisbursement, AmountOfDisbursement, DLId FK)

GainfulEmployement (GEId PK, CIPCode, CIPName, CredentialLevel, RepaymentRate, RepaymentNumerator, RepaymentDenominator, SchoolCode FK)

DirectLoanUnsubsidized (DLUnsubsidizedId PK, Recipients, NoOfLoans, AmountOfLoans, NoOfDisbursement, AmountOfDisbursement, DLId FK)

DLParentPlus (DLParentPlusId PK, Recipients, NoOfLoans, AmountOfLoans, NoOfDisbursement, DLId FK)

DLGradPlus (DLGradPlusId PK, Recipients, NoOfLoans, AmountOfLoans, NoOfDisbursement, AmountOfDisbursement, DLId FK)

Grants (GrantId PK, GrantProgram, SchoolCode FK)

FFEL (FFELId PK, Recipients, NoOfLoans, AmountOfLoans, NoOfDisbursement, FFELLoanType, LoanId FK)

FederalPellGrant (FederalPellGrantId PK, SumOfRecipients, SumOfDisbursement, GrantId FK)

TeachGrant (TeachGrantId PK, SumOfRecipients, SumOfDisbursements, GrantId FK)

IraqAfghanGrant (IraqAfghanGrantId PK, SumOfRecipients, SumOfDisbursements, GrantId FK)

KEY

The PK means Primary key

The FK means Foreign Key

Best Practices

I have used primary keys to ensure there is no duplication of records

I have used foreign keys so as foster relationship among the tables

I have used aliases in creating the SQL queries

I have ensured that you cannot insert an empty data by using the SQL command NOT NULL while creating the attributes.