Search results for 'Access College Pet Sitters'

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.

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.

COM 330 Chapter 12 Assignment

1. The following data structure and constraints exist for a magazine publishing company.
a) The company publishes one regional magazine in each region: Florida (FL), South Carolina (SC), Georgia (GA), and Tennessee (TN).
b) The company has 300,000 customers (subscribers) distributed throughout the four states listed in Part a.
c) On the first of each month, an annual subscription INVOICE is printed and sent to each customer whose subscription is due for renewal. The INVOICE entity contains a REGION attribute to indicate the state (FL, SC, GA, TN) in which the customer resides:

CUSTOMER (CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_CITY, CUS_ZIP, CUS_SUBSDATE)
INVOICE (INV_NUM, INV_REGION, CUS_NUM, INV_DATE, INV_TOTAL)

The company’s management is aware of the problems associated with centralized management and has decided to decentralize management of the subscriptions into the company’s four regional subsidiaries. Each subscription site will handle its own customer and invoice data. The management at company headquarters, however, will have access to customer and invoice data to generate annual reports and to issue ad hoc queries such as:
• List all customers by region.
• List all new customers by region.
• Report all invoices by customer and by region.

Given those requirements, how must you partition the database?

2. Given the scenario and the requirements in Question 2, answer the following questions:
a. What recommendations will you make regarding the type and charistics of the required database system?
b. What type of data fragmentation is needed for each table?
c. What criteria must be used to partition each database?
d. Design the database fragments. Show an example with node names, location, fragment names, attribute names, and demonstration data.
e. What type of distributed database operation must be supported at each remote site?
f. What type of distributed database operations must be supported at the headquarters site?

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.

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.

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.

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 4 Creating Reports and Forms AC 209 - AC 265

Project — Reports and Forms
Camashaly Design has realized several benefits from using the database of clients and analysts. Camashaly hopes to realize additional benefits using two custom reports that meet their specific needs. Figure 4 – 1 shows the first report, which is a modified version of an existing report. The report features grouping. The report shown in Figure 4 – 1 groups records by client types. There are three separate groups, one each for the three possible client types: NON, RET, and SER. The appropriate type appears before each group. The totals of the Amount Paid, Current Due, Total Amount, and Hrs YTD fields for the clients in the group (called a subtotal) appear after the group. At the end of the report are grand totals of the same fields.

Figure 4 – 2 shows the first page of the second report. This report encompasses data from both the Business Analyst Table and the Client table. Like the report in Figure 4 – 1, the data is grouped, although this time it is grouped by analyst number. Not only does the analyst number appear before each group, but the first name and last name of the analyst appear as well. Like the first report, this report contains subtotals.

Camashaly also wants to improve the data entry process by using a custom form, as shown in Figure 4 – 3. The form has a title and a date. It does not contain all the fields in the Client table, and the fields are in a different order than in the table. For this form,
Camashaly likes the appearance of including the fields in a grid.

Microsoft Access 2010 CHAPTER 5 Multitable Forms Camashaly Design AC 274 - AC 326

Project — Multitable Forms
Camashaly Design uses its database to keep records about clients and business analysts. After using the database for several months, however, Camashaly has found that it needs to maintain additional data on its business analysts. The company wants to identify those business analysts who have a professional certifi cate in business analysis, store the start date of each business analyst in the database, and include notes about each business analyst as well as the business analyst’s picture. Additionally, business analysts now maintain files about potential contacts. These files are separate from the database; some are maintained in Word and others in Excel. Camashaly would like a way to attach these files to the corresponding business analyst’s record in the database. Finally, Camashaly wants to add the Phone Number field to the Business Analyst Table. Users should type only the digits in the telephone number and then have Access format the number appropriately. If the user enters 7195558364, for example, Access will format the number as (719) 555-8364.

After the proposed fields have been added to the database, Camashaly wants users to be able to use a form that incorporates the Client and Business Analyst tables and that includes the newly added fields as well as some of the existing fields. The form also should include the client number, name, amount paid, and current due amount for the clients of each business analyst. Camashaly would like to see multiple clients on the screen at the same time (Figure 5 – 1). The database should allow users to scroll through all the clients of a business analyst and to open any of the attachments concerning the business analyst’s Client Notes. Finally, Camashaly requires queries that use the Certification, Start Date, and Comment fields.