Section 1
Working on this individual project assumes that you have completed the hands-on practice 2 from module 4; consequently, you should have
· Created PATIENT, PHYSICIAN, CONSULTATION, PRESCRIPTION, DRUG, PRES_DRUG, PAT_DRUG_ALGY, OFFICE, PHARMACY tables
· Implemented all FK relationships for PATIENT, PHYSICIAN, CONSULTATION, PRESCRIPTION, PRES_DRUG
· Inserted “dummy” records into PATIENT, PHYSICIAN, PRESCRIPTION, DRUG, and OFFICE tables
Note: In case that you have not worked on the hands-on practice 2 (HOP2), please refer to module 4 where the instructions for it are available. Otherwise, you could use the HOP2 solution file (HIT.accdb) I uploaded on week five. So, either using the database you have developed or HIT.accdb, perform the following activities:
|
1) Insert 6 “dummy” records into the PHARMACY table (you can use SQL statements or directly type in the value,
you can use unique values of your choosing for the PK)
Try to use actual values of the NPICode from National NPI Number Registry
http://www.npinumberlookup.org/, enter “Walgreens” into “Last Name” field and select “State” as KS to get NPI codes, 1205188281 will be the value for Emporia Walgreens.
2) Add values to the FK PharmacyID in PRESCRIPTION (Note that any value you enter must already be present as a PK value in PHARMACY)
3) Insert 6 “dummy” records into the CONSULTATION table (you can use SQL statements or directly type in the value,
you can use unique values of your choosing for the PK). Note that PatientID and PhysicianID are FKs and the values you enter here must already be present as PK values in PATIENT and PHYSICIAN respectively (i.e., the referential integrity condition).
4) Insert 6 “dummy” records into the PRES_DRUG table (you can use SQL statements or directly type in the value,
you can use unique values of your choosing for the PK). Note that you have to meet referential integrity conditions.
5) Implement the M: N allergy relationship between PATIENT and DRUG by using PAT_DRUG_ALGY as the junction table. A junction table will have the PKs of the tables they are connecting as FKs – in this case, PAT_DRUG_ALGY has PatientID (which is the PK of PRESCRIPTION) and DrugID (which is the PK of DRUG) as FKs. Use the ALTER TABLE SQL statements to implement PatientID and DrugID as FKs in PAT_DRUG_ALGY.
6) Insert 6 “dummy” records into the PAT_DRUG_ALGY table (you can use SQL statements or directly type in the value,
you can use unique values of your choosing for the PK). Note that you have to meet referential integrity conditions.
7) Create a NURSE table using the CREATE TABLE SQL statement.
8) Implement the 1: 1 relationship between NURSE and OFFICE (OfficeID is an FK in NURSE).
9) Insert 6 “dummy” records into the NURSE table (you can use SQL statements or directly type in the value,
you can use unique values of your choosing for the PK).
10) Create the junction table NUR_SCH table using the CREATE TABLE SQL statement.
11) Implement the two FK relationships of the NUR_SCH table.
12) Insert 6 “dummy” records into the NUR_SCH table (you can use SQL statements or directly type in the value,
you can use unique values of your choosing for the PK).
13) Create and populate the INGREDIENT table.
14) Create and populate the DRUG_INGDT table.
15) Save the MS Access database, naming it as “Lastname_Firstname_FP_Dbase,” in my case the file would be named Chavarria_Juan_FP_Dbase.
16) Provide an E-R diagram developed in either MS Visio or Draw.io. Save the file as a PDF file and upload it. Name the file as “Lastname_FirstName_FP_ERDiagram, ” in my case the file would be named Chavarria_Juan_FP_ERdiagram.
Section 2
This section of the assignment requires elaborating a description of a plausible scenario and actions that would need to be performed to justify the database analysis and design that you worked on part 1.
The description must include
· The type of organization that may take advantage of a database design like this, so you will have to
o Provide a mission statement for the organization
o Describe a possible organizational scenario that supports the implementation of the database design
· Techniques that could be used to gather requirements and types/profile of type that should be involved along with the rationale for selecting them
· Context and data flow diagram level-0 supporting the logical design/implementation of the implemented database model
· A Microsoft Project File representing the major stages and activities required to plan, analyze, design, and implement the database used in this assignment
Submission instructions
You will submit the following three files
1. Microsoft Access database file in section 1
2. Visio or Draw.io E-R Diagram in section 1
3. Word document with the description requested in section 2
4. Microsoft Project or Project Libre file with the content requested in section 2