List, in alphabetical order, the names of all patients who had consultations after 01 January 2018. Which practice (by name) has the most recent hire (i.e. doctor with latest employment date)? Which doctor has had the least number of consultations? Give the doctor's name and the number of consultation s/he has given.
"6.1,6.2,and 6.1 are answered"
**As per Bartleby guidelines, solving the first 3 sub-parts as the question contains many parts and the required parts to be answered is not mentioned.
It will involve the below steps:
The
**Primary key is bold and underlined, Foreign key is italic
-
- Patient(Pat_ID, Pat_Name, Pat_Age, Pat_Gender)
- Doctor(Doc_ID, DOc_Name, Doc_EmployDate, Prc_ID)
- Consultation(Doc_ID, Pat_ID, Con_Date)
- Practice(Prc_ID, Prc_Name, Prc_YearEstablished, Prc_Province)
Below is the script to create the tables with attributes and foreign key constraints:
//Script will create the database named TrueHealth and then populate the tables:
create database trueHealth;
use truehealth;
CREATE TABLE Patient(Pat_ID CHAR(4) primary key, Pat_Name varchar(50), Pat_Age int, Pat_Gender varchar(10));
CREATE TABLE Practice(Prc_ID int primary key, Prc_Name varchar(50), Prc_YearEstablished int, Prc_Province varchar(50));
CREATE TABLE Doctor(Doc_ID CHAR(4) primary key, Doc_Name varchar(50), Doc_EmployDate date, Prc_ID int,
constraint doc_prc foreign key (Prc_ID) references Practice(Prc_ID));
CREATE TABLE Consultation(Doc_ID char(4), Pat_ID char(4), Con_Date date,
primary key (Doc_id, Pat_id),
constraint doc_cons foreign key (Doc_ID) references Doctor(Doc_ID),
constraint pat_cons foreign key (Pat_ID) references Patient(Pat_ID) );
Result:
Tables will get created with database:
Below SQL will insert the required data in the tables as below:
INSERT INTO PATIENT values('P001', 'PatientName1', 15,'Male');
INSERT INTO PATIENT values('P002', 'PatientName2', 35,'Female');
INSERT INTO Practice values(101, 'Padetrician', 2010, 'Gauteng');
INSERT INTO Practice values(102, 'General', 2015, 'Rohtik');
INSERT INTO DOCTOR VALUES('D001', 'DOctorname1', '2020-12-12', 101);
INSERT INTO DOCTOR VALUES('D002', 'DOctorname2', '2016-01-12', 102);
INSERT INTO CONSULTATION values('D001', 'P001', '2021-01-01');
INSERT INTO CONSULTATION values('D002', 'P001', '2016-01-01');
Data is each table is now as below:
Patient:
Doctor:
Practice:
Consultation:
LOGIC: The requirement is to retrieve all Practice records that are having Prc_Province = Gauteng
SQL: Below SQL will return the required data:
Select * from Practice where Prc_Province = 'Gauteng'
Output:
![The entity relationship diagram below models the components of a database belonging to
TrueHealth, a network of medical centers. The database allows TrueHealth to keep track
of doctors at each practice and their patients.
Patient
PK Pat ID
Consultation
Doctor
PK,FK1 Doc_ID
PK Doc ID
1..1
0..
1..1
Pat_Name
PK, FK2 Pat ID
Doc_Name
Pat Age
Con_Date
Doc_EmployDate
Pat_Gender
FK Prc_ID
0..*
1..1
Practice
PK Prc ID
Prc_Name
Prc_YearEstablished
Prc_Province
Write SQL statements for the following:
Create all the necessary tables. Name the tables; and all their attributes
appropriately.
Insert TWO records into each of the tables created to demonstrate that you
have created them correctly.
List all the TrueHealth practices based in Gauteng.
List, in alphabetical order, the names of all patients who had consultations
after 01 January 2018.
Which practice (by name) has the most recent hire (i.e. doctor with latest
employment date)?
Which doctor has had the least number of consultations? Give the doctor's
name and the number of consultation s/he has given.
6.1
6.2
6.1
6.2
6.3
6.4](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F86e848c1-61b9-4813-9106-dd9d1c331554%2F13ec9dcf-1626-48d2-94ed-a4bc10658411%2Fv6y7ak_processed.png&w=3840&q=75)
![](/static/compass_v2/shared-icons/check-mark.png)
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Concepts of Database Management](https://www.bartleby.com/isbn_cover_images/9781337093422/9781337093422_smallCoverImage.gif)
![Prelude to Programming](https://www.bartleby.com/isbn_cover_images/9780133750423/9780133750423_smallCoverImage.jpg)
![Sc Business Data Communications and Networking, T…](https://www.bartleby.com/isbn_cover_images/9781119368830/9781119368830_smallCoverImage.gif)