Learning Outcomes

 Describe relational database management system.

 Explain fields and records.
 Apply SQL DDL to create tables in a database.
 Differentiate between SQL DDL, DML, and DQL.
 Apply SQL DML to manipulate records in tables.
 Apply SQL DQL to query records of tables.
 Unit Overview
• The unit covers implementation of relational database using relational database
management systems(RDBMSs), particularly Microsoft Access and manipulation
of database using Structured Query Language (SQL).
• More specifically, it will focus on how to store, organize, and use data in the
database using SQL.
• A database is a structured collection of data that's organized and stored in a
computer system.
• Databases are used to store information about people, products, orders, and more.
Overview of Relational Database Management System

At the end of this section, students will be able to:

 Understand Relational Database Management System (DBMS)
 Describe features of RDBMS
• The term RDBMS usually refers to various types of software systems developed
in order to manage databases.

• RDBMS is used to create, maintain, and provide controlled access to a relational

database.

• RDBMSs organize data into tables with logical connections, called relationships,
between them. This allows for flexible access and reassembly of data.

• E.g. the popular RDBMs include Microsoft Access, Oracle, Microsoft SQL Server,
MySQL, SQLite, IBM DB2, and PostgreSQL, MySql, MongoDB, and Maria DB.
• A relational database is based on a relational data model.
• The relational data model is a way to store and manage data in tables, which are related to
each other using common attributes.

• Data are stored in a two-dimensional table, which contains columns or

fields and rows or records.
 Each column of a table represents an attribute or data value, and
each row in a table represents a tuple or record.
 Attributes are the set of properties to describe the instances of the
entity.
• For example, a student can be an entity. The attributes of a student
can be described in terms of student id, name, age, grade level and
sex.
• A record is a row or a tuple in the table. It contains a single data value in
each column.
• A fields are attributes that describe the entity.
• When a field is a primary key, the data value is unique, meaning that a
record with it cannot be duplicated.
• Each field in a table has to be given a name and data type
• A data type is the type of data value you want to store in the field
• All Database Management System(DBMS) provide data types from which to select
and use to define the datatype for fields of a table
 For example, in MS Access, when you create a new database table,
you specify what type of data is to be stored in each field.
 Class work
Activity 3.1
1. Give sample data value based on the data type described in the
following table.

2. Given a patient table, write five fields and their data types to
describe a patient with a data values.
 Data Type Use For Size
Up to 255 characters. To control the
Text or combinations of text and numbers, such as addresses. Also numbers
Text maximum number of characters that
that do not require calculations
can be entered,
Memo Lengthy text and numbers, such as notes or descriptions. Up to 64,000 characters.
Numeric data to be used for mathematical calculations. Set the FieldSize 1, 2, 4, or 8 bytes. 16 bytes for
Number
property to define the specific Number type. Replication ID (GUID) only.
Date/Time Dates and times. 8 bytes.
Use the Currency data type to prevent rounding off during calculations.
Currency 8 bytes.
Accurate to 15 digits to the left of the decimal point and 4 digits to the right.
AutoNumb Unique sequential (incrementing by 1) or random numbers automatically 4 bytes. 16 bytes for Replication ID
er inserted when a record is added. (GUID) only.
Fields that will contain only one of two values, such as Yes/No, True/False,
Yes/No 1 bit.
On/Off.
Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets,
Up to 1 gigabyte (limited by disk
OLE Object pictures, sounds, or other binary data), created in other programs using the
space).
OLE protocol,
Hyperlink Field that will store hyperlinks. A hyperlink can be a UNC path or a URL. Up to 64,000 characters.
Lookup Creates a field that allows you to choose a value from another table or from a
typically 4 bytes.
Wizard list of values using a combo box.
 Cont’d
• In relational database, each table that has a key field (primary key) uniquely
identifies each record.
• This key field can be used to create relationship and link one table of data to another.

• A foreign key column in a table points to a column with unique values in another table
Classwork

• Identify entity, attributes, data types, primary/foreign, record, and type of r/ship
Group Discussion
Answer
#1
Relational database management system
• The relationship is based on relational data model
• The basic structure of a relational database is table that comprises rows that are
made up of one or more columns.
• Table represents entities or event, column represent attributes, and each row rep-resents records.
• SQL is a language for relational database.
• Primary key is a field uniquely identifies each row.
• Foreign key is a field which is a primary key in another table and used to create link
or relationship between tables.
•The most common DBMS for the last 40 years.
• Limitation - now challenged with big data – which require highly flexible and adapt-able schema.
• Examples of RDBMS include MS Access, Oracle, MongoDb, etc.
 Answer
