Homework 1 Assignment Paper
Homework 1. Due Wednesday8/30, Before Class. 10 Points. (Graded on 40-point basis then scaled down.)
If you have questions about the class or this homework, feel free to contact me at Zhiping.walter@ucdenver.edu, 303-315-8840, or stop by my office.
Problem 1. (20 Points): Consider the tables given below:
Doctor table gives a list of doctors.
Patient table gives a list of patients and their info.
Shift table gives a list of shifts in a day.
Appointment table gives a list of appointments between a doctor and a patient. An appointment is scheduled on a specific day during a specific shift.
1). Create a database file containing the following 4 tables (Doctor, Patient, etc.). Use copy-paste from the datasheet view to copy the data directly from this Word file unto a database table. Or you can copy-paste a table to EXCEL then import from EXCEL. If you want to just type in the data for the smaller tables (e.g., Doctor table), that’s fine. Name your tables with the names given here. Name your database YourLastNameFirstNamehw1.accdb.
2). Based on the business rules described below, identify and set the primary key in ACCESS for each table. DO NOT ADD A COLUMN TO A TABLE AS THE PRIMAY KEY.If there are multiple possibilities for primary key, choose the one that you think is the best.
- Each doctor has a unique id.
- Each patient has unique patient id.
- Each shift is assigned a unique shift number.
- Each appointment is assigned a unique appointment id.
3) After setting the primary keys, identify and set foreign keys in ACCESS. (Wait until after Monday 8/28’s class to do this problem.)
Doctor: (DoctorIdLastname, FirstName, DateJoined)
| DoctorId | LastName | FirstName | DateJoined |
| D1 | Johnson | Emily | 5/1/2005 |
| D2 | Michaels | Susan | 6/7/2004 |
Patient:(PatientID, Lastname, FirstName, Phone, Insurance, FirstVisit, Email)
| PatientId | Lastname | FirstName | Phone | Insurance | FirstVisit | |
| P1 | Ford | George | (860) 645-5678 | BCBS | 5/1/2006 | Ford12@comcast.net |
| P2 | Gibbs | Mary | (860) 429-8790 | Kaiser | 6/5/2006 | Gibbs34@comcast.net |
| P3 | Jordan | Jeff | (860) 486-4646 | BCBS | 6/8/2007 | Jeff1200@gmail.com |
| P4 | Rhees | John | (617) 424-1234 | Cigna | 7/5/2008 | John32@hotmail.com |
| P5 | Skoog | Jane | (860) 644-8585 | BCBS | 7/7/2008 | Jane.Skoog@email.cudenver.edu |
| P6 | Smith | Dan | (860) 643-4687 | Kaiser | 8/9/2009 |
Shift: (ShiftNum, From, To)
| ShiftNum | From | To |
| 1 | 9:00 AM | 9:30 AM |
| 2 | 9:30 AM | 10:00 AM |
| 3 | 10:00 AM | 10:30 AM |
| 4 | 10:30 AM | 11:00 AM |
| 5 | 11:00 AM | 11:30 AM |
| 6 | 1:00 PM | 1:30 PM |
| 7 | 1:30 PM | 2:00 PM |
| 8 | 2:00 PM | 2:30 PM |
| 9 | 3:00 PM | 3:30 PM |
| 10 | 3:30 PM | 4:00 PM |
Appointment: (ApptNum, ApptDate, DoctorId, ShiftNum, ScheduledWith, ShowedUpOrNot)
| ApptNum | ApptDate | Doctorid | ShiftNum | ScheduledWith | ShowedUporNot |
| 1001 | 1/10/2010 | D1 | 1 | P1 | No |
| 1002 | 1/10/2010 | D1 | 2 | P2 | No |
| 1003 | 11/15/2011 | D1 | 1 | P1 | Yes |
| 1004 | 11/15/2011 | D1 | 1 | P4 | No |
| 1005 | 11/15/2011 | D1 | 2 | P2 | Yes |
| 1006 | 11/15/2011 | D1 | 3 | P4 | Yes |
| 1007 | 12/11/2011 | D1 | 6 | P4 | Yes |
| 1008 | 12/11/2011 | D2 | 6 | P2 | No |
| 1009 | 12/11/2011 | D2 | 6 | P3 | No |
| 1010 | 3/10/2012 | D2 | 4 | P5 | Yes |
| 1011 | 3/10/2012 | D2 | 4 | P6 | No |
| 1012 | 5/12/2012 | D2 | 8 | P6 | Yes |
| 1013 | 5/16/2012 | D2 | 1 | P2 | No |
| 1014 | 6/7/2012 | D1 | 1 | P1 | Yes |
| 1015 | 6/7/2012 | D1 | 2 | P2 | Yes |
| 1016 | 6/7/2012 | D1 | 3 | P2 | No |
| 1017 | 6/7/2012 | D1 | 3 | P3 | Yes |
| 1018 | 6/7/2012 | D1 | 4 | P4 | Yes |
| 1019 | 6/7/2012 | D1 | 5 | P5 | Yes |
| 1020 | 6/7/2012 | D1 | 6 | P6 | No |
| 1021 | 6/16/2012 | D1 | 4 | P2 | Yes |
| 1022 | 10/10/2012 | D1 | 2 | P1 | No |
| 1023 | 10/11/2012 | D2 | 3 | P1 | No |
| 1024 | 12/10/2012 | D1 | 1 | P1 | No |
| 1025 | 12/10/2012 | D1 | 2 | P2 | No |
| 1026 | 12/10/2012 | D1 | 5 | P4 | No |
| 1027 | 12/10/2012 | D2 | 2 | P5 | No |
The following two tables, OrderDetails.xlsx and Grades are not part of appointment database. However, in order to simplify submission, put them in YourLastNameFirstNamehw1.accdb anyway.
Problem 2 (10 Points). Use the Excel file Homework 1OrderDetails.xlsx posted with this homework.
- Import the file to form a table (Table OrderDetails) in accdb. Open it in design view and fix data types so they fit the data. When importing, import it with no primary key defined.
- The table OrderDetailscontains items that are ordered in each order. For example, OrderId O1562 ordered two items: SKU1 and SKU3. For each item ordered in each order, the table records price of that item in that order. Identify the primary key for OrderDetails table based on the business rules described above and set the primary key in ACCESS.
Problem 3 (10 Points). Use the database file Homework 1Grades.accdb posted with this homework.
- Import Grades table to accdb. When importing, import it with no primary key defined.
- The table, Grades, contains student grades for courses they take in each semester. Each student gets one grade for each course he takes during one semester. If a student has to re-take a course in a different semester, grades earned in both semesters are recorded. Identify the primary key for Grades table based on the business rules described and set the primary key in ACCESS.
Academic Honesty Rules (Please also see the syllabus)
University rules and procedure will be followed when academic dishonesty is suspected. The following passages discuss some but not all forms of academic dishonesty that may occur in this course.
Homework
All required items are to be done individually. Individual work is not group work. In group work, students contribute to one piece of work and then put all group member’s names on it. Submitting work with only your name on it is a statement that you and only you are responsible for all of the work.
Discussions are allowed for homework assignments. For written assignments, each student must write his or her own answers to homework problems. Copying from each other is cheating. For electronic assignments, each student must start and work on his/her own file; two or more students working on one electronic file is cheating even if later on each modified the original file.
Obtaining partially or fully completed homework file (paper or electronic) from another student or from other sources is not acceptable and is an act of academic dishonesty.
Please be advised that if you submit solutions obtained from other people or other sources, it is evident to me because 1) I can certainly tell if the solution was prepared by me and not by you, 2) discrepancy between your performance in class and your submitted homework will alert me, and 3) discrepancy between your tests and your submitted homework will alert me.
Prohibited Acts
The following behaviors are not allowed and will be brought up on charges of academic dishonesty:
- Giving your Canvas login userid and password to someone else.
- Giving another student a copy of your paper or electronic homework file, be it partially or fully completed;
- Giving assignment solutions that I have posted in Canvas to anyone or any entity outside this class.
- Posting your partially or fully completed homework on a web site or otherwise networked location that is accessible to other people;
- Taking over another student’s homework file and starting to do the homework for that student;
- Dictating to another student what to do on that student’s homework file;
- Two or more students’ submitting a homework file that originated from a single file, even if each student revised the file later and even if all students worked together to create the original file;
- Two or more students’ working on ONE homework file for any part of that homework file;
- Requesting (verbally or through email or through other ways) a partially completed or fully completed homework file (paper or electronic) from another student or from any source;
- Obtaining another student’s homework file without that student’s knowledge;
- With another student’s homework solution open or otherwise available, copy that student’s solution to your own homework file;
- Obtaining solutions to homework assignments from someone or some source other than your instructor;
- Having someone else do part or all of your work;
- Having someone else correct your work before submitting;
Allowed Collaborative Activities
The following collaborative activities among students are ALLOWED for homework assignments:
- After you have tried working on an issue and still cannot resolve a problem, ask another student to point out what is wrong with your solution; this can only be done in person because you cannot send or give your homework file to another person under any circumstance.
- Two or more students working side by side in the Business Lab and discuss issues raised; All students involved must each have his (her) own homework file to work on; Two or more students cannot work on the same file; You cannot copy another student’s solutions—see prohibited acts, item (11).
I allow the above two collaborative activities because I believe students learn a great deal from each other that way. Students should not abuse this policy by submitting work that was not your own, by submitting work originated from another student, or by submitting work that originated from one single file. If Student A tells Student B what to type and where to click on Student B’s homework file, Student B is not really “working” on his own file. Consequently, the file submitted by Student B is not really his own work.
When two students work on one electronic file, it’s necessarily the case that one is typing/clicking and the other is just watching. Just watching another student work on the computer and say “that sounds good” is not working on your homework. “We worked together and then he emailed me the finished the file” is not an acceptable excuse for submitting the same file.
The post Homework 1 Assignment Paper appeared first on Homeworkacetutors.com.
Comments
Post a Comment