"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...






"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.


Database management systems store records about separate related entities and manage them efficiently. Also, they provide mechanisms to fetch those values when required. DML/DDL/DCL queries can be formed to create/destroy relations, fetch data from one or more relations, etc.


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.





Step 2


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:









Step 3



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');







Step 4


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';





The entity relationship diagram below models the components of a database belonging to<br>TrueHealth, a network of medical centers. The database allows TrueHealth to keep track<br>of doctors at each practice and their patients.<br>Patient<br>PK Pat ID<br>Consultation<br>Doctor<br>PK,FK1 Doc_ID<br>PK Doc ID<br>1..1<br>0..<br>1..1<br>Pat_Name<br>PK, FK2 Pat ID<br>Doc_Name<br>Pat Age<br>Con_Date<br>Doc_EmployDate<br>Pat_Gender<br>FK Prc_ID<br>0..*<br>1..1<br>Practice<br>PK Prc ID<br>Prc_Name<br>Prc_YearEstablished<br>Prc_Province<br>Write SQL statements for the following:<br>Create all the necessary tables. Name the tables; and all their attributes<br>appropriately.<br>Insert TWO records into each of the tables created to demonstrate that you<br>have created them correctly.<br>List all the TrueHealth practices based in Gauteng.<br>List, in alphabetical order, the names of all patients who had consultations<br>after 01 January 2018.<br>Which practice (by name) has the most recent hire (i.e. doctor with latest<br>employment date)?<br>Which doctor has had the least number of consultations? Give the doctor's<br>name and the number of consultation s/he has given.<br>6.1<br>6.2<br>6.1<br>6.2<br>6.3<br>6.4<br>

Extracted text: 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
Jun 11, 2022
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here