|
CGS 1570
Chapters 11, 12 & 13: Access
Assignment 9
Name: ______________________________ Section (Time/Day): ________________________
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
1. Read Access Chapters 11, 12, and 13. Pay careful attention to basic concepts including:
2. After reading Access Chapters 11-13, 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. “Mary Jones Data Base”) and storing it on your media.
b. Create four tables for the database as follows: |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Field Type Course Text Credits Number Course Name Text
Primary Key is Course
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Field Type Section Number Course Text Begin Date/Time End Date/Time Days Text Room Number
Primary Key is Section |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Field Type Student ID Number Student Name Text Street Text City Text State Text ZIP Number
Primary Key is Student ID
Add your name with a Student ID and other data |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Field Type ID Autonumber Section Number Student ID 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. Establish appropriate One-to-Many relationships between the Tables in your database (Database Tools Tab/Relationships Group). 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 Student ID 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. You will now create queries based on your database. Name each query with the question number and your name (e. g. “3a – Mary Jones”).
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 the query.
b. Create a query based on Student Table and Registration Table. Include Student ID, Student Name, Section and Grade. Name and save the query. Print the results of the query.
c. Create a query based on the Course, Section, Student and Registration Tables. Include the Student Name, Course, Course Name, Section and Grade. Name and save the query. Print the results of the query.
d. Create a query based on Course Table (all fields). Using a wild card, select courses with the COP prefix. Name and save the query. Print the results of 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 the query.
f. Create a query based on the Student Table (Student-Name and City fields). Select students who live in Milton or Mobile. Name and save the query. Print the results of the query.
Staple your printouts in order and attach cover page when submitting the assignment. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||