Creating Pharmaceutical, Doctor, Patient, Drug, And Prescription Tables In An Essay.

Pharmaceutical Table

Implement SQL script solution1.sql that contains SQL statements required to create the relational tables listed below, including the appropriate versions of all primary and foreign key integrity constraints. You must also implement all the “other consistency constraints” listed in the following specification. CSCI235- Database Systems Page 2 of 8 The sample database contains information about the drug supplies by pharmaceutical company. The drugs are prescribed by doctors to his/her patients if deemed necessary, on a different occasion.

A doctor may be a family doctor for many patients. The relational schemas, the meaning of attributes and specifications of primary and foreign keys are given below. PHARMACEUTICAL Relation of pharmaceutical company. PharName PharTelephone VARCHAR2(30) VARCHAR2(25) Name of a pharmaceutical company. The telephone number of a pharmaceutical company. Primary key = PharName DOCTOR Relation of doctor issuing prescription. DID DName Specialty YearOfExp VARCHAR2(20) VARCHAR2(50) VARCHAR2(70) NUMBER Identification number of a doctor.

The name of a doctor. The specialty of a doctor. The number of years of experience. Primary key = DID PATIENT Relation of patient receiving prescription. PID PName PDOB PAddress PPostalCode FamilyDoctor VARCHAR2(20) VARCHAR2(50) DATE VARCHAR2(70) VARCHAR2(12) VARCHAR2(20) Identification number of a patient. The name of a patient. The date of birth of a patient. The address of a patient. The postal code of a patient. The patient’s family doctor. Primary key = PID Foreign key = FamilyDoctor references DOCTOR(DID) DRUG Relation of drug supplied by various pharmaceutical company.

PharName Tradename Formula VARCHAR2(30) VARCHAR2(30) VARCHAR2(70) Name of a pharmaceutical company supplying the drug. Tradename of a drug. The formula used for the drug. Primary key = PharName, Tradename Foreign key = PharName references PHARMACEUTICAL(PharName) CSCI235- Database Systems Page 3 of 8 PRESCRIPTION Relation of prescription prescribed. DID PID PharName Tradename PrescriptionDT Quantity VARCHAR2(20) VARCHAR2(20) VARCHAR2(30) VARCHAR2(30) Date Number Id. of doctor issuing the prescription. Id. of patient receiving the prescription. Name of pharmaceutical co. supplying the drug. Trade name of the drug. Date of prescription. Quantity of the drug prescribed. Primary key = (DID, PID, PharName, Tradename, PrescriptionDate) Foreign key 1 = (DID) references DOCTOR(DID) Foreign key 2 = (PID) references PATIENT(PID) Foreign key 3 = (PharName, Tradename) references DRUG(PharName, Tradename) Constraint:

Check Quantity between 1 and 30. Deliverables Submit a file solution1.lst with a report from the execution of SQL script solution1.sql. The report should contain both the SQL statements and their output. Remember to execute SQL*Plus commands SET ECHO ON and SET FEEDBACK ON at the beginning of the script, Do NOT change ECHO and FEEDBACK system variables later on in the script. To get a report from the execution of a script use SQL*Plus statement SPOOL solution1 in the first line of the script and statement SPOLL OFF in the last line of the script. Task 2 (1 mark) Structural modifications of relational tables Implement SQL script solution2.sql that performs the following structural modifications to the sample database described in

Doctor Table

Task 1. A “structural modification” means, that to implement the new requirements, you may create a new table or drop an existing table, add new columns or remove an existing column, add a new constraint, or remove or modify an existing constraint. a) The owner of the database would like to speed up the queries of information about patients. The information is retrieved through providing a patient name. (0.25 mark) CSCI235- Database Systems Page 4 of 8 b) .

The owner of the database would like to add to the database a gender of the patient. The values ‘M’ and ‘F’ must be used to indicate if a patient is male or female. (0.25 mark) c) The owner of the database decided to capture the information about a pharmacist who dispenses the prescribed drugs and his/her relationship with the prescription. A pharmacist may dispense many prescription, and each prescription is dispensed only by one pharmacist. A pharmacist is described by name, addressed and a license number. The license number uniquely identifies each pharmacist.

The data type of the attributes is up to you, but it must be realistic. (0.5 mark) Deliverables Submit a file solution2.lst with a report from the execution of SQL script solution2.sql. The report should contain both the SQL statements and their output. Remember to execute SQL*Plus commands SET ECHO ON and SET FEEDBACK ON at the beginning of the script, Do NOT change ECHO and FEEDBACK system variables later on in the script. To get a report from the execution of a script use SQL*Plus statement SPOOL solution2 in the first line of the script and statement SPOLL OFF in the last line of the script.

