DATABASE

DESIGN
NORSHADILA | MAS AYU | PUZIAHHAIZA

TEKNOLOGI & PERKOMPUTERAN

Copyright ©2021
All rights reserved. No part of this publication may be reproduced, distributed, or transmitted
in any form or by any means, including photocopying, recording, or other electronic or
mechanical methods, without the prior written permission of the publisher, except in the case
of brief quotations embodied in critical reviews and certain other non-commercial uses
permitted by copyright law. For permission requests, write to the publisher, addressed
“Attention: Permission Coordinator,” at the address below.

Politeknik Mersing
Jalan Nitar,
86800 Mersing
Johor Darul Ta’zim
Telephone : 07-7980001
Fax : 07-7980002
Website : http://www.pmj.edu.my

Printed in Malaysia

First Printing, 2021

eISBN : 978-967-2904-15-1

Authors :
Norshadila binti Ahmad Badela, Mas Ayu binti Mohd Ariff, Puziahhaiza binti Pazui

Editorial :
Norshadila binti Ahmad Badela

Graphic Designer :
Mas Ayu binti Mohd Ariff
 Page |2

DEDICATIONS

Thanks to ALLAH S.W.T for His mercy and guidance in giving, us full of strength to complete
this e-book.
A lot of thanks to our Head Of Department, Puan Azreen Binti Khamis for all of her support

and guidance in helping us to finish our e-book. Special appreciation also to the PMJ

e-learning team for giving us permission and great cooperations. We also would like to say

thank you to our beloved family for their never-ending support.

Page |3
 Page |4

FUNDAMENTAL OF DATABASE SYSTEM

DATABASE

TYPE OF LOGICAL DATA MODEL PROPERTIES OF DATABASE

A collection of information that is Completeness
organized so that it can be easily : Integrity
♪ Accessed Flexibility
♪ Managed Efficiency
♪ Updated

DATABASE MANAGEMENT SYSTEM (DBMS)

DEFINITION
A software that handles the storage, retrieval and updating of data in computer system.

DBMS ARCHITECTURE

CENTRALIZE DISTRIBUTE
 Page |5

DATA MODEL

DEFINITION
A logical structure of a database, including the relationships and constraints that
determine how data can be stored and accessed.

TYPE OF LOGICAL DATA THREE-SCHEMA ARCHITECTURE

Model
Object Based Logical Model
♪ Entity-Relationship Data Model
…
END USER

Record Based Logical Model EXTERNAL EXTERNAL EXTERNAL

LEVEL VIEW VIEW
♪ Hierarchical Data Model
♪ Network Data Model External /
Conceptual
♪ Relational Data Model Mapping

CONCEPTUAL
CONCEPTUAL SCHEMA
LEVEL
CATEGORIES OF DBMS
Conceptual /
Desktop Database Internal
Mapping
♪ Microsoft-Access
♪ FoxPro INTERNAL
INTERNAL SCHEMA
♪ FileMaker Pro LEVEL
♪ Paradox
♪ Lotus

STORED DATABASE
Server Database
♪ Oracle
♪ Microsoft SQL Server
♪ IBM
♪ DB2
 Page |6

Activity 1A

i. Write the definition of Database.

Answer :

ii. List three (3) importance of databases in everyday life.

a. __________________________________________________________________
b. __________________________________________________________________
c. __________________________________________________________________

iii. Draw the database development process.

Answer :
 Page |7

Activity 1B

i. Describe the properties of database.

a. Completeness
___________________________________________________________________
___________________________________________________________________

b. Integrity
___________________________________________________________________
___________________________________________________________________

c. Flexibility
___________________________________________________________________
___________________________________________________________________

d. Efficiency
___________________________________________________________________
___________________________________________________________________

e. Usability
___________________________________________________________________
___________________________________________________________________

ii. DBMS provide several functions in addition to simple file management. List five (5)
functions that DBMS provide.
a. ___________________________________________________________________
b. ___________________________________________________________________
c. ___________________________________________________________________
d. ___________________________________________________________________
e. ___________________________________________________________________
 Page |8

iii. Discuss the importance of DBMS.

Answer :
 Page |9

Activity 1C

i. List five (5) purposes of database.

Answer :

ii. List three (3) problems using traditional approach and give an example for each
problem.

Answer :
 P a g e | 10

iii. Draw and describe two (2) database architecture.

Answer :
 P a g e | 11

Activity 1D

i. Describe the following logical Data Model.

a.Object Based Logical Model

Answer :

b. Record Based Logical Model

Answer :
 P a g e | 12

ii. Write the correct Data Model for the given explanation.

 Each parent can have

many children.
 Each child has only one
parent
 Tree is defined by path
that traces parent
segments to child
segments, beginning
 from the left
Represent complex
 data relationships more
effectively
 Improve database
performance
 Impose a database
standard


 Performs same basic

functions provided by
hierarchical and
network DBMS
systems.
 RDBMS handles all the
complex physical
details.
 segments, beginning
from the left
 P a g e | 13

Activity 1E

i. Based on the following diagram, fill in the blanks.

ii. List software that use for following categories of DBMS.

a.Desktop Database

Answer :
 P a g e | 14

b. Server Database

Answer :

iii. Identify the users of DBMS

Answer :
 P a g e | 15

RELATIONAL DATA MODEL

Relational Database Model

A relational database is a type of database that stores and provides access to data points that
are related to one another. A relational database management system (RDBMS) is a database
management system (DBMS) based on the relational model invented by E. F. Codd in year
1970.

Example of RDBMS Packages Objective of Relational Model

IBM My ♥ To allow a high degree of data

DB2 SQL
independence

Microsoft ♥ To provide considerable grounds

Oracle
SQL Server for dealing with data semantics,
reliability, and redundancy
Dataphor SQLite
problems.
♥ To allow the expansion of set-
oriented data manipulation
languages
 P a g e | 16

Advantages and Disadvantages of Using Relational Model

Pro Cons

Simplicity: simpler than the Few relational databases have

hierarchical and network model. limits on field lengths which can't
Easy to use: tables consisting of be exceeded.
rows and columns are quite Relational databases can
natural and simple to understand sometimes become complex as
Data independence: The Structure the amount of data grows, and the
of Relational database can be relations between pieces of data
changed without having to change become more complicated.
any application. Complex relational database
Scalable: database should be systems may lead to isolated
enlarged to enhance its usability. databases where the information
cannot be shared from one system
Structural Independence: The to another.
relational database is only
concerned with data and not with
a structure. This can improve the
performance of the model.

Relational Database Structure

Relation Schema
•A relation schema represents the name of table with its
attributes.

•Relation to Tuple
(table) •a single row of a table, which contains a single record.
•Tuple (row of
Cardinality
record)
•Total number of rows present in the Table.
•Cardinality
•Attribute Domain
Relational (Column/ •A domain is the set of allowable values for one or more
Components Field)
attributes.
•Degree
(Number of Degree
columns) •The total number of attributes which in the relation is
called the degree of the relation
•Domain
•Entity Attributes
•Each column in a Table which has certain charateristics
known as attributes.
Entity
•Things or object in real world with independent
existence such as person, job,event etc.
 P a g e | 17

Types Of Attributes

Simple Attributes Single Valued Attributes

Attribute cannot be further Only have one value for the particular entity
divided such as Age. such as IC number.
Composite Attributes Multivalued Attributes
Comprise of more than one Attributes that can have many values such as
attributes such as address consist student
of postal code and city. can have many certificate like UPSR, PMJ,
SPM ect.
Stored Attributes
Such as date_of_birth

Derived Attributes
Can be computed from other attributes such as Age
Derived from date_of_birth.

Common Relational Model Terms

Table is also called Relation

Domains
STUDENT

ID NAME AGE CITY

Primary Key
S1 MIMI 21 JB
Total of row is
Tuples/Row

Cardinality

S2 TEO 25 KB

S3 KUMAR 20 KL

Attributes/Column

Total of column is Degree

 P a g e | 18

Relational Scheme

A named of relation defined by a set of attributes and domain name pairs. Its distinct from
all other relation names in relational schema.
The relation schema normally represented as below which Primary Key is underlined.

STUDENT (IDNumber, Name, Gender, PhoneNum, Department, Address)

Relational Keys

A candidate key selected to uniquely identify all other attribute

Primary Key
values in any given row. Cannot contain null entries.

A minimal super key. A super key that does not contain a subset of
Candidate Key
attributes that is itself a super key

An attribute (or combination of attributes) used strictly for data

Alternate key
retrieval purposes.

An attribute (or combination of attribute) in one table whose

Foreign key values must either match the primary key in another table or be
null.
 P a g e | 19

Data Integrity
ENTITY INTERGRITY DESCRIPTION

All primary key are unique, and no part of primary key may
Requirement
be null
Guarantees that each will have a unique identity and

Purpose ensures that foreign key values can properly reference

primary key values
REFERENTIAL DESCRIPTION
INTERGRITY
A foreign key may have either a null entry – as long as it is
not a part of its table’s primary key – or an entry that

Requirement matches the primary key value in a table to which it is

related. (Every non-null foreign key value must reference an
existing primary key value.)
Makes it possible for an attribute NOT to have a
corresponding value, but it will be impossible to have an
invalid entry. The enforcement of the referential integrity
Purpose
rule makes it impossible to delete a row in one table whose
primary key has mandatory matching foreign key values in
another table.

Integrity Constraints

Entity ● Unique
Integrity ● Primary Key

♥ Not Null
Domain ♥ Check
Integrity
♥ Default

Referential
Integrity  Foreign Key
 P a g e | 20

Relational Algebra

A procedural query language used to query the database tables to access data in
different ways.

Types of Operators

Select Symbolic
notation
Project
Retrieve
Inner Join
Information
Operators

Outer Join

Cross Product

Union
Traditional
Intersection
Operator
Difference
 P a g e | 21

Activity 2A

Explain the following terms briefly.

Tuple

Attribute

Domain

Degree

Cardinality

Relation
 P a g e | 22

Activity 2B

Identify attribute, domain, degree, tuple and cardinality for the following tables:
LECTURER

StaffNum ICNum Name Position Department PhoneNum

Kejuruteraan
IT001 781103-03-5333 Abdullah Pensyarah 09-8840023
Perisian
Sains
IT002 680923-11-4908 Maimunah Prof Madya 09-8840024
Komputer
Sistem
IT003 801204-06-2323 Kamal Pensyarah 09-8840025
Maklumat

IT004 600720-06-5380 Jamilah Prof Madya Multimedia 09-8840026

Kejuruteraan
IT005 590101-01-2425 Hakim Professor 09-8840027
Perisian

IT006 810303-19-9923 Anis Pensyarah Multimedia 09-8840028

Answer :

Attribute

Domain

Degree

Tuple

Cardinality
 P a g e | 23

Activity 2C

Determine the Primary, Candidate, Foreign and Alternate key for the table below.

Branch_info
Branch_Id Branch_Name Branch_Code
1 Computer Science CSE
2 Electronic ECE
3 Mechanical MCE
4 Information Technology ITE
5 Civil CVE

Student_Information
Fath_
Stu_Id Coll_Id Stu_Name Ic_Num Address Branch_Id Session
Name
1 12100 Amir Hadi 1254 Jb 1 2015
2 12101 Haziq Ali 1264 Kl 1 2016
3 12102 Puja Kumar 1302 Kluang 3 2015
4 12103 Meera Din 1004 Kb 5 2014
5 12104 Suzie John 2612 JB 1 2016
6 12105 Choo Tan 1287 KL 2 2015

Answer :
i. Primary Key -

ii. Candidate Key -

iii. Foreign Key -

iv. Alternate Key -

 P a g e | 24

Activity 2D

Based on given table, generate the output based on operation below.

a. σ class=’Business’ (Seat)

Answer :

b. πnationality (Booking)

Answer :
 P a g e | 25

c. σ nationality = “French” (Booking) X σclass=”Business” (Seat)

Answer :

d. πname (σ class=’Business’(Booking Seat))

Answer :

e. Booking Seat

Answer :
 P a g e | 26

Activity 2E

Solve the problem based on tables below:

1. Solve the following relational expressions.

a. PName (RAge>25 (User))

Answer :
 P a g e | 27

b. RId>2VAge!=31 (User)

Answer :

c. RUser.OccupationaId=Occupation.OccupationId (User X Occupation)

Answer :
 P a g e | 28

d. User Occupation City

Answer :

e. PName,Gender (RCityName = “Boston” (User City))

Answer :
 P a g e | 29

ENTITY RELATIONSHIP DIAGRAM (E-R) MODEL &

NORMALIZATION

Normalization

Process of removing redundant data from your tables to improve storage efficiency, data
integrity, and scalability.

Anomalies

• Errors or inconsistencies that may result when a user attempts to update a table that
contains redundant data
• Modification Anomalies
• Changed an incorrect data, sometimes could involve many records having to be
changed, resulted the possibility of some changes being made incorrectly.
• Insert Anomalies
• Impossible process of inserting vital data into the database because other data is
not already there.
• Delete Anomalies
• Unintentionely deleting a data, and the deletion process may result in the
deletion of the only instance of other, required data.

Information Redundancy
• Duplication of data due to the storing of same data multiple times. Redundant
information may waste our storage size.

Functional Dependencies

• Describe relationships between attributes in a relation.

Transitive Dependencies

• A condition in which an attribute is dependent on another attributes that is

not part of the primary key.
 P a g e | 30

Steps in Normalization

Remove repeating groups Remove transitive dependencies

UNF 1NF 2NF 3NF

Remove functional dependencies

BOYCE-CODD Normal Form (BCNF)

Boyce Codd normal form (or BCNF or 3.5NF)

BCNF dependencies are like 3NF dependencies but they involve some key
attributes
BCNF often arises when a 1:M relationship is modeled as a M:N
relationship
A relation is in Boyce-Codd Normal Form if and only if every determinant is a
candidate key.

Entity Relationship Diagram

Detailed, logical representation of entities, associations and data elements for an

organization or business.

Must be flexible enough so that it can be used and understood in practically any
environment where information is modelled
 P a g e | 31

Basic Element Of ERD

Types Chen Model Crow’s Foot Model

Entity

Entity
Attribute Name
List of
Attributes

Relationship
Verb phrase

Common Properties for ERD Elements

Element Entity
• Person, place, object, event or concept about which data to be maintained

Element Relationship
• Association between the instances of one or more entity types

Element Attribute
• Named property or characteristic of an entity

weak entity Entity set tha does not have a primary key

Existence of a weak entity set depend on the existence of identifying entity

set
- Must relate to the identifying set via a total, one-to-many relationship set
from the identifying to the weak entity set
- Identifying relationship depicted using a double diamond

Relationships can be classified as either

one-to-one one-to-many many-to-many

 P a g e | 32

♪ Chen Model
1 to represent ONE
M to represent MANY

♪ Crow’s Foot Model

Exactly ONE ZERO or ONE ONE or MORE

relationship relationship relationship

ZERO or MORE MORE than ONE

relationship relationship

Cardinality & Connectivity

 P a g e | 33

Activity 3A

Draw a Chen Model ER diagram for the following:

For each of the following pairs of rules, identify two entity types and one relationship.
State the connectivity and existence of the relationship in each case.

i. A department employs many persons. A person is employed by, at most, one

department.

Answer :

ii. A manager manages, at most, one department. A department is managed by, at

most, one manager.

Answer :

iii. A writer may write many books. A book may be written by many writers.

Answer :

iv. A team consists of many players. A player plays for only one team.

Answer :

v. A flight-leg connects two airports. An airport is used by many flight-legs.

Answer :
 P a g e | 34

Activity 3B

