Project 5: Database Tables

Available Points:

  1. In class project option: You must be in class to participate.

    2.     5% 1. Bring DVD to class
      10% 2. Inventory table printout
      10% 3. Correct data types set in table
      10% 4. Correct validation rule in table
      10% 5. Correct validation text in table
      10% 6. Query #1 printout
      10% 7. Query #2 printout
      10% 8. Report printout
      25% 9. FTP your lastname_DB.mdb to the project_5 folder of your student folder on the Student Web Server.
    100% TOTAL

  2. Out of class project option: You must do this if you will miss class during the in class option.

    3.     5% 1. Table #1 printout
        5% 2. Table #2 printout
      20% 3. Tables have at least five records each
      15% 4. Tables are related in database
      15% 5. Correct data types set in table
      40% 6. FTP your lastname_DB.mdb to the project_5 folder of your student folder on the student web server.
    100% TOTAL

Due Dates:

Cascade: Wednesday November 18, 2009
Sylvania: Thursday, November 19, 2009

Related Readings:

Text: Chapter 10 (Database Management)

Web Page Notes: Database

Using Access 2003

Using Access 2007

eTutorials for MS Access

Other Internet Pages identified below

This assignment covers Database design concepts and using Microsoft Access. The resulting lastname_DB.mdb file should be uploaded to your student folder on the PCC SWS server. DO NOT use Microsoft Works for this assignment. Works creates a flat-file or a one table database and is not a relational database program. It does not allow you to create more than one table in a database, nor allow tables to be linked together for queries, forms or reports. If you use Microsoft Works, you will receive a 0 for the assignment.

Database Design

  1. In Class project option:
     Cascade students must attend class on Monday, November 9th and Monday, November 16th to complete this option.

    Sylvania students must attend class on Thursday, November 12th to complete this option.

    1. Bring a movie DVD or the information from the cover to class on Wednesday, August 5 (Cascade) or Monday, August 3 (Sylvania).

    2. As a class, we will build a movie database table called Inventory including defining:
      • Records: they will be each movie with ID numbers (Primary Key) assigned automatically by Access.
      • Field: fields for each record will be determined by the class as part of the database design process.
      • Validation rules for select fields data entry.
      • Each student will key in their DVD data to the master database
      • Each student will duplicate the master table and its data in their own database file called lastname_DB.mdb as the class builds the master.
      • Students will complete two queries and a report from their database file.
        1. A query that includes the movie title, actors and the MPAA rating.
        2. A query that includes the movie title and the year made for all movies made from 2004 to 2005, or a range approriate to the data.
        3. A report for the query for the year range in item "b".

    3. The use of a multi-table database will be demonstrated in class.

  2. Out of class project option: You are still expected to attend class, but may complete this on your own using the tutorials by the due date.

    1. Your task in this assignment is to create a database with two tables and to link the two tables via a primary key and foreign key. Use MS Access to create your database tables. Each table should include at least 5 appropriate fields and you must enter at least 5 records of data into each table.

    2. Create your entities/tables from one or two of the topics listed below or make up your own topics. Since you are going to join your tables, try to make the data realistic so it looks like the two tables are actually related.
      • Rivers
      • Planets
      • Rap Singers
      • Rock Groups
      • Actors/Actresses
      • Birds
      • Plants
      • Animals
      • Movies
      • Books
      • Music
      • Anything of special interest to you

    3. Your database should be named lastname_DB.mdb (Microsoft Access) but the "lastname" should be replaced with your lastname. NOTE: You should end up with ONE database that has TWO tables in it. You should only submit one mdb database.

MS Access 2007 Users: If you are creating your database on your own PC and you have Access 2007 installed, Access 2007 automatically creates the database with an accdb extension. For assignment submission, please create a database in the Access 2003 format by doing the following:

  • Click the Office Button in Access and point at Save As
  • Click Access 2002-2003 Database
  • If the Microsoft Office Access window opens with the message "All open objects must be closed prior to converting to a different version. Do you want Microsoft Office Access to close the objects?" Click the Yes button.
  • The Save As window will open.
    • In the Save in field, select the folder in which you want your Access 2003 database to be saved.
    • In the File name field, type in a name for your database.
    • Make sure that Microsoft Access Database (2002-2003)(*.mdb) is selected in the Save as type field.
    • Click Save.
  • The original database is closed and a copy is opened in the 2002-2003 format (mdb extension). Any changes that are made will be made in the 2002-2003 format.
    • You will not need to re-save this database
    • It is this mdb file that you want to transfer to the web server and submit to the assignment dropbox.

Submitting Your Project

  1. In Class project option:
    1. Your lastname_DB.mdb should be posted in the project_5 folder of your student folder on the student web server.

  2. Out of class project option:
    1. Your lastname_DB.mdb should be posted in the project_5 folder of your student folder on the student web server.

Revised: 09-15-2009 Russ Erdman