EPPD1063: COMPUTER APPLICATIONS
LAB TEST TWO
12 June 2024
(2 HOURS)
Microsoft Access
QUESTIONS:
As a database company, university has hired your company to design a student’s database. Currently, the person in charge of students’ profile keeps the data in four word documents. You will analyze and develop the database for them base on the information given in Table below:
Duration: 2 hours Total Marks: 100
Instructions:
1. Open a file in the Assignment folder in UKMFOLIO and read the attachment file of your Lab test Two.
2. Start Access and open a new blank database. Name your database file as Test2_your matric number and save in your pendrive. Example Test2_A100010.
3. Create a table called Student Information from Table 1. Accept Stud ID as the default primary key field and assign Text for the data type. Create appropriate fields with appropriate data type for the rest of table Student Information. For Status use lookup wizard. For postcode and telephone number, you must use input mask, and for gender, you must use validation rule. Make sure your field size is appropriate to reduce data error. (8 marks)
4. Create a table called Student Performance from Table 2. Accept Stud ID as the default primary key field and assign Text for the data type. Create appropriate fields with appropriate data type for the rest of table Student Performance. Make sure your field size is appropriate to reduce data error. Note:For GPAand CGPAatfieldsizechangetodouble, formatisfixedandsetdecimalplacesto2decimalpoint. (8marks)
5. Create a table called Program from Table 3. Accept Program ID as the default primary key field with the Text data type. Create appropriate fields with appropriate data type for the rest of table Program. Create another column and name is as Total Fees. Calculate the Total Fees that the student must pay for the duration of their study. Make sure your field size is appropriate to reduce data errors. (5marks)
6. Create a table called State from Table 4. Accept State ID as the default primary key field and assign Text for the data type. Create appropriate fields with appropriate data type for the rest of table State. Make sure your field size is appropriate to reduce data error. (5marks)
7. Enter twenty (20) records into Table 1 and Table 2, three (3) records in Table 3, and fifteen
(15) records in Table 4. (10marks)
8. View the records in the Student Information table in datasheet view. Sort the datasheet in ascending order by StudID, and then save the table. (2marks)
9. View the records in the Student Performance table in datasheet view. In datasheet view, sort the datasheet ascending order by program ID, and then save the table. (2marks)
10. View the records in the Program table in datasheet view. In datasheet view, sort the datasheet ascending order by Program ID, and then save the table. (2marks)
11. View the records in the State table in datasheet view. In datasheet view, sort the datasheet ascending order by State ID, and then save the table. (2marks)
12. Create 4 forms out of the four tables. Don’t forget to name each form as follows: Student Info Form, Student Performance Form, Program Info Form and State Info Form. Make sure your form has the current Date and Time. (4marks).
13. Use the Relationships window to join the four tables based on the primary key field. Enforce referential integrity between the four tables. (8marks)
Note:Makesureyouclosealltablesbeforecreatingtherelationships.
14. Create queries to get information about:
a. Stud ID, Student Name, Contact No from Student Information table, Year and CGPA from Student Performance table, Program Name and Duration from Program table, and lastly State Name from State table. Name your query as Student Result Query. (5marks)
b. Stud ID, Student Name, Contact No from Student Information table, Year from Student Performance table, Program Name and Total Fees from Program table. Name your query as Student Program and fees. (5marks)
c. Male students and their Student ID who are from the state of Selangor and their CGPAs above 3.00. (4marks)
d. Female students and their first name who are taking Economics program and their States of Origin. (4marks)
15. Using the query instep 14a, create a report grouping by State Name and sort your StudID in ascending order. Name your report as Student Result’sReport. (9marks)
16. Create your Switchboard and make at least 4 items in your Switchboard (5marks).
17. Make your forms attractive (15marks)
18. Close the database and then exit Access.
19. Save your database in a folder that you have created before. UPLOAD YOUR DATABASE TO UKMFOLIO.