Question

Lab 1 – Databases, Schemas, and Basic Tables

For this lab we will be creating a small Student Loan Database.

Make sure to open your screenshot word document. Include the required screenshots of your code in the document.

Database: Create a new database: StudentLoan_LastName.
Schemas: Create the following schemas. Make sure to screenshot and run your code:

1. Student
2. Guarantor 3. Institution 4. Activity
5. Loan
6. Lender

Tables: First complete the word document for designing the tables like we did in the slides. Use the information below to complete the file.

Note the schemas the tables are associated with:

Schema. Table Name

Student tblStudent

Guarantor.   tblGuarantor

Institution.   tblInstitution

Activity. tblActivityReport

Loan.   tblLoan tblLoanStatement

Lender. tblLender

Finally, write and screenshot the code for creating the following tables in your database. Make sure to run the code to create the tables:

tblStudent –include the student’s names, date of birth, and gpa.

tblGuarantor – include the guarantor’s name, address, city, state, zip, and phone

number.

tblInstitution – include the institution’s name, address, city, state, zip, and phone number.

tblActivityReport - include the name of the activity, begin date for activity, end date for activity, and how much the activity costs.

tblLoan – include the name of the loan type, the amount of the loan, the begin date of the loan, the id of the guarantor of the loan, the id of the lender for the loan, and the id of the student who took out the loan.

tblLoanStatement – include the amount of the loan, the id of the student who took out the loan, the amount of money the loan is for, the graduation date of the student, and the monthly payment amount for the loan.

tblLender – include the lender’s name, address, city, state, zip, and phone number.

Backup the database file, zip it.

Submit the following:

1.the zip of your database file

2.the table creation word document

3.the code screenshots document

Grading Rubric:

Grade item   Points

Create correct database 10pts

Create all 6 schemas   20pts

Create and associate the correct tables with their schemas. Make sure to use correct data types. 30pts

Screenshot Document   15pts

Table Creation Document   25pts

Total   100pts

Deductions

Exact Same Code/Documents as Another Student   -10pts per infraction or 0 on assignment

Word Documents Not Named Correctly   -5pts per document

Incorrect database name used   -10pts

Formatting changed in Word Documents -10pts per document

Using your own Word Document instead of the one provided   No points for docs

Database Backup/Word document Incorrect/Unreadable -100pts

Answer 1

Tables creation:Queries

• create table tblStudent(student_Name char(20),student_ID integer primary key,Student_DOB DATE,Student_GPA float(2));
• create table tblGuarantor(Guarantor_Name char(20),Guarontar_ID integer primary key,Address varchar(50),city char(20),state char(10),zip integer,phoneNumber integer);
• create table tblInstitution(Institution_Name char(20),Address varchar(50),city char(20),state char(10),zip integer,phoneNumber integer);
• create table tblActivityReport(Activity_Name char(20),Activity_Bigin_Date DATE,Activity_End_Date DATE,Activity_Cost float(10));
• create table tblLender(Lender_Name char(20),Lendor_ID integer primary key,Address varchar(50),city char(20),state char(10),zip integer,phoneNumber integer);
• create table tblLoan(Loan_Type char(20),Loan_Amount integer,Loan_Begin_Date DATE,Guarantor_ID integer references tblGuarantor(Guarantor_ID),Lendor_ID integer references tblLender(Lendor_ID),Student_ID integer references tblStudent (Student_ID));
• create table tblLoanStatement(Loan_Amount float(10),Student_ID integer references tblStudent (Student_ID),Graduation_Year_of_Std YEAR,Monthly_Payment integer);

Screenshots: