DATA PROCESSING SS2

WEEK 1

RELATIONAL MODEL

What is Relational Model?

Relational Model (RM) represents the database as a collection of relations. A

relation is nothing but a table of values. Every row in the table represents a
collection of related data values. These rows in the table denote a real-world entity
or relationship.

The table name and column names are helpful to interpret the meaning of values in
each row. The data are represented as a set of relations. In the relational model,
data are stored as tables. However, the physical storage of the data is independent
of the way the data are logically organized.

Some popular Relational Database management systems are:

 DB2 and Informix Dynamic Server – IBM

 Oracle and RDB – Oracle
 SQL Server and Access – Microsoft

Creating Relations using application package e.g. SQL

A database relation is a predefined row and column format for storing information
in a relational database. Relations are equivalent .to tables

What is SQL?

SQL is Structured Query Language which is a computer language for storing,

manipulating and retrieving data stored in a relational database, it includes
database creation, deletion, fetching rows and modifying rows etc.

SQL is an ANSI (American National Standards Institute)standards but there are

different versions of the SQL language.

SQL is the standard language for Relation Database System. All relational
database management systems like MYSQL, MS ACCESS, Oracle, Sybase,
Informix, postgres and SQL Server uses SQL as standard database language.
SQL Commands

The standard SQL commands to interact with relational databases are CREATE,
SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be
classified into groups based on their nature:

DDL – Data Definition Language:

CREATE: Creates a new table, a view of a table, or other object in database
ALTER: Modifies an existing database object, such as a table
DROP: Deletes an entire table, a view of a table or other object in the database
DML – Data Manipulation Language:
UPDATE: Modifies records
DELETE: Delete records
DCL – Data Control Language:
GRANT: Give a privilege to user
REVOKE: Takes back privilege granted from user
DQL: Data Query Language
SELECT: Retrieves certain records from one or more tables
Creating Database using SQL
The SQL CREATE DATABASE statement is used to create new SQL database.
Syntax:
Basic syntax of CREATE DATABASE statement is as follows:
CREATE DATABASE DatabaseName;
Always database name should be unique within the RDBMS.
Example:
If you want to create a new database <School>, then CREATE DATABASE
statement would be as follows: SQL>CREATE DATABASE School;
Make sure you have administrative privilege before creating any database. Once a
database is created, you can check it in the list of databases as follows:
SQL>SHOW DATABASES;
DROP or DELETE Database
The SQL DROP DATABASE statement is used to drop any existing database in
SQL schema.
Syntax:
Basic syntax of DROP DATABASE statement is as follows:
DROP DATABASE Database Name:
Always database name should be unique within the RDBMS.
Example:
If you want to delete an existing database<School>, then DROP DATABASE
statement would be as follows: SQL>DROP DATABASE School;
NOTE: Be careful before using this operation because by deleting an existing
database would result in loss of complete information stored in the database.
CREATE Table
Creating a basic table involves naming the table and defining its column and each
column`s data type.
The SQL CREATE TABLE statement is used to create a new table.
Syntax:
Basic syntax of CREATE TABLE statement is as follows:
CREATE TABLE table _name (
Column1 datatype,
Column2 datatype,
Column3 datatype,
…..
ColumnN datatype,
PRIMARY KEY(one or more column ));
CREATE TABLE is the keyword telling the database system what you want to do.
In this case, you want to create a new table. The unique name or identifier for the
table follows the CREATE TABLE statement.
Then in brackets comes the list defining each column in the table and what sort of
data type it is. The syntax becomes clearer with an example below.
Example:
Following is an example which creates a CUSTOMERS table with ID as primary
key and NOT NULL are the constraints showing that these fields cannot be NULL
while creating records in this table: SQL>CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARRCHAR (20) NOT NULL
AGE INT NOT NULL
ADDRESS CHAR (25)
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID));

You can verify if your table has been created successfully by looking at the
message displayed by the SQL server otherwise you can use DESC command as
follows:
SQL> DESC CUSTOMERS;
INSERT INTO STATEMENT
The SQL INSERT INTO statement is used to add new rows of data to a table in the
database.
Syntax
There are two basic syntaxes of the INSERT INTO statement which are shown
below.
INSERT INTO TABLE_NAME (column1, column2,…columnN)
VALUES (value1, value2, value3…valueN);
Here, column1, column2, column3, …columnN are the names of the columns in
the table into which you want to insert the data.
You may not need to specify the column(s) name in the SQL query if you are
adding values for all the columns of the table. But make sure the order of the
values is in the same order as the columns in the table.
The SQL INSERT INTO syntax will be as follows –
INSERT INTO TABLE_NAME VALUES (value1, value2, value3…valueN);
Example
The following statements would create three records in the CUSTOMERS table
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
(1, BISI, 32, Ajegunle, 2000);
INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY)
(2, ODOGWU, 17, Olodi, 5000);
INSERT INTO CUSTOMER (ID, NAME, AGE, ADDRESS, SALARY)
(3, ZAINAB, 55, Abuja, 3000);
You can create a record in the CUSTOMERS VALUES (7, Muffy, 24, Indonesia,
10000 );
WEEK 2
RELATIONAL MODEL 2
INTEGRITY CONSTRAINTS OVER RELATION
Database integrity refers to the validity and consistency of stored data. Integrity is
sometimes expressed in terms of constraints, which are consistency rules that the
database is not permitted to violate. Constraints may apply to each attribute or they
may apply to relationships between tables. Integrity ensures that changes (update,
delete, insertion) made to the database by authorized users do not result in the loss
of data consistency.
Constraints are the rules enforced on data columns on table. These are used to limit
the type of data that can go into a table. This ensures the accuracy and reliability of
the data in the database.
Constraints could be column level or table level. Column level constraints are
applied only to one column whereas table level constraints are applied to the whole
table.
Following are commonly used constraints available in SQL.
1. NOT NULL Constraint: Ensures that a column cannot have a NULL value
2. DEFAULT Constraint: Provides a default value for column when none is
specified.
3. UNIQUE Constraint: Ensures that all values in a column are different
4. PRIMARY Key: Uniquely identified each rows/records in a database table
5. FOREIGN Key: Uniquely identified a row and column in another database
table
6. CHECK Constraint: The CHECK constraint ensures that all values in a
column satisfy certain conditions.
7. INDEX: Use to create and retrieve data from the database very quickly
Constraints can be classified when a table is created with the CREATE TABLE
statement or you can use ALTER TABLE statement to create constraints even after
the table is created.
Integrity constraints are used to ensure accuracy and consistency of data in a
relational database. Data integrity is handled in a relational database through the
concept of referential integrity.
There are many types of integrity constraints that play a role in referential integrity
(RI). These constraints include Primary Key, Foreign Key, Unique Constraints and
other constraints.
TYPES OF INTEGRITY CONSTRAINTS
1. Domain integrity: it means the definition of a valid set of values for an
attribute
2. Entity integrity constraint: This rule states that in any database relation value
of attribute of a primary key can’t be NULL
3. Referential integrity constraint: It states that if a foreign key exist in a
relation , then either the foreign key value must match a primary key value of
some tuple in its home relation or the foreign key must be NULL
The rules are:
 You can’t delete a record from primary table, if matching records exist in a
related field
 You can’t change a primary key value in the primary table, if the table hs
related records.
 You can’t enter a value in the foreign key field of the related table that
doen’t exist in the primary key of the primary table.
 However, you can enter a null value in the foreign key,specifying that the
records are unrelated.
4. Key constraints: A key constraints is a statement that a certain minimal
subset of the fields of a relation is a unique identifier for a tuple.
There are four types of key constraints
 Candidate key
 Super key
 Primary key
 Foreign key
Candidate key: A minimal super key is called a candidate key. An entity can have
more than one candidate key. A super key such that no proper subset is a super key
within a relation
Super key: An attribute or set of attributes that uniquely identifies a tuple within a
relation
Primary key: It is a key in a relational database that is a unique identifier for each
record
Foreign key: It is a key from another table that refers to a specific key usually the
primary key
Note: Concept of Relational database
Table and Relation
Table is a database concept, relation is a relational model concept
ROW (Tuple and Record)
A Row is a database concept, tuple is a relational model concept, a Record is a
little bit outdated term for a tuple or record
COLUMN and ATTRIBUTES
A column is a database concept; an attribute is a relational model concept
PRIMARY KEY and FOREIGN KEY
Primary key is the column (or set of columns) which values uniquely identify the
row. A foreign key is a column whose values refer to the primary key of another
table.
BASIC TERMS IN RELATIONAL DATABASE
1. Table: The data in RDBMS is stored in database objects called tables. The
table is a collection of related data entries and it consist of columns and
rows. Remember, a table is the most common and simplest form of data
storage in a relational database. Following is an example of a CUSTOMERS
table:
ID NAME AGE ADDRESS SALARY
1 Stephen 32 Lagos 2000.00
2 Bola 25 Abuja 1500.00
3 Emeka 23 Uyo 2000.00
4 Akon 25 Rivers 6500.00
5 Ayo 27 Benue 8500.00
6 John 22 Lagos 4500.00
7 Victor 24 Lagos 10000.00
 2. Field: Every table is broken up into smaller entities called fields.The fields
in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS, and
SALARY.

A field is a column in a table that is designed to maintain specific information

about every record in the table

WEEK 3

Enforcing data integrity in databases

Data integrity refers to the correctness and completeness of data within a
database. To enforce data integrity, you can constrain or restrict the data values
that users can insert, delete, or update in the database. For example, the integrity of
data in the pubs2 and pubs3databases requires that a book title in the titles table
must have a publisher in the publishers table. You cannot insert books that do not
have a valid publisher into titles, because it violates the data integrity
of pubs2 or pubs3.
Transact-SQL provides several mechanisms for integrity enforcement in a database
such as rules, defaults, indexes, and triggers. These mechanisms allow you to
maintain these types of data integrity:
1. Requirement – requires that a table column must contain a valid value in
every row; it cannot allow null values. The create table statement allows
you to restrict null values for a column.
2. Check or validity – limits or restricts the data values inserted into a table
column. You can use triggers or rules to enforce this type of integrity.
3. Uniqueness – no two table rows can have the same non-null values for one
or more table columns. You can use indexes to enforce this integrity.
4. Referential – data inserted into a table column must already have matching
data in another table column or another column in the same table. A single
table can have up to 192 references.
5. Consistency of data values in the database is another example of data
integrity.
As an alternative to using rules, defaults, indexes, and triggers, Transact-SQL
provides a series of integrity constraints as part of the create table statement
to enforce data integrity as defined by the SQL standards
WEEK 4

TOPIC: DATABASE SECURITY

Data security is the practice of keeping data protected from corruption and
unauthorized

access. The focus behind data security is to ensure privacy while protecting
personal or

corporate data.

It is a means of putting in place the different form of information security controls

to protect

database against compromise of their confidentiality, integrity and availability.

RISK ASSESSMENT

This will enable you to identify the risks you are faced with and what could happen
if valuable

data is lost through theft, malware infection or a system crash.

SECURING DATA

Since data can be compromised in many ways, the best security against misuse or
theft involves

a combination of technical measures, physical security and a well-educated staff.

You should

implement clearly defined polices into your infrastructure and effectively present
them to the

staff.

TYPES OF SECURITY CONTROL ON THE DATA

1. Access Control
Is the selective restriction of access to a place or other resource. The act of
accessing may mean

consuming, entering, or using. Permission to access a resource is called

authorization.

2. Auditing

Database auditing involves observing a database so as to be aware of the actions of

database

users. Database administrators and consultants often set up auditing for security
purposes, for

example, to ensure that those without the permission to access information do not
access it.

3. Authentication

Is the validation control that allows you to log into a system, email or blog account
etc. Once logged in, you have various privileges until logging out. Some systems
will cancel a session if

your machine has been idle for a certain amount of time, requiring that you prove

authentication once again to re-enter.You can log in using multiple factors such as
a password,

a smart card or even a fingerprint.

4. Encryption

This security mechanism uses mathematical scheme and algorithms to scramble

data into

unreadable text. It can only be decoded or decrypted by the party that possesses the
associated key.

5. Back Up

This is the process of making copy and archiving of computer data in the event of
data
losswhich is used to restore the original data.

6. Password

This is sequence of secret characters used to enable access to afile, program,

computer system

and other resources.

IMPORTANCE OF DATA SECURITY

Data security is critical for most business and even home computer users. Client
information,

payment information, personal files, bank account details- all this information can
be hard to

replace and potentially dangerous if it falls into the wrong hands. Data lost due to
disaster such

as a flood of fire is crushing, but losing it to hackers or a malware infection can

have much

greater consequences.

MAJOR THREATS TO DATA SECURITY

a. Accident can happen due to human error or software/ hardware error.

b. Hackers could steal vital information and fraud can easily be perpetrated.

c. Loss of data integrity.

d. Improper data access to personal or confidential data.

e. Loss of data availability through sabotage , a virus , or a worm.

INTEGRITY CONTROLS

BACKUPS
Is the process of copying and archiving of computer data so it may be used to
restore the

original after a data loss event.

Backupshave two distinct purposes. The primary purpose is to recover data after its
loss, be it

by data deletion or corruption. The secondary purpose of backups is to recover data

from an

earlier time, according to a user-defined data retention policy, typically configured

within a

backup application for how long copies of data are required. Backup is just one of
the disaster

recovery plans.

APPLICATION SECURITY

Application security is the use of software, hardware and procedural methods to

protect

application from external threats.

THE ROLE OF DATABASE ADMINISTRATOR IN DATA SECURITY

A database administrator (DB) is a person responsible for the installation,

configuration,

upgrade, administration, monitoring and maintenance of databases in an

organization. The role

includes the development and design of database strategies, system monitoring and
improving

database performance and capacity, and planning for future expansion

requirements. They may

also plan, co-ordinate an implement security measures to safeguard the database.

A database administrator’s responsibilities can include the following tasks:

3. Installing and upgrading the database server and application tools.

4. Allocating system storage and planning future storage requirements for the
database

system

5. Modifying the database structure, as necessary, from information given by

application

developers.

Enrolling users and maintaining system security.

5. 7. Ensuring compliance with database vendor license agreement.

6. 8. Controlling and monitoring user access to the database.
7. 9. Monitoring and optimizing the performance of the database.
8. 10. Planning for backup and recovery of database information.
9. 11. Maintaining archive data.
10.12. Backing and restoring databases.
11.13. Contacting database vendor for technical support.
12.14. Generating various reports by querying database as per need.
13.GENERAL EVALUATION
14.1. Define the roles of a database administrator in data security
15.2. Define backup and list its importance in data security
 WEEK 6

FILE ORGANISATION 1

File Organization

The term "file organization" refers to the way in which data are stored in a file and,

consequently, the method(s) by which it can be accessed.

File organization Terms

a.   Block: A block is the physical unit of transfer between the backing store and

the main memory.

b.  Bucket: A bucket is the logical unit of transfer between the backing store and

main memory.

c.   Hit: In processing a record that is required is called a hit.

Types of File Organization

There are mainly four types of file organization:

1.  Serial file organization

2.  Sequential file organization

3.  Index file organization

4.  Random file organization

Serial file organization

Serial files are stored in chronological order, that is as each record is received it is

stored in the next available storage position. In general it is only used on a serial

medium such as magnetic tape.

Sequential file organization

Sequential files are files whose records are sorted and stored in an ascending or

descending on a particular key field.

Index file organization

An index is an alphabetical list of names, subjects, etc., with references to the

places where they occur. Indexed Sequential file organization is logically the same

as sequential organization, but an index is built indicating the block containing the

record with a given value for the Key field.

Random file organization

A randomly organized file contains records arranged physically without regard to

the sequence of the primary key. Records are loaded to disk by establishing a

direct relationship between the Key of the record and its address on the file,

normally by use of a formula (or algorithm) that converts the primary Key to a

physical disk address. This relationship is also used for retrieval.

WEEK 8

TOPIC: FILE ORAGNIZATION

An Index is a copy of database table that has been reduced to certain fields and the

copy is

always in sorted form. The index also contains a pointer to the corresponding

record of the

actual table so that the fields not contained in the index can also be read. Index

contains a

value and a pointer to first record that contains data value.

A Database Index is a data structure that speeds up certain operation on a file. The

Operation

involves a search key which is the set of record files( in most cases a single field).

The elements

of an index are called data entries. Data entries can be actual data record. A given

file of data

records can have several indexes, each with different search keys as showed in the

table below.

Customer ID Name Address City State Zip

001 Mr Daniel 10,Bale str. Maryland Lagos 1011

002 Mrs Okon 5, Oju-ile Ota Ogun 1021

003 Mr David 26, Dalemo

str.
Ikeja Lagos 1023

The search engine searches for a value in table or file in two ways. The table scan

which is

sequential and index which is random.

Indexes are special lookup tables that the database engine uses to speed up data

retrieval. An

index in a database is similar to an index in the back of a book.

An index table or file consists of records called index entries. It is of the form

The search key field is used to sort the rows (in the index column) and the pointer

field (in index

column) indicates where the actual data in the table will be retrieved. When a table

has an

index it simply means the records in that table has been sorted in one way or the

other.

Indexes are automatically created when primary key and unique constraints are

defined on

table columns.

Search- key Pointer

Clustered index is an index whose sorting order determines the order of how the

rows/records

in a table are stored. There could be only one clustered index in a table because

there could

always be one way of arranging the records in a table at a given time. For example,

if you are

asked to arrange some tables in a room, you could arrange them in a round form,

row form or

packed them close together, only one way at a time. Clustered index also means

that related

values in a table are stored close to each other according to the order of the index.

1. CLUSTERED INDEX

A Clustered index is when a file is organized so that the ordering of data records is

the same as

or closes to the ordering of data entries. A clustered index can take place only if the

data

records are sorted on the search key field. For example, suppose that students

records are

sorted by age; an index on age that stores data entries in sorted order by age is a

clustered
index.

Indexes that maintain data entries in sorted order by search key use a collection of

index

entries, organized into a tree structure to guide searches for data entries. Thus,

clustered

indexes are relatively expensive to maintain when the file is updated, when data

entries are to

be moved across pages, and if records are identified by a combination of page id

and slot as is

often the case, all places in the database that point to a moved record must also be

updated to

point to the new location.These additional updates can be time consuming.

The table below illustrate a clustered index file:

Student ID Name Age

00231364OJ Olu Jacob 12

00241265AF Agu Faith 13

00251057AJ Abiola Joseph 13

00211362MS Mathew Stephen 14

00251302TB TjomasBintu 15

2. UNCLUSTERED INDEX
This an index whose sorting order does not determine the order of how the

rows/records in a

table are stored. This means that the search keys in the index column is sorted in

one order

while the actual records or rows are sorted in another order or are not sorted at all.

This is an index that is not clustered. A data file can contain several unclustered

index. For

example, supposing that students records are sorted by age; and if additional index

on gpa

field is included, it is called unclustered index.

DENSE INDEX

This is said to be dense if it contains (at least) one data entry for every search key

value that

appears in a record in the indexed file.

In a dense index, index record appears for every search key value in the file or

table. That is

every search key in the index column has a particular record it will point to in the

table or file.
For example,

From the figure above, we can see that each search key in the index has a particular

record that

it point to in the base table

SPARSE INDEX

In a sparse index, each search key does not have a corresponding record it point to

but may

point to a group of records in the base table. For example:

From the figure above, search keys such as 12121, 15151 do not have

corresponding records in

the index but you can search for them through 10101 key to retrieve their records

in the base

table.

A Sparse Index contains one entry for each page of records in the data file. The

index record

contains the search key and a pointer to the first data record with that search key

value. A

Sparse index must be clustered and it is smaller than a dense index.

PRIMARY AND SECONDARY INDEX

PRIMARY INDEX
Primary index is an index defined on a primary key column(s) of a relation with

unique

constraint which guarantee that the field will not contain duplicate values and

determine the

order of how the records are physically stored on the disk. Note that this is also

called clustered

index.

This is an index on a set of fields that includes the primary key. Primary index

contains records

that are usually clustered. A primary index is created for the primary key of a table.

SECONDARY INDEX

Secondary index is an index defined on a non-key field which may contain

duplicate values and

as such does not determine the order of how the records are physically stored on a

disk. It is

also called non-clustered index.

For example, in student database, student ID is used to look up for a student as the

key,

however, one might want to look up for a student using LastName by creating

secondary index
on that column.

10101

12121

15151

22222

32343

10101 OJO Maths 90000

12121 ANYAOGU DP 75000

15151 ROBERT ICT 65000

22222 ADEREMU Computer 60000

32343 TUNDE Music 50000

10101

22222

32343

10101 OJO Maths 90000

12121 ANYAOGU DP 75000

15151 ROBERT ICT 65000

22222 ADEREMU Computer 60000

32343 TUNDE Music 50000

Secondary index is an index that is not a primary index i.e. it does not include

primary key.

Secondary index can be created on non- key attribute. It contains duplicate data

entries.

A Unique index is an index in which the search key contains some candidate key.

EVALUATION

1. Distinguish between dense index and sparse index

2. Explain primary and secondary index

INDEXES USING COMPOSITE SEARCH KEYS

Composite search keys or concatenated keys are when the search key for an index

contain

several fields. For example, considering a collection of employee records with field

name, age

and salary stored in sorted order by name. if the search key is composite, an

equality query is

one in which each field in the search key is bound to a constant. For example, we

can ask to

retrieve all data entries with age = 20 and sal = 10, the hashed file organization

supports only
equality queries since a hash function identifies the bucket containing desired

records only if a

value is specified for each field in the search key.

The search key for an index can contain several fields, such keys are called

Composite Search

Keysor Concatenated Keys.

Range Queryis the one in which not all fields in the search key are bound to

constants. For

example, we can ask to retrieve all data entries with age = 20; this query implies

that any value

is acceptable for the sal _eld. Another example of a range query is when ask to

retrieve all data

entries with age < 30 and sal> 40

WEEK 9

TOPIC: PRESENTATION PACKAGE

Definition
A presentation program is a software package used to display information in the
form of a slide show. It has three major functions: an editor that allows text to be
inserted and formatted, a method for inserting and manipulating graphic images,
and a slide-show system to display the content.
Examples of presentation package
i. Microsoft PowerPoint
ii. Macromedia flash
 iii. Windows movie maker
iv. Open Office
v. Apple Keynote
vi. Open Office
vii. Impress
viii. Corel Presentations
ix. Adobe Persuasion
x. Flowboard
xi. Kingsoft Presentation
xii. Prezi.

Features of presentation package

·         Creation of Slides: A slide is a single screen of a presentation, and every
presentation is composed of several slides. Slides contain any mixture of text,
images, video, animations, links and sound
·         Animation: Animation effects allow the various elements on each slide to
appear after a certain amount of time or when a presenter presses a button.
·         Transitions: This is how the presentation software “moves” the display of one
slide to another. Transitions usually include dissolving from one slide to the next
or the current slide being moved in some way to show the next slide as though it
was underneath.
·       
Benefits of Presentation Package
i.          Easy to create colorful and attractive design/presentation
ii.         Easy to convey messages to the audience
iii.        Easy to present and maintain eye contact with large audience
iv.        Enhances the assimilation of information
v.         It makes the presentation interesting
vi.        It supports multimedia
vii.       To add) create slides effects
viii.      Useful for developing speaker interaction
ix.        It supports Object Linking and Embedding (OLE)
x.         It engages multiple learning styles
xi.        It improves audience focus
xii.       It supports easy way to organize ideas and information
xiii.      Presentation in the absence of the speaker.
Uses of Presentation Package
(1) As a slide show
(2) In delivering multimedia lectures
(3) In writing multimedia story book

WEEK 10
TOPIC: ENTERPRENEURSHIP