ICT (Elective) – 2A Databases P.

Elective A – Ch.1-5 Advanced Databases

Section 1: Database Definition

- Some Database Management System (DBMS) provides GUI, while some do not.
- Structural Query Language (SQL) can be used to define a relational database in command line interface.
▪ Create database and table
Function Syntax Example
Create CREATE DATABASE <name> CREATE DATABASE MyDB;
database#
Create CREATE TABLE <table> CREATE TABLE stdDB
table ( <field> <type>, ( sid INTEGER,
…, hkid CHAR(10),
<field> <type> ) name VARCHAR(50),
sex CHAR(1),
dob DATE,
grad BOOLEAN)
CREATE TABLE stdDB
( sid INTEGER PRIMARY KEY,
hkid CHAR(10) UNIQUE,
name VARCHAR(50) NOT NULL,
sex CHAR(1) CHECK (sex in ('M','F')),
dob DATE,
grad BOOLEAN DEFAULT FALSE)
CREATE TABLE bookRecord
( sid INTEGER NOT NULL,
bkid INTEGER NOT NULL,
PRIMARY KEY (sid, bkid),
FOREIGN KEY (bkid) REFERENCES bookDB (bkid),
FOREIGN KEY (sid) REFERENCES stdDB (sid))
# Not supported by Microsoft Access

▪ Constraints
Keyword Constraints
UNIQUE No repeated data is allowed in the field
NOT NULL The field cannot be empty
DEFAULT Adding default value to the field
• Will be applied if no value specified when inserting record
CHECK Adding validation check to the field
• Using scope operator such as BETWEEN … AND, IN, LIKE, >, <, …

Exercise
1. Write an SQL statement to create a table called Teacher with two columns named Name and Age respectively.
ICT (Elective) – 2A Databases P. 2

2. Create a table called Book that contains fields named BookID, Title and Type such that a value for BookID
must be entered for each row and its value is unique within the table.

3. Create a table called “Student” with the primary key “StdID”.

4. Create a table “LoanRecord” with a primary key “LoanRecID” and two foreign keys “StdID” and “BookID”
that references tables “Student” and “Book” respectively.
ICT (Elective) – 2A Databases P. 3

▪ Altering the table structure

Function Syntax Example
Update ALTER TABLE <table> ALTER TABLE stdDB
table ADD COLUMN <field> <type> ADD COLUMN temp INTEGER;
design ALTER TABLE <table> ALTER TABLE stdDB
ALTER COLUMN <field> <type> ALTER COLUMN temp CHAR(10);
ALTER TABLE <table> ALTER TABLE stdDB
DROP COLUMN <field> DROP COLUMN temp;
ALTER TABLE <table> ALTER TABLE stdDB
ADD PRIMARY KEY <field> ADD PRIMARY KEY sid;
Remove DROP TABLE <table> DROP TABLE bookRecord;
table
Remove DROP DATABASE <name> # DROP DATABASE MyDB;
database

Exercise
5. A table Student has the following structure:
Student
Field Name Type Size Contents
Sid Character 4 Student ID
Name Character 50 Name of the student
Class Character 20 Class of the student
Age Integer -- Student age

(a) Add a column named PhoneNo with 8 digits.

(b) Remove the column Age.

(c) Change the data type Class to character of size 2.

(d) Set the field Name to a required field.

(e) Set the field Sid as the primary key.

ICT (Elective) – 2A Databases P. 4

- View
▪ It is a virtual table that links to part of the data in the database, not necessarily in the same table.
▪ We can perform calculation and query with the views.
▪ Views are read only, so insert, delete and update to a view are not allowed.
▪ It is in the form of a saved query in Microsoft Access.

Function Syntax Example

Define CREATE VIEW <ViewName> CREATE VIEW MyStdDB AS
view AS <SQL query> SELECT sid, name
FROM stdDB
WHERE grad
Remove DROP VIEW <ViewName> DROP VIEW MyStdDB
view

Advantage:

Disadvantage:

- Index
▪ It is an extra table added in the database, sorted in a frequently used field.
▪ When running query on the table, instead of sequential search, binary search can be done on index table.

Advantage:

Disadvantage:

Function Syntax Example

Define index CREATE INDEX <IndexName> CREATE INDEX MyName
ON <table> (<field> [order]) ON stdDB (name DESC)
Remove index DROP INDEX <IndexName> DROP INDEX MyName
ON <table> ON stdDB
ICT (Elective) – 2A Databases P. 5

