‫ْ‬ ‫َّ‬ ‫ٰ‬ ‫ْ‬ ‫َّ‬

‫هللا الرحمن الر ِحي ِم‬ ‫ْ‬

‫ِبس ِم ِ‬
INTRODUCTION TO INFORMATION
AND COMMUNICATION
TECHNOLOGY

Database
Lecture # 14
By: Mr. Muhammad Tahir Sohail
Lecturer
Department of Computer Science
 What is Data?
• Data is a collection of a distinct small unit of
information. It can be used in a variety of forms
like text, numbers, media, bytes, etc. It can be
stored in pieces of paper or electronic memory,
etc.
• The word 'Data' originated from the word 'datum'
which means 'single piece of information.' It is the
plural of the word datum.
• In computing, Data is information that can be
translated into a form for efficient movement and
processing. Data is interchangeable.
 What is a Database?
A database is an organized collection of data so that
it can be easily accessed and managed.
– Database, often abbreviated DB, is a collection of data
organized so that you can access, retrieve, and use it
– Databases are designed to offer an organized mechanism
for storing, managing, and retrieving information
Examples of Databases
 1. Phone Directory
A phone directory is a simple example of a
database. A phone directory stores the phone
numbers of different persons. Searching for a
phone number from the phone directory is very
easy as all phone numbers are stored in an
organized way.
 2. Library
A library contains thousands of books. It is very
difficult to handle the records of all these books
without a database. A database system can be used
to store the records of books, members of the
library, issuance and recovery of books, etc. The
database can be used to search the required books
easily. This database can help in doing research
work.
 3. Accounts
A database is used to control the accounts system
of an organization. The accounts database keeps
the record of all financial transactions of the
organization. It can be used to perform different
calculations to find information about the business
such as annual profit, trial balance, ledger, etc.
 4. College
A college has many students in different classes. A
database may be used to keep the records of the
students, fee transactions, examination information
and other data of the college. It can also store the
attendance of the students.
 File Processing System
Traditional or simple file processing is the first
computer-based method to handle business
applications. In the past, many organizations stored
data in files on tape or disk. The data was managed
using a file-processing system. In a typical file
processing system, each department in an
organization has its own set of files. The files are
designed especially for their applications. The
records in one file are not related to the records in
any other file.
 File Processing System
Consider an example of a student's file system. The student file will
contain information regarding the student (i.e., roll no, student
name, course, etc.). Similarly, we have a subject file that contains
information about the subject and the result file which contains
information regarding the result.
Some fields are duplicated in more than one file, which leads to
data redundancy. So, to overcome this problem, we need to create a
centralized system, i.e., a DBMS approach.
Database Management System
A database approach is a well-organized collection
of data that are related in a meaningful way and
can be accessed by different users but stored only
once in a system. The various operations performed
by the DBMS system are Insertion, deletion,
selection, sorting, etc.
Database Management System
In this figure, duplication of data is reduced due to
centralization of data.
Database Management System
Database Management System (or DBMS) is a
software (application program) that facilitates users
in creating, organizing, deleting, updating, and
manipulating data in a database
DBMS stores large collections of data, organizes the
data, and becomes a data storage system DBMS
allows you to;
▪ create database
▪ add, change, and delete data
▪ sort and retrieve data
▪ create forms and reports
Database Management System
Individuals involved with a DBMS:
• Database designers: They design the database
• Database developers: They create the database
• Database programmers: They write programs
needed to access the database or tie the
database to other programs
• Database administrators: They are responsible
for managing the databases within an
organization
• End users: Individuals who enter data, update
data, and retrieve information from the database
 Database Models
The database model defines how data in a database
is organized and linked together.
More precisely, it is the structure or format of a
database.
Four commonly used database models are:
▪ Hierarchical database model
▪ Network database model
▪ Relational database model
▪ Object-oriented database model
 Relational Database Model
