Not add patient patient table may get integrity constraint error

DOCTOR (DID, NAME, SPECIALTY, AGE)
Where:

DID is doctor’s ID

Where:

PID is Patient’s ID

APPOINTMENT( App_I, PID, DID, t_ID, t_Desc, charges, d_treatment)

Where:

t_desc is treatment description

charges is the amount charged for that service

a) Provide and CREATE statements for tables DOCTOR, PATIENT and APPOINTMENT with appropriate primary and foreign keys.

b) Develop tables in ORACLE and load the data, provide table structure and content in table contents and table structures

Answer the following Questions below:

Give the names of all specialties (make sure to remove duplicates)
Give the names of doctors who have at least two L’s in their names
Give the count of doctor by their specialties.
Give the id’s of doctors who treated either Joze or Gobi
Give the names of patients whose names contain e and i in any order. (i.e., it could be i and e)

you may get an integrity constraint error, submit the query with error part and explain why this error is occurring

d. Draw the relationship (1:1 or 1:m or m:n) between the following:

f. If App_id is removed from APPOINTMENT table, how would it impact PK and FKs? What would be PK and FKs of this relation?

ANSWER FORMAT

(Do not submit insert statement used to load base tables; too many insert statements will clutter up the assignment)

Appendix A

Sample data for DOCTOR (DID, NAME,SPECILATY,AGE)

PATIENT ( PID,P_NAME,P_ADDRESS,P_INS,INS_ADDRESS)

PID	P_NAME	P_ADDRESS	P_INS	INS_ADDRESS
P340	Joze	Baltimore	Aetna	Columbia, MD
P330	Kathy	Baltimore	UNH	Baltimore, MD
P341	Bill	Virginia	BCBS	Philadelphia, PA
P342	Dolly	Baltimore	Aetna	Columbia, MD
P343	Lee	Baltimore	Aetna	Columbia, MD
P331	Bentley	Baltimore	Aetna	Columbia, MD
P350	Crispp	Virginia	BCBS	Philadelphia, PA
P310	Liou	Towson	Aetna	Columbia, MD
P315	Berty	Baltimore	UNH	Baltimore, MD
P318	Jolly	Baltimore	Aetna	Columbia, MD
P324	Peppy	New York	CIGNA	New york, NY
P510	Terry	Baltimore	Aetna	Columbia, MD
P520	Berdy	Baltimore	Aetna	Columbia, MD
P540	Bonggo	Virginia	BCBS	Philadelphia, PA
P550	Gobi	Baltimore	CIGNA	New york, NY
P560	Sahara	Baltimore	UNH	Baltimore, MD
P600	Pinky	Baltimore	Aetna	Columbia, MD
P610	Ulertt	Baltimore	CIGNA	New york, NY
P640	Berry	Philadelphia	BCBS	Philadelphia, PA
P660	Loews	Baltimore	CIGNA	New York, NY

APPOINTMENT table sample data:

Requirement	Max points	Your Grade
Part A: Create statements (all 3 tables) including PK and FK of each table	6


Table structure of each table	2
Table contents of each table	2

	28 (2 points each query)

Part D: ERD	3

	2
	2

Total Points	45