Exercise
6. An organisation manages a professional examination which includes several subjects. Candidates enroll in the
examination through training centres. Each candidate enrolls in at least one subject. Markers can mark one
subject only. The organisation uses the following database tables to store the information on the examination.
CAND
Field name Type Description
CNO Integer Identity number of the candidate
CNAME Character Candidate name
SEX Boolean Sex
CDOB Date Date of birth
TNO Integer Identity number of the training centre
SBJCODE Integer Identity number of the subject that the candidate enrolls in
SBJNAME Character Name of the subject that the candidate enrolls in

(a) Suppose that only one database table will be indexed.

(ii) Write a SQL command to create an index file named IND with the primary key of CAND.

(2 marks)
(iii) A simple SQL command has the three parts, SELECT, FROM and WHERE. Which part will the index file
facilitate? Explain briefly.

(1 mark)
[2014-DSE-ICT 2A-Q2]
ICT (Elective) – 2A Databases P. 6

Section 2: Data Manipulation

- SQL can be used to manage data from the database, including data insertion, update and deletion.

Function Syntax Example

Insert a INSERT INTO <table> INSERT INTO bookRecord
record VALUES (<val>, …, <val>) VALUES (981234, 8891041);
INSERT INTO <table> INSERT INTO stdDB (sid, hkid)
(<field>, …, <field>) VALUES (981234, 'A123456(7)');
VALUES (<val>, …, <val>)
INSERT INTO <table> INSERT INTO lateRn (sid, bkid)
(<field>, …, <field>) SELECT sid, bkid
<SQL query> FROM bookRecord
WHERE dueDate < date();
Delete a DELETE FROM <table> DELETE FROM stdDB
record WHERE <condition> WHERE sid=981234;
Update a UPDATE <table> UPDATE stdDB
record SET <field>=<value> SET form=form+1
WHERE <condition> WHERE form < 6
Query SELECT <field>, …, <field> SELECT class, count(*) AS cnt
FROM <table>,…, <table> FROM stdDB
WHERE <condition> WHERE dob<#1990/1/1#
GROUP BY <field> GROUP BY class
HAVING <condition> HAVING count(*)>30
ORDER BY <field>, <field> ORDER BY class DESC;

Exercise
7. A company is using a table employees to store the information of its employees. Below are some data stored.
Employees
employee_id first_name last_name hire_date salary
001 John Chan 2023-07-15 60000
002 Jane Mak 2023-07-20 55000
003 Michael Leung 2023-07-25 65000

Write SQL statements to complete the following tasks.

(a) A new staff member, Chris Wong, is just employed on 1 Aug 2023, with the employee ID 004 and a monthly
salary of $58000.

(b) The company is making a salary adjustment, and all the employees will have an increment of $500.

(c) Jane Make is leaving the company.

ICT (Elective) – 2A Databases P. 7

Section 3: Database Query

- Query with Functions
▪ Numeric functions
Function Meaning Example Output
ABS Absolute value select ABS(345) 345
select ABS(-345) 345
INT Integral value select INT(-12.34) -12
(rounding down to the nearest select INT(98.76) 98
integer)
select INT(-98.76) -99

▪ String functions
Function Meaning Example Output
LEN String length select LEN('Munsang') 7
LOWER Lower case select LOWER('Munsang') Munsang
UPPER Upper case select UPPER('Munsang') MUNSANG
LEFT Substring from the left select LEFT('Munsang',3) Mun
RIGHT Substring from the right select RIGHT('Munsang',3) ang
MID Substring from the middle select MID('Munsang',3,3) Nsa
INSTR Check the starting position of the select INSTR('Munsang','n') 3
search string in a given string select INSTR('Munsang','xx') 0
(with an optional start searching
select INSTR(4,'Munsang','n') 6
location)
TRIM Remove leading and trailing spaces select TRIM(' IM SC ') IM SC
SPACE Return a string with the number of select SPACE(10)
spaces specified in the arguement
(10 spaces)
CHR Return the character with the given select CHR(38) &
ASCII code
VALUE Convert numerical characters into a select VAL('2457') 2457
number select VAL(' 2 45 7') 2457
(Blanks and tabs are skipped)
select VAL('24 and 57') 24

▪ Date functions
Function Meaning Example Output
DATE Create a date select DATE() (The date today)
DAY Return the day of a given date select DAY('2024/9/1') 1
MONTH Return the month of a given date select MONTH('2024/9/1') 9
YEAR Return the year of a given date select YEAR('2024/9/1') 2024

Reference: Access Functions (by category)

https://support.microsoft.com/en-us/office/access-functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83
ICT (Elective) – 2A Databases P. 8

- Take the following database as an example:

Table: student
field type width contents
id Number Integer Student identification number
name Text 10 Name
dob Date -- Date of birth
sex Text 1 Sex: (M = Male; F = Female)
class Text 2 Class
hcode Text 1 House of the student. (R = Red; Y = Yellow; B = Blue; G = Green)
dcode Text 3 District code (e.g. TST = Tsim Sha Tsui; SSP = Shum Shui Po)
remission Boolean -- Whether the student is receiving fee remission (學費減免)
mtest Number Integer Math test score

▪ Examples:
 List the record of the student whose birthday is in January.
SELECT class, name, dob FROM student
WHERE MONTH(dob)=1

class name dob

Result 1A Mary 1986/01/10
1B Kitty 1987/01/25
1A Luke 1986/01/01
1B Eddy 1986/01/15
1B Teddy 1986/01/30

 List the names and ages (1 d.p.) of 1B girls.

SELECT name, ROUND((DATE()-dob)/365,1) AS age FROM student
WHERE class="1B" AND sex="F"

name age
Result Wendy 12.1
Kitty 11.5
Janet 12.4
Sandy 12.3
Mimi 12.2

 Find out the number of students in each form.

SELECT left(class,1) as form, count(*) as cnt FROM student
GROUP BY left(class,1)

form cnt
Result 1 28
2 22
ICT (Elective) – 2A Databases P. 9

- Query with Multiple Tables

Data can be stored on multiple tables, so we need to retrieve data from two or more tables.

Combine
Tables

Join Set
(Combine by (Combine by
columns) rows)

Cross Join Inner Join Outer Join Union Intersect Minus

Equi-join Non-equi join Left Outer Right Outer

Full Outer Join
(=) (>,<,>=,<=,<>) Join Join

Natural Join

Take the following tables as an example:

Table: student Table: music
Field Type Description Field Type Description
id Numeric student id number id Numeric student id number
sname Character name of student name Character music instrument
dob Date date of birth
sex Character sex: M/F
class Character class
hcode Character house code: R/Y/B/G
dcode Character district code
remission Boolean fee remission

Sample data:
id sname dob sex class ClassNo hcode dcode remission id type
9801 Peter 2012/6/14 M 1A 1 R SSP FALSE 9802 Flute
9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE 9803 Violin
9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE 9804 Piano
9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE 9806 Recorder
ICT (Elective) – 2A Databases P. 10

▪ Cross Join
Example:
SELECT student.name, student.id, music.id, music.type
FROM student, music

student.name student.id music.id music.type

Peter 9801 9802 Flute
Peter 9801 9803 Violin
Peter 9801 9804 Piano
Peter 9801 9806 Recorder
Mary 9802 9802 Flute
Mary 9802 9803 Violin
Mary 9802 9804 Piano
Mary 9802 9806 Recorder
Johnny 9803 9802 Flute
Johnny 9803 9803 Violin
Johnny 9803 9804 Piano
Johnny 9803 9806 Recorder
Wendy 9804 9802 Flute
Wendy 9804 9803 Violin
Wendy 9804 9804 Piano
Wendy 9804 9806 Recorder

▪ Inner Join
 Equi-join
Example:
SELECT *
FROM student, music
WHERE student.id = music.id

or
SELECT *
FROM student INNER JOIN music
ON student.id = music.id

student.id sname dob sex class ClassNo hcode dcode remission music.id type
9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE 9802 Flute
9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE 9803 Violin
9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE 9804 Piano

 Natural Join
Natural join is similar to equi-join, but the common field is appeared once only in the result.
Example:
SELECT *
FROM Student NATURAL JOIN Club
# Not supported in Microsoft Access

id sname dob sex class ClassNo hcode dcode remission type

9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE Flute
9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE Violin
9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE Piano
ICT (Elective) – 2A Databases P. 11

▪ Outer Join
 Left Outer Join
◼ All the records on the left table will be kept, even if there is no match from the right table.
Example:
SELECT student.name, student.id, music.id, music.type
FROM student LEFT OUTER JOIN music
ON student.id = music.id

student.id sname dob sex class ClassNo hcode dcode remission music.id type
9801 Peter 2012/6/14 M 1A 1 R SSP FALSE (null) (null)
9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE 9802 Flute
9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE 9803 Violin
9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE 9804 Piano

 Right Outer Join

◼ All the records on the right table will be kept, even if there is no match from the left table.
Example:
SELECT student.name, student.id, music.id, music.type
FROM student RIGHT OUTER JOIN music
ON student.id = music.id

student.id sname dob sex class ClassNo hcode dcode remission music.id type
9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE 9802 Flute
9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE 9803 Violin
9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE 9804 Piano
(null) (null) (null) (null) (null) (null) (null) (null) (null) 9806 Recorder

 Full Outer Join

◼ All the records on both left and right table will be kept, even if there is no match from the other
table.
Example:
SELECT student.name, student.id, music.id, music.type
FROM student OUTER JOIN music
ON student.id = music.id

or
SELECT student.name, student.id, music.id, music.type
FROM student FULL OUTER JOIN music
ON student.id = music.id

student.id sname dob sex class ClassNo hcode dcode remission music.id type
9801 Peter 2012/6/14 M 1A 1 R SSP FALSE (null) (null)
9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE 9802 Flute
9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE 9803 Violin
9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE 9804 Piano
(null) (null) (null) (null) (null) (null) (null) (null) (null) 9806 Recorder
ICT (Elective) – 2A Databases P. 12

▪ Set operations
 Union
◼ Records will be in the combined result set if it is in either set.
Example:
SELECT id FROM student
UNION
SELECT id FROM music

id
9801
9802
9803
9804
9806

 Intersect
◼ Records will be in the combined result set if it is in both sets.
Example:
SELECT id FROM student
INTERSECT
SELECT id FROM music
# Not supported in Microsoft Access
id
9802
9803
9804

 Minus
◼ Records will be in the combined result set if it is in the first set but not in the second set.
Example:
SELECT id FROM student
MINUS
SELECT id FROM music
# Not supported in Microsoft Access
id
9801

Example:
SELECT id FROM music
MINUS
SELECT id FROM student

id
9806
ICT (Elective) – 2A Databases P. 13

- Subquery
Another common type of query is by nested queries with multiple SELECT statements. It can be used to solve multi-
table questions.
▪ Examples:
 List the record of the oldest student.
SELECT * FROM student WHERE dob =
(SELECT min(dob) FROM student)

or

SELECT * FROM student WHERE dob <= ALL

(SELECT dob FROM student)

id sname dob sex class ClassNo hcode dcode remission

Result 9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE

 List the record of student who is learning violin.

SELECT * FROM student WHERE id IN
(SELECT id FROM music WHERE type = ' Violin')

or

SELECT * FROM student WHERE id = ANY

(SELECT id FROM music WHERE type = ' Violin')

id sname dob sex class ClassNo hcode dcode remission

Result 9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE

 List the record of student who did not learn any musical instrument.
SELECT * FROM student WHERE id NOT IN
(SELECT id FROM music)

Or

SELECT * FROM student LEFT OUTER JOIN music

ON student.id = music.id
WHERE music.id is null

id sname dob sex class ClassNo hcode dcode remission

Result 9801 Peter 2012/6/14 M 1A 1 R SSP FALSE
ICT (Elective) – 2A Databases P. 14

 List the students who do not learn any musical instrument.

SELECT * FROM student WHERE id NOT IN
(SELECT id FROM music)

or

SELECT * FROM student WHERE NOT EXISTS

(SELECT * FROM music WHERE student.id = music.id)

id sname dob sex class ClassNo hcode dcode remission id type EXISTS?
9801 Peter 2012/6/14 M 1A 1 R SSP FALSE 9802 Flute 
9803 Violin 
NO
9804 Piano 
9806 Recorder 

9802 Mary 2012/1/10 F 1A 2 Y HHM TRUE 9802 Flute 

9803 Violin 
YES
9804 Piano 
9806 Recorder 

9803 Johnny 2012/3/16 M 1A 3 G SSP FALSE 9802 Flute 

9803 Violin 
YES
9804 Piano 
9806 Recorder 

9804 Wendy 2012/7/9 F 1B 1 B YMT FALSE 9802 Flute 

9803 Violin 
YES
9804 Piano 
9806 Recorder 

id sname dob sex class ClassNo hcode dcode remission

Result
9801 Peter 2012/6/14 M 1A 1 R SSP FALSE

 List the youngest student in each class.

SELECT class, sname, dob FROM student x WHERE dob =
(SELECT max(dob) FROM student y WHERE x.class = y.class)

student x student y
class sname dob class sname dob max(dob)
1A Peter 2012/6/14  1A Peter 2012/6/14
1A Mary 2012/1/10 2012/6/14 
1A Johnny 2012/3/16

1A Mary 2012/1/10  1A Peter 2012/6/14

1A Mary 2012/1/10 2012/6/14 
1A Johnny 2012/3/16

1A Johnny 2012/3/16  1A Peter 2012/6/14

1A Mary 2012/1/10 2012/6/14 
1A Johnny 2012/3/16

1B Wendy 2012/7/9  1B Wendy 2012/7/9 2012/7/9 

class sname dob

Result 1A Peter 2012/6/14
1B Wendy 2012/7/9
ICT (Elective) – 2A Databases P. 15

Section 4: Relational Database

- Relational Database Terminology
Terms Meanings
Entity ⚫ An entity is a unique and identifiable object.
⚫ Entities can be actual physical objects, such as a person, an item, or a place, or they can
be abstract, such as a bank account, a transaction, or an event.
Tuple ⚫ A tuple describes an entity in a database and is usually represented as a row in a table.
Attribute ⚫ The different characteristics of an entity are called attributes. An entity can be represented
by a set of attributes, typically shown as columns in a table.
⚫ The number of attributes is referred to as the degree of the relation.
Domain ⚫ A domain is the set of all possible values for an attribute.
⚫ The domain can be restricted by data types (e.g., text, date) or by additional validation
rules (e.g., validation rules, input masks).
Relationship ⚫ A relationship is a connection between tables.
⚫ Common relationships are expressed by sharing values of certain attributes, indicating the
association between a tuple in one data table and one or more tuples in another table.

Entity

Tuple

Field (Attribute)

Key
StudentID EnglishName Sex Class ClassNumber Class Teacher Room
(Relationship)
015034 Peter Chan M 1D 1 1A Mr Leung 101
015035 Jenny Hung F 1A 16 1B Ms Wong 102
015036 Jimmy Hung M 1C 15 1C Mr Yau 103
015037 Jason Lim M 1D 23 Record 1D Ms Lam 104
015038 Betty Lam F 1C 22 (Tuple)
ICT (Elective) – 2A Databases P. 16

More to Learn: Derived Attribute

Some attributes can be derived from other attributes. Derived attributes can be calculated by the original attribute.
Attribute: Date of birth Class Weight and Height Score
Derived attribute: Age Form BMI Rank

Inconsistent updating and deleting may cause data error and therefore it is not recommended.

Terms Meanings
Candidate Key ⚫ Since every tuple in a relational database is unique, we can identify certain attributes to
distinguish different tuples in a table. These attributes are called candidate keys.
⚫ The values of a candidate key must be unique and cannot be null.
⚫ A candidate key can consist of a single attribute or a combination of multiple attributes.
Such candidate keys are called composite keys.
Primary Key ⚫ A candidate key selected as the identifier in a table is called a primary key.
⚫ Each table can have multiple candidate keys, but only one primary key.
Foreign Key ⚫ A foreign key is an attribute in one table that is also the primary key of another table.
⚫ E.g. In a student academic record, the course number of the courses taken is stored, and
the course number is the primary key of another table. Foreign keys are typically used to
represent relationships between tables.
Schema ⚫ A relational schema consists of the relation name, attribute names, and primary key.
⚫ E.g. Employee (empID, name, department, supervisor)
or
Employee empID name department supervisor
ICT (Elective) – 2A Databases P. 17

Section 5: Database Design

