MS Access

Final Project Part IV: Using MS Access

1. Based on the design created in Week 2, create the physical design of the database using MS Access.
 Create tables that represent the entity that you created in Week 2.
 Create the required columns with the appropriate data types.
 Specify an appropriate primary key for the table.
 Specify constraints for columns wherever applicable

2. Enter at least ten records in the database.

3. Query the database.
Create queries to filter records based on various conditions that you can think of. Some queries you can create include:
 If your table has an Age column, then create a query to view the records of students of the same age.
 If your table has an Address column, then create a query to view the records of students living in the same area.

4. Check if there is a need to redesign the database.

5.Create entry forms for the database.
 Create a form that helps you enter records in the table you created.
 Ensure that the constraints you applied in the table are also checked  in the form.
 Suggest a strategy to ensure that the primary key values are not repeated.

6.Create a report that displays records of all students.

Design the database, enter data, run the queries, create the forms, create the reports and submit your database to the Dropbox. In a Word document, share your experience about this design and implementation process and discuss the need and scope for redesigning the database.

Database Management Midterm RRGroup database

1. You are creating a database for donations that are made to different non-for-profit agencies by different individuals. The database is made of three tables: Contributors (the list of donors with information about them), Donations (the list of generous gifts) and Agencies (the list of non-for profit organizations that receive gifts).
2. Download all the files from the Midterm folder. Remember: it is best to first save them on your local computer before you open them and start working on them.
3. Open the database named RRGroup. It comes with the Contributors table. This is the one you are going to do all work.
4. Populate the Agency table with data from Agency text file.
5. Examine the Gifts Excel file and based on that file design the third table (headings, primary key, data type). Save it as Donations. Populate it with data from the Gifts file. Remember about the Primary Key.
6. Establish relationships between all three tables. Do all that is necessary to have them established. All of them must be “One-To-Many”.
7. Create a form for the Contributors table. Be sure it displays only one record at a time. Save it.
8. Create a query with the following data:
 a. Name of the Agency
 b. Number of donors.
 c. Average amount of donation.
  i. Name this query “Average Gift”
9. Create another query with the following data:
 a. Donor’s last name
 b. Agency name
 c. Agency’s city. Provide ability to enter the city you want to view in the query
 d. Amount of donation. You want to see donations over $50.
 e. You want to see those donations that need to be picked up.
  i. Attention: all the criteria must be “AND” – this query should display only those donors that gave gifts to agencies from an entered city and in the amount over $50 and gave gifts that need to be picked up.
  ii. Name this query “Gifts in a City”
10. One more query. This time no criteria, only 3 fields: Donor’s last name, Agency Name and amount of donation. Save it as “Query for report”.
11. Create a report for the “Query for report”. Make sure that records are listed by Contributors. All other settings can be left as the default ones. Name the report: List of Contributions.

Access Capstone Project
Creating, Querying, and Maintaining the Wee Ones Database Problem:

Elizabeth Jordan has asked you to work on a special project for the Wee Ones Day Care. She has been keeping information about the children in the program and their parents in Word documents and Excel workbooks, and she would like you to create an Access database for storing this information, using the data shown in Figure 1 – 7.

Elizabeth keeps a list of the children and their enrollment details in a Word document. She maintains their tuition and fee information in an Excel workbook.

She also maintains information on the children’s parents. She keeps their parents’ addresses in a Word document. However, she keeps the previous and current balances for each parent in an Excel workbook, which she provides to you electronically. The parents’ financial information includes the parent ID, their last and first names, previous balance, and current balance.

Elizabeth also keeps a list of which children belong to which parent. Each child has one parent listed as the ‘parent of record,’ but a parent or guardian may be responsible for more than one child. The following parents have one child in the program: Cho Song, Karen Leon, Mara Olivo, Mary Ann Arnold, Daniel Navarro, Addas Abdi, and Claire Montout. Allen Kwan has two children in the program. The Hunter twins’ mother’s name is Jennifer White. Barbara Lehmann is responsible for Emma Chao, Lucas Jordan, and Paul Lehmann. Susan Hargraves has reserved a place at Wee Ones for her child, but her child has not yet enrolled.

Instructions Part 1: Using the data shown in Figure 1 – 7, design and create the Wee Ones database. Use the database design guidelines provided in Chapter 1 to help you in the design process.