You as a database developer has been given a task to develop a simple database of students’
enrolment in Polytechnic Mersing. The following information should be considered:

 Lecturer is an entity with the attributes: lecturer name, lecturer ID and lecturer last
name. The lecturer ID attribute is also an identifier.

 Class is an entity with the attributes: class ID, class name. The class ID attribute is also
an identifier.

 Course is an entity with the attributes: course ID and course name. The course ID
attribute is also an identifier.

 Room is an entity with the attribute: room location, room caption and room ID. The
room ID attribute is also an identifier.

And the following about the model's relationships:

 a lecturer teaches zero, one or many classes and a class is taught by one lecturer
 a course may generate zero, one or many classes and a class comes from one
course
 a class is held in one room but a room has many classes

i. Set the attributes of each entity with a correct naming guidelines

Entity Attributes
LECTURER
CLASS
COURSE
ROOM

ii. Draw a Crow’s Model ER diagram for the above requirements :

Answer :
 P a g e | 35

Activity 3C

i. Write the business rules that are reflected in ER-diagram below.

Answer :

ii. Write ten cardinalities that are appropriate for this ERD below.

Answer :
 P a g e | 36

Activity 3D

Student Student Course Credit Academic

Address Course Name Grade Programme
No Name Code Hours Advisor
System
DFC
Analysis And 4 A
1234
Design
Melissa Human Diploma In
DFP Hanafi
2016001 Abdul Mersing Computer 4 B Digital
2345 Salim
Malik Interaction Technology

Database DFC
4 A
Design 3456

Programming DFC
4 A
Fundamentals 1023
Diploma In Ahmad
Arif Danial Internet DFC
2016115 Ipoh 3 B Information Bukhari
Mohamad Publishing 3123
Technology Hamzah
Multimedia DFM
3 A
Technology 4125

Multimedia DFM
3 B
Technology 4125
Database DFC 4 A Diploma In Nazlina
Lee Guan
2016204 Bertam Design 3456 Computer Muhd
Jian
Human DFP 4 B Science Johari
Computer 2345
Interaction

i. Classify the repeating groups that occur in the above-mentioned table.

Answer :
 P a g e | 37

ii. Identify functional dependency of that table and then group it into its entity.

Answer :

iii. Identify transitive dependency of the table and then group it into its entity.

Answer :
 P a g e | 38

Activity 3E

Based on the relation PROJECT below, produce the Normalization process from 1NF-3NF.

PROJECT
Project_No Project_name Staff_no Staff_name Job_classification Charge_hour Hour
00689 Samat Elect Engineer RM 20 20
1 DADD 00500 Ali Web Developer RM 22 18
00444 Azie Web Developer RM 22 15
00123 Kamal System Analyst RM 25 30
2 Progra-mming
00999 Hazli Technical Support RM 16 56
00689 Samat Elect Engineer RM 20 40
3 Multi-media
00111 Anaz Programmer RM 18 35

Answer :
1NF
STAFF
Staff_no Staff_name Job_classification Charge_hour Hour
00689 Samat Elect Engineer RM 20 20
00500 Ah Web Developer RM 22 18
00444 Azie Web Developer RM 22 15
00123 Kamal System Analyst RM 25 30
00999 Hazlim Technical Support RM 16 56
00689 Samat Elect Engineer RM 20 40
00111 Anaz Programmer RM 18 35
STAFF(Project_No, Staff_no, Project_name, Staff_name,
Job_classification, Charge_hour, Hour)

PROJECT
Project_No Project_name
1 DADD
2 Programming
3 Multimedia
PROJECT(Project_No, Project_name)
 P a g e | 39

Answer :
2NF
 P a g e | 40

Answer :

3NF
 P a g e | 41

STRUCTURED QUERY LANGUAGE

Structured Query Language is a simple programming language used for accessing and
managing data in relational databases.

Used Of SQL Allow user to create database and relation structure

Perform basic data management tasks, such as the insertion,

modification, and deletion of data from the relations

•Data Manipulation Language (DML)

Parts of •Data Definition Language (DDL)
SQL •Transaction Control Language (TCL)
•Data Control Language (DCL)

• Used to define the database schema. It simply deals with descriptions

of the database schema and is used to create and modify the
DDL structure of database objects in the database.
• Example : CREATE, DROP, ALTER, TURNCATE, COMMENT, RENAME
• Deals with the manipulation of data present in the database
DML
• Example : SELECT, INSERT, UPDATE, DELETE
• DCL mainly deals with the rights, permissions and other controls of
DCL the database system.
• Example : GRANT, REVOKE
• TCL commands deals with the transaction within the database.
TCL
• Example : COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
 P a g e | 42

SQL CONSTRAINTS

Used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table.

To ensures the accuracy and reliability of the data in the table.

Example : NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK,

DEFAULT, INDEX

SQL DATA TYPES

CHARACTER/
NUMERIC DATA/TIME BINARY
STRING

bit date char binary

smallint
time
decimal varchar varbinary
datetime
numeric text varbinary (max)
timestamp
float
int year varchar (max) image

SYNTAX SELECT column list

FROM table list
[ WHERE condition list ]
[ GROUP BY column list ]
[ HAVING condition list]
[ ORDER BY column list
[ ASC | DESC ] ];
 P a g e | 43

AND,OR,NOT

< ,<= ,>, >=, <>

WHERE
CLAUSE

BETWEEN, IS NULL, LIKE, IN

AGGREGATE ARITHMETIC COMPUTATIONS

FUNCTIONS OVER A SET OF ROWS

GROUP BY clause to group HAVING clause used to

the output of aggregate restrict the output of the
functions by one or more GROUP BY clause by
attributes. selecting only the aggregate
rows that match a given
condition.
 P a g e | 44

OUTER
JOIN
INNER
JOIN
Specify for two or more tables.
If do not specify a join condition, the
DBMS will automatically perform a
Cartesian product of tables which
specify in the FROM clause.

NATURAL
JOIN
 P a g e | 45

Activity 4A

1. What is difference between a WHERE clause and a HAVING clause for a SELECT query?
______________________________________________________________________
______________________________________________________________________

2. Explain why it might be appropriate to declare an attribute that contains only digits as
character data type instead of numeric data type.
_______________________________________________________________________
_______________________________________________________________________

3. Explain the difference between an ORDER BY clause and a GROUP BY clause.

________________________________________________________________________
________________________________________________________________________

4. What is the difference between the COUNT aggregate function and the SUM aggregate
function?
________________________________________________________________________
________________________________________________________________________

5. Explain why it would be preferable to use a DATE data type to store data instead of a
character data type.
________________________________________________________________________
________________________________________________________________________

6. What is difference between a DELETE command and a DROP command?

________________________________________________________________________
________________________________________________________________________

7. What is difference between logical operators of AND operator and OR operator?

_______________________________________________________________________
_______________________________________________________________________
 P a g e | 46

8. What is function of DISTINCT clause in SQL?

_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________

9. List the DATE formats which exist in the MySQL.

________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________

10. What is difference between NULL and NOT NULL values?

________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
 P a g e | 47

Activity 4B

i. Write the suitable SQL data type and sample data that created for table structure as
shown below.
DATABASE: CONSULT_COMPANY
JOB
ATTRIBUTE NAME
DATA DECLARATION SAMPLE DATA
(FIELD)

JOB_CODE (PK)
VARCHAR (10) J10001
(Example)

JOB_DESCRIPTION

JOB_CHARGE_HOUR

JOB_LAST_DATE

EMPLOYEE
ATTRIBUTE NAME
DATA DECLARATION SAMPLE DATA
(FIELD)

EMP_NO (PK)

EMP_NAME

EMP_HIREDATE

JOB_CODE (FK) REFER TO THE JOB TABLE

EMP_YEARS

PROJECT
ATTRIBUTE NAME
DATA DECLARATION SAMPLE DATA
(FIELD)

PROJ_NO (PK)

PROJ_NAME

PROJ_VALUE

PROJ_BALANCE

