Access Assignment

Instructions

1. There are three parts to this assignment: WasteNot Recycling, Scale Classic Cars, and Seminar Database Design (ERD).

2. You will need to use Microsoft Access for this assignment for the queries, reports and forms. Microsoft Access is available for the Windows version of Microsoft Office only. If you do not have Microsoft Access, it is available in all of the computer labs (locations at http://offices.depaul.edu/is/services/labs-classrooms/computer-labs/Pages/default.aspx).

3. Download the Access.zip file from Desire2Learn. Extract all of the files and rename the database to databasename_yourinitials.mdb. For example, I would rename the WasteNot Recycling database to WasteNot_DKT.mdb. This is the database used to create your answers for the queries, reports, and forms.

4. You will need to add your name and the date to each report.

5. For the Seminar Database Design, you can use Microsoft Word for the Database Design answers (or Microsoft Visio or an illustration program). If you use Microsoft Visio or an illustration program, please save your answers as a PDF. Use the information in the description to create your answers.

6. Each answer is worth 5 points.

7. For the Seminar Database Design answers, you can print to a PDF by using the doPDF printer in the labs or you can use Microsoft Word.You must upload your Access answers in Access database format.

8. Please put all of the documents and the final database into a folder using Access_yourinitials for the name (mine would be Access_DKT). Compress this folder using Windows 7's built-in compression utility and upload the compressed folder to Desire2Learn.

WasteNot Recycling

WasteNot Recycling picks up recyclables from homeowners inBoulder, Colorado. The CUSTOMER table holds static customer information such as name, address, and phone. The CUSTOMER RECORD table holds dataabout each recyclable pickup. Enough test data has been added to each table to test queries and create forms and reports (use WasteNot.mdb). The owners of WasteNot Recycling have asked you to assist with creating several queries, a form, and a report. Specifically they need you to do the following:

1. Create a query called "01-May Pickup" to list customers whohad their first pickup in May 2004. Sort the records by customer's last name. Include the following fields:
a. Last Name
b. First Name
c. Street
d. City
e. State
f. ZipCode
g. Phone
h. FirstPickup

2. Create a query called "02-Customer Weight" to determine the total weights of paper and other products each customer has had picked up. Include the following fields:
a. Last Name
b. First Name
c. SumOfWeights (Paper)
d. SumOfWeights (Other)

3. Create a query called "03-Low Volume" to display customers with less than 10 pounds in either recyclable field. Include the following fields:
a. Last Name
b. First Name
c. Street
d. City
e. Weight Paper
f. Weight Others

4. Create a form called "Customers" that allows the owners to enter data into the CUSTOMER and CUSTOMER RECORD tables. You can choose the design (aesthetics), however, locate an appropriate graphic to include on the form. All controls must be aligned and sized properly.
a. Customer ID
b. Last Name
c. First Name
d. Street
e. City
f. State
g. ZipCode
h. Phone
i. FirstPickup
j. CUSTOMER RECORD

5. Create a report called "Customer Weights" that groups and sums records by customer. Include your name, the date and time as well as the following fields:
a. Last Name
b. First Name
c. Service Date
d. Weight Paper
e. Weight Other
 
Scale Classic Cars

Johnny Krol runs a body shop specializing in restoring classic cars. Johnny owns threeand began collecting scale models when his wife put her foot down and said no to building more garage space.

The Scale Classics Web site began as a technology class project for Johnny's son J.J., who created a basic text and graphics informational site. Johnny liked the site, but wanted a complex site dedicated to the serious collector. He envisioned a storefront, auction house, and collector's forums.Unable to find such a site, Johnny hired a local consultant to build the site, found a processing house to manage orders and payments, and began shipping scale models from the body shop.

The storefront largely contains American classic cars, which come in 1/18, 1/24, 1/43, and 1/64 scale. Popular foreign cars are also available. Johnny has hired you to create some business analysis queries, a form and a report using his database, ClassicCars.mdb. Specifically, he wants you to:

1. Create a query called "01-Coupe" to show records with "coupe" in the model name. Include the following fields:
a. Make
b. Model

2. Create a query called "02-Less than $35" to show models that cost less than $35 and sort from highest to lowest price. Include the following fields:
a. Car ID
b. Make
c. Model
d. Price

3. Create a query called "03-Model" that lists cars grouped by their make. Sort by model and calculate the average price for each make. Include the following fields:
a. Make
b. Model
c. Price

4. Create a form called "Catalog" that allows Johnny to enter new classic cars and update existing records. Use a Columnar format and the Sandstone style. Include the following fields:
a. Car ID
b. Make
c. Model
d. Scale ID
e. Price

5. Create a report called "Cars by Model" that lists cars grouped by their make. The report should display all fields from the CATALOG table, sort by model name, and calculate the average price for each make. Use the Formal style. Include your name, the date and time.
 
Seminar Database Design (ERD)

Instructions

You can use Microsoft Word for the Seminar Database Design answers. Use the information in the description to create your answers. If you draw your diagrams on a piece of paper, then please scan the drawings or photograph them and add them to your Word document.
Please answer the questions based on the information in the Description. Note that this is hypothetical and not based in reality.

Description

In conjunction with taking an introductory computer concepts course, DePaul has decided to test the idea of offering weekend seminars to cover the basics of the Internet and the Web. Initially, DePaul will offer two such seminars:

• Web101 - The Basics of the Web and Internet
• Web205 - Building a Web Site

Web101 will have five different sections, and Web 205 will have four different sections.

Although they are not required to, students can enroll in one or both seminars. The seminars are held for eight hours on a single day. There is no cost associated with taking the seminars. One teacher, from a pool of qualified teachers, will be assigned to each section of each seminar. Some teachers will obviously not be assigned to teach any of the sections, and some teachers may be assigned to several different sections.

Finally, the system should track the final grades assigned to each student. One more thing, DePaul is simply testing this idea in this particular term. So, do not worry about the term (e.g., Fall, Winter, Spring, or Summer) or the year.
Business Rules

1. Record as many business rules as you can based oninformation in the description.
Entity Classes

1. Create a simple two-column table that lists all the entity classes in the left column and their associated primary keys in the right column.

Entity Relationship Diagrams

1. Define and draw the basic relationships among the entity classes.
2. Define among which of those entity classes relationships exist and then write some verbs that describe each relationship just as in Figure C.4 in the textbook.Draw these.
3. Define and draw the relationships among the entity classes as in Figure C.6 in the textbook.
4. Define and draw the cardinality among the entity classes as in Figure C.8 in the textbook.
5. Provide a narrative description of each numerical relationship in Question 4.
6. Eliminate the many-to-many relationship between Student and Seminar Section by creating acomposite entity. Follow the guidelines in the text. Name the composite entity.
7. Draw the entity relationship diagram for Question 6 including the composite entity from Question 6, Student and Seminar Section. Use Figure C.7 as an example.
8. Create a table for the Student relation. Fill in the column headings by identifying what information belongs in the Student relation. Use simple attributes. Then, add some data in the remaining rows. Limit it to five rows of information.

Attachment:- Assignment.rar