M

UNIVERSITY FOR DEVELOPMENT STUDIES

DEPARTMENT OF REAL ESTATE AND LAND MANAGEMENT

INFORMATION COMMUNICATION TECHNOLOGY II

Database systems, concepts, architecture and relations

Course instructor: (Ir) Appau Williams Miller

M LECTURE CONTENT

• Concepts of Database

• Advantages and disadvantages of database

• Database users

• Database administrators

• Relational database model

Course instructor: (Ir) Appau Williams Miller

M CONCEPTS OF DATABASES

A database A very large, well-structured and integrated collection of data that serves
the needs of multiple users within one or more organizations

Data are known facts that can be recorded and that have implicit meaning.

Implicit properties of databases

■ A database represents some aspect of the real world, sometimes called the miniworld
or the universe of discourse (UoD). Changes to the miniworld are reflected in the
database.
■ A database is a logically coherent collection of data with some inherent meaning. A
random assortment of data cannot correctly be referred to as a database.
■ A database is designed, built, and populated with data for a specific purpose. It has
an intended group of users and some preconceived applications in which these users
are interested.

Database management system (DBMS) is a collection of programs that enables

users to create and maintain a database. The DBMS is a general-purpose software
system that facilitates the processes of defining, constructing, manipulating, and
sharing databases among various users and applications

Course instructor: (Ir) Appau Williams Miller

M DATABASE CONCEPTS

Data abstraction generally refers to the suppression of details of data organization and storage,
and the highlighting of the essential features for an improved understanding of data
A data model: a collection of concepts that can be used to describe the structure of a
database—provides the necessary means to achieve this abstraction. By structure of a database
we mean the data types, relationships, and constraints that apply to the data
An attribute represents some property of interest that further describes an entity, such as the
employee’s name or salary

Database schema
The description of a database is called the database schema, which is specified during database
design and is not expected to change frequently

Course instructor: (Ir) Appau Williams Miller

M
DATABASE CONCEPTS

Diagram showing attributes and tuples

Course instructor: (Ir) Appau Williams Miller

 DATABASE CONCEPTS
M
Types of Schema
• The physical schema describes the database design at the physical level, while the
• logical schema describes the database design at the logical level. A database may also have
several schemas at the view level, sometimes called subschemas, that describe different
views of the database.
An instance
• The collection of information stored in the database at a particular moment is called an
instance of the database
Data manipulation is
• The retrieval of information stored in the database
• The insertion of new information into the database
• The deletion of information from the database
• The modification of information stored in the database

Course instructor: (Ir) Appau Williams Miller

M DATABASE CONCEPTS

Queries
Query – primary mechanism for retrieving information from a database,
consists of questions presented to the database in a predefined format – an
expression stored in a database having a unique name

Types of queries:
– Select query
– Action queries (Make-Table, Append, Update, Delete)
– Crosstab query

Example of query
select customer.customer-name
from customer
where customer.customer-id = 192-83-7465

Transaction Precisely formulated request to make changes to data in the

database (may include data extraction).
Query and Transaction can be viewed as a high-level computer program

Course instructor: (Ir) Appau Williams Miller

M DATABASE CONCEPTS

Tuple selection is the retrieval of the tuples specified by a given condition. We

are selecting a subset of the data present

Attribute projection is the retrieval of indicated attributes from all tuples in

the relation

Course instructor: (Ir) Appau Williams Miller

 DATABASE USERS
M
• Naive users are unsophisticated users who interact with the system by invoking one of the
application programs that have been written previously. The typical user interface for naive
users is a forms interface, where the user can fill in appropriate fields of the form. Naive
users may also simply read reports generated from the database

• Application programmers are computer professionals who write application programs.

Application programmers can choose from many tools to develop user interfaces

• Sophisticated users interact with the system without writing programs. Instead, they form
their requests in a database query language. They submit each such query to a query
processor, whose function is to break down DML statements into instructions that the storage
manager understands. Analysts who submit queries to explore data in the database fall in this
category.

• Online analytical processing (OLAP) tools simplify analysts’ tasks by letting them view
summaries of data in different ways

• Specialized users – write specialized database applications that do not fit into the traditional
data processing framework

Course instructor: (Ir) Appau Williams Miller

 DATABASE ADMINISTRATORS
M
• Schema definition. The DBA creates the original database schema by executing a set of data
definition statements in the DDL. Storage structure and access-method definition.

• Schema and physical-organization modification. The DBA carries out changes to the
schema and physical organization to reflect the changing needs of the organization, or to alter
the physical organization to improve performance.

• Granting of authorization for data access. By granting different types of authorization, the
database administrator can regulate which parts of the database various users can access. The
authorization information is kept in a special system structure that the database system
consults whenever someone attempts to access the data in the system.
•
• Routine maintenance. Examples of the database administrator’s routine maintenance
activities are: Periodically backing up the database, either onto tapes or onto remote servers,
to prevent loss of data in case of disasters such as flooding. Ensuring that enough free disk
space is available for normal operations, and upgrading disk space as required.

Course instructor: (Ir) Appau Williams Miller

 TRANSACTION MANAGR AND STORAGE MANAGERS