Data is represented in terms of tuples (rows) in RDBMS.
A relational database is the most commonly used
database. It contains several tables, and each table has
its primary key.
Due to the collection of an organized set of tables, data
can be accessed easily in RDBMS.
▪ The relational database model is the most popular
and widely used
▪ In the relational database model, all data is
represented in terms of tuples, grouped into
relations
▪ It consists of a collection of tables that store sets of
data
 Relational Database Model
▪ The standard fields and records are represented
as columns (fields) and rows (records) in a table
▪ Data from several tables is tied together (related)
using a field that the tables have in common
▪ A Database Management System that supports a
relational database model is called a Relational
Database Management System or RDBMS
– RDBMS organizes and describes the data
– It normally contains two or more tables
– Using RDBMS, maintaining data is easier
Relational Database Model
Relational database model
example
 Components of Relational
Database Model
Field
▪ Often called a column
▪ Hold an individual piece of data
▪ Is named descriptively
▪ Name, address, e-mail, and phone number are
examples
▪ Field(s) may contain no data
 Components of Relational
Database Model
Record
▪ Often called a row
▪ One full set of fields
▪ Smith, Joe, 123 Some Street, 412-555-7777 is
an example record
▪ Tables may have unlimited rows
 Components of Relational
Database Model
Table
▪ One complete collection of records
▪ Databases may have thousands of tables
Form
▪ Present one record to the user
▪ Often used to add, change, or view data
Report
▪ Produce printed results (summaries) from the
database
 Keys
▪ Keys play an important role in the relational database.
▪ It is used to uniquely identify any record or row of data
from the table. It is also used to establish and identify
relationships between tables.
▪ For example, ID is used as a key in the Student table
because it is unique for each student. In the PERSON table,
passport_number, license_number, SSN are keys since they
are unique for each person.
 Types of keys
Super Key
▪ A super key is an attribute set that can uniquely
identify a tuple. A super key is a superset of a
candidate key.
For example: In the above EMPLOYEE table, for (EMPLOEE_ID,
EMPLOYEE_NAME), the name of two employees can be the same,
but their EMPLYEE_ID can't be the same. Hence, this combination
can also be a key.
The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-
NAME), etc.
 Types of keys
Candidate key
▪ A candidate key is an attribute or set of attributes that
can uniquely identify a tuple.
▪ Except for the primary key, the remaining attributes
are considered a candidate key. The candidate keys are
as strong as the primary key.
For example: In the EMPLOYEE table, Employee_ID is best suited for
the primary key. The rest of the attributes, like SSN,
Passport_Number, License_Number, etc., are considered a
candidate key.
 Types of keys
Primary key
▪ It is the first key used to identify one and only one instance
of an entity uniquely. An entity can contain multiple keys,
as we saw in the PERSON table. The key which is most
suitable from those lists becomes a primary key.
▪ In the EMPLOYEE table, ID can be the primary key since it is
unique for each employee. In the EMPLOYEE table, we can
even select License_Number and Passport_Number as
primary keys since they are also unique.
For each entity, the primary key
selection is based on requirements
and developers.
 Types of keys
Alternate key
There may be one or more attributes or a combination of
attributes that uniquely identify each tuple in a relation. These
attributes or combinations of the attributes are called the
candidate keys. One key is chosen as the primary key from
these candidate keys, and the remaining candidate key, if it
exists, is termed the alternate key.
In other words, the total number of the alternate keys is the
total number of candidate keys minus the primary key. The
alternate key may or may not exist. If there is only one
candidate key in a relation, it does not have an alternate key.
 Types of keys
Alternate key
For example, employee relation has two attributes,
Employee_Id and PAN_No, that act as candidate keys. In this
relation, Employee_Id is chosen as the primary key, so the
other candidate key, PAN_No, acts as the Alternate key.
 Types of keys
Composite key
Whenever a primary key consists of more than one attribute,
it is known as a composite key. This key is also known as
Concatenated Key.
For example, in employee relations, we assume that an employee
may be assigned multiple roles, and an employee may work on
multiple projects simultaneously. So, the primary key will be
composed of all three attributes, namely Emp_ID, Emp_role, and
Proj_ID in combination. So, these attributes act as a composite key
since the primary key comprises more than one attribute.
 Types of keys