EMP_NO (FK) REFER TO THE EMPLOYEE TABLE

 P a g e | 48

ASSIGNMENT
ATTRIBUTE NAME
DATA DECLARATION SAMPLE DATA
(FIELD)

ASSIGN_NO (PK)

ASSIGN_DATE

PROJ_NO (FK) REFER TO THE PROJECT TABLE

EMP_NO (FK) REFER TO THE EMPLOYEE TABLE

ASSIGN_CHARGE_HOUR

ii. Create the database and all tables according to the data declaration which consist of
sample data given in Activity 4B (i).

Answer:
 P a g e | 49

Activity 4C

i. Underline the SQL statements below using the SweetHome case study which consist of
the following tables.

Guidelines: PRIMARY KEY (_____________) FOREIGN KEY (_ _ _ _ _ _ _ _ _ _ _ _)

a. Branch (branchNo, street, city, postcode);

b. Staff (staffNo, fName, lName, position, gender, DOB, salary, branchNo);
c. PropertyForRent (propertyNo, type, rooms, rent, ownerNo, staffNo, branchNo);
d. Client (clientNo, fName, lName, telNo, prefType, maxRent, email);
e. PrivateOwner (ownerNo, fName, lName, address, telNo, email, password);
f. Viewing (clientNo, propertyNo, viewDate, comments);

ii. Write SQL query for create tables using the suitable data declaration in (i).

Answer:
 P a g e | 50

Activity 4D

User
Id Name Age Gender OccupationId CityId
1 John 25 Male 1 3
2 Sara 20 Female 3 4
3 Victor 31 Male 2 5
4 Jane 27 Female 1 3

Occupation City
OccupationId OccupationName CityId CityName
1 Software Engineer 1 Halifax
2 Accountant 2 Calgary
3 Pharmacist 3 Boston
4 Library Assistant 4 New York
5 Toronto

Figure 4D

As shown in Figure 4D, solve the requirements below.

i. Create tables for User, Occupation and City.

Answer :
 P a g e | 51

ii. Insert all data regarding to the Figure 4D.

Answer:

iii. Display the number of users according to their genders.

Answer:

iv. Find the user that works as software engineer and age from 25 until 30 only.

Answer:
 P a g e | 52

v. Display all the information of user named as Sara and Victor which consist of Name,
Age, Gender, OccupationName and CityName only.

Answer:
 P a g e | 53

Activity 4E

Complete the database and answer all the questions below using MySQL.

Database: CAR
CUSTOMER
PHONE_
CUSTID CUSTNAME CAR_NUMBER REGISTER_DATE
NUMBER
C0001 David Teo WUV2318 81691 2014-01-09
C0002 Sulaiman Ali JKL9782 61291 2011-05-07
C0003 Kang Gary MAM123 41972 2010-06-13
C0004 Ji Hyo SAA888 11341 2012-09-26
C0005 Karigalan KUL511 21412 2003-07-18

CAR_COMPONENT
COMPID COMPNAME PRICE
KK001 ISWARA Rear Lamp 100
KK002 WIRA Front Bumper 450
KK003 ISWARA Front Mirror 300
KK004 WAJA Break Lamp 250
KK005 WIRA Front Lamp 600

CLAIM
CLAIMID CLAIM_DATE CUSTID
CL001 2014-04-19 C0005
CL002 2014-01-28 C0001
CL003 2014-02-20 C0003
CL004 2014-09-30 C0002
CL005 2014-12-07 C0004

COMPONENT_CLAIM
CLAIMID COMPID QUANTITY
CL001 KK001 1
CL003 KK003 2
CL002 KK002 1
CL005 KK005 1
CL004 KK004 2
 P a g e | 54

STEP 1 : Create table CUSTOMER, CAR_COMPONENT, CLAIM and COMPONENT_CLAIM.

Answer:
 P a g e | 55

STEP 2 : Insert all data into each table.

Answer:
 P a g e | 56

STEP 3 : Type SQL command to find customer name from CUSTOMER table.

Answer:
 P a g e | 57

STEP 4 : Type SQL command to display component ID, component name and price is
between 200 and 400.
Answer:
 P a g e | 58

STEP 5 : Open the SQL Editor and type SQL command to display car number begin with J.

Answer:
 P a g e | 59

STEP 6 : Open the SQL Editor and type SQL command to display claimID and quantity where
claim number is CL001 and CL004.

Answer:
 P a g e | 60

STEP 7 : Open the SQL Editor and type SQL command to find the minimum value for price in
the CAR_COMPONENT table.

Answer:
 P a g e | 61

STEP 8 : Open the SQL Editor and type SQL command to find the maximum value for price in
the CAR_COMPONENT table.

Answer:
 P a g e | 62

STEP 9 : Open the SQL Editor and type SQL command to find the average value for price in
the CAR_COMPONENT table.

Answer:
 P a g e | 63

STEP 10 : Open the SQL Editor and type SQL command to find the sum of price in the
CAR_COMPONENT table.

Answer:
 P a g e | 64

STEP 11 : Open the SQL Editor and type SQL command to count the number of customers.

Answer:
 P a g e | 65

DATABASE TRANSACTION MANAGEMENT

A database transaction is a logical unit of database operations, which is executed as

a whole to process user requests for retrieving data or updating the database.

Transaction Processing Systems (TPS)

A type of information system that collects, stores,
modifies and retrieves the data transactions of an
enterprise. Also attempt to provide
predictable response
times to requests,
Each transaction is usually
although this is not as
Transaction processing short duration and the
critical as for real-time
allows only predefined, processing activity for each
systems.
structured transactions. transaction is programmed
in advance.

VALUE OF TPS

• Fast performance with a rapid response time

Performance • Measured by the number of transactions they can process in a given
period of time.

• The system must be available during the time period when the users
Continuous are entering transactions.
availability
• A breakdown will disrupt operations or even stop the business.

• The system must be able to handle hardware or software problems

without corrupting data.
Data integrity
• Multiple users must be protected from attempting to change the
same piece of data at the same time

• Often users of transaction processing systems are casual users

Ease of use • The system should be simple for them to understand, protect them
from data-entry errors as much as possible, and allow them to easily
correct their errors.

• The system should be capable of growth at incremental costs, rather

Modular than requiring a complete replacement.
Growth • It should be possible to add, replace, or update hardware and
software components without shutting down the system.
 P a g e | 66

Batch Transaction Processing

Relies on accumulating transaction data over a period of time and

then processing the entire batch at once.

Batch processing is usually cyclic: daily, weekly, or monthly run

cycle is established depending on the nature of the transactions

Cheaper than on-line processing

Easier to control than on-line processing

Being captured using disk files

Database is constantly out of date

On-Line Transaction Processing System (OLTP)

Each transaction is completely processed immediately upon

entry.

the most common mode of used today

More costly than batch processing

Database is always up to date

Require the use of fast secondary storage such as magnetic disks

 P a g e | 67

Properties Of Database Transaction

A C I D
ATOMIC CONSISTENT ISOLATED DURABLE

Atomic Consistent Isolated Durable

• All or nothing • The database • Data used • Database
• All of the tasks must be in a during the changes are
of a database consistent execution of a permanent after
transaction must before and after database trrhe
be completed the database transactionmust transaction
• If incomplete transaction. It not be used by completes
due to any means that a another
possible database database
reasons, the transaction must transaction until
database not break the the execution is
transaction must database completed
be aborted. integrity
constraints.

SYNTAX
START TRANSACTION: begins a new
transaction.
COMMIT: commits the current
transaction, making its changes
permanent.
ROLLBACK: rolls back the current
transaction, canceling its changes.
 P a g e | 68

CONCURRENCY CONTROL PURPOSE

• To prevent two different users (or two different connections by the same user)
from trying to update the same data at the same time.

Lost
Update

INTERFERENCE PROBLEM
ARISE FROM
The SIMULTANEOUS ACCESS
Scheduler TO DATABASE
Uncomitted
Dependency