M
• Transaction Management
• A transaction is a collection of operations that performs a single logical function in a
database application.
• Transaction-management component ensures that the database remains in a consistent
(correct) state despite system failures (e.g., power failures and operating system crashes) and
transaction failures.
• Concurrency-control manager controls the interaction among the concurrent transactions,
to ensure the consistency of the database
• Storage Management
• Storage manager is a program module that provides the interface between the low-level
data stored in the database and the application programs and queries submitted to the system.
• The storage manager is responsible to the following tasks: interaction with the file manager
efficient storing, retrieving and updating of data

Course instructor: (Ir) Appau Williams Miller

M
ADVANTAGES OF USING DBMS

• Controlling Redundancy : redundancy in storing the same data multiple times

leads to several problems. It leads to duplication of effort and wastage of storage
space is when the same data is stored repeatedly.
• Restricting Unauthorized Access
• Providing Backup and Recovery
• Flexibility
• Availability of Up-to-Date Information

Course instructor: (Ir) Appau Williams Miller

 DISADVANTAGES OF DBMS
M
• Danger of a Overkill: For small and simple applications for single users a database system is
often not advisable.

• Complexity: A database system creates additional complexity and requirements. The supply
and operation of a database management system with several users and databases is quite
costly and demanding.

• Qualified Personnel: The professional operation of a database system requires appropriately

trained staff. Without a qualified database administrator nothing will work for long.

• Costs: Through the use of a database system new costs are generated for the system itself but
also for additional hardware and the more complex handling of the system.

• Lower Efficiency: A database system is a multi-use software which is often less efficient
than specialised software which is produced and optimised exactly for one problem.

Course instructor: (Ir) Appau Williams Miller

M

RELATIONAL DATABASE

Course instructor: (Ir) Appau Williams Miller

M RELATIONAL DATABASE

RELATIONAL DATABASE

The relational model represents the database as a collection of relations.

Informally, each relation resembles a table of values or, to some extent, a flat
file of records

Each row in the table represents a collection of related data values. A row
represents a fact that typically corresponds to a real-world entity or relationship.
The table name and column names are used to help to interpret the meaning of
the values in each row

Course instructor: (Ir) Appau Williams Miller

 BASIC DATA TYPES
• Numeric data types
 Integer numbers: INT, INTEGER, SMALLINT, BIGINT
 Floating-point (real)
 numbers: REAL, DOUBLE , FLOAT
 Fixed-point numbers: DECIMAL(n,m), DEC(n,m), NUMERIC(n,m), NUM(n,m)

• Character-string data types

 Fixed length: CHAR(n), CHARACTER(n)
 Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n), LONG
VARCHAR

• Large object data types

 Characters: CLOB, CHAR LARGE OBJECT , CHARACTER LARGE OBJECT
 Bits: BLOB, BINARY LARGE OBJECT

• Boolean data type

 Values of TRUE or FALSE or NULL

• DATE data type

 Ten positions
 Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD

Course instructor: (Ir) Appau Williams Miller

 CONSTRAINTS IN RELATIONAL DATABASE MODEL

PRIMARY KEY constraint is a column or set of columns that uniquely identifies each row
in a table.

 No primary-key value can appear in more than one row in the table.
 To satisfy a PRIMARY KEY constraint, both of the following conditions must be true:
 No column that is part of the primary key can contain a null.
 A table can have only one primary key

In a CREATE TABLE statement, the column-level PRIMARY KEY constraint syntax is

stated:

CREATE TABLE clients (client_number NUMBER(4) CONSTRAINT

client_client_num_pk

Note that the column-level simply refers to the area in the CREATE TABLE statement where
the columns are defined. The table level refers to the last lines in the statement below where
the individual columns are defined

Course instructor: (Ir) Appau Williams Miller

 CONSTRAINTS IN RELATIONAL DATABASE MODEL

FOREIGN KEY Constraint Syntax: The syntax for defining a FOREIGN KEY

constraint requires a reference to the table and column in the parent table

FOREIGN KEY

 Default operation: reject update on violation

 Attach referential triggered action clause in case referenced tuple is deleted

 Options include SET NULL, CASCADE, and SET DEFAULT

 Foreign key declaration must refer to a table already created

Course instructor: (Ir) Appau Williams Miller

 BASIC SQL STATEMENTS

All retrievals use SELECT statement:

SELECT <return list>
FROM <table list>
WHERE <condition> ]
where <return list> is a list of expressions or column names whose values are
to be retrieved by the query
<table list> is a list of relation names required to process the query
<condition> is a Boolean expression that identifies the tuples to be retrieved
by the query

Example SELECT title, year, genre FROM Film WHERE director = 'Steven
Spielberg' AND year > 1990

Course instructor: (Ir) Appau Williams Miller

EXAMPLE OF SQL OPERATIONS OF A COMPANYS RECORDS

Course instructor: (Ir) Appau Williams Miller

EXAMPLE OF SQL OPERATIONS OF A COMPANYS RECORDS

Course instructor: (Ir) Appau Williams Miller

EXAMPLE OF SQL STATEMENTS

Course instructor: (Ir) Appau Williams Miller