Search results for 'microsoft access 2010'

DBM/380 Week 3 ERD Smith Consulting in Access

Individual Database Creation Project

The following assignment is based on the database environment chosen and discussed in the Week Two Individual Assignment.

Create a Microsoft® Visio® diagram to create a detailed Entity Relationship Diagram(ERD) using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty’s feedback.

Create a Microsoft® Access® database to create the preliminary database tables, columns with data types, primary keys, and relationships.

DBM/380 Week 4 Normalization of the Smith Consulting ERD

The following assignment is based on the database environment chosen and created in the Week Three Individual Assignment.
Your database project must meet the following assessment requirements:

Design and develop a database using professional principles and standards.
Provide a logical and physical design of the database.
Use relational database software application to develop database.   
Provide an entity relationship diagram.
Normalize the database.
Generate and provide test data.

Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF).

Use the Microsoft® Access® database created in Week Three to create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables.

Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access® database.

Submit the ERD and final database to the appropriate Assignment link.

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.

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.