Inconsistent
Retrieval

LOCK
GRANULARITY

CONCURRENCY
TWO-PHASE
CONTROL WITH
DEADLOCKS LOCKING (2PL)
LOCKING
PROTOCOLS
METHODS

LOCKS
 P a g e | 69

TOOLS TO PREVENT THE INTERFERENCE PROBLEMS

• A procedure used to control concurrent access to data.

Locks When transaction is accessing the database, a lock may
deny access to other transaction to prevent incorrect results

Two-Phase • The protocol utilizes locks that block other transactions from
Locking accessing the same data during a transaction's life.
(2PL)
protocol • Protocol to prevent lost update problems

RECOVERY TOOLS

•Transaction log records all transactions and the database modifications

made by each transaction.
TRANSAC- •History of database changes which include undo and redo.
TION LOG

•Enables to back up and restore databases.

DATABASE •Backup and restore component provides an important safeguard for
protecting critical data stored in database.
BACKUP

•Writes all dirty pages for the current database to disk.

•Dirty pages are data pages that have been entered into the buffer
CHECKPOINT cache and modified, but not yet written to disk.
 P a g e | 70

Activity 5A

Describe the properties of database transaction according to situation below in Figure 5A.

1. start transaction
2. read (A)
3. A = A–700
4. write (A) Transaction to transfer money from account A to B
5. read(B)
6. B = B+700
7. write(B)
8. commit

Figure 5A

a. Atomicity

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

b. Consistent

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

c. Isolation

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

d. Durability

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
 P a g e | 71

Activity 5B
Perform a transaction of a given database in Figure 5B, using SQL statements.

Figure 5B

Many real world scenarios require transaction mostly in banking, finance and trading domain.
Database transaction is implemented in SQL using three keywords START TRANSACTION, COMMIT
and ROLLBACK. Based on the solution of scenario shows in Figure 5B, write the SQL statement to
transfer RM900 from Account _Number 1001 to Account _Number 2002.

Answer:
 P a g e | 72

Activity 5C

Use START TRANSACTION AND COMMIT statement.

Note: You have to activate Xampp in order to execute the command.

STUDENT

ICNum Name Email

941018052157 Suffian Iskandar suffian@yahoo.com

950802016214 Sara Summayyah sara_maya@gmail.com

Table 5C (i)

Figure 5C (i)

Step 1: Create database DAFTAR

Step 2: Type USE

 P a g e | 73

Step 3: Create table STUDENT

Step 4: Type the SQL command to insert all data into STUDENT table

Step 5: Type SQL command to display all values in STUDENT table

Step 6: Type the SQL command with START TRANSACTION

Step 7: Type SQL command with to insert new data into STUDENT table

ICNum: 921120034454
Name: Syed Auris
Email: syris@yahoo.com
 P a g e | 74

Step 8: Use select statement again command to display data in STUDENT table. State your
observation.

Step 9: Type SQL command to ROLLBACK the transaction.

Step 10: Use select statement again command to display data in STUDENT table. State
your observation.

Step 11: Type SQL command to COMMIT the transaction.

Figure 5C (ii)
 P a g e | 75

Activity 5D

Higher Order Thinking Skills (HOTS) Questions

1. Explain the statement of ‘a transaction is a logical unit of work’.

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

2. What is the consistent database state, and how it is achieved?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

3. List and discuss the four transaction properties. (A.C.I.D)

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
 P a g e | 76

4. What is a transaction log and its functions?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

5. What is a scheduler and the important to concurrency control?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

6. What is lock and how it works?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
 P a g e | 77

7. What is concurrency control and its objective?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

8. What is an exclusive lock and binary lock?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________

9. What is a deadlock and how it can be avoided?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
 P a g e | 78

10. What is the recovery tools and its explanations?

_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
_________________________________________________________________
 P a g e | 79

Activity 5E

Explain the interference problem arise from simultaneous access to database as situated
in figures below.

Figure 5E (i)

Answer:
 P a g e | 80

An Uncommitted Data Problem

TIME TRANSACTION STEP STORED VALUE
1 T1 Read PROD_QOH 35
2 T1 PROD_QOH = 35 + 100
3 T1 Write PROD_QOH 135
4 T2 Read PROD_QOH 135
(Read uncommitted data)
5 T2 PROD_QOH = 135 – 30
6 T1 ***** ROLLBACK ***** 35
7 T2 Write PROD_QOH 105

Figure 5E (ii)

Answer:
 P a g e | 81

Figure 5E (iii)

Answer :
 P a g e | 82

BIBLIOGRAPHY

Coronel, C. & Morris, S. (2018). Database Systems : Design, Implementation & Management

13th Edition. Cengage Learning, Inc. (ISBN: 9781337627900)

Hogan, R. (2018). A Practical Guide to Database Design. Taylor & Francis. Ltd.

(ISBN 9781138578067)

Watt, A. (2014, October 24). Chapter 11 Functional Dependencies – Database Design – 2nd

Edition. Pressbooks. https://opentextbc.ca/dbdesign01/chapter/chapter-11-

functional-dependencies/

Rungta, K. (2021, May 29). Functional Dependency in DBMS: What is, Types and Examples.

Https://Www.Guru99.Com/. https://www.guru99.com/dbms-functional-

dependency.html

Data Anomalies. (n.d.). Jhigh.Co.Uk. Retrieved June 9, 2021, from

http://jhigh.co.uk/Higher/dbases/anomalies.html

Data Anomalies | Database Management | Fandom. (n.d.). Database Management.

Retrieved July 8, 2021, from

https://databasemanagement.fandom.com/wiki/Data_Anomalies
 P a g e | 83

ANSWER
Activity 1A Activity 1C

i. A collection of information that is organized so that i.

it can be easily accessed, managed and updated.  Data redundancy and inconsistency
 Difficulty in accessing data
Collection of related data that is shared by the various  Data isolation — multiple files and formats
categories of users to meet the requirements of an  Integrity problems
organization’s information.  Atomicity of updates
ii. e-commerce, social Gaming, Online Television  Concurrent access by multiple users
Streaming  Security problems

ii.

a. Data Security
Example:
i. The Customer_Transaction file has details about the
total available balance of all customers.
ii. A customer wants information about his/her
account balance.
iii. In a file system it is difficult to give the customer
access to only his/her data in the file.
iii. iv. Thus enforcing security constraints for entire file or
for certain data items are difficult.

v. b. Data Redundancy
Activity 1B Example:
i. At a university, information of a staff might be at all
i. a. Ensures that users can access the data they want. departments.
b. Ensures that data is both consistent (no contradictory ii. It can cause inconsistent data when in update.
data) and correct (no invalid data), and ensures that users 0
c. Data Isolation
trust the database. Example:
c. Ensures that a database can evolve to satisfy changing user i. A teller looking up a balance must be isolated from
requirements. a concurrent transaction involving a withdrawal
from the same account. Only when the withdrawal
d. Ensures that users do not have unduly long response times transaction commits successfully and the teller
when accessing data. looks at the balance again will the new balance be
reported.
e. Ensures that data can be accessed and manipulated in
ways which match user requirements. d. Program/ Data Dependence
Example:
ii.
i. Assume in a banking system there is need to find
out the names of all customers who live within a
 Allow Concurrency
particular postal-code area.
 Control Security
ii. But there is only a program to generate the list of
 Maintain Data Security all customers.
 Provide for Backup and Recovery iii. The bank officer has now two choices: either
 Control Redundancy obtain the list of all customers and extract the
 Allow Data Independence needed information manually or ask a system
 Provide Non-Procedural Query Language programmer to write the necessary application
 Perform Automatic Query Optimization program.
iv. Both the alternatives are obviously unsatisfactory.
iii.
 It helps to make data management more efficient and
effective. e. Concurrent Access Anomalies
 A database management system stores, organizes and Example:
manages a large amount of information within a single i. Bank account A containing RM 6000/-. If two
software application. transactions of withdraw funds (RM 500/- and RM
 Use of this system increases efficiency of business 1000/- respectively) from account about same
