Need help.
1
U.S Student Aid Database Schema and Query Recommendations
PAGE
2
U.S Student Aid Database Schema and Query Recommendations
U.S. Student Aid Database Schema and Query Recommendations
Student Name
DAT/390
July 22, 2019
Ashish Gulati
The following tables are for the U.S Aid Student Database:
Create Table GainfulEmployment
|
CREATE TABLE [dbo].[GainfulEmployment] ( [OPEID] INT NOT NULL PRIMARY KEY, [InstitutionName] NCHAR(50) NULL, [Street] INT NULL, [Address] NCHAR(50) NULL, [City] NCHAR(10) NULL, [State] NCHAR(10) NULL, [ZipCode ] NCHAR(10) NULL, [InstitutionType] NCHAR(10) NULL, [CIPCode] NCHAR(10) NULL, [CIPName] NCHAR(20) NULL, [CredentialLevel] NCHAR(10) NULL, [Repayment Rate] INT NULL, [Repeymeant Rate Numerator] INT NULL, [Repayment Rate Denominator] NTEXT NULL, [Debt to Earning Annual Rate] NCHAR(10) NULL, [Debt to Earning Annual Rate Numerator] NCHAR(10) NULL, [Debt to Earning Annual Rate Denominator] NCHAR(10) NULL, [Debt to Earning Discretionary Rate] NCHAR(10) NULL, [Debt to Earning Discretionary Rate Numerator] NCHAR(10) NULL, [Debt to Earning Discretionary Rate Denominator] NCHAR(10) NULL, [Median Private Loans] NCHAR(10) NULL, [Median Instittution Loans] NCHAR(10) NULL, [Median Title 4 Loans] NCHAR(10) NULL, [Median Title 4 Loans (Award Version)] NCHAR(10) NULL ) |
Create Tale FederalWorkStudy
|
CREATE TABLE [dbo].[FederalWorkStudy] ( [OPEID] INT NOT NULL PRIMARY KEY, [ $ Federal Award ] MONEY NULL, [Recipients] MONEY NULL, [Disbursements] MONEY NULL ) |
Create Table Federal SuplementEducationalGrant
|
CREATE TABLE [dbo].[FederalSupplementEducationalGrant] ( [OPEID] INT NOT NULL PRIMARY KEY, [$ Federal Award] MONEY NULL, [Recipients] MONEY NULL, [Disbursements] MONEY NULL ) |
Create Table Federal Pell Grant Program
|
CREATE TABLE [dbo].[Federal Pell Grant Program] ( [OPEID] INT NOT NULL PRIMARY KEY, [Sum of Recipients] NCHAR(10) NULL, [Sum of Disbursements] NCHAR(10) NULL ) |
Create table DL_Unsubsidized_Graduate
|
CREATE TABLE [dbo].[DL_Unsubsidized_Graduate] ( [OPEID] INT NOT NULL PRIMARY KEY, [Recipients] NCHAR(10) NULL, [Number of Loans originated] NCHAR(10) NULL, [$ of loans originated] NCHAR(10) NULL, [Number of Disbursements] NCHAR(10) NULL, [$ of Disbursements] NCHAR(10) NULL ) |
Create Table DL_Unsubsidized
|
CREATE TABLE [dbo].[DL_Unsubsidized] ( [OPEID] INT NOT NULL PRIMARY KEY, [Recipients] NCHAR(10) NULL, [Number of loans originated] NCHAR(10) NULL, [$ of Loans originated] NCHAR(10) NULL, [Number of Disbursements] NCHAR(10) NULL, [$ of Disbursements] NCHAR(10) NULL ) |
Create Table DL_Subsidized
|
CREATE TABLE [dbo].[DL_Subsidized] ( [OPEID] INT NOT NULL PRIMARY KEY, [Recipients] NCHAR(10) NULL, [number of loans originated] NCHAR(10) NULL, [$ of loans originated] NCHAR(10) NULL, [Number of Disbursements] NCHAR(10) NULL, [$ of Disbursements] NCHAR(10) NULL ) |
Create Table DL_ParentPlus
|
CREATE TABLE [dbo].[DL_ParentPlus] ( [OPEID] INT NOT NULL PRIMARY KEY, [Recipients] NCHAR(10) NULL, [Number of Loans originated] NCHAR(10) NULL, [$ of Loans originated] NCHAR(10) NULL, [Number of Disbursements] NCHAR(10) NULL, [$ of Disbursements] NCHAR(10) NULL ) |
Create Table DL_Grad_Plus
|
CREATE TABLE [dbo].[DL_Grad_Plus] ( [OPEID] INT NOT NULL PRIMARY KEY, [Recipients] NCHAR(10) NULL, [Number of Loans originated] NCHAR(10) NULL, [$ of Loans originated] NCHAR(10) NULL, [Number of Disbursements] NCHAR(10) NULL, [$ of Disbursements] NCHAR(10) NULL ) |
Create Table CampusBased
|
CREATE TABLE [dbo].[CampusBased] ( [OPEID] INT NOT NULL PRIMARY KEY, [School] NCHAR(50) NULL, [State] NCHAR(10) NULL, [ZipCode] NCHAR(10) NULL, [SchoolType] NCHAR(10) NULL ) |
Create Table Teach Program
|
CREATE TABLE [dbo].[Teach Program] ( [OPEID] INT NOT NULL PRIMARY KEY, [Sum of Recipients] NCHAR(10) NULL, [Sum of Disbursements] NCHAR(10) NULL ) |
Create Table School
|
CREATE TABLE [dbo].[School] ( [SchoolCode] INT NOT NULL PRIMARY KEY, [SchoolName] NCHAR(50) NULL, [Address] NCHAR(50) NULL, [City] NCHAR(20) NULL, [StateCode] NCHAR(2) NULL, [ZipCode] INT NULL, [Province] NCHAR(20) NULL, [Country] NCHAR(20) NULL, [PostalCode] INT NULL ) |
Create Table PerkinLoans
|
CREATE TABLE [dbo].[PerkinLoans] ( [OPEID] INT NOT NULL PRIMARY KEY, [Recipients] INT NULL, [Disbursements] INT NULL ) |
Create Table LoanVolume
|
CREATE TABLE [dbo].[LoanVolume] ( [OPEID] INT NOT NULL PRIMARY KEY, [School] NCHAR(50) NULL, [State] NCHAR(10) NULL, [Zipcode] INT NULL, [SchoolType] NCHAR(10) NULL ) |
Create Table Grants
|
CREATE TABLE [dbo].[Grants] ( [OPEID] INT NOT NULL PRIMARY KEY, [School] NCHAR(10) NULL, [State] NCHAR(10) NULL, [ZipCode] NCHAR(10) NULL, [SchoolType] NCHAR(10) NULL ) |
Screenshot for all the tables created
Best practices in creating the queries:
· I have used aliases in creating calculated fields
· I have secured and ensured that there is no duplication for unique id’s
· While the designated queries have been incorporating the relationships between them
PAGE
2