🗽Materialy designed stellar🚀 database app
For our Entities-Relations Model, we consider all the below described entities as strong.
Patients receive drug prescriptions by doctors.
Each patient is uniquely identified by a PatientID -- PRIMARY KEY🔑, and is characterized by the following simple, as well as composite, attributes:
- Age
- FullName
- FirstName
- LastName
- Address
- Town
- StreetName
- Number
- PostalCode
Doctors prescribe drugs and monitor patients.
Each doctor is uniquely identified by a DoctorID -- PRIMARY KEY🔑, and is characterized by the following simple, as well as composite, attributes:
- Specialty
- ExperienceYears
- FullName
- FirstName
- LastName
Pharmaceutical companies provide pharmacies with drugs.
Each pharmaceutical company is uniquely identified by a PharmaceuticalCompanyId -- PRIMARY KEY🔑, and is characterized by the following simple attributes:
- Name
- PhoneNumber
In addition, we consider that each pharmaceutical company has a unique name and a unique phone number, thus making the Name attribute a Candidate Key and the PhoneNumber a Single-valued (not Multivalued) attribute.
Drugs are produced by pharmaceutical companies.
Each drug is uniquely identified by a DrugId -- PRIMARY KEY🔑, and is characterized by the following simple attributes:
- Name
- Formula
Additionally, we consider as unique the commercial name of each drug, thus making the Name attribute a Candidate Key.
Prescriptions-R-X pharmacies.
Each pharmacy is uniquely identified by a PharmacyID -- PRIMARY KEY🔑, and is characterized by the following simple, as well as composite, attributes:
- Name
- Address
- Town
- StreetName
- Number
- PostalCode
Again, we considering as unique the name of each pharmacy, making the Name attribute a Candidate Key.
Connects a patient with a corresponding doctor.
Each patient can be seen by at most one doctor, while each doctor can see any number patients.
Also, each patient has a doctor monitoring him, while a each doctor has at least one patient to monitor.
- Mapping Cardinality
- 1:N
- Total Participation
- DOCTOR
- PATIENT
Connects a pharmacy with its selling drugs.
Each pharmacy can have for sale any number of drugs, while a drug can be available at any number of pharmacies. Also, the relation holds a Price attribute, representing the selling price of a drug.
Additionally, we consider that each pharmacy can have drugs for sale, while a drug can be unavailable at all pharmacies.
- Mapping Cardinality
- N:M
- Total Participation
- Pharmacy
- Partial Participation
- Drug
- Relation Attributes
- Price
Connects a drug with the pharmaceutical company producing it.
Each drug can be produced by at most one pharmaceutical company, while a pharmaceutical company can be producing any number of drugs.
Additionally, each drug can be produced by a pharmaceutical company, while a pharmaceutical company might not be producing some drugs out of the totally registered ones.
- Mapping Cardinality
- 1:N
- Total Participation
- Drugs
- Partial Participation
- Pharmaceutical Company
Connects a patient with a doctor and a prescribed drug.
This is a ternary relationship, where a patient can receive a prescription for any number of drugs by any number of doctors, a doctor can prescribe any number of drugs to any number of patients, and a drug can be prescribed to any number of patients by any number of doctors. Also, the relation holds a Date attribute, representing the prescription date, and a Quantity attribute, representing the quantity of the prescribed drug.
Additionally, for a patient might not exist a corresponding prescription, and a doctor might not have prescribed a prescription for a corresponding patient, while a drug might not have been prescribed by a doctor for a patient.
- Mapping Cardinality
- N:M:K
- Partial Participation
- PATIENT
- DRUG
- DOCTOR
- Relation Attributes
- Date
- Quality
Finally, if a doctor prescribes the same drug to the same patient, more than one time, then the last prescription is the one registered in our database.
Connects a pharmaceutical company with a pharmacy.
A pharmacy can be in contract with any number of pharmaceutical companies, while a pharmaceutical company can be in contract with any number of pharmacies. Also, the relations holds a StartDate and an EndDate attribute, representing the contract initialization and termination dates accordingly, a Text attribute, representing the text upon which the contract was agreed and signed by both counterparts, and a Supervisor attribute, representing the contract supervisor.
We consider that each pharmacy is in contract with a pharmaceutical company, to supply itself with drugs, while each registered pharmaceutical company is in contract with one of the pharmacies.
- Mapping Cardinality
- N:M
- Total Participation
- PHARMACEUTICAL COMPANY
- PHARMACY
- Relation Attributes
- StartDate
- EndDate
- Text
- Supervisor
Finally, if a pharmacy signs a contract with the same company, more than one time, then the last contract is the one registered in our database.
Each strong entity is immediately converted to a relation.
Keys are noted in a bold & tilted fashion.
Also, we replaced the composite attribute Address with the simple attributes composing it.
- Patient (PatientId, FirstName, LastName, Town, StreetName, Number, PostalCode, Age)
- Doctor (DoctorId, FirstName, LastName, Specialty, ExperienceYears)
- PharmaceuticalCompany (PharmaceuticalCompanyId, Name, PhoneNumber)
- Drug (Drug, Name, Formula)
- Pharmacy (PharmacyId, Name, Town, StreetName, Number, PostalCode, PhoneNumber)
- SeenBy (PatientId, DoctorId)
- Sell (PharmacyId, DrugId, Price)
- Make (DrugId, PharmaceuticalCompanyId)
- Prescription (PatientId, DoctorId, DrugId, Date, Quantity)
- Contract (PharmacyId, PharmaceuticalCompanyId, StartDate, EndDate, Text, Supervisor)
Structure designs pulled from the latest main Manhattan MySQL source.
DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient` (
`patient_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`surname` varchar(20) NOT NULL,
`town` varchar(20) NOT NULL,
`street_name` varchar(20),
`number` int(11),
`postalcode` int(11),
`age` int(2) NOT NULL,
`doctor_id` int(11) NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`patient_id`),
KEY `doctor_id` (`doctor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `prescription`;
CREATE TABLE `prescription` (
`patient_id` int(11) NOT NULL,
`doctor_id` int(11) NOT NULL,
`drug_id` int(11) NOT NULL,
`date` date NOT NULL,
`quantity` int(10),
PRIMARY KEY (`patient_id`,`doctor_id`,`drug_id`),
KEY `patient_id` (`patient_id`),
KEY `doctor_id` (`doctor_id`),
KEY `drug_id` (`drug_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TRIGGER IF EXISTS `patient_age_insert_check`;
DELIMITER //
CREATE TRIGGER `patient_age_insert_check` BEFORE INSERT ON `patient`
FOR EACH ROW IF NEW.age < 0 || NEW.age = 0 THEN SET NEW.age = NULL; END IF;
//
DELIMITER;DROP TRIGGER IF EXISTS `doctor_deletion`;
DELIMITER //
CREATE TRIGGER `doctor_deletion` BEFORE DELETE ON `doctor`
FOR EACH ROW BEGIN
DELETE FROM patient
WHERE OLD.doctor_id = patient.doctor_id;
END
//
DELIMITER ;# clone the repository
git clone https://github.com/manhattanhq/manhattan.git
# navigate to the project directory
cd manhattan✨ <> with ❤️ by George, Konstantinos & Klaus ✨
| George Baxopoulos 📫 | Konstantinos Mitropoulos 📫 | Klaus Sinani 📫 |
MIT © George Baxopoulos, Konstantinos Mitropoulos & Klaus Sinani