Instructions Part 2: Answer the following questions, using the Parent table, and include the Parent ID, parent’s first and last names, City, Previous Balance, and Current Balance fields. Save each query.

Instructions Part 3: Elizabeth needs to update some parent and child information. Create a split form for the Parent table. Filter the form to locate Parent ID P-131 and change their address to 1010 Florissant Rd, Lincolnville, 02923. Save the form as Parent Split Form.

Microsoft Access 2010 Chapter 2 Lab 2: Querying the Walburg Energy Alternatives Database

Problem: The manager of the Walburg Energy Alternatives store has determined a number of questions he wants the database management system to answer. You must obtain answers to the questions posed by the manager.

Instructions: Use the database created in the In the Lab 2 of Chapter 1 on page AC 67 for this assignment, or see your instructor for information on accessing the fi les required for this book.

Perform the following tasks:
1. Open the Walburg Energy Alternatives database and create a query that includes all fields and all records in the Item table. There should be only one column in the design grid. Name the query Lab 2-2 Step 1 Query.
2. Create a query that includes the Item Number, Description, Cost, and Vendor Code fields for all items where the vendor code is JM. Save the query as Lab 2-2 Step 2 Query.
3. Create a query that includes the Item Number and Description fields for all items where the description starts with the letters, En. Save the query as Lab 2-2 Step 3 Query.
4. Create a query that includes the Item Number and Description fields for all items with a cost less than $4.00. Save the query as Lab 2-2 Step 4 Query.
5. Create a query that includes the Item Number and Description fields for all items with a selling price greater than $20.00. Save the query as Lab 2-2 Step 5 Query.
6. Create a query that includes all fields for all items with a vendor code of AS and where the number on hand is fewer than 10. Save the query as Lab 2-2 Step 6 Query.
7. Create a query that includes all fields for all items that have a selling price greater than $10.00 or a vendor code of JM. Save the query as Lab 2-2 Step 7 Query.
8. Join the Vendor table and the Item table. Include the Vendor Code and Vendor Name fields from the Vendor table and the Item Number, Description, On Hand, and Cost fields from the Item table. Sort the records in ascending order by item number within vendor code. Save the query as Vendor-Item Query.
9. Create the form shown in Figure 2 – 93. The form uses the Vendor-Item Query.
10. Create a query that includes the Item Number, Description, On Hand, and Cost fields. Calculate the inventory value (on hand*cost) for all records in the table. Assign the alias Inventory Value to the calculated field. Change the caption for the On Hand column to In Stock. Format the Inventory Value field as currency with two decimal places. Sort the records in descending order by inventory value. Save the query as Lab 2-2 Step 10 Query.
11. Create a query that calculates and displays the average cost of all items. Save the query as Lab 2-2 Step 11 Query.
12. Create a query that calculates and displays the average cost of items grouped by vendor code. Save the query as Lab 2-2 Step 12 Query.
13. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 1 Lab 2: Creating the Walburg Energy Alternatives Database
Problem: Walburg Energy Alternatives is a nonprofit organization that promotes the use of energy alternatives such as solar power and wind power. The organization provides a variety of services and funds itself through donations. Recently, the organization decided to sell a small number of items in its education center to help fund programs. The store purchases the items from vendors that deal in energy-saving products. Currently, the information about the items and vendors is stored in the Excel workbook shown in Figure 1–95. Each item is assigned to a single vendor, but each vendor may be assigned many items. You volunteer part-time at the store, and the store manager has asked you to create a database that will store the item and vendor information. You have already determined that you need two tables in which to store the information: an Item table and a Vendor table.

Instructions: Perform the following tasks:
1. Design a new database in which to store all the objects related to the items for sale. Call the database Walburg Energy Alternatives.
2. Use the information shown in the Excel workbook in Figure 1–95 to determine the primary keys and determine additional fields. Then, determine the relationships between tables, the data types, and the field sizes.
3. Create the Item table using the information shown in Figure 1–95.
4. Create the Vendor table using the information shown in Figure 1–95. Be sure that the field size for the Vendor Code in the Item table is identical to the field size for the Vendor Code in the Vendor table. Add the caption, Phone, for the Telephone Number field.
5. Add the appropriate data to the Item table. Resize the columns to best fit the data and save the changes to the layout.
6. Add the appropriate data to the Vendor table. Resize the columns to best fit the data and save the changes to the layout.
7. Create a query for the Item table. Include the Item Number, Description, Cost, Selling Price, and Vendor Code in the query. Save the query as Item Query.
8. Open the Item Query and add a criterion to limit retrieval to those items supplied by Scryps Distributors. Save the query as Item-Scryps Query.
9. Create a simple form for the Item table. Use the name, Item, for the form.
10. Create the report shown in Figure 1–96 for the Item table. Do not add any totals.
11. Change the database properties, as specified by your instructor. Submit the database in the format specified by your instructor.