- Entity Relationship (ER) Model is used in data abstraction, representing the data structure of the database.
- ER Diagram is a tool to visualize ER Model.
▪ ER Diagram is the structural format of the database.
▪ ER diagram helps the conversion of the logical model into database design.
▪ ER diagram provides the purpose of real-world modelling of objects which makes them intently useful.
Term Symbol Meaning
Entity ⚫ An entity is a unique and identifiable object.
Entity ⚫ Entities can be actual physical objects, such as a person, an item,
or a place, or they can be abstract, such as a bank account, a
transaction, or an event.
⚫ An entity consists of multiple attributes.
⚫ Entities may be related to one another.
Attribute ⚫ An attribute is different characteristics of an entity.
Attribute ⚫ An attribute is data stored in the database.
⚫ The entity-relationship model should only include the attributes
needed in the database.
Relation ⚫ A relationship is a connection between entities.
Relation ⚫ A relationship can also contain attributes.
⚫ It can be a binary relationship, or it may be a unary relationship or
a n-ary relationship.
⚫ Having different cardinality
◆ One-to-one
1 1

◆ One-to-many
1 M

◆ Many-to-many
M M

⚫ Having different modalities

◆ Mandatory

◆ Optional

⚫
ICT (Elective) – 2A Databases P. 18

- Relationship
▪ A relationship is used to express the connection between entities.
▪ In an ER diagram, we sometimes omit attributes due to spatial constraints.

Exercise
8. Based on the following situational description, fill out the entity-relationship diagram (ER diagram).

E.g. Each student has a student ID, and

each ID belongs to only one student. 1 1 Student
Student has card

(a) Each class consists of at least one

student. Each student is assigned to
only one class. Student belongs class

(b) In the athletics meet, each event must

have at least one participant. Each
student can sign up for certain events.

(c) Students can borrow multiple books

from the library, but each book can
only be borrowed by one student.

(d) Students must enroll in at least one

course. Each course must also have at
least one student enrolled.

(e) Students can order at most one meal

box, and each meal box can only be
sold to one student.

(f) Students can serve as committee

members in multiple clubs, but each
committee position can only be held by
one student.

(g) Students can apply for multiple

scholarships. Each scholarship can
accept applications from multiple
students.
ICT (Elective) – 2A Databases P. 19

More to Learn: Relationships

Some special examples of relationships are shown below:
▪ Unary relationship
 A relation involves one entity only

1 1

Person Marry Employee Supervise

1 M

▪ Multiple relationships
 More than a single relationship is present between the same pair of entities.

award
1 M

Student Scholarship

M M
apply

- Attributes
▪ In an entity-relationship diagram, we use ovals to represent attributes, connected to the entity by a straight line.
▪ Since the foreign key is already represented by the relationships between entities, there is no need to draw it
again in the ER diagram.
▪ Note the following points when drawing ER diagram:
 Attributes must be unique.
 Each entity must include a primary key. The attribute name of the primary key is underlined.
 Foreign keys do not need to be drawn.
 Derived data (such as levels calculated from classes) do not need to be drawn.
 If there are multivalued attributes, they should be represented by double ovals.
ICT (Elective) – 2A Databases P. 20

▪ Example:
In a school, each teacher teaches at least one class. Each class is taught by one or more teachers. Each teacher
can have multiple qualifications. On the other hand, each class must have one form teacher.

Name M M
teach
ClassID

TeacherID Teacher Class

serve as
form teacher
Qualification 1 1

Exercise
9. Based on the following situational description, fill out the entity-relationship diagram (ER diagram) (including
attributes and cardinality).

A school collects and stores student information, including student ID, name, address, and date of birth.
Students can be assigned one or more positions, such as prefect, monitor, chairperson, etc.
ICT (Elective) – 2A Databases P. 21

Section 6: Normalization
- Data Inconsistency
▪ Data anomaly
 Poorly designed database with data redundancy may lead to data inconsistency.
Type Description
Insertion anomaly Inserting a new record with a foreign key not yet exist
Deletion anomaly Deleting a record which is a foreign key of other records
Update anomaly Changes are updated on part but not all of the affected records.

▪ Data integrity
 We should consider the following database design factors to minimize data inconsistency.
Type Description
Entity integrity The primary key of each record should be unique and not null.
Domain integrity Data types and constraints of the fields is valid.
E.g. Mark should be in a range of 0 to 100; Name should be text; Only M or F
is accepted in gender.
Referential integrity Foreign key is present so that the relationship between tables is valid.

Exercise
10. A charity organization recruits students to organize social service groups in each district. A student may have a leader
in each district.
Field name Description Data type Example
SID Identity code of student String 012345
RDATE Date of registration Date 1/10/2020
SNO Number of services participated in Integer 6
LEADER Identity code of leader String 098765
Primary key: SID