Task 3 (1 mark) Data manipulation operations Implement SQL script solution3.sql with the following data manipulations on the sample database. You are not allowed to suspend, delete, and modify the consistency constraints. a) A NEW female patient Ann Miller has appointed Doctor Martin Lucas as her family doctor. Ann has been treated today for fever and has been prescribed with 10 tablets of the drug ‘Propranolol’ supplied by Pfizer. The NRIC of Ann is S2598868B. Ann was born on 9 September 1992, and she lives at 22nd street, Singapore. The postal code Ann lives in is 650142. The identification number of Doctor Martin Lucas is 3332858. Dr Martin Lucas has 10 years of experiences as general practitioner.

Patient Table

Add to the database the new information of Ann and the prescription she received from Doctor Martin Lucas. You can assume that attribute ‘Gender’ was successfully added to the table PATIENT. (0.5 mark) CSCI235- Database Systems Page 5 of 8 b) Delete from the database all information related to the doctor Bruce Thompson including his relationship as a family doctor and the prescription prescribed by him. Note that when the database was created, the delete on cascade constraint was not implemented. The objective of this exercise is to assess if you understand the processes involved in deleting information under the specified situation. You will be assessed based on the correctness of your SQL statements. (Hint. Be careful on the referential integrity constraints.) (0.5 mark) Deliverables Submit a file solution3.lst with a report from the execution of SQL script solution3.sql. The report should contain both the SQL statements and their output. Remember to execute SQL*Plus commands

SET ECHO ON and SET FEEDBACK ON at the beginning of the script, Do NOT change ECHO and FEEDBACK system variables later on in the script. To get a report from the execution of a script use SQL*Plus statement SPOOL solution3 in the first line of the script and statement SPOLL OFF in the last line of the script. Task 4 (1 mark) Access control Implement SQL script solution4.sql that enforces the following access control rules. Suppose that all the relational tables were created by you and the tables are owned by you. You received a request which has been approved by the respective manager to allow the user account SYSTEM an access to the relational tables owned by you. You may use views in your implementation. You do not need to create the user SYSTEM. The requests are as follow: a) Grant the user account SYSTEM such that SYSTEM can retrieve or modify any relational tables except PRESCRIPTION, and SYSTEM is able to grant any of these privileges to other users. (0.25 mark)

b) Grant the user account SYSTEM such that SYSTEM can retrieve all details of DOCTOR and PRESCRIPTION prescribed by the doctor ‘Steve Miller’. (0.25 mark) c) Grant the user account SYSTEM the right to refer to the primary key of the relational table DRUG from foreign keys included in the relational tables owned by SYSTEM. (0.25 mark) d) Grant the user account SYSTEM the right to retrieve the patient’s identification number, name, and family doctor. (0.25 mark) CSCI235- Database Systems Page 6 of 8 Deliverables Submit a file solution4.lst with a report from the execution of SQL script solution4.sql. The report should contain both the SQL statements and their output. Remember to execute SQL*Plus commands SET ECHO ON and SET FEEDBACK ON at the beginning of the script, Do NOT change ECHO and FEEDBACK system variables later on in the script. To get a report from the execution of a script use SQL*Plus statement SPOOL solution4 in the first line of the script and statement SPOLL OFF in the last line of the script. Task 5 (2 marks) Data retrieval operations Pre-task actions .

Drug Table

The SQL scripts CreateDbPartyChef.sql and DropDbPartyChef.sql can be found in the zipped Assignment 2 folder. Unzip the folder and execute the script CreateDbPartyChef.sql to create a sample database. The database contains information about the parties held by clients and the chefs engaged in parties. The specialities of chefs are also stored in the sample database. It is strongly recommended to discover a conceptual schema (UML diagram) of the database. However, there will be no mark awarded for discovering a conceptual schema. Implement SQL script solution5.sql that performs the following data retrieval operations using .

SELECT statement of SQL. a) List the full details and age of all chefs who are between 21 and 40 years old. b) List the client first and last name, email, and a total number of parties each client held. c) List the first name, last name and the total number of chef’s engagements in the parties. Do not ignore chefs who have not engaged in any party before. d) For all chefs who have not been engaged in any party list the chef’s first and last name together with his/her speciality. Order your output by speciality

e) List the full details of the chefs who specialise in both ‘Chinese Cuisine’ and ‘Japanese Cuisine’. CSCI235- Database Systems Page 7 of 8 f) List the first name and last name of the chefs who have been engaged by the clients Charles Carter or John Young. g) List the full details of the chefs who have been engaged in any party for more than 2 times. Order the output by the chef’s last name followed by first name. h) List the most possessed speciality together with the total number of chefs who specialize in the speciality. i) List the full details of the chefs who have been engaged in a party in May 2018 at Grand Hall, level 2, MBS. j) For all most engaged chefs, list the firstname, lastname, and the total number of time a chef is engaged. By most engaged, it means chefs who has been engaged in any party the most number of time. Deliverables Submit a file solution5.lst with a report from the execution of SQL script solution5.sql.