Create a database using MS ACCESS or SQL (your choice).  Your database will be a college database, such as Tiny College that tracks students, the classes they are enrolled in, the location of the class and the instructor of the class.  You will also show reports and queries to your database.  Such as all students who have made an A in History 101, or all the classes that Professor Smith teaches and what buildings/room that he teaches in.  How many students are Math majors?  Etc….. These are just some examples; use your knowledge of system life cycle, prototyping, Implementation support, organizational issues, standards, etc…

Minimum of 25 students, 10 professors and 10 buildings/rooms, and 15 classes.

COM 330 Chapter 6 Assignment

1. Using the STUDENT table structure shown in Table P6.4, do the following.

Table P6.4 Sample STUDENT Records
Attribute Name    Sample Value    Sample Value    Sample Value    Sample Value    Sample Value
STU_NUM    211343    200128    199876    199876    223456
STU_LNAME    Stephanos    Smith    Jones    Ortiz    McKulski
STU_MAJOR    Accounting    Accounting    Marketing    Marketing    Statistics
DEPT_CODE    ACCT    ACCT    MKTG    MKTG    MATH
DEPT_NAME    Accounting    Accounting    Marketing    Marketing    Mathematics
DEPT_PHONE    4356    4356    4378    4378    3420
COLLEGE_NAME    Business Admin    Business Admin    Business Admin    Business Admin    Arts & Sciences
ADVISOR_LNAME    Grastrand    Grastrand    Gentry    Tillery    Chen
ADVISOR_OFFICE    T201    T201    T228    T356    J331
ADVISOR_BLDG    Torre Building    Torre Building    Torre Building    Torre Building    Jones Building
ADVISOR_PHONE    2115    2115    2123    2159    3209
STU_GPA    3.87    2.78    2.31    3.45    3.58
STU_HOURS    75    45    117    113    87
STU_CLASS    Junior    Sophomore    Senior    Senior    Junior

c. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

d. Draw the Crow’s Foot ERD.

Load the data from table P6.4 into an Access database. Add 5 additional students (make up your data) following the table structure.
Create a query in Access that list the students who have a Statistics major.
List the students who have a GPA of 3.45 or higher in a report.
Report the students who have the same advisor.

Submit c and d as an attachment, attach your Access database.

Student Database Design in Access

Using the student database designed from Chapter 2, create a simple Access Database with a minimum of 15 students, 4 classes each, 5 different instructors, in four different classroom buildings.

For example, Jennifer Smith has Math 101, Biology 201, Chemistry 100, and Speech 101. She has instructor Kelli Jones, Tom Tinker, Bryan Solider, Tiger Woods. Her building would include Silly Science, The Pond building, Mighty Duck, and Jimmy Carter. In your database design forms and queries.

INF322 DATABASE MANAGEMENT Week 5 Final Project Customer Service Database

The Final Project consists of building a customer service database to be submitted by the final day of the course.

Focus of the Project
1. Use the Access database you created in Week 3: Customer Service Database

2. Output 3 queries using SQL code and not the standard access view:
A query on all contacts in the customers table showing first name, last name, address, e-mail address and three additional fields from the table.
An update query that changes one or more customer records but not all records.
A delete query that removes one or more customer records but not all records.

3. Using SQL commands, add a new field called Birthday to the Customers table. Populate some of the fields with data.

4. Report on Customers in Database showing Names, State, and Phone Numbers.

5. Create a report showing contact name and birthdays. Do not show records without a birthday.

6. Analyze the data populated in the Database to show referential integrity. This includes a statement showing what the affected tables in each dataset are and how they are impacted in the case of an addition of data or a deletion of data.

7. All data and screen shots should be included in a Word document with a brief explanation of the actions you performed.