Access Practicals 3 : Student Project Management Database - Part 1

 

This is a practical exercise which will help you to grasp the concepts behind creating tables and setting up field properties for Access database. 

The goal of this exercise is to create a Simple Student Project Management System using Microsoft Access to manage student details, projects, supervisors, and project evaluations. As previously mentioned we are focusing on table creation, field setting and create relationships among tables.

You have been tasked with designing a database for a Science department to manage student project submissions. Each student works individually on their project under the guidance of a supervisor. The system must store information about students, the projects they are working on, their assigned supervisors, and the evaluations provided by supervisors at the end of each project.

 

Create a database in Microsoft Access. Name it as "ScienceProDB".

Create the following tables;

• Student Table - to store students data. Name it as "tblStudent".
  
• Project Table - to store and manage project data along with student and assigned supervisor. Name it as "tblProject".
• Supervisor Table - to store basic data of the supervisors. Name it as "tblSupervisor"
  
• Evaluations Table - to store evaluation data such as marks and grades given for the project. Name it as "tblEvaluation".

 

Structure of tables.

Student Table (tblStudent)

Project Table (tblProject)

 

Supervisor Table (tblSupervisor)

 

Evaluation Table (tblEvaluation)

Apply the below relationships to these tables;

1. Each student must be assigned to one project.
2. Each project must have one supervisor.
3. Each project will be evaluated by the assigned supervisor at the end.

Guide to apply above relationships as follows;

• Students table is linked to the Projects table via the StudentID.
• Supervisors table is linked to the Projects and Evaluations tables via SupervisorID.
• Projects table is linked to Evaluations through ProjectID.
  

Data for Student Table

Data for Project Table

 Data for Supervisor Table

Data for Evaluation Table

Create and execute following queries.

Create a query to show all the Projects that has Completed status. Save the query as "Completed Projects"

Create a query to show all the Projects that has On-going status. Save the query as "Ongoing Projects"

Create a query to show all the Projects Supervised by a Supervisor from Engineering Department. Save the query as "Engineering Department Projects"

Save your work.