Case 2: Gain Medical Supplies

Gain Medical Supplies, a manufacturer and distributor of equipment used in medical facilities, launched Gain Service Shop as an e-commerce site for hospitals and others to order spare and replacement parts.
Carrie Fraser is the owner of Gain Medical Supplies and she currently has a small group of employees for receiving online orders, answering customer questions and delivering parts. Carrie needs to oversee the manufacturing, equipment sales, and she needs to travel throughout the province to meet with customers..
Carrie’s e-commerce website needs to be upgraded. Her online payment system is working fine but her web pages need to be more attractive. She needs help to design a database to keep track of sales. And she also needs an excel spreadsheet to help her quickly calculate customer sales.

Part A: Database

Carrie has purchased MS Access for the database but needs help designing the tables and queries. She would like you to design the tables and queries for her. Note: I am assuming you don’t have MS Access at home so you don’t need to create the actual database.
The database should contain information on the following:

• Employee
• Customer
• Equipment
• Parts

(A1) Design the relational database
Use MS Word to create a design that shows the tables, fields, and relationships. You can use Microsoft Word’s Insert menu then choose Shapes to insert a Text Box and a Line.  If you don’t have MS Word or any other word processing software at home then you can use Google Docs. Click here to go to the Google Docs website. To use Google Docs you will need to create a Gmail account. To create a document using Google Docs you just need to click New and then Google Docs. In Google Docs click the Insert menu, choose Drawing, and then click New. Google Docs is a little more troublesome because you have to put a border around your table.  Once you have completed and saved your document in Google Docs you can close and then right-click the file in the directory and Download it as a Microsoft Word file (.docx) or a pdf file. Or you can just neatly draw your diagram on paper and submit a picture of it.
An example is shown here. Note that your diagram should clearly identify primary and foreign keys as well as the relationships between tables.
(A2) Create Queries
Carrie would like to have queries set up to find out the following information:

• All parts order by a all hospitals this next week
• The names of all customers served by an employee named James

You don’t need to create the actual queries with MS Access but instead use a simple diagram to show which tables, records, and criteria you would use in your query.

Part B: Web Development

Gain Medical Supplies website has not been updated since it was originally set up 5 years ago. In the meantime they have updated their fonts/colors used in marketing material, and would like to have the same style on their website. They would also like to have a new page for the home page of their website.
Use Notepad++ (or other text editor) to create a html page with the following content:
Webpage Requirements:

• Heading and introduction to the Company
• Services offered
• At least 1 image
• Useful links to websites about BC’s health care system

Part C: Spreadsheet

Carrie would like you to create a spreadsheet to help him calculate the cost of each adventure tour. The costs will include the following:

• Equipment/Parts Costs
• Installation Costs
• Delivery Costs
• Employee Labour Costs

Be sure to format the spreadsheet with headings, borders, colors, and currency formats ($).

Part D: Python

Gain Medical Supplies  would like you to create a program that can be added to their website. The purpose of the program is to allow the hospital to choose the part they would like to order and the quantity. Your program will then calculate how much the hospital needs to pay. You can refer to your excel spreadsheet formula to determine the calculation.
You should include a heading and introductory paragraph to be displayed on your program. At the end of the program there should be a message to thank the user for visiting the webpage.
Within the programming code itself, you should use good programming style.
You must only use code covered on this course.

Part E: Report

Using MS Word, write a report (300-400 words) which covers the following points:

1. How well do you believe you’ve met the requirements of this assignment?
2. What you have learnt from working on this assignment?
3. What would you do differently in the future?

Deliverables:

You will submit the files below in the following order:
Relational Database Model Table Design
Query Designs
Web Page
Python Program
Report
Before you submit your assignment, you will be required to verify that the work you are submitting is your own – an automatic message will appear when you click submit.