Problem Statements:
In this project, you will have to come up with MS-Access tables, Forms and Report for
the following database schema for XYZ University higher education wing, which has
only Ph.D. students enrolled. The DB is designed to arrange records of students enrolled
in Ph.D. for different disciplines.
Data Base Schema:
Student (
stu_id, stu_name, stu_gender, stu_age, year_admited, cgpa)
Dept (
dept_id, dept_name)
Course (
dept_id, course_no, course_name)
Enroll (
stu_id, dept_id, course_no, gpa)
All primary keys are bold. All foreign keys have the same name as the primary key that
they are referencing. The constraints or assumptions of Data base is as under,
Tool used:
You are required to use MS-Access 2003 (Recommended) or any version of MS-Access
compatible with MS-Access 2003. No other tool is required to develop project. If you are
developing your database in MS-SQL Server, you are free for that. But then you import
your Database in MS-Access 2003 for developing forms and report.
Description of entities in tables
:
Entity Name Description with Types preferred
Types are Not mandatory
Used In Table
stu_id Student ID (Numeric) Student
stu_name Student Name (Characters) Student
stu_gender Student Gender (Male / Female) (Char.) Student
stu_age Student Age (Tiny Integer) Student
year_admited Year When admitted in University (Date) Student
Cgpa CGPA in previous Academic career Student
dept_id Department ID (Numeric) Dept
dept_name Department Name (Character) Dept
dept_id Department ID (Numeric) Course
course_no Course Number (Numeric) Course
course_name Course Name (Character) Course
stu_id Student ID (Numeric) Enroll
dept_id Department ID (Numeric) Enroll
course_no Course Number (Numeric) Enroll
Gpa Grade in that Course (Float) Enroll
??
You are free to use any logical data type of entities as mentioned above
Sample Table Values:
Student
stu_id stu_name stu_gender stu_age year_admited cgpa
1 ABC X Male 27.5 2005 3.1
2 DEF Y Female 29 2006 3.0
3 GHI Z Female 28 2005 3.5
4 GKL O Male 29.5 2004 3.2
5 MNO P Male 30 2006 3.7
Dept
dept_id dept_name
1 CS
2 ECO
3 MTH
Course
dept_id course_no course_name
1 1 Networks
1 2 DBMS
3 3 Calculus
Enroll
stu_id dept_id course_no gpa
1 1 1 0.0
2 1 1 2.8
1 3 3 3.1
What to do:
??
Create FOUR tables using Access as schema provided above (05*4 =20)
??
Draw TWO forms (10*2=20)
??
Develop ONE report (10*1=10)
The details for tasks are as under,
1 - Creating Tables:
Create tables using MS-Access or MS-SQL Server. If using SQL Server, import data and
schema in MS-Access. You may create tables in MS-Access by
wizard or designing
table. You are not supposed to create tables by
entering data option.
You are also supposed to enter
at least 3-4 entries in a table to view in form and / or
report.
2 – Draw Forms:
You have to draw two (2) forms. One is simple and primitive type while other is a bit
advanced form designing.
a – Draw Form1:
You have to draw simple entry form for student record. You are free to design it rather by
wizard or by design option.
b – Draw Form2:
You have to draw a bit complex form as compared to form1. You have to merge two
tables in one form. Template is as under.
Where,
Attributes Table
Department ID, Course No and Course Name Course
Student ID and GPA Enroll
Criteria (Operations on Form, [Step by Step]):
??
First you select Department ID, populated list or combo box
??
Select Course ID from combo box or list again that correspond to particular
department
??
Course Name is displayed automatically as we select Course ID from List
??
Then all students along with their GPAs that registered / Enroll in this course is
displayed in Sub form or lower part of form
3 – Develop Report:
Template for report is as below,