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
