Create and Maintain a University DataBase

[RD1-23]

Tables:

STUDENT(Student#, LastName, FirstName, Address, City, State, Zip, Enroll_Date, Undergrad?)

COURSE(Course#, Title, CrHour, InstName)

INSTRUCTOR(InstName, InstOffice, Rank)

Take(Student#, Course#, Grade) 

1.Create the following table structures [AC 2.04-2.16]: 

STUDENT 

Student#	Last name	First name	Address	City	State	Zip	Enroll_Date	UnderGrad?
Text 10	Text 50	Text 50	Text 50	Text 50	Text 2	Text 10	Date/Time	Yes/No
1y Key	2y Index				> Format	Input Mask	Short Date Format
							System Date Default

COURSE

Course#	Title	CrHour	InstName
Text 10	Text 60	Byte	Lookup Wizard (INSTRUCTOR)
1y Key		Valid values: 1-4 hours

INSTRUCTOR

InstName	InstOffice	Rank
Text 50	Text 10	Text 20
1y Key

Take

Student#	Course#	Grade
Text 10	Text 10	Text 2
1y Key	1y Key

2.Create the following relationships [AC 3.06-3.12]: 

3.Create two EXCEL worksheets in code.xls for the following code tables:

Grade_Code 

Grade	Grade_Point
A	4
B	3
C	2
D	1
E	0

Rank_Code

Code	Description
1	Full
2	Associate
3	Assistant

4.Import the code tables from code.xls [AC 7.26-7.31]

5.Create a data entry form for the INSTRUCTOR table.Allow the user to select from a list of ranks available. [AC 5.23-5.40] 

6.Use the Instructor Entry Form to enter the following INSTRUCTOR data:

INSTRUCTOR

InstName	InstOffice	Rank
Lujan	BE109	Assistant
Morris	BE110	Full
Presley	BE144	Associate
Wilke	BE220	Full

7.Enter the following data directly by opening the COURSE table in Datasheet view:

COURSE

Course#	Title	CrHour	InstName
DIS 110	Introduction to DOS	2	Lujan
DIS 118	Microcomputer Applications	3	Wilke
DIS 138	Introduction to Windows	2	Lujan
DIS 140	Introduction toDatabase/Access	3	Presley
DIS 150	Introduction to Spreadsheet/Excel	2	Morris

8.Create a form for viewing, entering, and editing data in both the STUDENT (main form) and Take (sub form) tables.Allow the user to select from a list of courses available. [AC 5.44-5.49] 

9.

9.Use the Course Registration Form to enter the following STUDENT and Take data:

STUDENT

Student#	Last name	First name	Address	City	State	Zip	Enroll_Date	UnderGrad?
0103	O'Casey	Harriet	4088 Ottumwa Way	Lexington	KY	40515	8/25/97	Yes
0122	Logan	Janet	860 Charleston St.	Lexington	MA	55500	1/19/98	No
0123	Hagen	Greg	6065 Rainbow Falls Rd.	Springfield	MO	65803	6/10/97	Yes
0139	Carroll	Pat	4018Landers Lane	Lafayette	CO	84548	8/25/97	Yes
0148	Wolf	Bee	1775 Bear Trail	Cincinnati	OH	45208	1/19/98	Yes
0167	Krumple	Scott	580 E Main St.	Lexington	KY	40506-0034	8/25/97	No
0171	Harvey	Elliot	34 Kerry Dr	El Mano	CO	80646	8/25/97	Yes
0181	Zygote	Carrie	8607 Ferndale St	Grenoble	CA	91360-4260	8/25/97	Yes
0194	Loftus	Abner	8077 Montana Place	Big Fish Bay	WI	53717	1/19/98	Yes
0251	Grainger	John	2256 N Sante Fe Dr.	Iliase	CA	91210	1/19/98	Yes

Take

Student#	Course#	Title
0103	DIS 110	Introduction to DOS
0103	DIS 118	Microcomputer Applications
0122	DIS 118	Microcomputer Applications
0122	DIS 138	Introduction to Windows
0122	DIS 140	Introduction toDatabase/Access
0123	DIS 110	Introduction to DOS
0123	DIS 140	Introduction toDatabase/Access
0148	DIS 140	Introduction toDatabase/Access
0148	DIS 150	Introduction to Spreadsheet/Excel
0167	DIS 138	Introduction to Windows
0167	DIS 140	Introduction toDatabase/Access
0167	DIS 150	Introduction to Spreadsheet/Excel
0181	DIS 118	Microcomputer Applications
0181	DIS 140	Introduction toDatabase/Access
0181	DIS 150	Introduction to Spreadsheet/Excel

10.Create a grade entry form for the Take table.For an instructor, a class list will be generated for each class he/she teaches.Use Option Group button for grade entry [Note:This will require you to modify the Take table FIRST]. 

11.Use the Grade Entry Form to record the following grades:

InstName	Course#	Student#	Grade
Lujan	DIS 110	0103	A
Lujan	DIS 110	0123	B
Lujan	DIS 138	0122	A
Lujan	DIS 138	0167	C
Morris	DIS 150	0148	C
Morris	DIS 150	0167	D
Morris	DIS 150	0181	E
Presley	DIS 140	0122	A
Presley	DIS 140	0123	B
Presley	DIS 140	0148	C
Presley	DIS 140	0167	C
Presley	DIS 140	0181	B
Wilke	DIS 118	0103	A
Wilke	DIS 118	0122	A
Wilke	DIS 118	0181	D