operations and reduces overall costs
 P a g e | 84

time, result of the concurrent executions may i. b. Record Based Logical Model are based on application
leave the account in an incorrect state. and user levels of data. They are modelled considering
ii. Program on the behalf of each withdrawal read the logical structure of the objects in the database. This
the old balance, reduce amount and write result data models defines the actual relationship between
back. the data in the entities. There are 3 types of record
iii. If both two programs are concurrent they both based data models defined so far- Hierarchical,
may read the value RM 6000/-.
Network and Relational data models.
Depending on which one writes the value last, the
account may contain either RM 5500/- or RM 5000/-, Advantages :
rather than the correct value of RM 4500/-
It helps to address the issues of flat file data storage.
iii.
In flat files, data will be scattered and there will not
be any proper structuring of the data. This model
Centralize groups the related data into tables and defines the
relationship between the tables, which is not
addressed in flat files.

ii. Hierarchical Data Model

Network Data Model
Relational Data Model

Activity 1E

 A single central database accessed by multiple

users.
 Easier to organise, edit, query and backup.
 Can be slower because of high usage / load.

Distribute

i.

 Database split into multiple files.

 Data access and retrieval faster at nearest points ii. a. Microsoft Access, FoxPro, FileMaker Pro, Paradox,
 Need to be ensure data is consistent / Lotus
synchronised
b. Oracle, Microsoft SQL Server, IBM, DB2

Activity 1D ii. End User

Application Programmer
i. a. Object based Data Models is designed using the entities Database Administrator
in the real world, attributes of each entity and their
relationship. It picks up each thing/object in the real world
Activity 2A
which is involved in the requirement.

Advantages :  Row of relation

 It makes the requirement simple and easily Tuple  Also known as row or record
understandable by representing simple diagrams.  Named column of relation
 One can covert ER diagrams into record based data Attribute  Also known as Table
model easily.  Set of possible value of
 Easy to understand ER diagrams Domain attributes
 P a g e | 85

 Number of attributes of a
Degree relation
 Number of tuples of relation
Cardinality
 Relation used to refer a table c.
in a relational database.
Relation  Also be known as
relationship

Activity 2B
d.
Attribute StaffNum, ICNum, Name, Position, Department, PhoneNum

StaffNum ICNum Name Position Department PhoneNum

IT001 78110 Abdullah Pensyarah Kejuruteraan 09-

3-03- Perisian 8840023
5333
IT002 68092 Maimunah Prof Sains 09-
3-11- Madya Komputer 8840024
4908
IT003 80120 Kamal Pensyarah Sistem 09-
4-06- Maklumat 8840025
Domain 2323
IT004 60072 Jamilah Prof Multimedia 09- e.
0-06- Madya 8840026
5380
IT005 59010 Hakim Professor Kejuruteraan 09-
1-01- Perisian 8840027
2425
IT006 81030
3-19-
Anis Pensyarah Multimedia 09-
8840028
Activity 2E
9923

Degree 6

Tuple 6

Cardinality 6

Activity 2C a.
i. Primary Key
Branch_Info table: Branch_Id
Student_Information Table: Coll_Id

ii. Candidate Key

Branch_Info table: Branch_Id, Branch_Name,
Branch_Code
Student_Information table: Stu_Id, Coll_Id, Ic_Num b.

iii. Foreign Key

Branch_Id

iv. Alternate Key

Branch_Info table: Branch_Name, Branch_Code
Student_Information table: Student_Id, Ic_Num c.

Activity 2D

d.
a.

e.
b.
 P a g e | 86

Activity 3A 5. Each maintenance record is generated by one and

only one vehicle.
6. Some vehicles have not (yet) generated a
i. maintenance procedure.

ii.

ii.

iii.

iv. Activity 3D

i.
Course Credit
v. Course Name
Code Hours
System Analysis DFC123
4
And Design 4
Activity 3B Human Computer DFP234
4
Interaction 5
i.
DFC345
Entity Attributes Database Design 4
6
LECTURER Lect_ID, Lect_FName, Lect_LName Programming DFC102
CLASS Class_ID, Class_Name, Lect_ID 4
Fundamentals 3
COURSE Course_ID, Course_Name DFC312
ROOM Room_ID, Room_Loc, Room_Cap Internet Publishing 3
3
Multimedia DFM41
3
Technology 25

ii.

Entity Attributes
Student No, Student Name,
Student Address, Programme, Academic
Advisor
Course Code, Course Name, Credit
Course
Hours
ii. Student Student No, Course Code, Grade
Grade

Activity 3C
i. iii.
Entity Attributes
1. A client can own many vehicles. Student Student No, Student
2. Some clients do not own vehicles. Name, Address,
3. A vehicle is owned by one and only one client. Programme
4. A vehicle may generate one or more maintenance Course Course Code, Course
records. Name, Credit Hours
 P a g e | 87

Student Grade Student No, Course Code,

Grade
Programme Programme, Academic
Advisor
3NF
PROJECT
Activity 3E Project_No Project_name
1 DADD
2 Programming
1NF
3 Multimedia
STAFF
PROJECT(Project_no, Project_name)
Staff_ Staff_n Charge
Job_classification Hour
no ame _hour
00689 Samat Elect Engineer RM 20 20 STAFF
00500 Ah Web Developer RM 22 18 Staff_no Staff_name Job_classification
00444 Azie Web Developer RM 22 15 00689 Samat Elect Engineer
00123 Kamal System Analyst RM 25 30 00500 Ah Web Developer
00999 Hazlim Technical Support RM 16 56 00444 Azie Web Developer
00689 Samat Elect Engineer RM 20 40 00123 Kamal System Analyst
00111 Anaz Programmer RM 18 35 00999 Hazlim Technical Support
STAFF (Project_No, Staff_no, Project_name, Staff_name, 00111 Anaz Programmer
Job_classification, Charge_hour, Hour) STAFF(Staff_no, Staff_name, Charge_hour, Job_classification)

PROJECT
HOUR
Project_No Project_name
Staff_no Project_no Hour
1 DADD 00689 1 20
2 Programming 00500 1 18
3 Multimedia 00444 1 15
PROJECT(Project_No, Project_name) 00123 2 30
00999 2 56
00689 3 40
2NF
00111 3 35
STAFF
HOUR(Staff_no, Project_no, Hour)
Charge_
Staff_no Staff_name Job_classification
hour JOB
00689 Samat Elect Engineer RM 20 Job_classification Charge_hour
00500 Ah Web Developer RM 22 Elect Engineer RM 20
00444 Azie Web Developer RM 22 Web Developer RM 22
00123 Kamal System Analyst RM 25 System Analyst RM 25
00999 Hazlim Technical Support RM 16 Technical Support RM 16
00111 Anaz Programmer RM 18 Programmer RM 18
STAFF(Staff_no, Staff_name, Job_classification, Charge_hour) JOB(Job_classification, Charge_hour)
PROJECT
Project_No Project_name
Activity 4A
1 DADD
2 Programming 1. The WHERE clause is used to filter individual
3 Multimedia rows using either a comparison to a literal, or a
PROJECT(Project_No, Project_name) comparison to another column.
The HAVING clause is used to filter groups of rows
HOUR
using an aggregate expression. Often, the same
Staff_no Project_no Hour
aggregate expression is in the SELECT list.
00689 1 20
00500 1 18 You can use WHERE and HAVING in the same SELECT
00444 1 15 statement, if necessary.
00123 2 30 When a SELECT statement is executed, the WHERE
00999 2 56 clause is executed before the GROUP BY clause, if
00689 3 40 one exists. The HAVING clause is executed after the
00111 3 35 GROUP BY clause.
HOUR(Staff_no, Project_No, Hour)

2. Sometimes a number sequence is pointless as a

number. for instance, you would never perform a
mathematical function on a phone number or social
security number, but you might want to use them as
 P a g e | 88

