Basic Definitions

• Database: A collection of related data.

• Data: Known facts that can be recorded and have an
implicit meaning.
• 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.

Slide 1-1
Difference Between File and DBMS Operations
Difference Between File and DBMS
File Management System Database Management System

File System is a general, easy-to-use system to Database management system is used when security
store general files which require less security constraints are high.
and constraints.

Data Redundancy is more in file management Data Redundancy is less in database management
system. system.

Data Inconsistency is more in file system. Data Inconsistency is less in database management
system.

Centralisation is hard to get when it comes to Centralisation is achieved in Database Management

File Management System. System.

User locates the physical address of the files to In Database Management System, user is unaware
access data in File Management System. of physical address where data is stored.
Security is low in File Management System. Security is high in Database Management System.

File Management System stores unstructured Database Management System stores structured
data as isolated data files/entities. data which have well defined constraints and
interrelation.
Advantages of DBMS
1. Controlling Redundancy:
2. Integrity can be enforced: Integrity of data means that data in database
is always accurate
3. Inconsistency can be avoided : When the same data is duplicated and
changes are made at one site, which is not propagated to the other site, it
gives rise to inconsistency and the two entries regarding the same data will
not agree.
4. Data can be shared
5. Standards can be enforced
6. Restricting unauthorized access
7. Providing Backup and Recovery
8. Data Model can be developed
9. Concurrency Control
Disadvantages of DBMS
• 1. Complexity
• 2. Size
• 3. Performance
• 4. Higher impact of a failure
• 5. Cost of DBMS
• 6. Additional Hardware costs
Types of Databases
Database Application Architectures

Old Modern
Database Application Architectures
Three-Tier Architecture
• A 3-tier application is an application program that
is organized into three major parts, comprising of
data access layer tier at the bottom, the
application tier (business logic) in the middle and
the client tier (Presentation) at the top and each
tier is distributed to a different place or places in a
network.
1. Presentation Layer (UI)
2. Business Logic Layer
3. Data Access Layer
Three-Tier Architecture
• User (Presentation) Tier − End-users operate on this tier and they know
nothing about any existence of the database beyond this layer. At this
layer, multiple views of the database can be provided by the application.
All views are generated by applications that reside in the application tier.
• Application (Middle/Business) Tier − At this tier reside the application
server and the programs that access the database. For a user, this
application tier presents an abstracted view of the database. End-users
are unaware of any existence of the database beyond the application. At
the other end, the database tier is not aware of any other user beyond
the application tier. Hence, the application layer sits in the middle and
acts as a mediator between the end-user and the database.
• Database (Data) Tier − At this tier, the database resides along with its
query processing languages. We also have the relations that define the
data and their constraints at this level.
Three-Tier Architecture
• Presentation Layer (UI)
– This layer presents data to the user and optionally permits data
manipulation and data entry, also this layer requests the data form Business
layer.
– This layer accomplished through use of Dynamic HTML and client-side data
sources and data cursors.
• Business Logic Layer
– The business logic acts as the server for client requests from workstations.
– It acts according Business rules fetch or insert data through the Data Layer.
– In turn, it determines what data is needed (and where it is located) and acts
as a client in relation to a third tier of programming that might be located
on a local or mainframe computer.
– Because these middle-tier components are not tied to a specific client, they
can be used by all applications and can be moved to different locations, as
response time and other rules require.
• Data Access Layer
Three-Tier Architecture
• Presentation Layer (UI)
• Business Logic Layer
• Data Access Layer
– The third tier of the 3-tier system is made up of the
DBMS that provides all of the data for the above
two layers.
– This is the actual DBMS access layer.
– Avoiding dependencies on the storage mechanisms
allows for updates or changes without the
application tier clients being affected by or even
aware of the change.
Three Level Architecture of DBMS
Three Level Architecture of DBMS
• Physical Level:
– At physical level, the information about location of
database objects in data store is kept.
– Various users are DBMS are unaware about the
locations of these objects.
• Conceptual (Logical) Level
– Data is represented in the form of various database
tables.
– For Example, STUDENT database may contain STUDENT
and COURSE tables which will be visible to users but
users are unaware about their storage.
Three Level Architecture of DBMS
• Physical Level:
• Conceptual (Logical) Level:
• External (View) Level:
– specifies a view of the data in terms of conceptual level
tables.
– Each external level view is used to cater the needs of a
particular category of users.
– For Example, FACULTY of a university is interested in
looking course details of students, STUDENTS are
interested in looking all details related to academics,
accounts, courses and hostel details as well.
Data Independence
• Data independence means change of data at
one level should not affect another level. Two
types of data independence are required in
this architecture:
– Physical Data Independence:
– Conceptual Data Independence:
Data Independence
• Physical Data Independence:
– Any change in physical location of tables and
indexes should not affect conceptual level or
external view of data.
– This data independence is easy to achieve and
implemented by most of the DBMS.
• Conceptual Data Independence
Data Independence
• Physical Data Independence
• Conceptual Data Independence:
– The data at conceptual level schema and external level
schema must be independent.
– This means, change in conceptual schema should not affect
external schema.
– Adding or deleting attributes of a table should not affect
the user’s view of table.
– But this type of independence is difficult to achieve as
compared to physical data independence because the
changes in conceptual schema are reflected in user’s view.
Phases of database design
• Database designing for a real world application starts from
capturing the requirements to physical implementation using
DBMS software which consists of following steps
• Conceptual Design: The requirements of database are
captured using high level conceptual data model. For
Example, ER model is used for conceptual design of database.
• Logical Design: Logical Design represents data in the form of
relational model. ER diagram produced in conceptual design
phase is used to convert the data into Relational Model.
• Physical Design: In physical design, data in relational model is
implemented using commercial DBMS like Oracle, DB2.
Phases of database design
What Is a Data Dictionary?
• Contains information about the structures in the
database
• Also called
– System Catalog
• Strictly speaking this is what Oracle has
– Meta Data
• Generic term, for data about data
– Data Repository
• Synonym for Data Dictionary used to imply stand-alone systems
• According to Codd an RDB MUST use tables for its
Data Dictionary
Database Users
• Database administrators: responsible for authorizing access to the
database, for co-ordinating 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.
• End-users: they use the data for queries, reports and some of them
actually update the database content.
– Naïve or Parametric : they make up a large section of the end-user
population. 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.
– 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.
Slide 1-25
Dr. Codd
CSC 240 (Blum) 26
12 Rules By Dr. Codd
• Rule 1: Information Rule
– The data stored in a database, may it be user data or metadata, must be
a value of some table cell. Everything in a database must be stored in a
table format.
• Rule 2: Guaranteed Access Rule
– Every single data element (value) is guaranteed to be accessible logically
with a combination of table-name, primary-key (row value), and
attribute-name (column value). No other means, such as pointers, can
be used to access data.
• Rule 3: Systematic Treatment of NULL Values
– The NULL values in a database must be given a systematic and uniform
treatment. This is a very important rule because a NULL can be
interpreted as one the following − data is missing, data is not known, or
data is not applicable.
12 Rules By Dr. Codd
• Rule 4: Active Online Catalog
– The structure description of the entire database must be stored in
an online catalog, known as data dictionary, which can be accessed
by authorized users. Users can use the same query language to
access the catalog which they use to access the database itself.
• Rule 5: Comprehensive Data Sub-Language Rule
– A database can only be accessed using a language having linear
syntax that supports data definition, data manipulation, and
transaction management operations. This language can be used
directly or by means of some application. If the database allows
access to data without any help of this language, then it is
considered as a violation.
• Rule 6: View Updating Rule
– All the views of a database, which can theoretically be updated,
must also be updatable by the system.
12 Rules By Dr. Codd
• Rule 7: High-Level Insert, Update, and Delete Rule
– A database must support high-level insertion, updation, and deletion.
This must not be limited to a single row, that is, it must also support
union, intersection and minus operations to yield sets of data records.
• Rule 8: Physical Data Independence
– The data stored in a database must be independent of the
applications that access the database. Any change in the physical
structure of a database must not have any impact on how the data is
being accessed by external applications.
• Rule 9: Logical Data Independence
– The logical data in a database must be independent of its user’s view
(application). Any change in logical data must not affect the
applications using it. For example, if two tables are merged or one is
split into two different tables, there should be no impact or change
on the user application. This is one of the most difficult rule to apply.
12 Rules By Dr. Codd
• Rule 10: Integrity Independence
– A database must be independent of the application that uses it.
All its integrity constraints can be independently modified
without the need of any change in the application. This rule
makes a database independent of the front-end application and
its interface.
• Rule 11: Distribution Independence
– The end-user must not be able to see that the data is distributed
over various locations. Users should always get the impression
that the data is located at one site only. This rule has been
regarded as the foundation of distributed database systems.
• Rule 12: Non-Subversion Rule
– If a system has an interface that provides access to low-level
records, then the interface must not be able to subvert the
DBMS Vs RDBMS

DBMS RDBMS
Relationship between two files or tables Relationship between two tables or files
maintain programmatically can be specified at the time of table
creation

DBMS may satisfy less than 7 or 8 rules of 1. RDBMS usually satisfy more than 7 or 8
E.F. Codd rules of E.F. Codd

31
Types of Databases
• Hierarchical Databases
• Network Databases
• Object-Oriented Databases
• Relational Databases
• Cloud Databases
Hierarchical Databases

• Notice that due to such a structure, hierarchical

databases are not easily scalable; the addition of
data elements requires a lengthy traversal through
the database.
Network Databases

• The child records are given the freedom to associate with multiple parent records.
• As a result, a network or net of database files linked with multiple threads is
observed.
• Notice how the Student, Faculty, and Resources elements each have two parent
records, which are Departments and Clubs.
Object-Oriented Databases

• Information stored in a database is capable of being represented as an

object that responds as an instance of the database model.
• Therefore, the object can be referenced and called without any
difficulty
Relational Databases

• Note that all data is tabulated in this model. Therefore, every row of
data in the database is linked with another row using a primary key.
• Similarly, every table is linked with another table using a foreign key.
Database Schema
• A database schema is the skeleton structure that represents the
logical view of the entire database.
• It defines how the data is organized and how the relations
among them are associated.
• It formulates all the constraints that are to be applied on the
data.
• A database schema defines its entities and the relationship
among them.
• It contains a descriptive detail of the database, which can be
depicted by means of schema diagrams.
• It’s the database designers who design the schema to help
programmers understand the database and make it useful.
Database Schema
Database Schema
• A database schema can be divided broadly into two
categories −
• Physical Database Schema − This schema pertains
to the actual storage of data and its form of storage
like files, indices, etc. It defines how the data will be
stored in a secondary storage.
• Logical Database Schema − This schema defines all
the logical constraints that need to be applied on
the data stored. It defines tables, views, and
integrity constraints.
Database Instance
• A database instance is a state of operational
database with data at any given time.
• It contains a snapshot of the database.
• Database instances tend to change with time.
• A DBMS ensures that its every instance (state)
is in a valid state, by diligently following all the
validations, constraints, and conditions that
the database designers have imposed.
DBMS Languages: DDL
• In many one language, called the data definition
language (DDL), is used by the DBA and by
database designers to define both schemas.
• The DBMS will have a DDL compiler whose
function is to process DDL statements in order to
identify descriptions of the schema constructs
and to store the schema description in the DBMS
catalog.
DBMS Languages: DML
• Once the database schemas are compiled and the database
is populated with data, users must have some means to
manipulate the database.

• Typical manipulations include retrieval, insertion, deletion,

and modification of the data.

• The DBMS pro- vides a set of operations or a language

called the data manipulation language (DML) for these
purposes.
•
Interfaces in DBMS
• A database management system (DBMS) interface is a
user interface which allows for the ability to input
queries to a database without using the query language
itself.
• User-friendly interfaces provide by DBMS may include
the following:
• Menu-Based Interfaces for Web Clients or Browsing
• Forms-Based Interfaces
• Graphical User Interface
• Natural language Interfaces
• Speech Input and Output
Menu-Based Interfaces for Web Clients or
Browsing
• These interfaces present the user with lists of
options (called menus) that lead the user
through the formation of a request.
• Basic advantage of using menus is that they
removes the tension of remembering specific
commands and syntax of any query language,
rather than query is basically composed step
by step by collecting or picking options from a
menu that is basically shown by the system.
Forms-Based Interfaces
• A forms-based interface displays a form to each
user.
• Users can fill out all of the form entries to insert
a new data, or they can fill out only certain
entries, in which case the DBMS will redeem
same type of data for other remaining entries.
• This type of forms are usually designed or
created and programmed for the users that
have no expertise in operating system.
Graphical User Interface
• A GUI typically displays a schema to the user
in diagrammatic form.
• In many cases, GUI’s utilize both menus and
forms.
• Most GUIs use a pointing device such as
mouse, to pick certain part of the displayed
schema diagram.
Natural language Interfaces
• These interfaces accept request written in English or some
other language and attempt to understand them.
• The natural language interface refers to the words in its schema
as well as to the set of standard words in a dictionary to
interpret the request.
• If the interpretation is successful, the interface generates a high-
level query corresponding to the natural language and submits
it to the DBMS for processing, otherwise a dialogue is started
with the user to clarify any provided condition or request.
• The main disadvantage with this is that the capabilities of this
type of interfaces are not that much advance.
Speech Input and Output
• There is an limited use of speech say it for a query or
an answer to a question or being a result of a request
it is becoming commonplace Applications with
limited vocabularies such as inquiries for telephone
directory, flight arrival/departure, and bank account
information are allowed speech for input and output
to enable ordinary folks to access this information.
• The Speech input is detected using a predefined
words and used to set up the parameters that are
supplied to the queries.
Thank You