National University of Computer & Emerging Sciences –FAST (FSD) MIS-Lab
CL310- MIS Lab
Lab Assignment
Instructor Areeba
Waseem
Name
Roll#
FAST School of Management
Using Microsoft Access, we are going to define a database called Student
Registration consisting of six tables:
Student, with the following fields:
ID, primary key, text of length 4
Last Name, text of length 15
First Name, text of length 15
Major Code, text of length 3
Advisor Code, text of length 4
Credits, integer
Major:
Major Code, primary key, text of length 3
Major Name, text of length 30
Advisor:
Advisor id, primary key, text of length 4
Last Name, text of length 15
First Name, text of length 15
Course
Course Code, primary key, text of length 8
Course Name, text of length 30
Credits, integer
Section
Section ID, primary key, text of length 8
Course ID, text of length 8
Term, text of length 8
Section Year, text of length 4
Instructor, text of 30
Registration
Student ID, primary key, text of length 4
Section ID, primary key, text of length 8
Grade, text of length 2
The Database
Advisor
Advisor ID Last Name First Name
2222 Curie Pierre
3333 Copland Aaron
4444 Hopper Grace
5555 Kelly Patrick
6666 Ash Craig
Course
Course Code Course Name Credits
CHEM101 General Chemistry 1 4
CHEM102 General Chemistry 2 4
CHEM201 Organic Chemistry 1 4
CHEM202 Organic Chemistry 2 4
CS101 Introduction to Programming 3
CS102 Data Structures 3
CS103 Computer Architecture 3
ENG 102 Intermediate Writing 3
ENG 103 Advanced Writing 3
ENG 202 American Literature 3
ENG101 Basic Writing 3
ENG201 British Literature 3
HIS 102 Western Civilization 2 3
HIS 201 American History 1 3
HIS 202 American History 2 3
HIS101 Western Civilization 1 3
MUS 102 Music theory 2 4
MUS101 Music Theory 1 4
Major
Code Major
101 Chemistry
152 Computer Science
225 English
341 History
635 Music
Registration
StudId SecId Grade
1111 10110101 B+
1111 20110101 B
1122 20110101 A-
1122 50110101 A
2244 11510201 A
2244 11510301 A
Section
Section Id Course ID Term Course Year Instructor
10110101 CHEM101 Fall 2012 Silverman
10110102 CHEM101 Fall 2012 Cooper
10120101 CHEM201 Fall 2012 Kovacs
11510101 CS101 Fall 2012 Bloch
11510102 CS101 Fall 2012 Chase
11510201 CS102 Fall 2012 Siegfried
11510301 CS103 Fall 2012 Kowalski
20110101 ENG101 Fall 2012 MacGuirk
20110301 ENG103 Fall 2012 MacGuirk
30110101 HIS101 Fall 2012 Kelly
30110301 HIS201 Fall 2012 McPatrick
50110101 MUS101 Fall 2012 Gee
Student
ID Last Name First Name Major Code Advisor Code Credits
1111 Smith John 101 2222 23
1122 Jones Thomas 635 3333 45
2244 Siegfried Robert 152 4444 30
3344 Goldberg Stephen 101 2222 32
4455 Wayne Bruce 341 5555 24
5566 Kent Clark 225 6666 38
Once the database is created, you will create two queries:
1. Add data in student and course and section table using Form
2. One will display the first and last name of the student, the courses taken,
the credits offered for the course and the grade received.
3. The other will display the first and last name of the student, his/her major
and the first and last name of his/her advisor.
4. Create reports for the Query using Report Wizard
5. Delete Craig Ash's record from the ADVISOR table.
6. Use the Look-up wizard to add DEPARTMENT an additional column to
the ADVISOR table. Have it entries restricted to the Major codes
101, 152 , 225 , 341 , and 635 .
