INTRODUCTION TO DATABASE

SYSTEM

Databases and Database Users

1
Basic Definitions
• Database is a place/container where all the data is
stored. But what is data?
• In a database, even a smallest piece of information
becomes data.
• For example, Student is a data, course is a data, and
Color is a data, height, weight, food everything is data.
In short, all the living and non-living objects in this
world is a data.

2
Why we need data?
• We need data so that we can perform various actions
on them.
• Say, we do not have any database and we want to
record what is the height and weight of a baby in a
year.
• if we need to change any information, we can
update/delete them. But all the data will be at one
place - Database.

3
What type of data is stored in database?
• In a database, we would be grouping only related data
together and storing them under one group name
called table.
• This helps in identifying which data is stored where
and under what name.
• It reduces the time to search for a particular data in a
whole database.
• For example, Student, Teacher, Class, Subject,
Employee, Department etc form individual tables.
4
Table

5
How do we determine which data is relevant to be put in a
particular database?

• It all depends on what database we are developing,

and what is the exact requirement/purpose of it.
• Say, we need to create College database. What could
college database contain?
• Do we store all information under one table - College?
Will database be quick in getting the data or updating?

6
How do we determine which data is relevant to be put in a
particular database?

• RDBMS is a program that guides us how to

create and maintain a database.
• It tells us how to divide related information into
different tables and inter-relate them so that we
can select/insert/update/delete all the related
data easily and efficiently.

7
Basic Definitions/ Terminologies
• Database:
– A collection of related data.
• Data:
– Known facts that can be recorded and have an implicit
meaning.
• Mini-world:
– Some part of the real world about which data is stored in a
database. For example, student grades and transcripts at a
university.

8
Basic Definitions/ Terminologies
• Database Management System (DBMS):
– A software package/ system to facilitate the creation
and maintenance of a computerized database.
• Database System:
– The DBMS software together with the data itself.
Sometimes, the applications are also included.

9
Terminologies Con’t…
• Enterprise
– an organization : A library, a bank, a university, etc.
• Entity
– Person, place, thing, or event (property of an entity)
– An "object" in the real world that we are interested in:
– The object student is an entity
• Attribute (Field)
– A character or group of characters (alphabetic or numeric), that
has a specific meaning. Eg. Name, age, telephone, grade, sex,
etc.

10
Terminologies Con’t…
• Record
– A logically connected set of one or more Attributes that
describe a person, place or thing. (Logically related data)
• File
– A collection of related records. For example, a file might
contain data about customers; or students of a certain
department in a university.

11
Terminologies
• Database
• Data
• Mini-world
• Database Management System (DBMS):
• Database System:
• Enterprise
• Entity
• Attribute (Field)
• Record
• File

12
Types of Databases and Database Applications

• Traditional Applications:
– Numeric and Textual Databases
• More Recent Applications:
– Multimedia Databases
– Geographic Information Systems (GIS)
– Data Warehouses
– Real-time and Active Databases
– Many other applications

13
 Types of Databases and Database Applications
• A database has the following implicit/ absolute
properties
– 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/ ordered collection of
data with some inherent meaning. A random assortment of
data cannot correctly be referred to as a database.

Miniworld: Student, Instructor, Course, …

UoD: dealing with some particular topic, or discussion, or talk bout 14
Types of Databases and Database Applications

• A database has the following implicit/ absolute

properties
– A database is designed, built, and populated with data for a
specific purpose. It has an intended group of users and
some concept for applications in which these users are
interested.

15
Types of Databases and Database Applications

• A 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.

16
Typical DBMS Functionality
• Define a particular database in terms of its data types, structures, and
constraints
• Construct or Load the initial database contents on a secondary storage
medium
• Manipulating the database:
– Retrieval: Querying, generating reports
– Modification: Insertions, deletions and updates to its content
– Accessing the database through Web applications
• Processing and Sharing by a set of concurrent users and application
programs – yet, keeping all data valid and consistent

17
Typical DBMS Functionality

• Defining a database involves specifying the data

types, structures, and constraints of the data to be sto
• The database definition or descriptive information is
also stored by the DBMS in the form of a database
catalog or dictionary; it is called meta-data.
• Constructing the database is the process of storing
the data on some storage medium that is controlled by
the DBMS

18
Types of Databases and Database Applications

• Manipulating a database includes functions such as