The report should contain both the SQL statements and their output. Remember to execute SQL*Plus commands SET ECHO ON and SET FEEDBACK ON at the beginning of the script, Do NOT change ECHO and FEEDBACK system variables later on in the script. To get a report from the execution of a script use SQL*Plus statement SPOOL solution5 in the first line of the script and statement SPOLL OFF in the last line of the script. Task 6 (1 mark) Using the data dictionary Implement SQL script solution6.sql that lists the names and the sizes (in KBytes) of the data objects (relational tables, indexes, etc) created and owned by you. The script should produce the following output. Object Names Size in KB Focus on a query that retrieves required information from a data dictionary. CSCI235- Database Systems Page 8 of 8 2) USER_xxx views in data dictionary refer to the data objects created and owned by you. Deliverables Submit a file solution6.lst with a report from the execution of SQL script solution6.sql.

The report should contain both the SQL statements and their output. Remember to execute SQL*Plus commands SET ECHO ON and SET FEEDBACK ON at the beginning of the script, Do NOT change ECHO and FEEDBACK system variables later on in the script. To get a report from the execution of a script use SQL*Plus statement SPOOL solution6 in the first line of the script and statement SPOLL OFF in the last line of the script. Submissions This assignment is due on Monday, 13 May 2018 by 9:00 pm Singapore time. Submit the files solution1.lst, solution2.lst, solution3.lst, solution4.lst, solution5.lst, and solution6.lst through Moodle in the following way: (1) Access Moodle at 

(2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page

(3) When successfully logged in, select a site CSCI235 (SP218) Database Systems

(4) Scroll down to a section Submissions of Assignments

(5) Click at Submit your Assignment 1 here link.7

(6) Click at a button Add Submission

(7) Move a file, for example, solution1.pdf into an area. You can drag and drop files here to add them. You can also use a link Add… (8) Repeat the previous step

(7) for all files with the solutions, that is, solution1.lst, solution2.lst, solution3.lst, solution4.lst, solution5.lst, solution6.lst, and solution7.lst.

(9) Click at a button Save changes

(10) Click at a button Submit assignment

(11) Click at a button Continue A policy regarding late submissions is included in the subject outline. Only one submission per student is accepted. Assignment 2 is an individual assignment and it is expected that all its tasks will be solved individually without any cooperation with the other students. Plagiarism is treated seriously. Students involved will likely receive zero. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or over e-mail.

create pharmaceutical table

CREATE TABLE PHARMACEUTICAL

(

PharName VARCHAR2(30),

PharTelephone VARCHAR2(25),

Primary key(PharName)

)create doctor table

CREATE TABLE DOCTOR

(

DID VARCHAR(20),

DName VARCHAR(50),

Specialty VARCHAR(70),

YearOfExp NUMBER,

Primary key(DID)create patient table

CREATE TABLE PATIENT

(

PID VARCHAR(20),

PName VARCHAR(50),

PDOB DATE,

PAddress VARCHAR2(70),

PPostalCode VARCHAR(12),

FamilyDoctor VARCHAR(20),

Primary key(PID),

Foreign key(FamilyDoctor) REFERENCES DOCTOR(DID)create drug table

CREATE TABLE DRUG

(

PharName VARCHAR2(30),

Tradename VARCHAR2(30),

Formula VARCHAR2(70),

Primary key(PharName,Tradename),

Foreign key(PharName) references PHARMACEUTICAL(PharName)create prescription table

CREATE TABLE PRESCRIPTION

(

DID VARCHAR2(20),

PID VARCHAR2(20),

PharName VARCHAR2(30),

Tradename VARCHAR2(30),

PrescriptionDT DATE,

Quantity NUMBER,

Primary key(DID,PID,PharName,Tradename,PrescriptionDT),

FOREIGN key(DID) references DOCTOR(DID),

FOREIGN key (PID) REFERENCES PATIENT(PID),

FOREIGN key (PharName,Tradename) REFERENCES DRUG(PharName,Tradename),

Constraint Check_Quantity check(Quantity between 1 and 30)References

[9]J. Wang, Oracle database 11g. New York: McGraw-Hill, 2011.

[10]A. Momen, Oracle Database XE 11gR2 jump start guide. Birmingham, UK: Packt Pub., 2012.

  [11]R. Greenwald, R. Stackowiak and J. Stern, Oracle Essentials. Sebastopol: O'Reilly Media, Inc., 2009.