SULIT DEC40073: DATABASE SYSTEM / PKK

NAME: HERYANSHAH BIN SUHIMI@ SUHAIMI

REGISTRATION NO: 07DTK18F1016

ANSWER ALL QUESTIONS

SUBMIT YOUR ANSWER BEFORE 5 APRIL 2020 IN CIDOS

SECTION A : 60 MARKS

INSTRUCTION:
This section consists of FOUR (4) structured questions. Answer ALL the questions.

CLO1 QUESTION 1(a)

C1
List FOUR (4) general types of database. [4 marks]

- Department Database
- Workgroup Database
- Enterprise Database
- Personal Database

2 SULIT
 SULIT DEC40073: DATABASE SYSTEM / PKK

CLO1 QUESTION 1(b)

C2 Compare FOUR (4) differences between Database System and File Based System using
Table A1.
Table A1
Database System File Based System
Data is in one place Data are separate and isolate
Data can be placed in one place All the data may be separate and isolate
because of same file format and because of different file format and it’s
compatibility incompatible to combine all of them
System can detect if any duplicate data System can’t detect if any duplicate
data. Data duplication may occur and
user should update all the data
manually
All the data can be reached and opened Because of different file format, all the
with only one program. data can only be read with a suitable
program and it increase the number of
different application program.

[8 marks]

3 SULIT
 CLO1
C2
SULIT DEC40073: DATABASE SYSTEM / PKK

QUESTION 1(c)
Explain the following attribute with an appropriate example.
i. Model schema.
ii. Model instance. [8 marks]

i. Model Schema is a collection of entities in relationship diagram. It’s used as

reference for user to insert an appropriate data. The model schema data are
volatile data.

Example (i):
Nana_Shop
ID_B Name_B Price_B_Rm Quantity_B

ii. Model Instance is a collection of attributes in relationship diagram. It’s a data

that are inserted by a user in relationship diagram. The model instance data
are non-volatile data. It can change, depend on the user, times, currency and
other factor

Example (ii):
Nana_Shop
ID_B Name_B Price_B_Rm Quantity_B
B01 Chicken_Egg 10.90 30
B02 Dettol 23.90 1
B03 Cat_Food 16.90 1

*3 years later
Nana_Shop
ID_B Name_B Price_B_Rm Quantity_B
B01 Chicken_Egg 10.90 25
B02 Dettol 25.90 1
B03 Whiskis_Cat_Food 18.80 1
QUESTION 2
SOALAN 2

Statement 1

A company has several departments. Each department has a supervisor and at least one
employee. Employees must be assigned to at least one department. Project will be given
to the employee and it will be done in a4 group. At least one employee is assigned
SULIT
to one
project or more. The important data fields are the names of the departments, projects,
supervisors and employees, as well as the supervisor and employee number, department
number and a unique project number.
SULIT DEC40073: DATABASE SYSTEM / PKK

Based on Statement 1:

(a) State FOUR (4) existing entities. CLO1

- Department C1

- Supervisor
- Employee
- Project
[4 marks]

(b) Fill in all attributes of each entity listed in Table A2. CLO1
C2
Table A2
Entity Attribute
Department Dept_No, Dept_name
Supervisor SV_no, SV_name
Employee Emp_no, Emp_dept

[6 marks]
CLO1
C3

(c) Draw the Entity-Relationship (ER) diagram for Statement 1 with their relationship.

SV_name Supervisor SV_no

5 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

1
CLO1
C2

has

Dept_name Department Dept_no

m
1

have

Emp_Name Employee Emp_No

assigned

Project_Name Project Project_No

[10 marks]

QUESTION 3(a)
Explain TWO (2) integrity types in database.
- Entity Integrity: Entity integrity is an integrity rule which states that every table must have a
primary key and that the column or columns chosen to be the primary key should be unique
and not null. Every instance of an entity must have a unique primary key value that is not
null.

6 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

- Referential Integrity: a set of rules that prevents you from changing or deleting a record if CLO1
matching records exist in a related table C3

[4 marks]

QUESTION 3(b)
By referring to the Table A3b, carry out the result using the relational algebra operator
below:
StudentA  StudentB

Table A3b
StudentA
RegNo Name Programme
07DTK15F100
Insyirah DTK
1
07DTK15F200
Nur Husnina DTK
1
07DET15F2001 Ariqah DET
07DET15F1001 Ullima DET
07DEP15F2001 Hurriyah DEP

StudentB

RegNo Name Programme

07DTK15F100 Insyirah
DTK
1
07DET15F2002 Zawanah DET
07DET14F1001 Faqiha DET

Answer:
StudentA  StudentB
RegNo Name Programme
07DTK15F100
Insyirah DTK
1
07DTK15F200 Nur Husnina DTK

7 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

1
CLO1
07DET15F2001 Ariqah DET C3
07DET15F1001 Ullima DET
07DEP15F2001 Hurriyah DEP
07DET15F2002 Zawanah DET
07DET14F1001 Faqiha DET

