RIZAL TECHNOLOGICAL UNIVERSITY

Cities of Mandaluyong and Pasig

MODULE 10

Query Languages 5

JOINS (INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN)

Overview

In this module, various MySQL join clauses in the SELECT statement to query
data from two tables. A relational database consists of multiple related tables linking
together using common columns known as foreign key columns. Because of this, the
data in each table is incomplete from the business perspective.

A join is a method of linking data between one (self-join) or more tables based
on values of the common column between the tables. To join tables, you use the inner
join, left join, cross join, or right join clause for the corresponding type of join. The join
clause is used in the SELECT statement after the FROM clause.

Study Guide

A. Participate in the online discussion.

B. Complete the guided exercises and assessments.

Learning Competency

At the end of this module, you should be able to:

1. create join
2. create a left join,
3. create right join, and
4. create a cross join.

Database Management System | K. Morada | 2020 1

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Topic Presentation

INNER JOIN Clause

The INNER JOIN clause joins two tables based on a condition known as a join
predicate. The inner join clause compares each row from the first table with every row
from the second table. If values in both rows cause the join condition to be true, the
inner join clause creates a new row whose column contains all columns of the two rows
from both tables and includes this new row in the final result set. In other words, the
inner join clause includes only rows whose values match.

Ven Diagram:

Syntax:

SELECT column_list
FROM table1
INNER JOIN table2 ON join_condition;

Example 1: INNER JOIN (student and course)

SELECT student.student_id,
student.lname,
student.fname,
course.course_code
FROM student
INNER JOIN course ON student.course_id=course.course_id;

Output:

If the join condition uses the equal operator (=) and the column names in both
tables used for matching are the same, you can use the USING clause instead:

INNER JOIN course USING (course_id);

Database Management System | K. Morada | 2020 2

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Example 2: INNER JOIN (student, course, and college)

SELECT student.student_id AS ID,

student.lname AS 'Last Name',
student.fname AS 'First Name',
course.course_code AS 'Course Code',
college.name AS College
FROM student
INNER JOIN course ON student.course_id=course.course_id
INNER JOIN college ON course.college_id=college.college_id;

Optional

[ORDER BY student.student_id]

Output:

The join condition uses the equal operator (=), and if the column names in both
tables used for matching are the same, then the USING clause may be used.

INNER JOIN course USING (course_id)

INNER JOIN college USING (college_id)

LEFT JOIN Clause

A LEFT JOIN also requires a join-predicate. When joining two tables using a left
join, the concepts of left and right tables are introduced.

The left join selects data starting from the left table. For each row in the left table,
the left join compares with every row in the right table. If the values in the two rows
cause the join condition evaluates to be true, the left join creates a new row whose
columns contain all columns of the rows in both tables and includes this row in the
result set.

If the values in the two rows are not matched, the left join clause still creates a
new row whose columns contain columns of the row in the left table and NULL for
columns of the row in the right table.

Database Management System | K. Morada | 2020 3

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

In other words, the left join selects all data from the left table whether there
are matching rows that exist in the right table or not. In case there are no
matching rows from the right table found, NULLs are used for columns of the
row from the right table in the final result set.

Ven Diagram:

Syntax:

SELECT column_list
FROM table1
LEFT JOIN table2 ON join_condition;

RIGHT JOIN Clause

The RIGHT JOIN clause is similar to the left join clause, except that the treatment
of tables is reversed. The right join starts selecting data from the right table instead of
the left table.

The right join clause selects all rows from the right table and matches rows in the
left table. If a row from the right table does not have matching rows from the left
table, the left table column will have NULL in the final result set.

Ven Diagram:

Syntax:

SELECT column_list
FROM table1
LEFT JOIN table2 ON join_condition;

CROSS JOIN Clause

The CROSS JOIN makes a Cartesian product of rows from the joined tables. The
cross join combines each row from the first table with every row from the right table to
make the result set. The cross join clause does not have a join condition, unlike
the inner join, left join, and right join.

Database Management System | K. Morada | 2020 4

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Suppose the first table has n rows and the second table has m rows. The cross
join that joins the first with the second table will return nxm rows.

Ven Diagram:

Syntax:

SELECT column_list
FROM table1
CROSS JOIN table2;

Guided Exercises / Learning Activities

A. PREREQUISITES

1. Create the table between college and teacher and name it as colteach.
Originally, it is called as college_has_teacher table, as shown in the
schema below:

Here is the code:

CREATE TABLE colteach (

colteach_id INT NOT NULL AUTO_INCREMENT,
college_id INT,
teacher_id INT,
PRIMARY KEY(colteach_id))
CHARSET latin1
COLLATE latin1_general_ci
ENGINE=INNODB;

Database Management System | K. Morada | 2020 5

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

2. Update colteach.
2.1. For college_id

INSERT INTO colteach (colteach_id, college_id)

VALUES (1,1),
(2,1),
(3,2),
(4,3),
(5,1),
(6,1),
(7,2),
(8,3),
(9,1),
(10,2),
(11,1),
(12,2),
(13,3),
(14,2),
(15,3),
(16,1),
(17,1),
(18,3),
(19,3),
(20,1),
(21,2)
ON DUPLICATE KEY UPDATE
college_id=VALUES(college_id);

