Scribd
Upload
36 views2 pages

New SQL

The document outlines the SQL schema for a pharmacy management system, including tables for Pharmacies, Doctors, Drug Manufacturers, Employees, Drugs, Patients, Contracts, Work, Sells, and Prescribe. Each table is defined with primary keys, foreign keys, and relevant attributes to manage relationships between entities. Notable changes include the removal of certain primary keys and the addition of new attributes to enhance data integrity and functionality.

Uploaded by

alibayomi764
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
36 views2 pages

New SQL

The document outlines the SQL schema for a pharmacy management system, including tables for Pharmacies, Doctors, Drug Manufacturers, Employees, Drugs, Patients, Contracts, Work, Sells, and Prescribe. Each table is defined with primary keys, foreign keys, and relevant attributes to manage relationships between entities. Notable changes include the removal of certain primary keys and the addition of new attributes to enhance data integrity and functionality.

Uploaded by

alibayomi764
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE Pharmacies (

Phar_ID CHAR(5) PRIMARY KEY NOT NULL,


Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
Fax VARCHAR(50)
);

CREATE TABLE Doctors (


Doctor_ID CHAR(5) PRIMARY KEY NOT NULL,
-- Trade_Name VARCHAR(20) NOT NULL, -- 1- deleted
D_Name VARCHAR(20) NOT NULL,
Speciality VARCHAR(30) NOT NULL
-- PRIMARY KEY (Doctor_ID, Trade_Name),
-- FOREIGN KEY (Trade_Name) REFERENCES Drugs
);

CREATE TABLE Drug_Manufacturers (


Company_ID CHAR(5) PRIMARY KEY NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL
);

CREATE TABLE Employees (


Employee_ID CHAR(5) PRIMARY KEY NOT NULL,
Phar_ID CHAR(5) NOT NULL, -- 14 - primary key deleted
Name VARCHAR(20) NOT NULL,
-- PRIMARY KEY (Employee_ID, Phar_ID),
FOREIGN KEY (Phar_ID) REFERENCES Pharmacies
);

CREATE TABLE Drugs (


Trade_Name VARCHAR(20) PRIMARY KEY NOT NULL,
Company_ID CHAR(5) NOT NULL, -- 8- primary key removed
FOREIGN KEY (Company_ID) REFERENCES Drug_Manufacturers
);

CREATE TABLE Patients (


PID CHAR(5) PRIMARY KEY NOT NULL,
Doctor_ID CHAR(5) NOT NULL, -- 2- not primary
Name VARCHAR(20) NOT NULL,
Gender CHAR(1) NOT NULL,
Address VARCHAR(50) NOT NULL,
Contact_No VARCHAR(20) NOT NULL,
Insurance_Info VARCHAR(30),
-- PRIMARY KEY (PID, Doctor_ID),
FOREIGN KEY (Doctor_ID) REFERENCES Doctors
);

CREATE TABLE Contracts (


Phar_ID CHAR(5) NOT NULL,
Company_ID CHAR(5) NOT NULL,
Start_Date DATE NOT NULL,
End_Date DATE NOT NULL,
PRIMARY KEY (Phar_ID, Company_ID),
FOREIGN KEY (Phar_ID) REFERENCES Pharmacies,
FOREIGN KEY (Company_ID) REFERENCES Drug_Manufacturers -- 3- reference added
);

CREATE TABLE Work (


Phar_ID CHAR(5) NOT NULL,
Employee_ID CHAR(5) NOT NULL,
Shift_Start DATE NOT NULL,
Shift_End DATE NOT NULL,
PRIMARY KEY (Phar_ID, Employee_ID),
FOREIGN KEY (Phar_ID) REFERENCES Pharmacies, -- 4- wrong reference
FOREIGN KEY (Employee_ID) REFERENCES Employees
); -- 5 - should be deleted
-- 6 - must add a new orders entity

CREATE TABLE Sells (


Phar_ID CHAR(5) NOT NULL,
Trade_Name VARCHAR(20) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY (Phar_ID, Trade_Name),
FOREIGN KEY (Phar_ID) REFERENCES pharmacies, -- 7 - reference updated
FOREIGN KEY (Trade_Name) REFERENCES Drugs
);

CREATE TABLE Prescribe_Drug_And_Patient (


PID CHAR(5) NOT NULL,
Trade_Name VARCHAR(20) NOT NULL,
The_Date DATE, -- 13 - changed
Quantity INT NOT NULL,
PRIMARY KEY (PID, Trade_Name),
FOREIGN KEY (PID) REFERENCES Patients,
FOREIGN KEY (Trade_Name) REFERENCES Drugs
); -- 9- should be deleted

CREATE TABLE Prescribe (


Prescribe_ID CHAR(5) PRIMARY KEY NOT NULL, -- 10 - new primary key added
PID CHAR(5) NOT NULL, -- 11 - new attribute added
Doctor_ID CHAR(5) NOT NULL,
Trade_Name VARCHAR(20) NOT NULL,
The_Date DATE NOT NULL, -- 12 - name changed
Quantity INT NOT NULL,
-- PRIMARY KEY (Doctor_ID, Trade_Name),
FOREIGN KEY (PID) REFERENCES Patients,
FOREIGN KEY (Doctor_ID) REFERENCES Doctors,
FOREIGN KEY (Trade_Name) REFERENCES Drugs
);

You might also like