Database assignment I

In this assignment you create a database for a non-profit organization that connects teenagers with temporary jobs such as babysitting and yard work. The data is already in a workbook, so you will be import all the data from Excel, modifying the resulting tables, create a relationship, and create some queries.

You need a version of Access that can handle the .accdb file format (which is the default file format for Access 2010 and later). If you need a copy of Access and you have a Windows computer, then you can get a copy as part of the DreamSpark program that Kishwaukee CIS is a part of. What that means is that students currently enrolled in an active qualifying CIS course can download, install, and use a full copy of Access for their personal non-commercial use on their personal computer. You should have been sent an invitation to register for DreamSpark early in the semester. It was sent to your student email account. Contact the instructor if you have questions about DreamSpark.

This problem uses a number of features of Access and it is expected that there will be questions. Keep in mind that the CIS 123 website has a FAQ sheet for using Access, there will be a demonstration of Access in one of the optional class sessions, and there are many tutorials on how to use specific features in Microsoft Access on the Web. This is particularly useful if you want a question answered quickly outside of class time and you don't have an Access reference at hand.

  1. Download and save your individualized starting Access database and Excel workbook which you can get here. Remember where you download your files. Make sure you close Access BEFORE you upload your .accdb database file to the dropbox in Desire2Learn.
  2. Do NOT work closely together or copy or share work with other students. The penalties can be quite severe. You are welcome to ask all the questions you want and to seek help from the instructor.
  3. Open the database that you downloaded. The first step is to create three tables in the database by importing data from the excel workbook you downloaded.
  4. Import the data from the Employees worksheet in the teenwork.xlsx workbook. The first row of the worksheet contains headers for the data. Check the settings for each field in the import. The WorkerID should be an integer and indexed with no duplicates. The names and phone should be text fields. The rest of the fields (Weekend through Clerical) should be Yes/No fields. Choose to have the primary key set to be the WorkerID. This data will become the Employees table in the database. (5 pts)
  5. Once the Employees table has been imported, display the table in design view and change field properties as follows: Make the WorkerID field a required field. Change the sizes of the two name fields and the phone field to 15 characters. You should get a warning when you close the table because you are making some fields shorter. That is OK. (2 pts)
  6. Import the data from the Jobs worksheet in the teenwork.xlsx workbook. The first row of the worksheet contains headers for the data. Check the settings for each field in the import. The JobID should be an integer and indexed with no duplicates. The EmployerID should be a text field. The EmployeeID should be an integer. The next six fields (Weekends through Clerical) should be Yes/No fields. The date fields should be Date/Time fields. The rate field should be a double data type. Choose to have the primary key set to be the JobID. This data will become the Jobs table in the database. (5 pts)
  7. Once the Jobs table has been imported, display the table in design view and change field properties as follows: Make the JobID field a required field. Change the the size of the EmployerID field to 10 characters. You should get a warning when you close the table because you are making one field shorter. That is OK. (2 pts)
  8. Import the data from the Employers worksheet in the teenwork.xlsx workbook. The first row of the worksheet contains headers for the data. Check the settings for each field in the import. The EmployerID should be a text field, indexed with no duplicates. The name and phone should also be text fields. Choose to have the primary key set to be the EmployerID. This data will become the Employers table in the database. (3 pts)
  9. Once the Employers table has been imported, display the table in design view and change field properties as follows: Make the EmployerID field a required field. Change the the size of the EmployerID field to 10, the size of the employer name field to 30 characters, and the size of the phone field to 15 characters. You should get a warning when you close the table because you are making some fields shorter. That is OK. (2 pts)
  10. Use the Relationships button on the Database Tools tab of the ribbon to relate all three tables. The EmployerID has the same name in both tables it is in. The WorkerID is the same thing as EmployeeID. Relate the tables using those fields and choose ALL THREE referential integrity options. This includes enforce referential integrity, cascade update related fields, and cascade delete related records. Note: All of the tables must be closed before you can create a relationship. (4 pts)
    Teenwork database relationship
  11. Create a new query using design view
    1. Use the Employees and Jobs tables.
    2. Display the worker ID, employee last name, employee first name, job ID, start date, and end date fields. (2 pts)
    3. Sort the data in ascending order based on the employee last name, then employee first name, and then start date. The sort is required to be done in design view (NOT datasheet view). (2 pts)
    4. Save the query with the name EmployeeJobList (1 pt)
    Employee job list
  12. Create a new query using design view
    1. Use the Employers, Jobs, and Employees tables.
    2. Display the employer name, the job start date, and the employee last name and first name fields. (2 pts)
    3. Sort the data in ascending order based on the employer name and then by start date. The sort is required to be done in design view (NOT datasheet view). (2 pts)
    4. Save the query with the name JobsByEmployer
    Jobs by employer
  13. Now its time to find out if we can fill a new job request that came in. The job is for a babysitter on 3/9/13, which is a weekend. We will do this in steps. First we will find out what teens are listed as being able to handle babysitting on the weekend. Then we will find out which of those teens currently has a job on the date in question. Finally we will ask for any teens that are in the first list, but not in the second list. In other words, they are qualified and do not already have a job scheduled for that time.
  14. Create a new query using design view
    1. Use the Employees table.
    2. Display the worker ID, employee last name, employee first name, weekends, and babysitting fields. (2 pts)
    3. Sort the data in ascending order based on the employee last name, and then employee first name. The sort is required to be done in design view (NOT datasheet view). (2 pt)
    4. Only show those records where the Weekends field is Yes and the BabySitting field is Yes (2 pts)
    5. Save the query with the name WeekendBabySitting
    Weekend babysitting list
  15. Create a new query using design view
    1. Use the WeekendBabySitting query and Jobs table.
    2. You will need to tell the query that the WorkerID and EmployeeID fields are related, so click on the WorkerID field in the WeekendBabySitting query and drag to the EmployeeID field in the Jobs table to set up the connection. (1 pt)
    3. Display the worker ID, employee last name, employee first name, start date, and end date. (2 pts)
    4. Sort the data in ascending order based on the employee last name, then employee first name, and then start date. The sort is required to be done in design view (NOT datasheet view). (2 pt)
    5. Only show those records where the start date is <= 3/9/17 and the end date is >= 3/9/17 (2 pts)
    6. Save the query with the name JobsForMarch9
    list of jobs for March 9 for weekend babysitters
  16. Create a new query using design view
    1. Use the WeekendBabySitting and JobsForMarch9 queries.
    2. You will need to tell the query that the WorkerID fields in the two queries are related, so click on the WorkerID field in the WeekendBabySitting query and drag to the WorkerID field in the JobsForMarch9 query to set up the connection. (1 pt)
    3. You will also need to modify the join property of the connection you just made. Point at the line connecting the two fields and right click. You should see a context menu pop up with two choices (Join Properties and Delete). If you do not get that context menu, then click somewhere else to get rid of it and try again. Choose Join Properties. In the join Properties dialog, choose the option to "Include ALL records from WeekendBabySitting and only those records from JobsForMarch9 where the joined fields are equal." (2 pts)
    4. Display the worker ID, employee last name, and employee first name fields from the WeekendBabySitting query. Also include the worker ID from the JobsForMarch9 query, but don't display it. Select only those records where the worker ID field from JobsForMarch9 is Null. (4 pts)
    5. Save the query with the name AvailableForMarch9
    Employees available for March 9 babysitting job
  17. Submit your database file to the Desire2Learn assignment dropbox when you are finished. Make sure you have Access completely closed BEFORE you submit your file and make sure the file you submit has the .accdb extension. If you submit Inventory.lccdb, then you didn't shut down Access before submitting the file and you have submitted a file which is useless.

Resources

  1. Excel workbook with table data for imports
  2. Teenwork database relationship
  3. EmployeeJobList query results
  4. JobsByEmployer query results
  5. WeekendBabySitting query results
  6. JobsForMarch9 query results
  7. AvailableForMarch9 query results

There are 52 points total available for this assignment. Remember that there is a discussion forum available for asking questions about this assignment. There is also an Access FAQ page available on the CIS 123 web site. Class is also a great time to demonstrate Access usage questions, so feel free to bring in questions to a class session.