2.2. For teacher_id

INSERT INTO colteach (colteach_id, teacher_id)

VALUES (1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7),
(8,8),
(9,9),
(10,10),
(11,11),
(12,12),
(13,13),
(14,14),
(15,15),
(16,16),
(17,17),
(18,18),
(19,19),
(20,20),
(21,21)
ON DUPLICATE KEY UPDATE
teacher_id=VALUES(teacher_id);

Database Management System | K. Morada | 2020 6

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

B. Add the necessary FOREIGH KEY CONSTRAINTS to table colteach you may
name them fkcollege2 and fk_teacher
C. Using INNER JOIN, generate the output as illustrated below.

NOTE: It is understood that the values in your work may differ in terms of
teacher_id, empno, lname, fname, and mname.

Assessment

Answer online Assessment 10.

Assignment

1. Post your learning takeaways in single-page PDF format.

2. Accomplish Guided Exercises 10.
3. Answer Assessment 10.

References

MySQL TUTORIAL (2021). MYSQL Joins. Retrieved from

https://www.mysqltutorial.org/mysql-join/ on September 2, 2020

Database Management System | K. Morada | 2020 7

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

MODULE 9

Query Languages 4

FOREIGN KEY Constraint

Overview

This module will teach you how to use the FOREIGN KEY and add and drop a
foreign key constraint. A foreign key is a column or group of columns in a table that
links to a column or group of columns in another table. The foreign key places
constraints on data in the related tables, which allows MySQL to maintain referential
integrity.

Study Guide

A. Participate in the online discussion.

B. Complete the guided exercises and assessments.

Learning Competency

At the end of this module, you should be able to:

1. add foreign key constraint and
2. drop foreign key constraint

Database Management System | K. Morada | 2020 1

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Topic Presentation

A. ADDING FOREIGN KEY CONSTRAINT