#2
Field also referred to as column is an attribute that describe the entity or the
event in a table. E.g. fields for Personincludes Name, age, address; fields

#3
A record also called a tuple, is a row in a table. E.g. a record from ‘Student’
table could be: id: ‘S1001’, name: ‘Maru’, age: 20.
 At the end of this section, students will be able to:
 Describe SQL
 Explain the purpose of DDL, DML and DQL commands
 Open SQL in MS Access.

• What are the similarities and differences between

SQL and other programming languages?
Answer
• SQL is a language for database whereas programming language is a language to write
different types of applications(desktop or web based).
• SQL is for manipulating database.
• You can use SQL to create, insert, update and delete records of a table.
• SQL (Structured Query Language) is a standard language for accessing
and manipulating a database.
• SQL is a special-purpose query language meant for interacting with
relational databases such as Microsoft Access.
• SQL consists of a number of commands with further options to allow
you to carry out your operations with a database.
 Based on their purposes, three categories of SQL commands

1) DDL contains commands that allow you to create or modify tables and establish

relationship between tables

2) DML contains commands that are used to manage the database by performing

operations such as inserting, updating, deleting, and navigating through data.

3) DQL is used for querying or selecting all or subsets of data from a database.

Hint: the DBMS may give you other sets of commands to provide extra features.
However, the three categories of commands are common and applicable in all DBMSs.
Classwork

Activity 3.3
1. What SQL command can be used to:
create a table?
modify a table?
delete a table?
add a record to a table?
modify a record in a table?
remove a record in a table?
2. Categorize commands in question 1
under DDL, DML and DQL.
 Using SQL in Microsoft Access

 Microsoft Access is the default relational database program installed with the
Microsoft Office package.
 It offers the functionality of a database, and the programming capabilities to
create databases easily and navigate records.
 SQL command in Microsoft Access can be used to implement and manipulate.
 two ways of creating a database in Microsoft Access:

1. Create a Database from template.

2. Create a blank database.

 Cont’d

Three ways of creating database tables in Access.

1. Create a Table from Design View

2. Create a Table from Datasheet View

3. Create table from SQL

 Data Definition Language (DDL)

At the end of this section, students will be able to:

 Describe DDL language
 Write SQL command to create, modify and drop table.
 Create relationship between tables using ALTER command.
 Create relationship using ‘Relationships’ database tool in MS Access.

 DDL is part of SQL that is used to create and restructure a database.

 Some of the most fundamental DDL commands include CREATE TABLE, ALTER
TABLE, and DROP TABLE
CREATE TABLE Command: The CREATE TABLE command is used to create a new table in a database.

e.g SQL syntax for CREATE TABLE command

 CREATE TABLE table_name(
 Column1 datatype [Primary Key],
 [Column2 datatype][REFERENCES table_name2(Column1)],
 [Column3 datatype], [Column4 datatype]…)
Some of the more specialized data types that are used in SQL statement in Access
are listed below
Cont…
 Cont…

 If you create a relationship, as seen in earlier example, do not add data to

the table until a relationship is set up. Otherwise, it generates error if the
requirement is not met.
Create tables DEPARTMENT (upper) and COURSE(lower).
 Labwork
Activity 3.5

https://www.youtube.com/watch?v=6XHUI9YsJlQ&list=PLzr5fRV1AGV8dEGsRkghCpXNGEFI1PFaA&index=4
ALTER TABLE command

• Table can be modified using the ALTER TABLE command.

• Using the alter command, you can add column(s), drop column(s), and
change column definitions.
• It is also used to establish relationship between tables.
• The alter command can be used to modify the data type, add and drop
column .
• The alter command can be used to modify the column as primary key.
 Cont’d
Modify.

E.g. ALTER TABLE course ALTER column c_department char(15)

Remove.

E.g. ALTER TABLE course DROP column c_department

Add.

E.g. ALTER TABLE course ADD column c_department char(10)

Primary key.

