Order Details Order ID Item ID Quantity Unit Price

Orderdetailsorderiditemidquantityunitpriceo1561sku11140000o1562sku1

Orderdetailsorderiditemidquantityunitpriceo1561sku11140000o1562sku1

OrderDetails OrderID ItemID Quantity UnitPrice O1561 SKU1 1 $1,400.00 O1562 SKU1 1 $1,600.00 O1562 SKU3 2 $400.00 O1563 SKU2 1 $1,300.00 O1564 SKU1 1 $1,500.00 O1564 SKU3 1 $420.00 O1564 SKU4 3 $50.00 O1564 SKU5 3 $30.00 O1565 SKU1 2 $1,600.00 O1566 SKU4 7 $45.00 O1566 SKU5 5 $32.00 O1567 SKU2 1 $1,320.00 O1567 SKU3 1 $400.00 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) PatientId Lastname FirstName Phone Insurance FirstVisit Email P1 Ford George ( BCBS 5/1/2006 [email protected] P2 Gibbs Mary ( Kaiser 6/5/2006 [email protected] P3 Jordan Jeff ( BCBS 6/8/2007 [email protected] P4 Rhees John ( Cigna 7/5/2008 [email protected] P5 Skoog Jane ( BCBS 7/7/2008 [email protected] P6 Smith Dan ( Kaiser 8/9/2009 Shift: (ShiftNum, From, To) ShiftNum From To :00 AM 9:30 AM :30 AM 10:00 AM :00 AM 10:30 AM :30 AM 11:00 AM :00 AM 11:30 AM :00 PM 1:30 PM :30 PM 2:00 PM :00 PM 2:30 PM :00 PM 3:30 PM :30 PM 4:00 PM Appointment: (ApptNum, ApptDate, DoctorId, ShiftNum, ScheduledWith, ShowedUpOrNot) ApptNum ApptDate Doctorid ShiftNum ScheduledWith ShowedUporNot /10/2010 D P1 No /10/2010 D P2 No /15/2011 D P1 Yes /15/2011 D P4 No /15/2011 D P2 Yes /15/2011 D P4 Yes /11/2011 D P4 Yes /11/2011 D P2 No /11/2011 D P3 No /10/2012 D P5 Yes /10/2012 D P6 No /12/2012 D P6 Yes /16/2012 D P2 No /7/2012 D P1 Yes /7/2012 D P2 Yes /7/2012 D P2 No /7/2012 D P3 Yes /7/2012 D P4 Yes /7/2012 D P5 Yes /7/2012 D P6 No /16/2012 D P2 Yes /10/2012 D P1 No /11/2012 D P1 No /10/2012 D P1 No /10/2012 D P2 No /10/2012 D P4 No /10/2012 D 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 .

Paper For Above instruction

The task involves creating and organizing a comprehensive database that encapsulates multiple interconnected tables and data sets, including order details, healthcare appointment schedules, and student grades. This entails designing the structure of each table, defining primary keys, and establishing foreign key relationships according to specific business rules. By accurately importing data, setting appropriate data types, and intentionally assigning primary and foreign keys, a robust relational database can be developed to enhance data integrity, facilitate efficient querying, and support the management of complex data relationships.

Firstly, the database creation begins with the OrderDetails table, which records specific items ordered in various transactions. The primary key for this table should uniquely identify each record, commonly involving a combination of OrderId and ItemId, as each order can contain multiple items but each item within an order is unique. Proper data types must be assigned to match the imported data, such as text or number for OrderId and ItemId, and currency or decimal for unit prices. Establishing this primary key ensures that each ordered item in a specific order is uniquely identifiable, preventing duplicate records and maintaining referential integrity.

Next, the healthcare appointment scheduling system involves several tables: Doctor, Patient, Shift, and Appointment. The Doctor table would have DoctorId as a primary key, which is a unique identifier for each doctor, along with their last name, first name, and date joined. The Patient table similarly contains a unique PatientId along with demographic and contact information. The Shift table comprises an assigned ShiftNum, which uniquely identifies each shift, along with start and end times. The Appointment table must include an AppointmentId as a primary key, uniquely identifying each scheduled appointment, with foreign keys linking it to DoctorId, PatientId, and ShiftNum. These relationships enable tracking which doctor is scheduled for which patient during specific shifts on certain dates, exemplifying a well-structured scheduling database.

Furthermore, to integrate existing grade and order data, additional tables—OrderDetails and Grades—are imported into the same database file. For OrderDetails, since multiple items can be associated with a single order, the composite primary key should involve OrderId and ItemId, reflecting the unique combination of each item in each order. For the Grades table, the business rule indicates that each student can receive multiple grades per course (across different semesters), but the combination of StudentId, CourseId, and Semester(s) should serve as the composite primary key, ensuring that each record distinctly represents a student's performance in a specific course session.

Finally, setting up foreign key constraints is crucial for maintaining referential integrity across tables. For instance, the Appointment table's DoctorId must correspond to a valid DoctorId in the Doctor table, and similarly for PatientId and ShiftNum. These relationships enforce data consistency whereby any appointment references existing doctors, patients, and shifts. Also, during import and design, appropriate data types are specified to match the data imported from Excel or other sources, avoiding type mismatch errors. The overall goal remains to build an integrated database that supports complex querying, accurate data management, and adherence to real-world business rules.

References

• Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.