[8 marks]

QUESTION 3(c)
Referring to Table A3c, show the result using the relational algebra operator below:
1. MatNo, ICNo (CourseCode=DEC40073 or Class=DTK4A)(STUDENT)
2. StudName, Class (CGPA>=2.50 and CourseCode=DEC40073)(STUDENT)

Table A3c
STUDENT
MatNo StudName IC No Class CGPA CourseCode
07DTK17F100
Mardhiyyah 901221-12-0426 DTK4A 3.21 DEC40062
1
07DTK17F200
Syauqiah 901009-06-1524 DTK4A 2.50 DEC40062
4
07DTK17F100
Taqiyyah 900804-08-4501 DTK4A 2.89 DEC40073
2
07DTK17F100
Umar 900625-01-5128 DTK4B 3.88 DEC40073
3

Answer:

1.
STUDENT

8 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

MatNo IC No
07DTK17F100
901221-12-0426
1
07DTK17F200
901009-06-1524
4
07DTK17F100
900804-08-4501
2
07DTK17F100
900625-01-5128
3

2.
STUDENT
StudName Class
Taqiyyah DTK4A
Umar DTK4B

[8 marks]

SECTION B : 20 MARKS

CLO1
INSTRUCTION: C4
This section consists of ONE (1) essay question. Answer the question.

QUESTION 1
Based on the Table B1(a), transform that table to the SQL command that will create the table
structure for a table named STUDENT. The basic STUDENT table structure is summarized
in Table B1(b). (Note that the ClassCode is the FK to STUDENT).

Next, determine the SQL command to insert a new record as shown below in Table B1(a).
 07DTKF1013, Faiznor, 8 April 1994, Female, 02, Semporna

9 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

Then, illustrate a result using the SQL command below:

SELECT RegNo, Name, ClassCode AS NewClass
FROM STUDENT
ORDER BY Sex, Address DESC;

Table B1(a)
STUDENT
RegNo Name Sex BirthofDate Address ClassCode
07DTKF1001 Uthman Male 20 Januari 1994 Tuaran 02
07DTKF2001 Aisyah Female 22 December 1995 Ranau 01
07DETF2001 Abu Bakar Male 14 June1995 Tawau 01
07DEEF1041 Ali Male 25 March1995 Keningau 01
07DEPF2041 Khadijah Female 23 February 1995 Sandakan 02

PROGRAMME
ClassCode ClassName
01 Mawar
02 Cempaka

Table B1(b)
Attribute (Field) Name Data Declaration
RegNo Varchar (10)
Name Varchar
Sex Varchar (6)
Birthofdate Date
Address Varchar
ClassCode Integer (2)

Answer:

Step 1: Insert SQL to create tables.

CREATE TABLE PROGRAMME

(ClassCode INT(2)NOT NULL UNIQUE,
ClassName VARCHAR,
PRIMARY KEY(ClassCode));

10 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

CREATE TABLE STUDENT

(RegNo VARCHAR(10)NOT NULL UNIQUE,
Name VARCHAR,
Sex VARCHAR(6),
BirthofDate DATE,
Address VARCHAR,
ClassCode INT(2),
PRIMARY KEY(RegNo),
FOREIGN KEY(ClassCode) REFERENCES
PROGRAMME(ClassCode));

Additional SQL code:

INSERT INTO PROGRAMME

VALUES(01,‘Mawar),
(02,‘Cempaka);

INSERT INTO STUDENT

VALUES(‘07DTKF1001’,‘Uthman’,‘Male’, ‘20 Januari 1994’,‘Tuaran’,02),
(‘07DTKF2001’,‘Aisyah’,‘Female’,‘22 December 1995’,‘Ranau’,01),
(‘07DETF2001’,‘Abu Bakar’,‘Male’,‘14 June 1995’,‘Tawau’,01),
(‘07DEEF1041’,‘Ali’,‘Male’,‘25 March 1995’,‘Keningau’,01)
(‘07DEPF2041’,‘Khadijah’,‘Female’,‘23 February 1995’,‘Sandakan’,02);

Step 2:

INSERT INTO STUDENT

VALUES(‘07DTKF1013’,‘Faiznor’,‘Female’,‘8 April 1994’,‘Semporna’,02);

(Make sure the arrangement is correct)

Step 3:

11 SULIT
SULIT DEC40073: DATABASE SYSTEM / PKK

SELECT RegNo, Name, ClassCode AS NewClass

FROM STUDENT
ORDER BY Sex, Address DESC;

STUDENT
RegNo Name ClassCode NewClass
07DTKF1013 Faiznor 02 02
07DEPF2041 Khadijah 02 02
07DTKF2001 Aisyah 01 01
07DETF2001 Abu Bakar 01 01
07DTKF1001 Uthman 02 02
07DEEF1041 Ali 01 01

[20 marks]

12 SULIT