FOREIGN KEY Syntax:

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (column_name, ...)
REFERENCES parent_table(column_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

Details:

1. Specify the foreign key constraint name that you want to create after
the CONSTRAINT keyword. If you omit the constraint name, MySQL
automatically generates a name for the foreign key constraint.

2. Specify a list of comma-separated foreign key columns after the FOREIGN

KEY keywords.

3. Specify the parent table followed by a list of comma-separated columns to

which the foreign key columns reference.

4. Specify how the foreign key maintains the referential integrity between the child
and parent tables using the ON DELETE and ON UPDATE clauses.
The reference option determines the action MySQL will take when values in the
parent key columns are deleted (ON DELETE) or updated (ON UPDATE).

MySQL has five reference options: CASCADE, SET NULL, NO

ACTION, RESTRICT, and SET DEFAULT.

1. CASCADE: if a row from the parent table is deleted or updated, the

values of the matching rows in the child table are automatically deleted
or updated.

2. SET NULL: if a row from the parent table is deleted or updated, the
foreign key column (or columns) values in the child table are set
to NULL.

3. RESTRICT: if a row from the parent table has a matching row in the
child table, MySQL rejects deleting or updating rows in the parent table.

4. NO ACTION: is the same as RESTRICT.

Database Management System | K. Morada | 2020 2

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

5. SET DEFAULT: is recognized by the MySQL parser. However, this

action is rejected by both InnoDB and NDB tables.

B. DROP FOREIGN KEY CONSTRAINT

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example:

ALTER TABLE course

DROP CONSTRAINT fk_college;

Guided Exercises / Learning Activities

PREREQUISITES

1. Add the following records to the course table as highlighted below:

2. Alter table course, add column college_id after the course_id.

ALTER TABLE course

ADD COLUMN college_id INT AFTER course_id;

Output:
DESCRIBE course;

Database Management System | K. Morada | 2020 3

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

3. Update the column college_id of table course.

UPDATE course
SET college_id=2 WHERE course_id<9;

Output:
SELECT * FROM course LIMIT 8;

UPDATE course
SET college_id=1 WHERE course_id>8 AND course_id<14;

Output Option 1:
SELECT * FROM course
WHERE WHERE course_id>8 AND course_id<14;

Output Option 2:
SELECT * FROM course
WHERE WHERE course_code LIKE ‘%CAS%’;

UPDATE course
SET college_id=3 WHERE course_code LIKE ‘%CED%’;

Output:
SELECT * FROM course
WHERE WHERE course_code LIKE ‘%CED%’;

Database Management System | K. Morada | 2020 4

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

ADD FOREIGN KEY CONSTRAINT

1. Relationship between college and course

ALTER TABLE course

ADD CONSTRAINT fk_college
FOREIGN KEY (college_id)
REFERENCES college(college_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Output:
DESCRIBE course;

Check the output from INFORMATION_SCHEMA:

SELECT * FROM INFORMATION_SCHEMA.TABLE_ CONSTRAINTS

WHERE TABLE_SCHEMA= ‘dbschool21’
AND CONSTRAINT_TYPE= ‘FOREIGN KEY’;

2. Relationship between course and student

2.1. Add new column

ALTER TABLE student

ADD COLUMN course_id INT;

Output:
DESCRIBE student;

Database Management System | K. Morada | 2020 5

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

2.2. Designate the course_id number to each student

INSERT INTO student (student_id, course_id)

VALUES (1,1),
(2,1),
(3,6),
(4,8),
(5,2),
(6,9),
(7,12),
(8,14),
(9,18),
(10,7)
ON DUPLICATE KEY UPDATE
course_id=VALUES(course_id);

Output:
DESCRIBE student;

2.3. Add Foreign Key Constraint

ALTER TABLE student

ADD CONSTRAINT fk_course
FOREIGN KEY (course_id)
REFERENCES course(course_id)
ON UPDATE CASCADE
ON DELETE CASCADE;

Output:
DESCRIBE student;

Database Management System | K. Morada | 2020 6

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Check Output from INFORMATION_SCHEMA:

SELECT * FROM INFORMATION_SCHEMA.TABLE_ CONSTRAINTS

WHERE TABLE_SCHEMA= ‘dbschool21’
AND CONSTRAINT_TYPE= ‘FOREIGN KEY’;

Assessment

Answer online Assessment 9.

Assignment

1. Post your learning takeaways in single-page PDF format.

2. Accomplish Guided Exercises 9.
3. Answer Assessment 9.

References

MySQL TUTORIAL (2021). MYSQL Foreign Key. Retrieved from

https://www.mysqltutorial.org/mysql-foreign-key/ on September 2, 2020

Database Management System | K. Morada | 2020 7

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

MODULE 8

Query Languages 3

1. ALTER TABLE statement

2. UNIQUE Constraint

Overview

In this module, you will learn how to use the ALTER TABLE statement to add a
column, alter a column, rename a column, drop a column and rename a table. Further,
you will learn about the UNIQUE constraint and how to use the UNIQUE constraint to
enforce the uniqueness of values in a column or a group of columns in a table.

The UNIQUE constraint can ensure that values in a column or a group of columns
are unique—for example, email addresses and phone numbers, student numbers, and
employee numbers.

Study Guide

A. Participate in the online discussion.

B. Complete the guided exercises and assessments.

Learning Competency

At the end of this module, you should be able to:

1. add, modify, change, drop, and rename a column,
2. rename table,
3. enforce the unique constraint.

Database Management System | K. Morada | 2020 1

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Topic Presentation

A. ADD COLUMN Syntax:

1. For single column

ALTER TABLE table_name

ADD [COLUMN] new_column_name column_definition
[FIRST | AFTER column_name];

Example:

ALTER TABLE student

ADD COLUMN email VARCHAR (20) AFTER dob;

Output: Use DESCRIBE student;

2. For multiple columns

ALTER TABLE table_name

ADD [COLUMN] new_column_name column_definition
[FIRST | AFTER column_name],
ADD [COLUMN] new_column_name column_definition
[FIRST | AFTER column_name],
...;

Example:

ALTER TABLE student

ADD mobileno INT AFTER dob,
ADD address VARCHAR(100) AFTER mobileno;

Database Management System | K. Morada | 2020 2

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Output: Use DESCRIBE student;

B. MODIFY COLUMN Syntax:

For multiple columns

ALTER TABLE table_name

MODIFY [COLUMN] column_name column_definition
[ FIRST | AFTER column_name],
MODIFY [COLUMN] column_name column_definition
[ FIRST | AFTER column_name],
...;

Example:

ALTER TABLE student

MODIFY email VARCHAR(50),
MODIFY mobileno BIGINT;

Output: Use DESCRIBE student;

Database Management System | K. Morada | 2020 3

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

C. CHANGE COLUMN Syntax:

ALTER TABLE table_name

CHANGE [COLUMN] original_name new_name column_definition
[FIRST | AFTER column_name];

Example:

ALTE TABLE student

CHANGE mobileno mobile_no BIGINT;

Output: Use DESCRIBE student;

D. DROP COLUMN Syntax:

ALTER TABLE table_name

DROP [COLUMN] column_name;

Example:

ALTER TABLE student

DROP address;

Output: Use DESCRIBE student;

Database Management System | K. Morada | 2020 4

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

E. RENAME COLUMN Syntax:

ALTER TABLE table_name

RENAME COLUMN original_name TO new_column_name;

Example:

ALTER TABLE student

RENAME COLUMN mobile_no TO mobileno;

Output: Use DESCRIBE student;

E. RENAME TABLE Syntax:

Option 1:

ALTER TABLE table_name

RENAME [TO] new_table_name;

Example:

ALTER TABLE student

RENAME TO students;

Output: SHOW TABLES;

Database Management System | K. Morada | 2020 5

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Option 2:

RENAME TABLE original_name TO new_name;

Example:

RENAME TABLE students TO student;

Output: SHOW TABLES;

Note: The RENAME TABLE, unlike ALTER TABLE, can rename multiple
tables within a single statement. See the syntax below:

RENAME TABLE original_name1 TO new_name1,

original_name2 TO new_name2,
original_name3 TO new_name3;

F. UNIQUE Constraint Syntax:

Option 1:

CREATE TABLE table_name(

...,
column_name data_type UNIQUE,
...
);

Option 2:

CREATE TABLE table_name(

...,
column_name1 column_definition,
column_name2 column_definition,
...,
UNIQUE(column_name1,column_name2)

);

Database Management System | K. Morada | 2020 6

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Option 3:

ALTER TABLE table_name

MODIFY column_name column_definition UNIQUE
[FIRST | AFTER ] column_name;

Example:

ALTER TABLE student

ADD phoneno BIGINT UNIQUE AFTER mobileno;

Output: DESCRIBE student;

Guided Exercises / Learning Activities

A. Add the following columns in table student:

mobileno, email, and address

B. Enforce the UNIQUE constraint to column mobileno and email.

C. Encode some dummy records for the above columns.

Assessment

Answer online Assessment 8.

Database Management System | K. Morada | 2020 7

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Assignment

1. Post your learning takeaways in single-page PDF format.

2. Accomplish Guided Exercises 8.
3. Answer Assessment 8.

References

MySQL 8.0. (2021). Documentation. Reference Manual. Retrieved from

https://dev.mysql.com/doc/refman/8.0/en/rename-table.html on September
2, 2020

Database Management System | K. Morada | 2020 8

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

MODULE 7

Query Languages 2

1. UPDATE statement
2. WHERE clause
3. DELETE statement
4. Data Types

Overview

Updating data is one of the most important tasks when working with a database.
This module will teach you how to use the MySQL UPDATE statement to update data
in a table. The UPDATE is a DML statement that modifies rows in a table. It allows you
to change the values in one or more single row columns or multiple rows. The
MySQL WHERE clause filters row from the result set. The WHERE clause allows you
to specify a search condition for the rows returned by a query. The MySQL
DELETE statement deletes data from a single table.

A database table contains multiple columns with specific data types such as
numeric or string. MySQL provides more data types other than just numeric and
string. The following characteristics can determine each data type in MySQL:

• The kind of values it represents.

• The space that takes up and whether the values are a fixed-length or variable
length.
• The values of the data type can be indexed or not.
• How MySQL compares the values of a specific data type.

Study Guide

A. Participate in the online discussion.

B. Complete the guided exercises and assessments.

Database Management System | K. Morada | 2020 1

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Learning Competency

At the end of this module, you should be able to:

1. update records,
2. use WHERE clause to filter records,
3. delete unnecessary records, and
4. identify data types.

Topic Presentation

UPDATE Statement with WHERE clause Syntax:

UPDATE table_name
SET
column_name1 =expr1,
column_name2 =expr2,
...
[WHERE
Condition];

1. Specify the name of the table to update data after the UPDATE keyword.
2. Specify which column you want to update and the new value in the SET clause.

To update values in multiple columns, you use a list of comma-separated

assignments by supplying a value in each column’s assignment in a literal
value, an expression, or a subquery.

3. Specify which rows are to be updated using a condition in the WHERE clause.
The WHERE clause is optional. If you omit it, the UPDATE statement will
modify all rows in the table.

For example, the statement below will show the list of students under the
college entity:

SELECT * FROM college;

Database Management System | K. Morada | 2020 2

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

From the table above, let us update the name value with college_id=3. Observe
that the name value with college_id=3 is encoded in uppercase (CED), which will be
updated to sentence case (Ced). To do this, see the execution below:

UPDATE college
SET name= ‘Ced’
WHERE college_id=3;

To check the result, use:

SELECT * FROM college;

or you may also limit the result to a certain record by adding a WHERE clause in
the SELECT statement:

SELECT * FROM college

WHERE college_id=3;

Using the same table (college), let us modify values with multiple columns:

UPDATE college
SET name = ‘cas’, description = ‘college of arts’
WHERE college_id=1;

Then check changes: SELECT * FROM college WHERE college_id=1;

Database Management System | K. Morada | 2020 3

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

DELETE Statement Syntax:

DELETE FROM table_name

WHERE condition;

In this statement:

1. Specify the table from which you delete data.

2. Use a condition to specify which rows to delete in the WHERE clause.
The DELETE statement will delete rows that match the condition,

Note: The WHERE clause is optional. If you omit the WHERE clause,
the DELETE statement will delete all rows in the table.

Let us use the teacher table for this example:

SELECT * FROM teacher;

Check teacher_id 10 and 11; they have identical values. Although there is a way
to prevent duplicate values by adding a UNIQUE constraint, that has not been
added in this table because there are chances where values found in lname,
fname, mname, and dob may truly occur. So, the DELETE statement may be
used in this case because the values are identical in all aspects except for the
teacher_id and empno.

Let us limit the result to duplicate values only:

SELECT * FROM teacher WHERE lname= ‘Pineda’;

Now let us delete the duplicate; in this case, it is the record with teacher_id=11:

Database Management System | K. Morada | 2020 4

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

DELETE FROM teacher WHERE teacher_id=11;

Check the result: SELECT * FROM teacher WHERE lname= ‘Pineda’;

Data Types Reference:

Guided Exercises / Learning Activities

A. Create the student and teacher tables, respectively. Follow the code
below:
CREATE TABLE student (
student_id INT NOT NULL AUTO_INCREMENT,
stud_no VARCHAR(11) UNIQUE,
lname VARCHAR(50),
fname VARCHAR(50),
mname VARCHAR(50),
dob DATE,
PRIMARY KEY (student_id))
CHARACTER SET latin1
COLLATE latin1_general_ci
ENGINE=INNODB;

Database Management System | K. Morada | 2020 5

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

CREATE TABLE teacher (

teacher_id INT NOT NULL AUTO_INCREMENT,
empno INT UNIQUE,
lname VARCHAR(50),
fname VARCHAR(50),
mname VARCHAR(50),
dob DATE,
PRIMARY KEY (teacher_id))
CHARACTER SET latin1
COLLATE latin1_general_ci
ENGINE=INNODB;

B. Encode the necessary data (dummy records) for each table as

required in GE2 of Module 2 and GE3 of Module 3.

Assessment

Answer online Assessment 7.

Assignment

1. Post your learning takeaways in single-page PDF format.

2. Accomplish Guided Exercises 7.
3. Answer Assessment 7.

References

MySQL 8.0. (2021). Documentation. Reference Manual. Retrieved from

https://dev.mysql.com/doc/refman/8.0/en/update.html on September
2, 2020

Database Management System | K. Morada | 2020 6

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

MODULE 6

Query Languages

1. SQL
2. Characteristics of SQL
3. Advantages of SQL
4. Parts of SQL Language

Overview

The name SQL, pronounced as “ess -cue -ell ” or ‘sequel ’ is the abbreviation for
the structured query language. The SQL consists of facilities for defining, accessing,
and managing relational databases. All tasks related to relational data management-
creating tables, querying the database, deleting, granting access to users, etc., can be
done using SQL. It has been accepted as an American standard by American National
Standards Institute (ANSI) and is a Federal Information Processing Standard (FIPS).
It is also an international standard recognized by the ISO. The first commercial DBMS
that supported SQL was Oracle in 1979. SQL statements can be invoked either
interactively in a terminal session or by embedding them in application programs.

Study Guide

A. Participate in the online discussion.

B. Complete the guided exercises and assessments.

Database Management System | K. Morada | 2020 1

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Learning Competency

At the end of this module, you should be able to:

1. define SQL,
2. determine the characteristics of SQL
3. list advantages of SQL
4. discern the parts or components of SQL language

Topic Presentation

Characteristics of SQL

The following are the important characteristics of SQL

1. SQL is extremely flexible.

2. SQL uses a free-form syntax that gives the user the ability to structure SQL
statements in a way best suited.
3. It is a free-formatted language, i.e., there is no need to start SQL statements in
a particular column or be finished in a single line.
4. It has relatively few commands.
5. It is a non-procedural language.

Advantages of SQL

The advantages of SQL are as follows:

1. SQL is a high-level language that provides a greater degree of abstraction than

procedural languages. The programmer has to specify what data is needed but
need not specify how to retrieve it.

2. SQL is a unified language. The same language can be used to define data
structures, query data, control access, insert, delete and modify occurrences
of the data, etc.

3. All the programs written in SQL are portable. Thus, they can be moved from
one database to another with very little modification. Such porting could be
required when DBMS needs to be upgraded or changed.

4. The language is simple and easy to learn. It can handle complex situations very
efficiently.

Database Management System | K. Morada | 2020 2

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

5. The language has a sound theoretical base, and there is no ambiguity about
how a query will interpret the data and produce the results. Thus the results to
be expected are well defined.

6. SQL processes sets of records rather than just one record at a time. This set-
at-a-time feature of the SQL makes it more powerful.

7. SQL as a language is independent of the way it is implemented internally. This

is because SQL specifies what is required and not how it should be done.

8. SQL enables its users to deal with many database management systems
where it is available.

Parts or Components of SQL Language

The SQL language is mainly divided into four major parts. The four parts are further
divided into subparts. The major parts and subparts are as follows:

1. Data-Definition Language (DDL)

The SQL DDL provides commands for defining the relations, deleting the
relations, and modifying the existing relation schemas.

1.1. View Definition Language (VDL) : The SQL DDL provides commands for
defining and dropping the views.
1.2. Integrity : The SQL DDL provides commands for specifying integrity
constraints that must be satisfied by the data stored in the database.
1.3. Authorization : The SQL DDL provides commands for specifying access
rights to the relations and views.

2. Data Manipulation Language (DML)

The SQL DML provides a query language. This query language is based on
relational algebra and tuple relational calculus. This contains commands to
insert tuples into the database, delete tuples from the database, and
modify/update tuples.

3. Data Control Language or Transaction Control Language (DCL or TCL)

The SQL DCL provides commands that help the DBA control the database,
such as granting or revoking privileges to access the database and storing or
removing transactions that would affect the database.

4. Embedded SQL and Dynamic SQL

4.1. Embedded SQL defines how the SQL statements can be embedded within
general-purpose programming languages like C, C++, Cobol, Pascal, etc.
The language in which SQL queries are embedded is a host language.
The SQL queries embedded in the host language constitute embedded
SQL.

4.2. Dynamic SQL allows programs to construct and submit SQL queries at run
time.

Database Management System | K. Morada | 2020 3

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Guided Exercises / Learning Activities

A. Activity for Data Definition Language (DDL)

1. Create a database
2. Create a table for the three entities:

college
college_id
name
description

course
course_id
course_code
description

subject
subject_id
subject_code
description
year_level
semester
pre_requisite
Steps:

1. Open MySQL and enter the password you provided to continue.

Note: In case you did not provide a password, just press Enter to continue.

2. Type SHOW DATABASES;

3. CREATE DATABASE IF NOT EXISTS dbschool21

CHARACTER SET latin1
COLLATE latin1_general_ci;

4. USE dbschool21;

5. CREATE TABLE college (

college_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
description VARCHAR( 100)NOT NULL,
PRIMARY KEY( college_id))
CHARACTER SET latin1
COLLATE latin1_general_ci
ENGINE=INNODB;

Database Management System | K. Morada | 2020 4

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

6. CREATE TABLE course (

course_id INT NOT NULL AUTO_INCREMENT,
course_code VARCHAR(50) NOT NULL,
description VARCHAR(100)NOT NULL,
PRIMARY KEY( course_id))
CHARACTER SET latin1
COLLATE latin1_general_ci
ENGINE=INNODB;

7. CREATE TABLE subject (

subject_id INT NOT NULL AUTO_INCREMENT,
subject_code VARCHAR(50) NOT NULL,
description VARCHAR( 100)NOT NULL,
units DECIMAL(10,2) NOT NULL ,
year_level INT,
semester VARCHAR(50) ,
pre_requisite VARCHAR(50) ,
PRIMARY KEY( teacher_id))
CHARACTER SET latin1
COLLATE latin1_general_ci
ENGINE=INNODB;

B. Activity for Data Manipulation Language ( DML)

1. The data and syntax for the college table.

INSERT INTO college (name, description)

VALUES ('CAS', 'College of Arts and Sciences'),
('CBET', 'College of Business and Entrepreneurial Technology'),
('CED', 'College of Education'),
('CEIT', 'College of Engineering and Industrial Technology');

2. Complete the data for the course table . Follow the syntax as to how the data
from the college table are encoded. Just modify the values following the
reference table below.

course_id course_code description

1 CBET-01-2018A Bachelor of Science in Accountancy
2 CBET-05-2018A Bachelor of Science in Office Administration Major in Office
Management
3 CBET-17-2018A Bachelor of Science in Business Administration Maj. in
Human Resource Devt. Mgt.
4 CBET-18-2018A Bachelor of Science in Business Administration Maj. in
Operations Management
5 CBET-19-2018A Bachelor of Science in Business Administration Maj. in
Marketing Management
6 CBET-20-2018A Bachelor of Science in Entrepreneurship
7 CBET-22-2018A Bachelor of Science in Business Administration Major in
Financial Management
8 CBET-25-2018A Bachelor of Science in Office Administration

Database Management System | K. Morada | 2020 5

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

ADDITIONAL : You may download TERMUX as an alternative for MySQL for mobile
users. Find the free version.

Additional steps after installation:

1. Type apt update and press the Enter key
2. Type apt upgrade
3. Type apt install mariadb . When asked to continue, select “Y”
4. Type cd ..
5. Type ls
6. Change directory to usr type cd usr
7. Type ls
8. Type cd etc
9. Type ls
10. Check if the my.cnf.d exist (In case not, type mkdir my.cnf.d )
11. Type clear
12. Change directory, type cd $HOME
13. Type ls
14. Type clear
15. Start MySQL daemon, type mysqld_safe -u root &
16. Star new session, type mysql -h localhost

NOTE: NEXT TIME YOU REOPEN THE APP, REMEMBER T O EXECUTE STEPS
15 AND 16 ONLY.

Assessment

Answer online Assessment 6 .

Assignment

1. Post your learning takeaways in single-page PDF format.

2. Accomplish Guided Exercises 6 .
3. Answer Assessment 6 .

Database Management System | K. Morada | 2020 6

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

References

Coronel, C. and Morris, S. (2015). Database Systems: Design, Implementation, and

Management. Cengage Learning

Heijden, H. (2009). Designing Management Information Systems. Oxford University

Press Inc.

Gupta, S. and Mittal, A. (2017). Introduction to Database Management System.

University Science Press

Database Management System | K. Morada | 2020 7

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

MODULE 5

Enhanced Entity Relationship (EER) Model

1. EER Model
2. Reduction of E-R and EER to Diagram into Tables

Overview

EER model is the enhanced version of the E-R model, which includes all the
basic concepts of the E-R model with the capability to support additional semantic
concepts of complex applications. These additional concepts are:

• Specialization
• Generalization
• Categorization

However, two other entity types must be described before discussing

specialization, generalization, and categorization: superclass (supertype) and
subclass (subtype).

Study Guide

A. Participate in the online discussion.

B. Complete the guided exercises and assessments.

Database Management System | K. Morada | 2020 1

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Learning Competency

At the end of this module, you should be able to:

1. differentiate superclass and subclass entity types,
2. reduce E-R and EER diagram into tables

Topic Presentation

Superclass and Subclass Entity Types

The most important new modeling construct introduced by EER was superclass
and subclass entity types. These are also known as supertype and subtype entities,
respectively. Using these two entity types, the E-R model can be divided into more
specialized sub-models or can join some sub-models to make a generalized E-R
model.

• Superclass Entity Type (Supertype): A superclass entity type is a generic

entity type that includes one or more distinct subclasses required to be
represented in a data model. It means members belonging to the subclass
are the same as the entity in the superclass. A superclass and a subclass
is a one-to-one (1:1) relationship. In some cases, a superclass can have
overlapping subclasses.

• Subclass Entity Type (Subtype): A subclass entity type is a more

specialized entity type that has a distinct role in the organization. A subclass
is a member of the superclass. It is one of the data-modeling abstractions
used in EER. A subclass may be further divided, and, in that case, it acts
as a superclass for its subclasses.

Database Management System | K. Morada | 2020 2

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Figure 1. Superclass/subclass relationship

Example:

PERSON entity (superclass entity type)

EMPLOYEE and CUSTOMER (subclass entity type)

Figure 2. Specialization and Generalization

1. Specialization includes subgrouping entities within an entity set having some
distinct nature than other entities. If deep information is needed, then go
towards specialization. In other words, Specialization is a process by which
any existing entity set is divided into smaller entity sets according to the
distinct or different nature of entities.

2. Generalization is when two or more entity sets can be combined into a single
entity set by determining similarities between the entities. It is an abstract view
of any Enterprise. Generalization proceeds from recognizing that a number of
entity sets share some common features. If an abstract view of information
is needed, then go towards generalization.

Database Management System | K. Morada | 2020 3

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

3. Specialization and generalization lead to attribute inheritance between

higher-level entity sets and lower-level entity sets. Inheritance is when a lower-
level entity set inherits (or takes) some properties of its higher-level entity set.

4. Aggregation is an abstraction process in which a relationship set is considered

a higher-level entity set.

Figure 3. E-R Model

Specialization and Generalization Constraints are applied to capture important
business rules of the relationships in an enterprise. There are two types of constraints:

1. Participation Constraints tell the participation of entity set in relationship sets.

There are two types of participation.

• Partial participation: If only some entities from entity set E

participates in relationships in set R, then it is known as Partial
participation. See Figure 4 (a).

• Total participation: If every entity from entity set E is participated

with at least one relation in relationship set R, it is known as Total
participation. Consider Figure 4(b).

Figure 4. E-R Model

2. Disjoint Constraints: Disjoint constraints describe the relationship between

members of different subclasses. According to the disjoint constraint, if the
subclasses of a specialization/ generalization are disjoint, then an entity can be
a member of only one subclass of that specialization/generalization.

Database Management System | K. Morada | 2020 4

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

3. Categorization is a modeling process of a single subclass having a

relationship with more than distinct superclasses. The subclass having more
than one superclass is known as a category, and the process of defining a
category is known as categorization. The symbol is shown in Figure 5 (a)
represents categorization. Consider Figure 5(b).

Figure 5. Categorization

In a bank, a person can be either an employee or a client, and both of them

may be investors. So, here subclasses employee and client act as Disjoint
Superclasses, and Subclass Investor acts as Category.

You cannot combine works-on and managers relationship sets because some
workers are not managers. Using aggregation, the works-on relationship set
acts as a higher entity set and solves this drawback of the E-R Model. E-R
Model with Aggregation is shown in Figure 2.19.

Figure 6. E-R model with aggregation

Database Management System | K. Morada | 2020 5

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Reduction of an E-R Diagram and EER Diagram into Tables

E-R diagrams have to be reduced in tables to represent the database in tabular
form. For each entity set, make a different table and for each relationship, set make a
different table.

1. Reduction of Strong Entity sets into Tables

For a strong entity set E with attributes a1, a2, ..., an, make a table having the
same name as entity set E and having n number of columns or table name is
equal to entity set name and number of columns is equal to a number of
attributes. Consider Figure 6 having a strong entity set Department with two
attributes Dept-ID and Dept-name.

Figure 7. E-R Model of Employee and Department Entity Set

See Table 1 with table name Department and two columns, Dept_ID and
Dept_name.

Table 1. The Department Table (Reduction of strong entity set)

Database Management System | K. Morada | 2020 6

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

2. Reduction of Composite Attributes

Create a separate column for each component attribute or part of composite

attributes for a composite attribute. Consider the example shown in Table 2.
The Name is a composite attribute with three component attributes First,
Middle, and Last. So, make three columns First-name, Middle-name, and Last-
name. The corresponding table is shown in Table 2.

Table 2. The employee table (Reduction of composite attributes)

3. Reduction of Multi-valued Attributes

For multi-valued attributes, make a separate table with columns C1

representing the primary key of entity set or relationship set and columns C2
representing the multi-valued attributes. Rows are equal to the total number of
values of that attribute. Consider Figure 7, in which Phone-No. is a multi-valued
attribute. So, make a table with two columns, one is Emp-ID (primary key of
Employee), and the second is Phone-No. (multi-valued attribute). Give any
name to that table. Check Table 3. If any employee has two phone numbers,
then it is possible to make two different entries in the table.

Table 3. The phone-number table (Reduction of multi-valued attributes)

4. Reduction of Weak Entity Sets

Let A be the weak entity set, and B be the strong entity set on which A depends.
Then, it is possible to make a table with table name as of Weak Entity Set
having columns equal to the

Figure 8. E-R diagram of weak entity set payment

Database Management System | K. Morada | 2020 7

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

attributes of Weak Entity set plus Primary Key of the Strong Entity set on which
Weak Entity Set depends. Consider the E-R diagram shown in Figure 8, in which
Payment is a Weak entity set that depends upon the Loan entity set. So, make
a table with table name Payment having four columns as shown in Table 4.

Table 4. The payment table (Reduction of weak entity set)

5. Reduction of Relationship Sets

Let R be the relationship set and E1, E2, ..., EN be the entity sets participating
in R. Make a table with table name as of Relationship Set having columns equal
to the number of attributes in relationship set (descriptive attributes) and
primary keys of all participating entity sets. Consider the ER diagram shown in
Figure 7, having a relationship set works-in having two participating entity sets,
Employee and Department. The corresponding table is shown in Table 5.

Table 5. The works-in table (Reduction of relationship sets)

5.1. Redundant Tables

The relationship set between weak and strong entity sets is treated
specially. Consider the E-R diagram shown in Figure 8, where weak
entity set, Payment depends on a strong entity set Loan having
relationship set loan-payment. The primary key of the Entity set Loan is
[loan-number], and of Weak entity set is [loan-number, payment-
number]. Table of entity set Payment has four attributes [loan-number,
payment-number, Payment-date, payment-amount]. If you make the
table of relationship set loan-payment, it contains attributes [loan-
number, payment-number]. This combination is already present in the
table of Payment. Even, there are no descriptive attributes. So, this
table is redundant and discarded.

5.2. Combination of Tables

Consider two entity sets, X and Y, connecting with relationship set XY.
Then, three tables named X, Y, and XY have to be made. If the
cardinality ratio between X and Y is many-to-many and X is totally
participated, combine tables X and XY. Consider the E-R diagram
shown in Figure 9, having two entity sets, Customer and Loan. The
relationship is many-to-many because a customer can take many loans,

Database Management System | K. Morada | 2020 8

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

and a single loan can be taken by more than one customer or joint loan.
The loan entity set has totally participated because every loan refers to
some customer. So, combine tables Loan and Borrower. But a loan
cannot exist with any customer, so two tables are needed, i.e.,

• Loan [loan-number, amount, customer-ID, Income]

• Customer [Customer-ID, Name]

Figure 9. Combination of tables

6. Reduction of Generalization

In generalizations, higher-level entity sets and lower-level entity sets are

considered. Make a table for a higher-level entity set with all its attributes. For
a lower-level entity set, make a table with all its attributes with the primary key
attributes of its higher-level entity set. Consider the E-R diagram shown in
Figure 7, in which Employee is a high-level entity set and Full Time Employee
and Part-Time Employee are two lower-level entity sets. So, make three tables
as given below:

• Employee [Emp-ID, Dept-ID, First-Name, Middle-Name, Last-Name, Salary]

• Full-Time Employee [Emp-ID, Qualification]
• Part-Time Employee [Emp-ID, Hours-Worked]

7. Reduction of Aggregation

The reduction of aggregation into tables is simple. Consider the E-R diagram
shown in Figure 6. For all entity sets, make tables as discussed earlier.
Consider the same approach as discussed earlier for making tables for
relationship sets. Take an example of a relationship set Manages. Make a table
managed with all descriptive attributes, the primary key of an entity set
Manager, and the relationship set works-on.

Database Management System | K. Morada | 2020 9

RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig

Guided Exercises / Learning Activities

Review for Midterm Exam.

Assessment

Answer online Assessment 5.

Assignment

1. Post your learning takeaways in single-page PDF format.

2. Accomplish Guided Exercises 5.
3. Answer Assessment 5.

References

Coronel, C. and Morris, S. (2015). Database Systems: Design, Implementation, and

Management. Cengage Learning

Heijden, H. (2009). Designing Management Information Systems. Oxford University

Press Inc.

Gupta, S. and Mittal, A. (2017). Introduction to Database Management System.

University Science Press

Database Management System | K. Morada | 2020 10