Suppose that D1 is database table used for a district.

D1
SID RDATE SNO LEADER
012345 1/10/2020 6 098765
010040 2/9/2023 2 098765
011564 8/8/2018 7 012011
012011 15/8/2022 4
098765 2/2/2017 4
ICT (Elective) – 2A Databases P. 22

(c) In each of the following parts, there is a new record to be inserted into D1, but it will cause an integrity problem
(such as entity integrity, referential integrity and domain integrity). State the integrity problem and explain your
answer briefly.

(i) SID RDATE SNO LEADER

012345 12/9/2022 3 098765

(2 marks)

(ii) SID RDATE SNO LEADER

012345 30/2/2022 5 098765

(2 marks)

(iii) SID RDATE SNO LEADER

012066 10/6/2022 3 023456

(2 marks)
[2024-DSE-ICT 2A-Q4]

- Normalization
▪ Due to the limitations of relational databases, even after we establish a data model, we may not be able to create
the corresponding database.
▪ If we can normalize the data model into a specific schema, the data transformation can succeed. For example,
entities in the data model will be converted into tables, and relationships will be converted into foreign keys
within those tables. However, many-to-many relationships cannot be represented by foreign keys.
ICT (Elective) – 2A Databases P. 23

▪ Steps to normalization
 Handling Relationships with Attributes
◼ Most attributes will be placed on the entity side.
◼ If it is unavoidable to place attributes on the relationship, this typically occurs only in many-to-many
relationships.
◼ Since we will ultimately convert entities into tables, relationships with attributes must be
transformed into entities if they contain attributes.
◼ Please refer to the examples in the next section.

 Handling Binary Many-to-Many Relationships

◼ In many-to-many relationships, since we cannot determine how many entities a record will relate
to, we cannot ascertain the number of foreign keys. Therefore, we will convert the many-to-many
relationship into two one-to-many relationships by introducing a new entity.

CustomerID GoodsID
M M
Customer Order Goods

Name Details

Date Quantity

CustomerID GoodsID
號
1 M M 1
Customer place Order include Goods

Name Date Quantity Details

◼ These entities produced from relationships are also known as associative entities.
➢ Associative entities are formed from the relationship between two entities, so their primary
keys are usually composed of the foreign keys of the related entities.
➢ Therefore, the primary keys of associative entities are often not shown in the ER diagram
ICT (Elective) – 2A Databases P. 24

 Handling Unary Many-to-Many Relationships

◼ When dealing with unary many-to-many relationships, we will also create a new entity to express
this many-to-many relationship.

EmpID M

Employee cooperate
Name
M

EmpID GroupID
M M
Employee cooperate Group
Name

More to Learn:
The transformed relationship may still be a binary many-to-many relationship. Try using the techniques from the
previous section to break down the binary many-to-many relationship in the example above.

 Handling Multivalued Attributes

◼ Similar to many-to-many relationships, it is challenging to express multivalued attributes through
relational database tables.
◼ Using groups of attributes to store multivalued attributes cannot help the design. Such groups are
known as repeating attributes.

ContactPerson ContactPerson1
CompID CompID
Company Company ContactPerson2
CompName CompName
ContactPerson3

◼ To resolve this issue, we can create a new entity to represent the multivalued attribute.

ContactPerson CompID

CompID Company Name

Company CompName 1
CompName M
assign ContactPerson

◼ A database table design which contains no multiple value attributes and repeating attributes is said
to be in First Normal Form (1NF).
ICT (Elective) – 2A Databases P. 25

 Removing Partial Dependency

◼ We should make sure that all records in the table are related to the entire primary key.
◼ If there are non-key fields that are only related to part of the primary key, it is known as partial
dependency. We need to remove them to avoid data anomalies.
◼ Example:

Database schema:
Invoice Invoice_No Order_Date Product_ID Product_Name Unit_Price Quantity

Invoice
Invoice_No Order_Date Product_ID Product_Name Unit_Price Quantity
0307 13/8/2011 011 Obobe 1200 5
0307 13/8/2011 012 Piccolo 1300 4
0308 14/8/2011 011 Obobe 1200 6
0309 16/8/2011 020 Violin 7100 3
0309 16/8/2011 021 Cello 12000 2
0309 16/8/2011 030 Piano 9000 1
0310 18/8/2011 020 Violin 7100 2

In the above example,

