CSC 443 Data Base Management Systems
Dr. R. M. Siegfried
Assignment 2 - Creating and Querying a Database Using MS Access
Due Friday, September 13, 2013
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:
- One will display the first and last name of the student, the courses taken,
the credits offered for the course and the grade received.
- 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.
[Back to the Assignment
Page]