Wk 4 - U.S. Student Aid Data Warehouse Evaluation [due Mon] Wk 4 - U.S. Student Aid Data Warehouse Evaluation [due Mon] Assignment Content This week you will continue your work on the project to evalu

In this paper we will come up with the database of the “Student aid” project. We will also come up with the SQL needed to populate the tables with data. We will also define an appropriate strategy to optimize and incorporate best practices in to the SQL. We will also document the SQL and metadata in Microsoft Excel spreadsheet. Lastly we will capture screenshots of each query.

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

GainfulEmployment (GEId PK, CIPCode, CIPName, CredentialLevel, RepaymentRate, RepaymentNumerator, RepaymentDenominator);

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

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

TeachGrant (TeachGrantId PK, SumOfRecepients, GrantId FK)

IraqAfganGrant (IraqAfganGrantId PK, SumOfRecepients, SumOfDisbursements, GrantId FK)

Grants (GrantId PK, GrantProgram, SchoolCode FK)

FEEL (FFELId PK, Recepients, NoOfLoans, AmountOfLoans, NoOfDisbursements, FFELLoanType, LoanId FK)

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

DLParentPlus (DLParentPlusId PK, Recepients, NoOfLoans,AmountOfLoans, NoOfDisbursement, AmountOfDisbursement, DLId FK)

Loans (LoanId PK, LoanType, SchoolCode FK)

DirectLoans (DLId PK, LoanType, LoanId FK)

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

The PK means Primary Key and the FK means the Foreign Key

I have used Primary Key to uniquely identify data in a table

I have used foreign key to foster relationship among the tables

I have used NOT NULL SQL keyword to ensure that you cannot input null values

I have used aliases when getting data from the tables in order to make the statement more concise and readable

I have used well known SQL editor, that is, PHPMyAdmin

Data Warehouse Integration Process with Internet Application

The main reason of the project to evaluate higher education student aid data. In order to achieve this, the raw data needs to be organized and amalgamated into a cornerstone of information which can be used by the school. The process is known as Extract, Transform, & Load (ETL)

Extraction

In this step of extraction, data is detached from its source and enabling making data available for advance processing. The essential data as a whole is reclaimed without disrupting the performance of the source system, locking or response time in a destructive fashion. This process typically comprises a cleaning phase where the data quality is made possible through data amalgamation. The unification rules will entail managing issue like making identifiers distinctive e.g. Groupings of gender, telephone number, in addition to zip code transformations into standard form along with authentication of address fields transformed into the appropriate setup.

Transformation

The next step uses a procedure to convert the source data into related measurements as a result identical elements of measurement. This transformation phase similarly connects data from a diversity of information bases, produces combinations, substitute keys and applies authentication and fresh values.

Loading

The last phase is the loading phase which is divided into further two stage process of deactivating constraints as well as indexes prior to the starting of the load process and later activates them upon completion of the load. The objective of the load process in this stage, is frequently the database.

Integration plan

The database will use the operational integration, which entails an access as well as integration of data amongst operational applications and databases, within the same institution. This school delivery internet application, uses student data from our student database for its functioning.

The first step is to note down every detail essential in this integration assignment. Like in our case, where we are integrating a new data warehouse with an internet application, we have to understand the deployment schedule. The idea is to document the rate of recurrence at which updating the incremental data needs to be undertaken. Additionally, evaluate and document the achievements owed to data integration solution, with respects to savings in time and cost.

The next issue is scrutinizing what the school’s needs undertake to realize these tasks. This also entails evaluating the existing systems and see what needs to be updated or removed. When all those ideas are put in place, the subsequent significant step is to transfer the data into the warehouse structure as well as trail the origin and how it relates to the system.

One more vital element is setting up the security policies depending with the risk level. This may entail’s security set ups like encryption. As soon as the plan is established, the next phase is the implementation of the plan.

Delivery Application

Student data

DLGradPlus

PK DLGradPlusId

Recepients

NoOfLoans

AmountOfLoans

NoOfDisbursement

AmountOfDisbursement

FK DLId

DLParentPlus

PK DLParentPlusId

Recepients

NoOfLoans

AmountOfLoans

NoOfDisbursement

AmountOfDisbursement

FK DLId

FFEL

PK FFELId

Recepients

NoOfLoans

AmountOfLoans

NoOfDisbursement

FFELLoanType

FK LoandId

DirectLoanUnsubsidized

PK DLUnsubsidizedId

Recepients

NoOfLoans

AmountOfLoans

NoOfDisbursement

AmountOfDisbursement

FK DLId

DirectLoanSubsidized

PK DLSubsidizedId

Recepients

NoOfLoans

AmountOfLoans

NoOfDisbursement

AmountOfDisbursement

FK DLId

Grants

PK GrantId

GrantProgram

FK SchoolCode

FederalPellGrant

PK FederalPellfrantId

SumOfRecepients

SumOfDisbursement

FK GrantId

TeachGrant

PK TeachGrantId

SumOfRecipents

SumOfDisbursements

FK GrantId

IraqAfghanGrant

PK IraqAfghanGrantId

SumOfRecepients

SumOfDisbursements

FK GrantId

GainfulEmployment

PK GEId

CIPCode

CIPName

CredentialLevel

RepaymentRate

RepaymentNumerator

RepaymentDenominator

DirectLoans

PK DLId

LoanType

FK LoanId

Loans

PK LoanId

LoanType

FK SchoolCode

School

PK SchoolCode

SchoolName

Address

City

State

Zip

Province

Country

PostCode

SchoolType