Question
(Attached script): create database Clinic; use Clinic; create table Patient( PNum INTEGER, PName CHAR(50), Address CHAR(50), Gender char(20), Age integer, PRIMARY KEY (PNum)); create table
(Attached script):
create database Clinic;
use Clinic;
create table Patient( PNum INTEGER, PName CHAR(50), Address CHAR(50), Gender char(20), Age integer, PRIMARY KEY (PNum));
create table Medicine( MedID INTEGER, MedName CHAR(50), Price double, PRIMARY KEY (MedID));
create table Prescription ( PNum INTEGER, MedID integer, Quantity integer, Total double, PDate date, primary key(PNum,MedID));
INSERT INTO `Clinic`.`Medicine` (`MedID`, `MedName`, `price`) VALUES ('101', 'Dolocet', '15');
INSERT INTO `Clinic`.`Medicine` (`MedID`, `MedName`, `price`) VALUES ('102', 'Panadol', '23');
INSERT INTO `Clinic`.`Medicine` (`MedID`, `MedName`, `price`) VALUES ('103', 'Revanin', '22');
INSERT INTO `Clinic`.`Medicine` (`MedID`, `MedName`, `price`) VALUES ('104', 'Flagyl', '7');
INSERT INTO `Clinic`.`Medicine` (`MedID`, `MedName`, `price`) VALUES ('105', 'Alfacort', '16.5');
INSERT INTO `Clinic`.`Patient` (`PNum`, `PName`, `address`, `gender`,`age`) VALUES ('1', 'Sami Jamal', 'Salmiya', 'Male','21');
INSERT INTO `Clinic`.`Patient` (`PNum`, `PName`, `address`, `gender`,`age`) VALUES ('2', 'Heba Ahmad', 'Egila', 'Female','19');
INSERT INTO `Clinic`.`Patient` (`PNum`, `PName`, `address`, `gender`,`age`) VALUES ('3', 'Amal Khaled', 'Ahmadi', 'Female','21');
INSERT INTO `Clinic`.`Patient` (`PNum`, `PName`, `address`, `gender`,`age`) VALUES ('4', 'Hadeel Mohammad', 'Hawalli', 'Female','20');
INSERT INTO `Clinic`.`Patient` (`PNum`, `PName`, `address`, `gender`,`age`) VALUES ('5', 'Demah Naser', 'Jahra', 'Female','22');
INSERT INTO `Clinic`.`Prescription` (`PNum`, `MedID`,`quantity`, `PDate`) VALUES ('3', '103','2', '2016-01- 06');
INSERT INTO `Clinic`.`Prescription` (`PNum`, `MedID`,`quantity`, `PDate`) VALUES ('5', '101', '3','2017-03- 04');
INSERT INTO `Clinic`.`Prescription` (`PNum`, `MedID`, `quantity`,`PDate`) VALUES ('2', '104', '1','2016-03- 04');
INSERT INTO `Clinic`.`Prescription` (`PNum`, `MedID`, `quantity`,`PDate`) VALUES ('1', '102','2', '2017-03- 09');
INSERT INTO `Clinic`.`Prescription` (`PNum`, `MedID`, `quantity`,`PDate`) VALUES ('4', '105', '6','2017-04- 07');
Problem Use the attached script (see last page) to create the following Clinic database: patient medicine PNum INT(11) PName CHAR(50) Address CHAR (50) Gender CHAR (20) Age INT (11) prescription PNum INT(11) MedID INT(11) Quantity INT(11) Total DOUBLE MedID INT(11) MedName CHAR(50) Price DOUBLE PDate DATE Answer the following questions: 1. Add a constraint on medicine table, to prevent duplicating values for the MedName column. (15 points) 2. Add a constraint on prescription table, to set the MedID column as foreign key that points to MedlD column in the medicine table. (15 points) 3. Create a trigger "TRG_YourName" (example: TRG_Ahmed Naser) on table prescription, to calculate the Total column automatically if the user updates the Quantity (20 points) Note: Total=Quantity * Price if Quantity is less than 5, and Total=Quantity * Price X 0.8 otherwise 4. Create view "DetailsMar 17" to show PName, MedName and Total columns for all prescriptions issued during 05-March-2017 until 10-April-2017. (20 points) 5. Create a procedure "Pro1", that takes one string (P) as parameter and displays PName, Address and Gender of the patient whose PName equals to P. (10 points) 6. Create procedure GetPrescription" that takes one integer input (P) which represents Patient Number (PNum), and displays PNum, PName, MedName and PDate of all prescriptions issued for that Patient. (10 points) 7. Create procedure Income that takes the salary of an employee as input, and display the net income as output based on the following criteria: (10 points) Salary Income tax >=2000 10% >=1000 to 2000 5% =2000 10% >=1000 to 2000 5%Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started