LAB Assesment ITS232 PDF

Preview

Summary

UNIVERSITI TEKNOLOGI MARA LAB ASSESSMENTCOURSE : INTRODUCTION TO DATABASE DESIGN COURSE CODE : ITS232/ICTDATE : JUNE 2020 STUDENT NAME :UiTM ID NO. :LECTURER :INSTRUCTIONS TO CANDIDATES Using IBM DB2, student must RUN the SQL, print screen OR using snipping tool for output and paste the output in th...

Description

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

UNIVERSITI TEKNOLOGI MARA LAB ASSESSMENT

COURSE

:

INTRODUCTION TO DATABASE DESIGN

COURSE CODE

:

ITS232/ICT200

DATE

:

JUNE 2020

STUDENT NAME

:

UiTM ID NO.

:

LECTURER

:

INSTRUCTIONS TO CANDIDATES 1.

Using IBM DB2, student must RUN the SQL, print screen OR using snipping tool for output and paste the output in the ANSWER column in this question paper.

2.

Answer ALL the questions and answer in English.

Part

Full Marks

A

20

B

30

Total Marks

50

Your Marks

PART A (20 MARKS) You need to develop a Hospital Tapah Database System based on the following requirements: © Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

Using IBM DB2, WRITE SQL statements and SCREENSHOT of the output.

1. Create a table named DOCTOR having the following Structure: Field Name Data Type DocID

integer

DocFName

Varchar(30)

DocLName

Varchar(30)

Gender

Varchar(30)

Expertise

Varchar(30)

Set DocID as Primary key ANSWER : COMMAND:

OUTPUT:

2. Create a table called PATIENT having the following structure: Field Name Data Type

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

PatID

Integer

PatName

Varchar(30)

PatAddr

Varchar(30)

PatGender

Varchar(30)

Age

integer

ContactNo

Varchar(20)

Set the field PatID as the Primary Key. ANSWER: COMMAND:

OUTPUT:

3. Create a table called WARD having the following structure: Field Name Data Type

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

WardID

Varchar(10)

WardName

Text

Location

Text

Set the field WardID as the Primary Key. ANSWER: COMMAND:

OUTPUT:

4. Create a table called DUTY having the following structure: Field Name Data Type DocID

© Hak Cipta Universiti Teknologi MARA

integer

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

WardID

Varchar(10)

PatID

Integer

BedNo

Varchar(10)

Diagnosis

Varchar(50)

DateDiagnose

Date

Set the field DocID, WardID and PatID as the composite Primary Key. DocID, WardID and PatID also foreign key ANSWER: COMMAND:

OUTPUT:

5. Enter the following data for the DOCTOR table: DocID DocFName DocLName

Gender

Expertise

101

Male

Surgery

Muhammad Fateh

© Hak Cipta Universiti Teknologi MARA

Faisal

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

102

Maryam

Mazlan

Female

Neurology

103

Sumayyah

Idris

Female

Haematology

ANSWER: COMMAND:

OUTPUT:

6. Enter the following data for the PATIENT table: PatID PatName PatAddr

© Hak Cipta Universiti Teknologi MARA

PatGender

Age

ContactNo

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

111

Khai Bahar

JC 2088, Jalan LKP 16, 77000 Jasin, Melaka

Male

23

0171234567

222

Haqiem Rusli

JC 404, Taman Maju, 77000 Male Jasin, Melaka

20

0124327889

333

Siti Nurhaliza Taruddin

JC 1717, Taman Merlimau Permai, 77300 Merlimau, Melaka

Female

40

0135577123

444

Jamaludin

1234, Kampung Buluh Gading, Manir 21200 Kuala Terengganu, Terengganu

Male

42

0176778899

ANSWER: COMMAND:

OUTPUT:

7. Enter the following data entry for the WARD table:

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

WardID

WardName

Location

C5

Medical Ward

Level 1, Block C

C10

Surgery Ward

Level 2, Block C

A4

First Class Ward

Level 4, Block A

ANSWER: COMMAND:

OUTPUT:

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

8. Enter the following data for the DUTY table: DocID WardID PatID BedNo Diagnosis

DateDiagnose

102

C5

444

24

Epilepsy

15/6/2020

103

A4

333

11

Low blood pressure

10/5/2020

101

C10

222

16

Tumor in stomach

1/5/2020

ANSWER: COMMAND:

OUTPUT:

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

PART B (30 MARKS)

Based on database you created in Part A, WRITE SQL statements and SCREENSHOT of your IBM DB2’s output for the following queries: 1.

Insert a new doctor data with the following: The doctor id = 104 Doctor First Name = Rashid Doctor Last Name = Salleh Gender = Male Expertise = Surgery (3 marks)

COMMAND:

OUTPUT:

2.

Find the doctors’ name and their positions who oversees patient name ‘Jamaludin’; (4 marks)

COMMAND:

OUTPUT:

3.

List all the patients who are from ‘Terengganu’.

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

(3 marks) COMMAND:

OUTPUT:

4.

Count how many doctors on duty for each ward from 1-May-2020 to 16-May-2020. (5 marks)

COMMAND:

OUTPUT:

5.

List the doctor’s name and expertise who are not doing any duty of any wards. (3 marks)

COMMAND:

OUTPUT:

6.

Trace the history of patient name ‘Haqiem Rusli’. Display the patient name, ward name, diagnosis, date diagnose and the doctor’s name on duty.

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL

CS/JUNE2020/ITS232_ICT200

CONFIDENTIAL

(4 marks) COMMAND:

OUTPUT:

7.

List the WardID that currently holds number of patients less than 10. (4 marks)

COMMAND:

OUTPUT:

8.

Update the diagnosis into Stomach Cancer for Patient ID = 222. (4 marks)

COMMAND:

OUTPUT:

END OF LAB TEST QUESTION

© Hak Cipta Universiti Teknologi MARA

CONFIDENTIAL...