Foreign key
▪ Foreign keys are the columns of the table used to point to
the primary key of another table.
▪ Every employee works in a specific department in a
company, and the employee and department are two
different entities. So, we can't store the department's
information in the employee table.
▪ That's why we link these two tables through the primary
key of one table.
 Types of keys
Foreign key
▪ We add the primary key of the DEPARTMENT table,
Department_Id, as a new attribute in the EMPLOYEE table.
▪ In the EMPLOYEE table, Department_Id is the foreign key,
and both tables are related.
 Structured Query Language
▪ Another commonly supported feature of a database is
enabling a user to search for desired information in the
database
▪ Structured Query Language (or SQL) is used to query
information from a database
▪ It is the most popular language used to create, modify,
retrieve, and manipulate information from relational
database management systems
 Structured Query Language
Rules:
SQL follows the following rules:
▪ Structure query language is not case-sensitive. Generally,
keywords of SQL are written in uppercase.
▪ Statements of SQL are dependent on text lines. We can use a
single SQL statement on one or multiple text lines.
▪ Using the SQL statements, you can perform most of the
actions in a database.
▪ SQL depends on tuple relational calculus and relational
algebra.
 Structured Query Language
SQL process:
▪ When an SQL command is executing for any RDBMS, then
the system figures out the best way to carry out the request
and the SQL engine determines how to interpret the task.
▪ In the process, various components are included. These
components can be optimization Engine, Query engine,
Query dispatcher, classic, etc.
▪ All the non-SQL queries are handled by the classic query
engine, but the SQL query engine won't handle logical files.
 Structured Query Language
SQL Datatype:
• SQL Datatype is used to define the values that a column can
contain.
• Every column is required to have a name and data type in
the database table.
Datatype of SQL:
 SQL SELECT Statement
In SQL, the SELECT statement is used to query or retrieve data
from a table in the database. The returns data is stored in a
table, and the result table is known as result set.
Syntax
SELECT column1, column2, ...
FROM table_name;
Here, the expression is the field name of the table that you
want to select data from.
Use the following syntax to select all the fields available in the
table:
SELECT * FROM table_name;
 SQL SELECT Statement
Example: EMPLOYEE
EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

To fetch the EMP_ID of all the employees, use the following

query:
SELECT EMP_ID FROM EMPLOYEE;
 SQL SELECT Statement
Example: EMPLOYEE

Output
EMP_ID

5
 SQL SELECT Statement
To fetch the EMP_NAME and SALARY, use the following query:
SELECT EMP_NAME, SALARY FROM EMPLOYEE;
Output

EMP_NAME SALARY

Kristen 150000

Russell 200000

Angelina 600000

Robert 350000

Christian 260000
 SQL SELECT Statement
To fetch all the fields from the EMPLOYEE table, use the
following query:
1. SELECT * FROM EMPLOYEE
Output

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

 Survey of DBMS
Oracle
▪ Most popular enterprise-level DBMS
▪ Very flexible storage system
▪ Can be very complex
▪ Platform independent
▪ Offers a wide range of solutions
▪ Supports both Relational and Object-Oriented models
 Survey of DBMS
MySQL
▪ Leading DBMS for Linux
▪ Very inexpensive
▪ Often faster than other DBMS
▪ Platform independent
▪ Supports Relational model
 Survey of DBMS
Microsoft Access
▪ DBMS from Microsoft
▪ It is a member of the Microsoft Office suite
▪ Runs on Microsoft platforms and a separate version for Mac
▪ Popular among small to medium size organizations
▪ Supports Relational model
 Survey of DBMS
Microsoft SQL Server
▪ Fastest growing DBMS
▪ Only runs on Microsoft platforms
▪ Eight different versions exist
▪ Extremely scalable architecture
▪ It can grow with the data
▪ Based on Relational model