string types - for instance to append or remove an

area code or something.
Activity 4B
3. The ORDER BY clause’s purpose is to sort the query i.
result by specific columns.
The GROUP BY clause’s purpose is summarize
unique combinations of columns values.

4. COUNT returns the number of values without regard

to what the values are.
SUM adds the values together and can only be
applied to numeric values.
5. The DATE data type uses numeric values based on the
Julian calendar to store dates. This makes date
arithmetic such as adding and subtracting days or
fractions of days possible.

6. DELETE remove some or all the tuples from a table.

DROP can remove entire schema, table, domain, or
constraints from the database.

7. The logical AND operator (a && b) creates

logical expressions where both values must be true for
the overall expression to also be true.
The logical OR operator (a || b) is an infix operator
made from two adjacent pipe characters. You use it to
create logical expressions in which only one of the two
values has to be true for the overall expression to be
true.

8. The SELECT DISTINCT statement is used to return only

distinct (different) values. Inside a table, a column
often contains many duplicate values; and sometimes
you only want to list the different (distinct) values.

9. The DATE type is used for values with a date part but
no time part. MySQL retrieves and displays DATE
values in 'YYYY-MM-DD' format. The supported range
is '1000-01-01' to '9999-12-31'.

10. NULL - an unknown value or no value or a missing

value. Eg: When user leaves the field without entering
details. ii. CREATE DATABASE CONSULT_COMPANY;
NOT NULL - non empty value. Eg: When user fill the CREATE TABLE JOB (
field with data. JOB_CODE VARCHAR (10) NOT NULL,
Zero or White Space - It is different from NULL. JOB_DESCRIPTION VARCHAR (100),
Eg: When user gives field values as Zero or JOB_CHARGE_HOUR INT,
White Spaces. JOB_LAST_DATE DATE,
PRIMARY KEY (JOB_CODE));