E.g. ALTER TABLE teacher ADD PRIMARY KEY (teacher_ID)

Create r/ship.

Eg. ALTER TABLE course ADD FOREIGN KEY(c_department) REFERENCES

department(d_number);
 Drop table command

• To delete the table already created

• Eample. DROP TABLE COURSE

If you delete a table with record, you cannot get it back.
Class work
 Create r/ship visually using ‘database tool’
A relationship helps you combine data from two or more different tables.

The relationship can be:

1) One-to-one

2) One-to- many

3) Many-to-many

Relationship in relational database mode

• For example, the relationship between Department and
Teacher is one-to-many. That means, one record in the
DEPARTMENT table is linked to many records in the Teacher
table. Thus, the many side is indicated with infinity symbol
and one side is indicated by
DML consists of commands such as inserting, updating, deleting, and
navigating through data
1) Insert: helps to insert new record to a table.
a) All string values to be inserted in a table with single or double quotation mark.
b) The column value should match the column data types of the table

Option1:
INSERT INTO table_name VALUES(value1, value2, value3…..)
OPTION2:
INSERT INTO table_name(column1, column2…..) VALUES(value1,
valu2…)
Lab work
• Activity 3.9
 Update command
• Not add new record to table nor does it removes record from table
• Simply update existing records.
Syntax
UPDATE table_name SET column1= value1,column2=value2… WHERE
condition;
e.g.UPDATE teacher set T_salary=15000 WHERE T_Salary<10000;
Lab work
• Activity 3.10
 Delete command
• Used to delete a record or multiple e.g.

records from the database DELETE FROM teacher WHERE

• Does not remove the table structure T_idno=‘teach/2312/91’

Syntax Delete multiple rows

DELETE FROM table_name WHERE DELETE FROM table_name

condition; Or

DELETE * FROM table_name;

 Data Query Language –Select Command
• The SELECT command provides options for filtering and getting more
meaningful results from the database.
 Cont’d
The syntax to retrieve all records from a table is:

SELECT * FROM table_name;

The asterisk is equivalent to listing all fields in the table.

Incorrectly spelled field name does not provide the expected output.
Lab work

Activity 3.13
 Selecting a Limited Number of Columns
SELECT field1, [field2] FROM table [WHERE criterion];
Examples1
SELECT S_Fname, S_MName, S_LName, S_sex
FROMSTUDENT
WHERE s_sex = ‘male’;
Examples2
SELECT CustomerId, SalesDate, Amount
FROM SALES
WHERE SalesDate >= ‘15-03-2020’
 Filter records on multiple criteria
Select records of a table with multiple criteria using AND and OR
Example 1
SELECT CustomerId, Sales-Date, Amount
FROM SALES
WHERE SalesDate >=’01-05-2020’ AND Amount > 12000;
Example 1
SELECT CustomerId, Sales-Date, Amount
FROM SALES
WHERE Amount <1000 OR
Amount > 15000;
SELECT Command ORDER BY Clause

SELECT with ORDER BY clause

SELECT column1, [column2], …
FROM table_name
ORDER BY [column] [ASC][DESC]
Example:
SELECT T_First_Name, T_Middle_Name, T_Sex, T_Salary
FROM Teacher
ORDER BY T_Salary
 Cont’d
To get salary in descending order, use the ORDER T_Salary DESC as follows:
SELECT T_First_Name, T_Middle_Name, T_Sex, T_Salary
FROM TEACHER
ORDER BY T_Salary DESC
Lab work
Activity 3.15
Selecting records from Two Tables

Syntax
SELECT table1.column1, [table1.column2], table2.column1,
[table2.column2]
FROM table1, table2
WHERE table1.column1 = table2.column2
Example
SELECT COURSE.Course_id, COURSE.Course_name, COURSE.Grade,
CourseOffering.Semester
FROM COURSE, CourseOffering
WHERE COURSE.Course_id = CourseOffering.Course_code;
Example2
SELECT COURSE.Course_id, COURSE.Course_name, COURSE.Grade,
CourseOffering.Semester
FROM COURSE, CourseOffering
WHERE COURSE.Course_id = CourseOffering.Course_code AND Semester=’I’;
Homework
Unit3:
After you take your own short note from the pdf provided,
write the answer to the review question on your exercise
book.

Review question
(P.96-99)