Courses/Computer Science/CPSC 203/CPSC 203 Template/Winter 2010 Lab Manual/Databases review

The following Access database has some basic data. Please download that data sheet (below), and do the following tasks:

1. Create a relationship between the BorrowedBooks table and the Students table, where the ID field in the Students table becomes a foreign key in the BorrowedBooks table.
2. Create a form to help you enter new data into the Students table.
3. Query1: Create a query that lists all information from the Students table, and the BorrowedBooks table
4. Query2: From the students and BorrowedBooks tables, list the fields: Student name, Bookname, loan duration, and department.
5. Query2b: Modify query2 to list information for the students who are in the "CS" department.
6. Query2c: Modify query2 to list the information of the students who are in "CS" department and their grade in SENG301 is "B" or "A".
7. Query3: Create an aggregate query (Group by) to count the number of students coming from every department.
8. Query3b: Modify query 3 to count the number of students coming from the "IT" department.
9. Query4: Create a query to show all the fields of the Students table. Add a new field "Computer knowledge" that would take its value from an If statement based on the CPSC203 grade, as follows:If CPSC203 grade = "A" then ComputerKnowledge = "Computer Expert", otherwirse if CPSC203 grade = "D" then ComputerKnowledge = "Knows of Computers", otherwise ComputerKnowledge = "Intermediate knowledge"

Media: Access_review.mdb

Please note that this review covers some of Access module material.