CGS 1570

 

Chapters 12 & 13: Access

 

Assignment 9

 

 

Name: ______________________________ Section (Time/Day): ________________________

 

1.         Read Access Chapter 12.  Pay careful attention to basic concepts including;

            Database, field, record, table

            Access objects: Table, Queries, Reports, Forms

            Design vs. Datasheet views of Tables and Queries

            Sort &Filter group on the Home Tab

            Relational data base (definition)

            Primary Key Field and Referential integrity

 

2.         Read Access Chapter 13 (pp. 707-728).  You will now create a database containing data relating to a college registration and grade reporting system.

 

a.     Create a blank database naming it “your name Data Base” (e.g. “Jones Data Base”) and storing it on your media.  (See Step 1 on p. 720).

 

b.     Create four tables for the database as follows:

Course Table

Course

Credits

Course Name

COP1000

2

Beginning Programming

CGS1000

2

Computer Applications

CGS1570

3

Computer Concepts

ENC1101

3

English I

COP1510

3

Programming I

COP2511

3

Programming II

 

Field                            Type

Course                         Text

Credits                         Number

CourseName               Text

 

Primary Key is Course

 

 

 

 

 

Section Table

Section

Course

Begin

End

Days

Room

1000

CGS1570

7:30:00 AM

8:45:00 AM

MW

2146

1002

COP1510

10:30:00 AM

11:45:00 AM

MW

2160

1003

CGS1570

9:00:00 AM

10:15:00 AM

MW

2146

1004

COP2511

9:00:00 AM

10:15:00 AM

TR

2146

1005

CGS1000

1:00:00 PM

2:15:00 PM

TR

2160

2000

COP2511

7:30:00 AM

8:45:00 AM

TR

2160

2001

COP1510

1:00:00 PM

2:15:00 PM

MW

2161

2003

CGS1000

2:00:00 PM

3:15:00 PM

TR

2160

 

Field                            Type

Section                      Number

Course                      Text

Begin                         Date/Time

End                            Date/Time

Days                          Text

Room                        Number

 

Primary Key is Section

 

 

 

 

Student Table

Student ID

Student Name

Street

City

State

ZIP

100

Que, Susy

100 17th Ave.

Pensacola

FL

32503

111

Jones, Jim

123 X St.

Pensacola

FL

32503

123

Smith, Mary

1000 Main St.

Pensacola

FL

32504

200

Blow, Joe

Box 13

Milton

FL

32544

300

Spade, Sam

1500 9th St.

Pensacola

FL

32503

301

Jones, Pam

1001 James Pl.

Mobile

AL

12300

 

Field                           Type

StudentID                Number

StudentName           Text

Street                       Text

City                          Text

State                         Text

ZIP                           Number

 

Primary Key is StudentID

 

Add your name with a Student ID and other data

Registration Table

ID

Section

Student ID

Grade

1

2003

100

A

2

1002

100

A

3

1003

301

B

4

1004

300

C

5

1004

123

C

6

2001

200

D

7

1003

111

A

8

1000

200

A

9

1002

123

B

10

1000

123

B

 

 

Field                          Type

ID                          Autonumber

Section                   Number

StudentID               Number

Grade                     Text

 

Primary Key is ID—the field automatically generated by Access.

 

Register yourself for a section and assign the grade of your choice.

c.   Read Chapter 13 (pp. 732-736).  Establish appropriate One-to-Many relationships between the Tables in your database (Database Tools Tab/Show/Hide Group). (See Step 5, pp. 741-742) Print the Relationships Report (Relationship Tools contextual tab/Tools Group).  Test the requirements for referential integrity that you have created by attempting to add a record to the Registration Table with a StudentID that has not been entered in the Student Table, or attempt to add a record in the Section Table for a course that is not found in the Course Table.

 

d.   Using the dialog box associated with the field name or using commands from the Sort & Filter group in the Home tab, sort the Student Table into ascending sequence using the Name field.  Select students who live in Florida.  Print the table.  Remove the filter.

 

3.         Read Chapter 13 (pp. 744-754).  You will now create Queries based on your database.  Name each Query with your name and the question number (e. g. “Jones Query 3a”).

 

a.   Create a query based on all of the fields in the Registration Table.  Use the Criteria row select students with a Grade of “A”.  Name and save the Query.  Print the results of running the query.

 

            b.   Create a Query based on Student Table and Registration Table.  Include StudentID, StudentName, Section and Grade.  Name and save the Query.  Print the results of running the query.

 

            c.   Create a Query based on the Course, Section, Student and Registration Tables.  Include the StudentName, Course, Course Name, Section and Grade.  Name and save the Query.  Print the results of running the Query.

 

            d.   Create a Query based on Course Table (all fields).  Using a wild card, select courses with the COP prefix (see p. 747).  Name and save the Query.  Print the results of running the Query.

 

            e.   Create a Query based on Student Table (all fields).  Use a comparison operator to select records with ZIP less than 30000.  Name and save the Query.  Print the results of running the Query.

 

            f.    Create a Query based on the Student Table (Student-Name and City fields).  Select students that live in Milton or Mobile.  Name and save the Query.  Print the results of running the Query.

 

 

Staple your printouts in order and attach cover page when submitting the assignment.