Part B. Database Implementation using Microsoft Access
Design a relationship report and print out the relationship diagram on one single page. You may find landscape orientation desirable when printing out the report that has the diagram.
C. Database Insert
A new customer, James Bond, just placed an order for two units of 200415 and two units of 400258. You are to use below data related to customer, order and shipping:
Order No: 300-19; Order Date: 2/17/2016
Password: Bond007
Payment No: P-422; Name on Card: James Bond; Credit Card Type: Discover; Credit Card No: 6011002020524567; Expiration Date: 12-19; Security Code: 888.
Shipping ID: S-619; Recipient: Stan Wang; Street Address: 1250 Bellflower Blvd. CBA 432; City: Long Beach; State: CA; ZIP Code: 90840
In addition, you will pretend you were James Bond, choose two security questions, and provide an answer to each of two questions.
Insert the needed records into the database to capture the transaction using the data above.
IS 300 Management Information Systems
College of Business Administration
3
D. Database Queries
1. Design a query to calculate the gross profit and gross margin for each of the products in your product worksheet in descending order of gross margin. The following attributes are to be displayed in sequence: Product No, Product Name, Unit Cost, Unit Price, Gross Profit, Gross Margin. Name the query “Product Gross Profit and Gross Margin.” Print out the result of the query (the datasheet view). In addition, capture a screen shot under Design View and print out the query.
2. Design a query that will display payment methods for the customer with Customer ID 1004. The following attributes are to be displayed in sequence: Credit Card Type, Name on Card, Expiration Date. Name the query “Payment methods for customer 1004.” Print out the result of the query (the datasheet view). In addition, capture a screen shot under Design View and print out the query.
3. Design a query that will display Last Name, First Name, and Email address of customers who have ordered product with Product No 200415, in ascending order of Last Name. Name the query “Customers who have ordered 200415.” Print out the result of the query. In addition, capture a screen shot Under Design View and print out the query.
4. Design a query that will display the products that need to be reordered and their suppliers. The following attributes are to be displayed in sequence: Product No, Product Name, On Hand, Safety Stock, Supplier No, Supplier Name. Arrange the records in ascending order of Supplier Name. Name the query “Suppliers for products that need to be reordered.” Print out the result of the query. In addition, capture a screen shot under Design View and print out the query.
5. Design a query that will list all data elements (including a calculated field for subtotal, but excluding order total) needed to generate an invoice for the order with Order No. 300-05. Print out the result of the query. In addition, capture a screen shot under Design View and print out the query.
In addition to hardcopy printouts (i.e., the relationship diagram, all Access tables, query designs, and the results of queries), and
I have bachelor and master degrees in IT. My Primary skills include Java, Python, VB/.NET, ArcGIS, and PHP. Also database skills include MS Access, MySQL, and Oracle. My Other skills include server and network administration and maintenance with Linux/Unix and Windows Networking.
We at LumiNysis are expert in Excel, Access, VBA and automation across industries and are able to covert any data into useful results
Please provide us an opportunity to cater your request on high priority.