Develop a plan to integrate this new data warehouse with an Internet application. Include in your plan:-A description of the transformation process-A schema diagram identifying the changes needed (rev
Eric Case
DAT 390
May 13, 2019
U.S. Student Aid Database Schema and Query Recommendations
IntroductionIn 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 TablesSchool (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 PracticesI 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.