Chapter 2 Fundamentals of Database Systems.pdf

Chapter 2 Fundamentals of Database Systems.pdf

1. MIT7103
Advanced Database Design and Management
Course Credit Unit: 4


By: Businge Phelix Mbabazi Abwooli
Associate Professor of Information Technology
PhDMIS, MIS, BCI, MCSA, CCNA, HCIA, HCIA
[phone]
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 1

2. Database Management Systems:
What to remember that a Database management system is software of
collection of small programs to perform certain operation on data and manage
the data.

Two basic operations performed by the DBMS are:
Management of Data in the Database
Management of Users associated with the database.

Management of the data means to specify that how data will be stored,
structured and accessed in the database.
Management of database users means to manage the users in such a way that
they can perform any desired operations on the database. DBMS also ensures
that a user can not perform any operation for which he is not allowed. And also
an authorized user is not allowed to perform any action which is restricted to
that user.

In General DBMS is a collection of Programs performing all necessary actions
associated
to a database

2
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607

3. Remember Database User
3
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607

4. Typical Components of a Database Environment:
Role of database users.


4
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607

5. Database is used to store data and DBMS uses
mechanisms to get data from the database
Application programs talk to DBMS and ask for the
data required

5
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607

6. Database designers design (for large organizations) the database
and install the DBMS for use by the users of the database in any
specific organization.
6
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607

7. Once Database has been installed and is functioning properly in a production
environment of an organization the Database Administrator takes over the
charge and performs specific DBA related activities.
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 7

8. Database administrator can interact with the database designer during database design phase so
that he has a clear idea of the database structure for easy reference in future.

1. This helps DBA perform different tasks related to the database structure.
2. DBA also interacts with the application programmers during the application development process
and provides his services for better design of applications.
3. End users also interact with the system using application programs and other tools as specified in
the description above.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 8

9. Advantages of a DBMS
Data Sharing
The data for different applications or subsystems is placed at
the same place. This introduces the major benefit of data
sharing. That is, data that is common among different
applications need not to be stored repeatedly.
Data Independence
Data and programs are independent of each other, so change
is once has no or minimum effect on other. Data and its
structure is stored in the database where as application
programs manipulating this data are stored separately, the
change in one does not unnecessarily effect other.
Controlled Redundancy
Means that we do not need to duplicate data unnecessarily;
we do duplicate data in the databases, however, this
duplication is deliberate and controlled.
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 9

10. Advantages of a DBMS Conti…

Better Data Integrity
Very important feature; means the validity of the data being entered in the
database.
Since the data is being placed at a central place and being managed by the
DBMS, so it provides a very conducive to check or ensure that the data
being entered into the database is actually valid. Integrity of data is very
important, since all the processing and the information produced in
return are based on the data. Now if the data entered is not valid, how can
we be sure that the processing in the database is correct and the results or
the information produced is valid? The businesses make decisions on the
basis of information produced from the database and the wrong
information leads to wrong decisions, and business collapse. In the
database system environment, DBMS provides many features to ensure
the data integrity, hence provides more reliable data processing
environment.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 10

11. Advantages of a DBMS Conti…

Data Consistency:
Data consistency means that the changes made to different
occurrence of data should be controlled and managed in such a
way that all the occurrences have same value for any specific data
item. Data inconsistency leads to a number of problems,
including loss of Information and incorrect results. In database
approach it is controlled because data is shared and consistency
is controlled and maintained.

Better Data Security:
All application programs access data through DBMS, So DBMS
can very efficiently check that which user is performing which
action and accessing which part of data , So A DBMS is the most
effectively control and maintain security of Data stored in a
database.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 11

12. Advantages of a DBMS Conti…

Faster Application Development:
The database environment allows us faster application development because of its
many reasons. As we know that database is designed with the factor of future
development in mind So whenever we have to build a new application to
meet the growing needs of the computerized environment, it may be easy
due to the following reason:
The data needed for the new application already resides in the database
The data might not already reside in the database but it could be derived from the
data present in the database
Thus we can say that, to develop a new application for an existing database system
less effort is required in terms of the system and database design.

Economy of Scale:
Databases and database systems are designed to share data stored in one location
for many different purposes, So it needs not be stored as many number of
times in different forms as it is used, for example the data used by Admission
Department of any education institution can be used to maintain the
attendance record of the students as well as the examination records of the
students. So it saves us lots of efforts and finances providing economy of
scale.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 12

13. Advantages of a DBMS Conti…

Better Concurrency Control:
Concurrency means the access of database form as number of points simultaneously.
Concurrency control means to access the database in such a way that all the data accesses
are completed correctly and transparently. One example of controlled concurrency is the use
of ATM Machine for withdrawal of money (cash). All ATM machines of a bank are
interconnected to a central database system worldwide, so that a user can access its
account from anywhere in the world and can get cash from any ATM terminal. As
there are thousands of ATM terminal across the world for a specific bank so as a result
thousands of user process and access the bank’s database. All this process is managed
concurrently using the database systems and is done in such an efficient manner that
no two user face any delay in the processing of their requests.

Better Backup and Recovery Facility:
Data is a very important resource and is very much valuable for any organization, loss of
such a valuable resource can result in a huge strategic disasters. As Data is stored on today’s’
storage devices like hard disks etc., It is necessary to take periodic backups of
data so that in case a storage device looses the data due to any damage we should be able to
restore the data a nearest point, Database systems offer excellent facilities for taking
backup of data and good mechanism of restoring those backups to get back the
backed-up data.
It some time happens that a database which was in use and very important transactions were
made after the last backup was made, all of a sudden due to any disastrous situation
the database crashes (improper shutdown, invalid disk access, etc.) Now in such a
situation the database management system should be able to recover the database to a
consistent state so that the transactions made after the last backup are not lost.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 13

14. Disadvantages of DBMS
1.Complexity
2.Cost of DBMS
3.Cost of conversion
4.Performance typically, a file-based system is written for a
specific application, such as invoicing
5.Higher impact of a failure

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 14

15. Disadvantages of DBMS
Complexity: As we’ve already mentioned, a DBMS is an
extremely complex piece of software, and all users
(database designers and developers, DBAs, and end-users)
must understand the DBMS’s functionality to take full
advantage of it.

Cost of DBMS: The cost of DBMSs varies significantly,
depending on the environment and functionality provided.
For example, a single-user DBMS for a PC may cost only
$100. However, a large mainframe multi-user DBMS
servicing hundreds of users can be extremely expensive,
perhaps $100,000 to $1,000,000. There is also the recurrent
annual maintenance cost, which is typically a percentage of
the list price.
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 15

16. Disadvantages of DBMS
Cost of conversion: In some situations, the cost of the DBMS and any extra
hardware may be insignificant compared with the cost of converting existing
applications to run on the new DBMS and hardware. This cost also includes the
cost of training staff to use these new systems, and possibly the employment of
specialist staff to help with the conversion and running of the system. This cost
is one of the main reasons why some companies feel tied to their current
systems and cannot switch to more modern database technology. The term
legacy system is sometimes used to refer to an older, and usually inferior,
system (such as file-based, hierarchical, or network systems).

Performance typically, a file-based system is written for a specific application,
such as invoicing. As a result, performance is generally very good. However, a
DBMS is written to be more general, to cater for many applications rather than
just one. The effect is that some applications may not run as fast using a DBMS
as they did before.

Higher impact of a failure: The centralization of resources increases the
vulnerability of the system. Since all users and applications rely on the
availability of the DBMS, the failure of any component can bring operations to
a complete halt until the failure is repaired.


By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 16

17. DBMS Features and capabilities

Query ability
Querying is the process of requesting attribute information from
various perspectives and combinations of factors. Example:
"How many 2-door cars in Texas are green?" A database query
language and report writer allow users to interactively
interrogate the database, analyze its data and update it according
to the users privileges on data.
Backup and replication
Copies of attributes need to be made regularly in case primary
disks or other equipment fails. A periodic copy of attributes may
also be created for a distant organization that cannot readily
access the original. DBMS usually provide utilities to facilitate
the process of extracting and disseminating attribute sets. When
data is replicated between database servers, so that the
information remains consistent throughout the database system
and users cannot tell or even know which server in the DBMS
they are using, the system is said to exhibit replication
transparency.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 17

18. Rule enforcement
Often one wants to apply rules to attributes so that the attributes are clean and
reliable. For example, we may have a rule that says each car can have only one engine
associated with it (identified by Engine Number). If somebody tries to associate a
second engine with a given car, we want the DBMS to deny such a request and
display an error message. However, with changes in the model specification such as,
in this example, hybrid gas-electric cars, rules may need to change. Ideally such
rules should be able to be added and removed as needed without significant data
layout redesign.
Security
Often it is desirable to limit who can see or change which attributes or groups of
attributes. This may be managed directly by individual, or by the assignment of
individuals and privileges to groups, or (in the most elaborate models) through the
assignment of individuals and groups to roles which are then granted entitlements.
Computation
There are common computations requested on attributes such as counting,
summing, averaging, sorting, grouping, cross-referencing, etc. Rather than have
each computer application implement these from scratch, they can rely on the
DBMS to supply such calculations.
Change and access logging
Often one wants to know who accessed what attributes, what was changed, and
when it was changed. Logging services allow this by keeping a record of access
occurrences and changes.



By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 18
DBMS Features and capabilities

19. DBMS - Architecture
The design of a DBMS depends on its architecture. It
can be centralized or decentralized or hierarchical.
The architecture of a DBMS can be seen as either
single tier or multi-tier. N-tier architecture divides the
whole system into related but independent n modules,
which can be independently modified, altered,
changed, or replaced.


By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 19

20. DBMS - Architecture
1.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.
2.Application (Middle) 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.
3.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.
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 20

21. 1-tier architecture
In 1-tier architecture, the DBMS is the only entity where the
user directly sits on the DBMS and uses it. Any changes done
here will directly be done on the DBMS itself. It does not
provide handy tools for end-users. Database designers and
programmers normally prefer to use single-tier architecture.

Imagine a person on a desktop computer who uses Microsoft
Access to load up a list of personal addresses and phone
numbers that he or she has saved in MS Windows' “My
Documents” folder.

This is an example of a one-tier database architecture. The
program (Microsoft Access) runs on the user's local machine,
and references a file that is stored on that machine's hard
drive, thus using a single physical resource to access and
process information.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 21

22. 1-Tier Architecture
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 22

23. 1-Tier Architecture

In this architecture, the database is directly available to the
user. It means the user can directly sit on the DBMS and
uses it.
Any changes done here will directly be done on the
database itself. It doesn't provide a handy tool for end
users.
The 1-Tier architecture is used for development of the
local application, where programmers can directly
communicate with the database for the quick response.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 23

24. 1-Tier Architecture
Another example of a one-tier architecture is a file server architecture. In
this scenario, a workgroup database is stored in a shared location on a
single machine. Workgroup members use a software package such as
Microsoft Access to load the data and then process it on their local
machine. In this case, the data may be shared among different users, but
all of the processing occurs on the local machine. Essentially, the file-
server is just an extra hard drive from which to retrieve files.

Yet another way one-tier architectures have appeared is in that of
mainframe computing. In this outdated system, large machines provide
directly connected unintelligent terminals with the means necessary to
access, view and manipulate data. Even though this is considered a
client-server system, since all of the processing power (for both data and
applications) occurs on a single machine, we have a one-tier architecture.

One-tier architectures can be beneficial when we are dealing with data
that is relevant to a single user (or small number of users) and we have a
relatively small amount of data. They are somewhat inexpensive to
deploy and maintain
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 24

25. 2-tier
If the architecture of DBMS is 2-tier, then it must have
an application through which the DBMS can be
accessed.
Programmers use 2-tier architecture where they access
the DBMS by means of an application. Here the
application tier is entirely independent of the database
in terms of operation, design, and programming
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 25

26. 2-tier
Two-tier architecture is one that is familiar to many of today's
computer users. A common implementation of this type of
system is that of a Microsoft Windows based client program
that accesses a server database such as Oracle or SQL Server.
Users interact through a GUI (Graphical User Interface) to
communicate with the database server across a network via
SQL (Structured Query Language).

In two-tier architectures it is important to note that two
configurations exist. A thin-client (fat-server) configuration
exists when most of the processing occurs on the server
tier. Conversely, a fat-client (thin-server) configuration exists
when most of the processing occurs on the client machine.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 26

27. 2-Tier Architecture

1.The 2-Tier architecture is same as basic client-server. In
the two-tier architecture, applications on the client end
can directly communicate with the database at the server
side. For this interaction, API's like: ODBC, JDBC are
used.
2.The user interfaces and application programs are run on
the client-side.
3.The server side is responsible to provide the
functionalities like: query processing and transaction
management.
4.To communicate with the DBMS, client-side application
establishes a connection with the server side.
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 27

28. 2
nd
Tier
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 28

29. By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 29

30. 3-tier Architecture

A 3-tier architecture separates its tiers from each other
based on the complexity of the users and how they use
the data present in the database. It is the most widely
used architecture to design a DBMS.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 30

31. 3-Tier
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 31

32. 3-Tier Architecture
1.The 3-Tier architecture contains another layer between
the client and server. In this architecture, client can't
directly communicate with the server.
2.The application on the client-end interacts with an
application server which further communicates with the
database system.
3.End user has no idea about the existence of the database
beyond the application server. The database also has no
idea about any other user beyond the application.
4.The 3-Tier architecture is used in case of large web
application.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 32

33. By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 33

34. Data independence
Data independence is the type of data transparency that matters
for a centralized DBMS. It refers to the immunity of user
applications to changes made in the definition and organization of
data.
Data Independence is defined as a property of DBMS that helps
you to change the Database schema at one level of a database
system without requiring changing the schema at the next higher
level. Data independence helps you to keep data separated from all
programs that make use of it.

Physical data independence deals with hiding the details of the
storage structure from user applications. The application should
not be involved with these issues, since there is no difference in the
operation carried out against the data.

The data independence and operation independence together gives
the feature of data abstraction. There are two levels of data
independence
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 34

35. Data independence
Data independence can be explained as follows: Each higher
level of the data architecture is immune to changes of the next
lower level of the architecture.

The logical scheme stays unchanged even though the storage
space or type of some data is changed for reasons of optimization
or reorganization. In this external schema does not change. In
this internal schema changes may be required due to some
physical schema were reorganized here. Physical data
independence is present in most databases and file environment
in which hardware storage of encoding, exact location of data on
disk, merging of records, so on this are hidden from user.

One of the biggest advantages of databases is data
independence. It means we can change the conceptual schema at
one level without affecting the data at another level. It also
means we can change the structure of a database without
affecting the data required by users and programs. This feature
was not available in the file oriented approach.
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 35

36. Data Independence Types

The ability to modify schema definition in one level
without affecting schema definition in the next higher
level is called data independence. There are two levels
1.Physical data independence
2.Logical data independence

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 36

37. Physical data independence
Physical data independence is the ability to modify the
physical schema without causing application programs
to be rewritten.

Modifications at the physical level are occasionally
necessary to improve performance. It means we
change the physical storage/level without affecting the
conceptual or external view of the data. The new
changes are absorbed by mapping techniques
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 37

38. Due to Physical independence, any of the below change
will not affect the conceptual layer.

1.Using a new storage device like Hard Drive or
Magnetic Tapes
2.Changing the access method.
3.Changes to compression techniques or hashing
algorithms.
4.Change of Location of Database from say C drive to
D Drive

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 38

39. Logical data independence
Logical data independence is the ability to modify the
logical schema without causing application program to be
rewritten. Modifications at the logical level are necessary
whenever the logical structure of the database is altered
(for example, when money-market accounts are added to
banking system).

Logical Data independence means if we add some new
columns or remove some columns from table then the user
view and programs should not change. For example:
consider two users A & B. Both are selecting the fields
"EmployeeNumber" and "EmployeeName". If user B adds a
new column (e.g. salary) to his table, it will not effect the
external view for user A, though the internal schema of the
database has been changed for both users A & B.

By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 39

40. Examples of changes under Logical Data
Independence

1.Due to Logical independence, any of the below
change will not affect the external layer.
2.Add/Modify/Delete a new attribute, entity or
relationship is possible without a rewrite of existing
application programs
3.Merging two records into one
4.Breaking an existing record into two or more records
By Businge Phelix Mbabazi Abwooli Assoc.Prof
of IT 0782823607 40