Assignment 3: Database
WEIGHTING: 20%
The Tertiary Scholarship and Loans Board (“TSLB”) is the Fijian Government Institution that oversees the new tertiary scholarships and new loans scheme established in 2014. TSLB was set up by the Fijian Government under the Tertiary Scholarship and Loans Decree in 2014. The institutions establishment is in line with the Fijian Government’s 2014 budget theme of “Building a Smarter Fiji”. The Government announced a number of major initiatives that now ensure that every young person has access to tertiary education. The Prime Minister and Minister for Finance,Commodore Josaia Bainimarama stated in his 2014 budget speech that his Government has allocated funds for the provision of Tertiary Scholarships.
CIN501 Productivity Software Assignment-Fiji National University Fiji.
You are assigned by TSLB Department to design their database for new student who are going to enroll for loan scheme.Create a new database using MS Access and name the database as FNU_TELS_SXXXXXXXXX_Student Name (where SXXXXXXXXX represents your ID number)
Part 1: Create Table
Your task is to create 3 tables i.e. (Student Detail, Course Details and Payment Details)
Create the following Data Dictionary for the Table: Student Details
Create the following Data Dictionary for the Table: Course Details
Create the following Data Dictionary for the Table: Payment Details
Fill in the description for all the field names
Part 2: Format
Tables: Student Details
Under the field properties change the format in way so that Student ID appear
as; S2018164701, S2018164702, S2018164703,………., S2018164710
(Note: Do not enter student’s id manually it should be automatically generated provided you have used the correct format, use lecture notes as a point of reference)
(Hint: See the pattern of the ID number only the last two digits are changing)
Part 3: Input Mask & Validation Rule
Create an appropriate Input Mask, Validation Rule and Validation Text for the selected field names for Tables: Student Details
Contacts
Create an input mask for Contact. Enter the Input mark as +(679)000-0000, where 0’s represents your phone numbers.
DOB
Create a validation rule so that student under 18 years of age won’t be able to apply, it will only permit students 18 years and above.
Write an appropriate validation text.
Gender
Create a validation rule so that users can either enter “M” for male or “F” for female.
CIN501 Productivity Software Assignment-Fiji National University Fiji.
Write an appropriate validation text.
Age
Create a validation rule so that student under 18 years of age won’t be able to apply, it will only permit students 18 years and above.
Write an appropriate validation text.
Email
Create a validation rule for email address.
Write an appropriate validation text.
Form 7 Mark
Create a validation rule so that marks under 200 won’t be able to qualify to apply, it will only permit marks that are 200 and above.
Write an appropriate validation text.
Part 4: Create the relationship the tables as Follows on the next page;
If any error occurs while making your relationship that means you have made a mistake while creating your table so you have to make a detour back the first part and correct your mistakes.
CIN501 Productivity Software Assignment-Fiji National University Fiji.
Part 5: Data Entry
Enter the following data in Student Details Table
Note: Do not enter ID Number as it will be generate automatically provided if you have entered the format correctly
Enter the following data in Course Detail Table (refer to next page)
Enter the following data in Payment Details Table
Part 6: Query
1.Design a query that will show only the Student ID, F_Name, L_Name, Program and Current Fees for all the students. Save Query as Query 1.
2.Design a query that will show only the Student ID, F_Name, L_Name, Program, Sponsored TELS and Outstanding Fees for the students who are sponsored by TELS. Save Query as Query 2.
3.Design a query that will show only the Student ID, F_Name, L_Name, Region, Program and Outstanding Fees for all students who belong to WEST region and Who have outstanding Fees. Save Query as Query 3.
4.Design a query that will show only the Student ID, F_Name, L_Name and Outstanding fees for all the students whose outstanding fees is greater than $1,000. Save Query as Query 4.
5.Design a query that will show Student ID, F_Name, L_Name, Contact, Region, Program and Sponsored TELS, Current Fees for all the students who belong to WEST region and who are doing BCOM program and are currently sponsored by TELS. Save Query as Query 5.
Part 7: Report
You are assigned to design a report for all the students who are sponsored by TELS and are currently during BCOM program and are based in WEST region who have outstanding fees.
You need to create 2 reports showing the report for females and males.
1.First Report should include all the BCOM female students who are TELS Sponsored and who are based in West Region who owe outstanding fees. (Report should show Student ID, F_Name, L_Name, Gender, Region, Sponsored TELS, Outstanding Fees)
2.Second Report should include all the BCOM male students who are TELS Sponsored and who are based in West Region who owe outstanding fees. (Report should show Student ID, F_Name, L_Name, Gender, Region, Sponsored TELS, Outstanding Fees)
3.Both Reports should display the total outstanding fees for female students in the first report and male students in the second report. Save both the report as the appropriate name.
CIN501 Productivity Software Assignment-Fiji National University Fiji.
ORDER Now This CIN501 Productivity Software Assignment And Get Instant Discount
