Create and Maintain a University DataBase

[AC A1]

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 35-72]: 

STUDENT [AC 179-183]

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  [AC 172-177; 185-187]

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 87-93]: 


 

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 67]

 

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

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 142-147; 246-251] 

9.

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

STUDENT

Student#

Lastname

Firstname

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