CS 16 (101) – Homework #2 – Due Friday, April 17, 2009

In this assignment you will practice the database skills you learned in lab, and also have the opportunity to create a new database from scratch.  For review, you may want to look over our Access labs handouts.

Database #1:  National Conference

You are planning to host a national conference next fall.  People will be coming to discuss their latest findings and practices in their field of expertise.  At the conference, people will be presenting research papers.  To judge the quality of papers, each one will be read by referees who will give each paper a score on a scale from 1 to 5.  In order to organize the conference, we need to set up a database that keeps track of the papers that will be presented at the conference, as well as the people involved.  Your Conference database will need the following tables:

• Author Table:  each author has a name and affiliation (i.e. company or university)
• Paper Table:  each paper needs a title
• Referee Table:  format is same as author table:  name and affiliation

This database will feature many-to-many relationships.  Note that an author could write more than one paper, and it’s usually the case that a paper has more than one author.  And also regarding referees:  a paper is typically read and scored by 3 or 4 referees.  Referees are usually responsible for reviewing several papers.  You will need to create two extra tables that allow us to complete these many-to-many relationships, similar to the Order Details table of the computer store database we saw in lab.  You will need to create a table that lists all the authors of each paper, and a table that shows the scores that each referee gave each paper.  Once you have designed all 5 tables, you should establish the relationships.

In order to make this database interesting, you will need to “populate” it with some sample input data.  Pretend that your conference has 15 papers, 10 authors and 7 referees.  Make up your own names, and referee scores.  Make your data interesting:  there should be several papers that have common authors, etc.

After you have created all the tables and their relationships, you will need to make three queries. 

• Papers and their Authors:  Here you’ll show the number and title of each paper, as well as a list of all the author names associated with the paper.
• Papers and their Scores:  For each paper, show paper number and title, and then each referee’s ID number and score for that paper.
• Award Papers:  List of papers that received a perfect score of 5 from at least one referee.

Finally, create a report based on the “Papers and their Scores” query.  In this report, also include the average score that each paper received.

Database #2:  Computer Store

You may remember this example database that we worked on during class.  We will create some additional queries and reports to this database. 

1. We would like to know which products have never been ordered.  To do this, we are going to create a new query.  Use the Find Unmatched Query wizard.  The query is going to take information from the Product table as well as the Order Details table.  Note that the primary key ProductID is common to both tables.  Tell the wizard that you would like to see all the available fields in the query, namely the ProductID, ProductName, UnitsInStock, UnitsOnOrder as well as UnitPrice. 

2. Next, we would like to calculate commissions for all of our employees.  Create a new query that will show all of the order details as well as the Sales Person responsible for each order.  Create a calculated field called Commission, which will be computed as 5%.  When this query is finished, create a report that is based on this query.  In this report, compute the total commission earned by each employee, as well as the total commission earned by all employees.

When you are finished with this assignment, please create a folder with your name on it, move the 2 database files into this folder, and then copy this folder into the class In folder.  (Just like you did on the first assignment.)  Alternatively, you may send me an e-mail message with the database files as attachments.  This latter alternative may be helpful if you are unable to access the CS file server.

One final reminder – please remember this is an individual assignment.  Please keep all of your homework private, and do not share any solutions.  Several years ago, a student failed because of copying another student’s work.