Patient Consultation Doctor PK Pat ID 0.. PK,FK1Doc 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 Pro_ID Prc_Name Prc_YearEstablished Prc_Province Write SQL statements for the following: 6.1 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.2 6.1 6.2 6.3 6.4
"6.1,6.2,6.1 are answered"
Note: Since you have posted multiple sub-questions in the same request, we will solve the first three questions for you. To get the remaining questions solved, please repost other questions independently.
Storing data and information using the database systems is secure and time-convenient. Relations are optimized or normalized so that no data loss or unnecessary data modification occurs in the database.
Answer:
6.1. There are four tables in this database system Doctor, Patient, Consultation, and Practice. The tables are designed with the defined attributes as:
Syntax:
CREATE TABLE TABLE-NAME(FIELD1 TYPE, FIELD2 TYPE, ...);
CREATE TABLE Patient(Pat_ID varchar(4) primary key,
Pat_name varchar(50),
Pat_age number,
Pat_gender varchar(4));
CREATE TABLE Practice(Prc_ID varchar(4) primary key,
Prc_name varchar(50),
Prc_yearestablished number,
Prc_province varchar(20));
CREATE TABLE Doctor(Doc_ID varchar(4) primary key,
Doc_name varchar(50),
Doc_employdate date,
Prc_ID varchar(4),
FOREIGN KEY (Prc_ID) REFERENCES Practice(Prc_ID));
CREATE TABLE Consultation(Doc_ID varchar(4),
Pat_ID varchar(4),
Con_date date,
primary key(Doc_ID,Pat_ID),
FOREIGN KEY (Pat_ID) REFERENCES Patient(Pat_ID),
FOREIGN KEY (Doc_ID) REFERENCES Doctor(Doc_ID));
Schema:
6.2. Adding two records in each table:
For adding records, the insert query should be used.
insert into Patient values('P001','Patient1',45,'M');
insert into Patient values('P002','Patient2',32,'F');
insert into Practice values('Pr01','Practice1',2010,'Gauteng');
insert into Practice values('Pr02','Practice2',2015,'Luxemberg');
insert into Doctor values('D001','Alam',date '2018-04-01','Pr01');
insert into Doctor values('D002','Abedin',date '2021-01-20','Pr02');
insert into Consultation values('D001','P002',date '2020-09-20');
insert into Consultation values('D001','P001',date '2021-01-24');
6.1. To show the TrueHealth practices in the location 'Gauteng', the select statement should be used providing the correct condition.
select * from Practice where Prc_province='Gauteng';
The practice records for the province "Gauteng" will be displayed. Again, you can show only the name of the practices mentioning the field name.
select Prc_name from Practice where Prc_province='Gauteng';
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