CREATE TABLE EMPLOYEE (

EMP_NO VARCHAR (5) NOT NULL,
JOB_CODE VARCHAR (10),
EMP_NAME VARCHAR (100),
EMP_HIRE_DATE DATE,
EMP_YEARS INT,
PRIMARY KEY (EMP_NO),
 P a g e | 89

FOREIGN KEY (JOB_CODE) REFERENCES JOB staffNo varchar (10) NOT NULL,
(JOB_CODE)); fName varchar (100),
lName varchar (100),
CREATE TABLE PROJECT ( position varchar (100),
PROJ_NO VARCHAR (10) NOT NULL, gender varchar (10),
EMP_NO VARCHAR (5), DOB date,
salary int,
PROJ_NAME VARCHAR (100),
Foreign Key (branchNo) REFERENCES Branch (branchNo),
PROJ_VALUE INT,
Primary Key (staffNo));
PROJ_BALANCE INT,
PRIMARY KEY (PROJ_NO), Create table Client (
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE clientNo varchar (10) NOT NULL,
(EMP_NO)); fName varchar (100),
lName varchar (100),
CREATE TABLE ASSIGNMENT ( telNo int,
ASSIGN_NO VARCHAR (8) NOT NULL, prefType varchar (100),
PROJ_NO VARCHAR (10), maxRent int,
EMP_NO VARCHAR (5), email varchar (100),
ASSIGN_DATE DATE, Primary Key (clientNo));
ASSIGN_CHARGE_HOUR INT,
Create table PrivateOwner (
PRIMARY KEY (ASSIGN_NO),
ownerNo varchar (10) NOT NULL,
FOREIGN KEY (PROJ_NO) REFERENCES PROJECT
fName varchar (100),
(PROJ_NO), lName varchar (100),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE address varchar (100),
(EMP_NO)); telNo int,
email varchar (100),
INSERT INTO JOB VALUES (‘J10001’,’SENIOR password varchar (100),
EXECUTIVE’, 3,’ 2018-10-07’); Primary Key (ownerNo));
INSERT INTO EMPLOYEE VALUES (‘E1001’,
‘AZKAYRA BINTI EL AZMAN’,’ 2019-02-08,’J1001’, Create table PropertyForRent (
5); propertyNo varchar (10) NOT NULL,
INSERT INTO PROJECT VALUES (‘P10016’, ‘SIME ownerNo varchar (10),
staffNo varchar (10),
DARBY LISTING PROPERTY’, 20000, 25000, ‘E1001’);
branchNo varchar (10),
INSERT INTO ASSIGNMENT VALUES (‘A722912’,
type varchar (50),
‘2019-05-16, ‘P10016’, ‘E1001’, 3); rooms varchar (50),
rent int,
Activity 4C
Foreign Key (ownerNo) REFERENCES PrivateOwner
(ownerNo),
i.
Foreign Key (staffNo) REFERENCES Staff (staffNo),
a. Branch (branchNo, street, city, postcode); Foreign Key (branchNo) REFERENCES Branch (branchNo),
b. Staff (staffNo, fName, lName, position, gender, Primary Key (propertyNo));
DOB, salary, branchNo);
c. PropertyForRent (propertyNo, type, rooms, rent, Create table Viewing (
ownerNo, staffNo, branchNo); clientNo varchar (10),
d. Client (clientNo, fName, lName, telNo, prefType, propertyNo varchar (10),
maxRent, email); viewDate Date,
e. PrivateOwner (ownerNo, fName, lName, address, comments varchar (150),
telNo, email, password); Foreign Key (clientNo) REFERENCES Client (clientNo),
f. Viewing (clientNo, propertyNo, viewDate,
comments); Foreign Key (propertyNo) REFERENCES PropertyForRent
(propertyNo));
ii.
Create table Branch (
branchNo varchar (10) NOT NULL, Activity 4D
street varchar (100),
city varchar (100), i. Create table City (
postcode int, CityId int NOT NULL,
Primary Key (branchNo));
cityName varchar (50),
Primary Key (CityId));
Create table Staff (
 P a g e | 90

Create table Occupation ( FROM User U, Occupation O, City C

OccupationId int NOT NULL, WHERE U.occupationId = O.occupationId
occupationName varchar (50), AND U.cityId = C.cityId
Primary Key (OccupationId)); AND Name IN ('Sara', ‘Victor’);

Create table User (

Id int NOT NULL,
Name varchar (50),
Age int,
Gender varchar(20),
OccupationId int,
CityId int,
Foreign Key (OccupationId) REFERENCES Occupation
(OccupationId),
Activity 4E
Foreign Key (CityId) REFERENCES City (CityId),
STEP 1:
Primary Key (Id));

ii.
INSERT INTO City VALUES (1,'Halifax');
INSERT INTO City VALUES (2,'Calgary');
INSERT INTO City VALUES (3,'Boston');
INSERT INTO City VALUES (4,'New York');
INSERT INTO City VALUES (5,'Toronto');

INSERT INTO Occupation VALUES (1,'Software Engineer');

INSERT INTO Occupation VALUES (2,'Accountant');
INSERT INTO Occupation VALUES (3,'Pharmacist');
INSERT INTO Occupation VALUES (4,'Library Assistant');

INSERT INTO User VALUES (1,’John’, 25,'Male', 1, 3);

INSERT INTO User VALUES (2,'Sara', 20,'Female', 3, 4);
INSERT INTO User VALUES (3,'Victor', 31,'Male', 2, 5);
INSERT INTO User VALUES (4,'Jane', 27,'Male', 1, 3);

iii.
SELECT Gender, COUNT (Id)
FROM User
Group by Gender;

iv. SELECT U.Name

FROM User U, Occupation O
WHERE O.occupationName ='Software Engineer'
AND U.Age BETWEEN 25 AND 30;

v. SELECT Name, Age, Gender,

O.occupationName, C.cityName
 P a g e | 91

STEP 2:

STEP 4 :

STEP 5 :

STEP 6 :

STEP 7 :

STEP 3 :
 P a g e | 92

STEP 8 : select balance from Account where

Account_Number='2002';
update Account set balance = balance - 900 where
Account_Number='1001';
update Account set balance = balance + 900 where
Account_Number='2002';
commit; //if all sql queries succeed

Activity 5C
Step 1:
STEP 9 :

Step 2 :

Step 3 :

STEP 10 :

Step 4 :

Step 5 :
STEP 11 :

Step 6 :
Activity 5A
a. If the transaction fails after step 4 but before step 8,
the updates on A should not be reflected in the Step 7 :
database (rollback).
b. The sum of A and B should not be changed by the
transaction. Step 8 :
c. If another transaction is going to access the partially
updated database between step 4 and 7, it will see an
inconsistent database (with a sum of A and B is less
than it should be).
d. Once the money has been transferred from A to B
(commit), the effect of the transaction must persist.

Activity 5B
start transaction
select balance from Account where
Account_Number='1001';
 P a g e | 93

Step 9 : database concurrency control and integrity. The

information stored in the log is used by the DBMS to
recover the database after a transaction is aborted
or after a system failure. The transaction log is
Step 10 : usually stored in a different hard disk or in a
different media (tape) to prevent the failure caused
by a media error.
5. The scheduler is the DBMS component that
establishes the order in which concurrent
database operations are executed. The scheduler
interleaves the execution of the database
operations (belonging to several concurrent
transactions) to ensure the serializability of
transactions. In other words, the scheduler
Step 11 : guarantees that the execution of concurrent
transactions will yield the same result as though
the transactions were executed one after another.
The scheduler is important because it is the DBMS
component that will ensure transaction
serializability. In other words, the scheduler allows
Activity 5D the concurrent execution of transactions, giving
end users the impression that they are the
1. A transaction is a logical unit of work that must DBMS's only users.
be entirely completed of aborted; no 6. A lock is a mechanism used in concurrency control
intermediate states are accepted. In other words, to guarantee the exclusive use of a data element to
a transaction, composed of several database the transaction that owns the lock. For example, if
requests, is treated by the DBMS as a unit of work the data element X is currently locked by
in which all transaction steps must be fully transaction T1, transaction T2 will not have access
completed if the transaction is to be accepted by to the data element X until T1 releases its lock.
the DBMS. Generally speaking, a data item can be in only two
2. A consistent database state is one in which all states: locked (being used by some transaction) or
data integrity constraints are satisfied. To achieve unlocked (not in use by any transaction). To access
a consistent database state, a transaction must a data element X, a transaction T1 first must
take the database from one consistent state to request a lock to the DBMS. If the data element is
another. not in use, the DBMS will lock X to be used by T1
3. Atomicity exclusively. No other transaction will have access to
requires that all parts of a transaction must be
X while T1 is executed.
completed or the transaction is aborted. This
property ensures that the database will remain 7. Concurrency control is the activity of coordinating
in a consistent state. the simultaneous execution of transactions in a
Consistency multiprocessing or multi-user database
Indicates the permanence of the database management system. The objective of concurrency
consistent state control is to ensure the serializability of
Isolation transactions in a multi-user database
means that the data required by an executing management system. (The DBMS's scheduler is in
transaction cannot be charge of maintaining concurrency control.)Because
accessed by any other transaction until the first it helps to guarantee data integrity and
transaction finishes. This property ensures data consistency in a database system, concurrency
consistency for concurrently executing transactions. control is one of the most critical activities
Durability performed by a DBMS. If concurrency control is
indicates that the database will be in a permanent not maintained, three serious problems may be
consistent state after the execution of a caused by concurrent transaction execution: lost
transaction. In other words, once a consistent updates, uncommitted data, and inconsistent
state is reached, it cannot be lost. retrievals.
4. The transaction log is a special DBMS table that 8. Binary Locks − A lock on a data item can be in two
contains a description of all the database states; it is either locked or unlocked.
transactions executed by the DBMS. The database Shared/exclusive − this type of locking mechanism
transaction log plays a crucial role in maintaining differentiates the locks based on their uses. If a lock
 P a g e | 94

is acquired on a data item to perform a write simultaneously and T1 is rolled back after the T2 has
operation, it is an exclusive lock. Allowing more than already accessed the uncommitted data. Refers to
one transaction to write on the same data item figure above, the original units are 35 then it have
an addition of 100 units. Now, the total units
would lead the database into an inconsistent state.
become 135. For any possibilities, it can occurs an
Read locks are shared because no data value is being error due to any transaction when T1 does not
changed. commit then T2 has read the uncommitted data.
9. Start by pointing out that, although locks prevent Now, T2 has made a new transaction which
serious data inconsistencies, their use may lead to subtracts 30 units from the current value which is
two major problems:1.The transaction schedule 135 units. Suddenly, T1 has rolled back then the
dictated by the locking requirements may not be value now become 35 units back. The correct
execution happens when T1 has rolled back then he
serializable, thus causing data integrity and
or she made a commit, the stored value will hold 35
consistency problems.2.The schedule may create units then subtracts the 30 units, the final result will
deadlocks. Database deadlocks are the equivalent be supposed to 5 units only.
of a traffic gridlock in a big city and are caused by iii. Inconsistent retrievals occur when transaction
two transactions waiting for each other to unlock accesses data before and after another transaction
data. finish working with such data. As shown in Figure 5E
In a real world DBMS, many more transactions can (iii), the original value of a, b and c are 100, 200 and
300. T1 had withdrawn the value of 100 and now the
be executed simultaneously, thereby increasing the
balance become 0. Then, T1 has made an additional
probability of generating deadlocks. Note that of 100 as deposit which the current value is 300 for
deadlocks are possible only if one of the now. At the same time, T2 reads the current value
transactions wants to obtain an exclusive lock on a as 0 after the T1 has made a subtraction. Then, T2
data item; no deadlock condition can exist among reads total of b is still 200 as the original value and
shared locks. not the value of transaction that have been made by
T1. So, it can occurs inconsistent reading of balance
10.
in this account because T1 reads the value of a b and
Log File c are 0, 300 and 300 but then T2 reads another
The file contains information about all changes balance amount as 0, 200 and 500
made to the database. It provides a history of
database changes made by the transactions.
Checkpoint -END-
The checkpoint is the time when all transactions
stop. At this point a checkpoint record is written
to the log and database buffers are written to
disk. Checkpoints are written at periodic
intervals and involve the following operations:
 Writing all log records in main memory to
secondary storage
 Writing all modified blocks in database
buffers to secondary storage
 Writing a checkpoint record to the log file

Activity 5E

i. Lost updates occur when two transactions which

made by Alice and Bob, they want to update the
quantity of product which is 7. Note that the first
transaction (Alice) has not yet been committed
when she update the product quantity from 7 to 6
while the second transaction (Bob) is also executed.
Bob had made quantity of product become 10. At
the same time Alice has done the commit which
quantity of product still hold value of 6,then Bob
also committed the transaction that promptly
overwrite the value for quantity of product which
supposed to hold the value of 6 become 10.
ii. Uncommitted data problem can arise when two
transactions (T1 and T2) are executed
P a g e | 95
A book related to DATABASE DESIGN basic knowledge, which has FIVE (5)
chapters. Each chapter have tutorial questions to enhance the
understanding of the chapter. It is suitable for all ages to learn Database
Design by using this book. This e-book also uses easy language to
understand and have examples of how to solve questions related to
databases.

AUTHORS

NORSHADILA BINTI AHMAD BADELA

Pensyarah
Jabatan Teknologi Maklumat & Komunikasi
Politeknik Mersing, Johor

MAS AYU BINTI MOHD ARIFF

Pensyarah
Jabatan Teknologi Maklumat & Komunikasi
Politeknik Mersing, Johor

PUZIAHHAIZA BINTI PAZUI

Pensyarah
Jabatan Teknologi Maklumat & Komunikasi
Politeknik Mersing, Johor

POLITEKNIK MERSING
Jalan Nitar,
86800 Mersing,
Johor

07-7980001 07-7980002
https://pmj.mypolycc.edu.my