➢ Invoice_No and Product_ID form a composite primary key
➢ Order_Date, Product_Name, Unit_Price, and Quantity are non-key fields

We know that:
➢ Order_Date is only related to Invoice_No.
➢ Product_Name and Unit_Price are only related to Product_ID.
➢ Quantity is related to both Invoice_No and Product_ID.
Therefore, this database design has partial dependency.

In this situation, anomalies may occur when adding, modifying, or deleting data, such as:
➢ A new product cannot be added to the database without an order.
➢ If a product only appears in one order and that order is cancelled, the product's information
will be lost.
➢ If a product's information is changed only in one order, data inconsistency will arise.

Invoice Invoice_No Order_Date

Invoice_Line Invoice_No Product_ID Quantity

Product Product_ID Product_Name Unit_Price

◼ A database design which is in 1NF with no partial dependency is said to be in Second Normal Form
(2NF).
ICT (Elective) – 2A Databases P. 26

 Removing Transitive Dependency

◼ Transitive dependency refers to a situation where certain non-key fields are not related to the
primary key but rather to another non-key field. It is necessary to eliminate transitive dependencies
to avoid data anomalies.
◼ Example:

Database schema:
Product Product_ID Product_Name Godown_ID Address Extension

Product
Product_ID Product_Name Godown_ID Address Extension
001 Clarinet 001 2/F 8 Kerfule Road 52
002 Trombone 001 2/F 8 Kerfule Road 52
003 Bugle 001 2/F 8 Kerfule Road 52
004 Trumpet 001 2/F 8 Kerfule Road 52
005 Saxophone 002 5/F 8 Kerfule Road 23
006 Tuba 002 5/F 8 Kerfule Road 23
007 French Horn 003 8/F 8 Kerfule Road 12

In the above example, the non-key fields Address and Extension are related to another non-key field
Godown_ID, which leads to the following issues:
➢ We cannot add new warehouse data without existing products.
➢ Deleting product records may result in the loss of warehouse data.
➢ When warehouse data is changed, all records must be updated; otherwise, data inconsistency
will occur.

To resolve this issue, we can split this table into smaller tables so that no fields are related to non-
key fields.

Product Product_ID Product_Name Godown_ID

Godown Godown_ID Address Extension

◼ A database design which is in 2NF with no transitive dependency is said to be in Third Normal Form
(3NF).
ICT (Elective) – 2A Databases P. 27

Exercise – Normalization
11. A teacher is using the following database table to store his students’ information and their results:

STUDENT (Stud_ID, Name, Addr, Tel, Class, Class_Teacher, (Test) )

Stud_ID is a unique student identification number. Name , Addr and Tel are the personal information of a student.
Class is the class of a student. Each student will be assigned into one class. Class_Teacher is the form teacher
of the class. Test keeps marks of many tests. Since each student takes multiple tests, therefore Test is a multi-
value attribute.

(a) Is the table above in 1st normal form (1NF)? If yes, explain why it is. Otherwise, convert it into 1NF.

(b) Is the table in (a) in 2nd normal form (2NF)? If yes, explain why it is. Otherwise, convert it into 2NF.

(c) Is the table in (b) in 3rd normal form (3NF)? If yes, explain why it is. Otherwise, convert it into 3NF.

12. The organizing committee of a joint-school competition is using the following tables to record the information of
athletes and events:

ATHLETE (Stud_Name, Stud_Addr, Sch_Name, Sch_Addr)

RESULT (Event_ID, Event_Name, Stud_Name, Result, Position)
ICT (Elective) – 2A Databases P. 28

In table ATHLETE, Stud_Name and Stud_Addr are the name and address of an athlete. Sch_Name and
Sch_Addr are the school name and school address of their representing school.

In table EVENT, Event_ID is the event identification code. Event_Name is the event name, e.g. Shot Put, Javelin,
etc. Result is the measured results of an athlete in an event. For example, if Peter Wong got a result 11.82s in his
100m event, Result stores 11.82. Position is the rank of an athlete in an event.

(a) Shall we choose Stud_Name as the primary key? Explain briefly.

(b) Which of the above fields can be removed? Explain briefly.

(c) State the primary key of table RESULT.

(d) Describe the data redundancy in table ATHLETE.

(e) Describe the data redundancy in table RESULT.

(f) Redesign the tables, so as to remove data redundancy. Write down all the foreign key(s).