代写存储过程、SQL函数语句

日期：2018-04-23 09:57

Each team is most likely to have the following tables in the existing model.  Use the Excel data provided with this phase link:

PATIENT (some teams called it EMPLOYEE or CUSTOMER)  

-If needed, alter your PATIENT table to include the fields in the imported patient Excel file.  Do not remove extra fields you may have in the table

-PATIENT table PK is in a 4-digit number

-The PATIENT table is likely to have relationships with other tables.  If PATIENT has a 1-to-m relationship with table XYZ, you will need to ensure that PatientID exists as foreign key in table XYZ.  Drop or add any constraints as needed

EMPLOYER

-If needed, alter your EMPLOYER table to include the fields in the imported EMPLOYER Excel file provided.  Do not remove any extra fields you may have in the table

-There is one employer record for now; all patients/employees work for that employer until further notice

-If the EMPLOYER table is joined with other tables, then keep in mind the PK/FK nature of the relationships

-Your model is most likely to have a relationship between EMPLOYER and EMPLOYEE (1-to-m).  

DOCTOR

-If needed, alter your DOCTOR table to include the fields in the imported DOCTOR Excel file provided.  Do not remove any extra fields you may have in the table

-Randomly distribute patients among the doctors available in DOCTOR Excel file.  You have 41 patients and 13 doctors, which leads to approximately 4 patients per doctor

-Establish a relationship between PATIENT and DOCTOR (1 doctor, may patients) if not available

-LicenseNo is the DOCTOR table PK, data type Number

PHARMACY

-If needed, alter your PHARMACY table to include the fields in the PHARMACY Excel file provided.  Do not remove any extra fields you may have in the table

-You have 26 pharmacies with unique Pharmacy Number

-Decide on how PHARAMACY will relate to other tables.  Example, if PHARMACY has a relationship with PATIENT, you might want to manually relate PATIENTS based on the location of pharmacies in or nearest the city where they live

-Before importing PHARMACY data into SQL, use Excel function to separate the city, State, Zip column into three separate columns: City, State and Zip.  Yes, there are ways to do this in Excel, Google it!

-Pharmacy number 99999 is the mail-in/Fax/Web prescription location

HOSPITAL

-If needed, alter your HOSPITAL table to include the fields in the HOSPITAL Excel file provided.  Do not remove extra fields you may have in the HOSPITAL table

-Ensure that you have relationship(s) between HOSPITAL and other tables.  Assume that doctors work in hospital locations

-Before importing HOSPITAL data into SQL, use Excel function to separate the city, State, Zip column into three separate columns: City, State and Zip

HEALTHCARE PLAN (or INSURANCE company)

-If needed, alter your HEALTH PLAN/INSURANCE table to include the fields in the HealthPlan Excel file provided.  Do not remove extra fields you may have in the Healthcare Plan table

-Randomly distribute patients among health plans as you did when you assigned patients to doctors.  No need to assign plans to doctors nor to establish a relationship between PLAN and DOCTOR (unless you feel necessary)

PRESCRIPTION

-This is an important table that contains prescription activities for several patients over a period of time

-This table has data that refer to patients and pharmacies where the drugs were picked up

Drug

-This table has two columns one for drug No and another for drug name

Additional Tables to be Populated

Choose a minimum of 3 tables and populate with your own data.  Those tables can be Basic, Subtype or Associative tables.  The choice of the 3 tables should be strategic!  For example, if any table given in the above section has relationships with any of the 3 additional tables, then this table would be a good choice to use.  Choose tables that facilitate joins or relationships.   A minimum of 5 records per each additional table is required; the more records, the better.  Indicate in BB if you exceed the minimum record count for 3 additional tables

For the 3 additional tables, create Excel files, populate with your own data then use Import Wizard.  Save the INSERT statements used and include them Populate_TeamOracleID.sql.  

For the 3 additional tables INSERT scripts use comments as follows:

/* Additional Table 1:  Table Name */

/* SQL statements used to populate the table */

Things to Consider in this Phase:

1.Do not remove tables that were created in Phase 3 in team’s account.  If you do, use BB comments to inform the instructor

2.Even though the 3 additional tables require a minimum of 5 records, populate those tables with your own data sufficiently enough to ensure that potential queries will generate substantial results.  The more records you add, the more realistic the outcome is.  This is really important for 1-to-m relationships

3.Given the sample data provided, you may have to adjust data type and field size

4.Maintain consistency in formats such as dates throughout the database

What to submit and Grading Criteria:

Upload Populate_TeamOracleID.sql, this file is reserved for data import scripts

1.Answer all questions on BB; review questions sooner than later.  Each team member must prepare a list of specific tasks that he/she performed in this Phase.  Send task list to team leader who will compile and enter in BB questions

2.Grading will not be as flexible as previous phases.  Please ensure that you meet the requirements fully, otherwise penalties will be assessed harshly!

3.Answers to any Phase questions will be shared with all groups via email, please check your messages

Suggested Order of Phase 4 Completion:

1.Examine the Excel data files and compare to existing tables

2.Using SQL, modify existing tables to accommodate incoming Excel data.  Do not remove tables, use ALTER or DROP column or constraint commands

3.Track your work in #2 in the designated file

4.Decide which 3 additional tables to populate and create Excel data file for each

5.Start the import process and track your work in the designated file

and run them as a script to delete the tables.