querying the database to retrieve specific data,
updating the database to reflect changes in the
miniworld, and generating reports from the data.
• Sharing a database allows multiple users and
programs to access the database simultaneously.
• An application program accesses the database by
sending queries or requests for data to the DBMS.

19
Typical DBMS Functionality
• Other features:
– Protection or Security measures to prevent unauthorized
access
– “Active” processing to take internal actions on data
– Presentation and Visualization of data
– Maintaining the database and associated programs over
the lifetime of the database application
• Called database, software, and system maintenance

20
Example of a Database (with a Conceptual Data Model)

• Mini-world for the example:

– Part of a UNIVERSITY environment.
• Some mini-world entities:
– STUDENTs
– COURSEs
– SECTIONs (of COURSEs)
– (academic) DEPARTMENTs
– INSTRUCTORs

21
Example of a Database (with a Conceptual Data Model)

• Some mini-world relationships:

– SECTIONs are of specific COURSEs
– STUDENTs take SECTIONs
– COURSEs have prerequisite COURSEs
– INSTRUCTORs teach SECTIONs
– COURSEs are offered by DEPARTMENTs
– STUDENTs major in DEPARTMENTs
• Note: The above entities and relationships are typically
expressed in a conceptual data model, such as the ENTITY-
RELATIONSHIP data model

22
Database system environment.

23
A database that stores student and course information.

Database manipulation
• Retrieve the transcript—a list of all courses and
grades—of ‘Smith’
• List the names of students who took the section
of the ‘Database’ course offered in fall 2008 and
their grades in that section
• List the prerequisites of the ‘Database’ course

Database updates include the following:

• Change the class of ‘Smith’ to sophomore
• Create a new section for the ‘Database’ course
for this semester
• Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’
section of last semester

24
Database Users
• Users may be divided into
– Those who actually use and control the database content,
and those who design, develop and maintain database
applications (called “Actors on the Scene”), and
– Those who design and develop the DBMS software and
related tools, and the computer systems operators (called
“Workers Behind the Scene”).

25
Database Users
• Actors on the scene

26
Database Users
• Actors on the scene
– Database administrators:
• Responsible for authorizing access to the database, for
coordinating and monitoring its use, acquiring software and
hardware resources, controlling its use and monitoring efficiency
of operations.
– Database Designers:
• Responsible to define the content, the structure, the constraints,
and functions or transactions against the database. They must
communicate with the end-users and understand their needs.

27
Categories of End-users
• Actors on the scene (continued)
– End-users: They use the data for queries, reports and
some of them update the database content. End-users can
be categorized into:
• Casual: access database occasionally when needed
• Parametric: they make up a large section of the end-user
population, in terms of a parameter.
– They use previously well-defined functions in the form of “canned
transactions” against the database.
– Examples are bank-tellers or reservation clerks who do this activity for an
entire shift of operations.

Canned: Kept, removed, preserved 28

Categories of End-users (continued)
• Sophisticated:
– These include business analysts, scientists, engineers, others thoroughly
familiar with the system capabilities.
– Many use tools in the form of software packages that work closely with the
stored database.
• Stand-alone:
– Mostly maintain personal databases using ready-to-use packaged
applications.
– An example is a tax program user that creates its own internal database.
– Another example is a user that maintains an address book

29
When not to use a DBMS
• Main inhibitors (costs) of using a DBMS:
– High initial investment and possible need for additional hardware.
– Overhead for providing generality, security, concurrency control, recovery,
and integrity functions.
• When a DBMS may be unnecessary:
– If the database and applications are simple, well defined, and not expected
to change.
– If there are tight real-time requirements that may not be met because of
DBMS overhead.
– If access to data by multiple users is not required.

30
When not to use a DBMS
• When no DBMS may suffice:
– If the database system is not able to handle the complexity
of data because of modeling limitations
– If the database users need special operations not
supported by the DBMS.

31
Characteristics of a Database
• Should be able to store all kinds of data that exists in
this real world.
• Should be able to relate the entities / tables in the
database by means of a relation. i.e.; any two tables
should be related.
• Data and application should be isolated. Because
database is a system which gives the platform to store
the data.

32
Characteristics of a Database
• There should not be any duplication of data in the
database
• DBMS has a strong query language
• Multiple users should be able to access the same
database, without affecting the other user.
• It supports multiple views to the user, depending on
his role

33
 Characteristics of a Database
• Database should also provide security
• Database should also support ACID property
– i.e.; while performing any transactions like insert, update
and delete, database makes sure that the real purpose of
the data is not lost
– For example, if a student’s address is updated, then it
should make sure that there is no duplicate data is created
nor there is any data mismatch for that student.

ACID (Atomicity, Consistency, Isolation, Durability) is

a set of properties of database transactions intended to 34
guarantee validity
Review Questions – Group Assignment
• Define the following terms: data, database, DBMS,
database system, database catalog, program-data
independence, user view, DBA, end user, canned
transaction, deductive database system, persistent
object, meta-data, and transaction-processing
application.
• What four main types of actions involve databases?
Briefly discuss each.

35
Review Questions – Group Assignment
• Discuss the main characteristics of the database
approach and how it differs from traditional file
systems.
• What are the responsibilities of the DBA and the
database designers?
• What are the different types of database end users?
Discuss the main activities of each.

36
Review Questions – Group Assignment
• Discuss the capabilities that should be provided by a
DBMS.
• Discuss the differences between database systems
and information retrieval systems.

37
Exercises -Individual
• Identify some informal queries and
update operations that you would expect
to apply to the database shown in the
Figure
• What is the difference between
controlled and uncontrolled
redundancy? Illustrate with examples.
• Specify all the relationships among the
records of the database shown in the
Figure

38
Exercises -Individual
• Give some additional views that may be
needed by other user groups for the
database shown in the Figure
• Cite some examples of integrity
constraints that you think can apply to
the database shown in the Figure
• Give examples of systems in which it
may make sense to use traditional file
processing instead of a database
approach.

39
Exercises -Individual
• Consider the Figure
– If the name of the ‘CS’ (Computer Science)
Department changes to ‘CSSE’ (Computer
Science and Software Engineering)
Department and the corresponding prefix for
the course number also changes, identify the
columns in the database that would need to
be updated.
– Can you restructure the columns in the
COURSE, SECTION, and PREREQUISITE
tables so that only one column will need to
be updated?

40
Lab - SQL Create Database
• SQL Create Database
– Before creating tables and other database objects, we
need a database to be created first
– Let us develop a simple database for library.
– This is a single container where we can have multiple
users, and their DB objects
– CREATE DATABASE library;

41
 Lab – SQL Create Database
– One should have admin privilege to create a DB and see what all
DBs are created. In addition, a DB cannot be created inside another
DB
– Above query simply creates a library database with all its parameter
values set to default
– It will have multiple parameter files to be set up while creating a DB.
– These files correspond to initialization parameter, configuration
files, tablespace details etc. these files will take default values if
they are not specified while creating a DB.

initialization parameter -- views, constraints, indexes, packages, procedures, functions 42

Lab – SQL Create Database
– When DB is created it will have number of default users
and they will be locked at the beginning.
– If we need to login to DB and need to use / create objects
inside them, then we need to unlock these users.
– This can be done while creating DB itself or latter by
altering the user.
– The two main default users of DB is SYS and SYSTEM. Its
password can be changed while creating DB as follows:

43
Lab – SQL Create Database
– CREATE DATABASE library
USER SYS IDENTIFIED BY sys_pwd
USER SYSTEM IDENTIFIED BY system_pwd;

– This will change their default password to the passwords

mentioned above. We can even change passwords of other
users while creating DB in the same way.

44
Lab – Drop Database
• Drop Database
– If database created is wrong by any chance or we do not
want it any more, then we can delete the database using
DROP command.
– DROP DATABASE library;
– Above query will drop the database whose name is
"library". It will also delete all the tables, views, constraints,
indexes, packages, procedures, functions etc from the
database.

45
Lab – Drop Database
• We can create any number of databases in a system
and we can use any one of them at a time depending
upon our need.
• In order to see the databases present in the system,
we use SHOW command. But one should have admin
access to issue this command.
• It will list all the databases present in the system.

46
Lab - Select Database
• SHOW DATABASES;
• Above query will display all the databases our server has as
shown below:
• Please note that these are not the
users. They are all different
databases present in the system
• Each database will have different users,
who have access to login to it.

47
Lab - Select Database
• We cannot access all database at the same time. If
we need to access any particular database, then we
have issue command like below:
• USE Library; -- library database will become active
and it will accept its credentials to login
• USE Faculty; -- Faculty database will be active.

48
Lab - Select Database
• When USE command is executed, it activates that
particular database, and user can login to the
database with their credentials.
• They can access all the DB objects for which they
have access.

49