CSI32 M1 Database Management System Theory and Practical Test Page 1

WALTER SISULU UNIVERITY
FACULTY OF NATURAL SCIENCE S
DEPARTMENT OF MATHEMATICAL SCIENCES AND COMPUTING
CSI32 M1 DATABASE MANAGEMENT SYSTEMS THEORY AND PRACTICAL TEST 2
Lecturer : Mr C. Kabuya
INSTRUCTIONS: Answer all questions . For Theory questions, use answer books. Total Marks : 100
Due Date: 8th November , 20 18. Time: 11:00rhs
Question One 2 5
a) With aid of an example explain the following relationships. 9
i) Recursive Relationship
ii) Ternary Relationship
iii) Weak Relationship
b) Using an example to demonstrate understanding, explain how single attributes and multivalued attributes are
handled in the relational databases. 6
c) Explain optional and mandatory participation in an entity relationship . Give an example. 6
d) List the four major characteristics that tables must conform to in order to be considered of well -informed
relations. 4
Question Two 2 5
a) By way of an example, explain the difference between an ORDER BY clause and a GROUP BY clause. 5
b) Consider the following relation with sample data
? The HEALTH CENTER table captures the data about patients’ appointments with doctors .
? Each patient has a unique Patient ID and a Patient Name
? Each doctor has a unique Doc ID and a Doc Name.
? Each patient has one insurance company.
? Each insurance company has a unique Insurance Co ID and Insurance Co Name.
? Each appointment is scheduled between one patient and one doctor/
? Each patient can have many appointments scheduled (but only one appointment scheduled with one
particular doctor).

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

i. Depict full key functional dependencies, partial functional dependencies (if any) and transitive
functional dependencies (if any) in the HEALTH CENTER table. 6
ii. Show the result of normalizing the HEALTH CENTER table to 2NF. 4
iii. Show the results of normalizing the HEALTH CENTER to 3NF. 5
iv. Draw an ERD after normalizing to 3NF clearly resolving any desi gn issues and showing
relationships, multiplicities and cardinalities in the relationships. 5

END THEORY TEST

CSI32 M1 Database Management System Theory and Practical Test Page 2

PRACTICAL TEST 50 Marks

1) This section must be attempt on the PC and evidence of work done if form of actual screenshots will be
required to be given full marks.
a) Based on the normalized tables in Question Two b. iii)
i) Create a database named doctors_student No. ( Replace studentNo with your own number ) 1
Add Evidence here

ii) Create all the tables identified. 8
Insert evidence/any comments on what you have created.

iii) Insert the provided in the HEALTH CENTER table into the newly created tables. 5
Insert evidence here

CSI32 M1 Database Management System Theory and Practical Test Page 3

iv) Based on your newly created tables, write following SQL command. Provide evidence of
command and the result
1. Displays a list of all doctors.

2
2. Displays a list of all patients whose names contains an ‘a’.

CSI32 M1 Database Management System Theory and Practical Test Page 4

3
3. Display a result similar to HEALTH CENTER data.
5
4. Add patient’s last name and age.
6
5. Add a condition that age must be in the range 0 to 120. 3

CSI32 M1 Database Management System Theory and Practical Test Page 5

6. After adding age, update the changes of t he patients Max 30yrs, Lee 50

. 4
7. Change the appointment date for patient Alen to 25 Dec .
2
2) Using the Ch07_Construct_Co database created during the class activity. Write the following SQL Comm
a) Create a table named JOB_1 which is a subset of JOB but wi th only the following attributes, JOB CODE,
DESCRIPTION AND JOB_CHG_HOUR. Insert only Jobs which have a JOB_CHG_HOUR of more than $60.
Evidence of SQL command, structure showing created data use desc JOB_1, showing data
5
b) Using JOB_1, retr ieve the JOB_CHG_HOUR which is the most expensive.

CSI32 M1 Database Management System Theory and Practical Test Page 6

3
c) Using the JOB_1, Change the job description for System Analyst to Software Engineer.
3

Must provide evidence of work done. Failure to do so means you lose marks.
Submission: PDF or Printed work with all evidence of work done on Canvas or via Email to
[email protected]
END OF PRACTICAL TEST
ALL THE SUCCESS!!!

x

Hi!
I'm Katy

Would you like to get a custom essay? How about receiving a customized one?

Check it out