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:
(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:
Heading and introduction to the Company
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:
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:
How well do you believe you’ve met the requirements of this assignment?
What you have learnt from working on this assignment?
What would you do differently in the future?
You will submit the files below in the following order:
Relational Database Model Table Design
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.
Number of Questions: 11
1. The Python program below has an error so the program will not run. What kind of an error is this?
Note: This kind of error occurs when the code does not follow the rules expected by the programming language.
2. In the studentaverage.py program shown in question #1 above, what is the data type of the variables stud01, stud02, and stud03?
3. In the studentaverage.py program shown in question #1 above, what is the data type of the variable average?
4. What is the error in the studentaverage.py program shown in question #1 above?
5. Which of the following variables are invalid?
6. Give the result of each of the following arithmetic expressions.
a. 19 % 3
b. 19 / 3
c. 19 // 3
d. 2 ** 5
e. 5 != 4
7. What is the data type of the variable x in the Python program below?
8. Give an example of what would appear if you ran the program in question #7 below. Be sure to include an input value.
9. The conditional statement below uses an if statement to check if the value of the variable is greater than zero. What is the datatype of the expression num > 0 ?
if num > 0:
print (“greater than zero”)
10. What is the value of the variable num3 after the statement in line 3 has been executed? Which message will be output by the if else statement, line 5 or line 7?
line 1: num1 = 27
line 2: num2 = 6
line 3: num3 = num1 % num2
line 4: if num3 > 3:
line 5: print (“greater than 3”)
line 6: else:
line 7: print (“not greater than 3”)
11. View the output of Running 1 and Running 2 for the Python program below and then fill in the blanks for the missing code in line 6 to line 9.
3 * 4 =
What is the answer? 12 <- The 12 is entered from the keyboard by the user while the program is running
You answer is correct
3 * 4 =
What is the answer? 7 <- The 7 is entered from the keyboard by the user while the program is running
Wrong. The answer is 12
line 1: print (“Math Test”)
line 2: num1 = 3
line 3: num2 = 4
line 4: print (num1,”*”,num2,”=”)
line 5: x = int(input(“What is the answer? “))
line 6: if (___________):
line 7: _____________________________
line 8: else:
line 9: _____________________________