I need a professional in excel and access to do a hw for me
anon27m
homework_1_is.docx
Problem 1: 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.
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)
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: Use the Excel file Homework 1 OrderDetails.xlsx posted with this homework.
1. Import the file to form a table (Table OrderDetails) in YourLastNameFirstNamehw1.accdb. Open it in design view and fix data types so they fit the data. When importing, import it with no primary key defined.
2. The table OrderDetails contains 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: Use the database file Homework 1 Grades.accdb posted with this homework.
1. Import Grades table to YourLastNameFirstNamehw1.accdb. When importing , import it with no primary key defined .
2. 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 .
3
