Submit the assignment in a word document format, DON’T forget your name, DON’t U

Question

Submit the assignment in a word document format, DON’T forget your name, DON’t USE SQL CODE FOR THIS ASSIGNMENT

Use Oracle Database Management System Interface to:

1. create the following tables:

PATIENTPER table with the following columns:

1.PatientID

2.FirstName

3.LastName

4.Address

5.City

6.State

7.ZipCode

8.Phone

9.Email

10.MemberID

Select the proper data type, size and the proper null/not null constraints as needed. In addition you need to assign primary key.

PATIENTMHIS table with the following columns:

1.RecordID

2.CardioHis

3.PulmoHis

4.AlergyHis

5.BloodHis

6.Hospitalization

7.CurrentMedication

8.SurgeryHis

Select the proper data type, size and the proper null/not null constraints as needed. In addition you need to assign primary key

IMAGINGORDER table with the following columns:

1.OrderNumber

2.ImDate

3.Organ

4.Reason

5.Diagnosis

Select the proper data type, size and the proper null/not null constraints as needed. In addition you need to assign primary key

LABORDER table with the following columns:

1.LabOrderNumber

2.OrDate

3.ReqBloodTest

4.Reason

Select the proper data type, size and the proper null/not null constraints as needed. In addition you need to assign primary key

INUSRANCEINFO table with the following columns:

1.MemberID

2.GroupID

3.Provider

4.ProviderPhone

5.ProviderAddress

Select the proper data type, size and the proper null/not null constraints as needed. In addition you need to assign primary key

2.Add CHECK constraints to the INSURANCEINFO to limit Provider column to be within the following group only (United, AvMed, Blue Cross Blue Shield, Medicare, Medicare)

3.Add a column to the PATIENTPER table called AdmissionDate

4.INSERT at least three rows into each table.

5.Use the proper columns to connect the tables with each other creating relational database (Hint: I used the MemberID to connect PATIENTPER and INSURANCEINFO, you need to connect the other tables the way you properly see it)

6.Use Oracle to create a report about the different columns in the created tables.

Notes:

You don’t need to write the SQL code for this assignment

Provide a snapshot from Oracle for (the tables that you created, the tables including the data that you inserted, The tables constraints specially for the PATIENTPER table after adding the check constraints, the customer table after adding the new column, also a snap shot of the relational database with all tables connected and a snap shot of a report about the different columns)

Submit the assignment in a word document format

To copy paste the snapshots from Oracle use the buttons (Alt) and (Print Screen), simultaneously, on your keyboard then paste into word

Explanation / Answer

CONCEPT:

This task is to create tables in Oracle using Oracle Data base Management System..

ANSWERS are as follows:

Tables Creation:

Table 1:

create table PATIENTPER (patientID varchar2(10),Fistname varchar2(10),LastName varchar2(10)
,Address varchar2(10),City varchar2(10),State varchar2(10),ZipCode integer(8),Phone number(10)
,Email varchar2(20),MemberID number(10))

Table 2:

create table PATIENTMHIS (RecordID varchar2(10),CardioHis varchar2(10),PulmoHis varchar2(10)
,AlergyHis varchar2(10),BloodHis varchar2(10),Hospitalization varchar2(10),CurrentMedication integer(8)
,SurgeryHis varchar2(20))

Table 3:

create table IMAGINGORDER (OrderNumber varchar2(10),ImDate Date,Organ varchar2(10)
,Reason varchar2(10),Diagnosis varchar2(10))

Table 4:

create table LABORDER (LabOrderNumber varchar2(10),OrDate Date,ReqBloodTest varchar2(10)
,Reason varchar2(10))

Table 5:

create table INUSRANCEINFO (MemberID varchar2(10),GroupID varchar2(10),Provider varchar2(10)
,ProviderPhone varchar2(10),ProviderAddress varchar2(10))

Adding check constraint

alter table
   INUSRANCEINFO
add constraint
   CHECK Provider
   (Provider IN
      (
       'United',
       'AvMed',
       ' Blue Cross Blue Shield',
       ' Medicare',
        'Medicare'
      )
   ) DISABLE|ENABLE;

Adding a Column :

alter table PATIENTPER
ADD AdmissionDate date

Inserting values

insert into PATIENTPER values('123','praveen','oracle','USA','CA',0099,1212123222,'chegg@chegg.com',123),
values('1234','praveen2','ora2cle','USA','CA',0099,1212123222,'chegg@chegg.com',123),
values('1235','praveen3','ora22cle','USA','CA',0099,1212123222,'chegg@chegg.com',123);

insert into PATIENTMHIS
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com');

insert into IMAGINGORDER
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com');

insert into LABORDER
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com');

insert into INUSRANCEINFO
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com'),
values('1234','praveen2','ora2cle','USA','CA','0099',
'1212123222,'chegg@chegg.com');

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects