"6.1,6.2 & 6.1 are answered" The given problem has an entity relationship diagram where the requirement is to implement the diagram in the database , insert records in the tables and then perform the sql script execution to return the result of mentioned statements. **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: Converting ERD to relational schema The database will need to create below tables: **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) 6.1 Create tables in SQL 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: 6.2 Insert records in each table 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: 6.1: TrueHealth practices based on Gauteng 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:
"6.1,6.2 & 6.1 are answered"
The given problem has an entity relationship diagram where the requirement is to implement the diagram in the
**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 database will need to create below tables:
**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:
Step by step
Solved in 2 steps
