Database-System Applications

Databases are widely used. Here are some representative applications:

• Enterprise Information
◦ Sales: For customer, product, and purchase information.
◦ Accounting: For payments, receipts, account balances, assets and other
accounting information.
◦ Human resources: For information about employees, salaries, payroll taxes,
and benefits, and for generation of paychecks.
◦ Manufacturing: For management of the supply chain and for tracking
production of items in factories, inventories of items in warehouses and stores,
and orders for items.
◦ Online retailers: For sales data noted above plus online order tracking,
generation of recommendation lists, and maintenance of online product
evaluations.
• Banking and Finance
◦ Banking: For customer information, accounts, loans, and banking
transactions.
◦ Credit card transactions: For purchases on credit cards and generation of
monthly statements.
◦ Finance: For storing information about holdings, sales, and purchases of
financial instruments such as stocks and bonds; also, for storing real-time
market data to enable online trading by customers and automated trading by
the firm.
• Universities: For student information, course registrations, and grades (in
addition to standard enterprise information such as human resources and
accounting).
• Airlines: For reservations and schedule information. Airlines were among the
first to use databases in a geographically distributed manner.
• Telecommunication: For keeping records of calls made, generating monthly
bills, maintaining balances on prepaid calling cards, and storing information
about the communication networks.
For example: The college Database organizes the data about the admin, staff,
students and faculty etc. Using the database, you can easily retrieve, insert, and
delete the information.

Data Independence
Data Independence
• Data independence refers characteristic of being able to modify
the schema at one level of the database system without altering
the schema at the next higher level.
There are two types of data independence:
1. Logical Data Independence
• Logical data independence refers characteristic of being able to
change the conceptual schema without having to change the
external schema.
• If we do any changes in the conceptual view of the data, then
the user view of the data would not be affected.
• Logical data independence is used to separate the external level
from the conceptual view.
• Logical data independence occurs at the user interface level
2. Physical Data Independence
• Physical data independence can be defined as the capacity to
change the internal schema without having to change the
conceptual schema.
• If we do any changes in the storage size of the database system
server, then the Conceptual structure of the database will not
be affected.
• Physical data independence is used to separate conceptual
levels from the internal levels.
• Physical data independence occurs at the logical interface level.
 1. Database Administrators
• In any organization where many persons use the same resources,
there is a need for a chief administrator to oversee and manage
these resources.
• In a database environment, the primary resource is the database
itself and the secondary resource is the DBMS and related software.
Administering these resources is the responsibility of the database
administrator (DBA).
• The DBA is responsible for authorizing access to the database, for
coordinating and monitoring its use, and for acquiring software and
hardware resources as needed.
• The DBA is accountable for problems such as breach of security or
poor system response time. In large organizations, the DBA is
assisted by a staff that helps carry out these functions.
Characteristics of DBMS Approach
1. Self-Describing Nature of a Database System
Database System Database + Metadata (Data Definition)
Stored in: DBMS Catalog (which contains information such
as the structure of each file, the type and storage format of each data
item, and various constraints on the data. )
Used by: DBMS Software and Database Users
• DBMS Software must work equally well with any number of Database
Applications.
• In traditional file processing, data definition Part of application
programs
Work with only one specific DB
• Database system contains the database itself and a complete
definition or description of the database structure and constraints.
• This definition is stored in the system catalog, which contains
information such as the structure of each file, the type and storage
format of each data item, and various constraints on the data. The
information stored in the catalog is called meta-data. (describes the
structure of the primary database )
2. Isolation between Programs and Data, and Data Abstraction
• In Traditional File processing structure of data files is embedded in
the application programs. (any changes to the structure of a file
may require changing all programs that access this file. )
• In database approach structure of data files is stored in the
DBMS catalog
separate from access programs (program- data independence)
2. Isolation between Programs and Data, and Data Abstraction
• The characteristic that allows program- data independence is called
data abstraction.
• DBMS provides users with conceptual representation of data.
• Data model type of data abstraction provides conceptual
representation
• The characteristic that allows program-data independence and
program-operation independence is called data abstraction.
• A DBMS provides users with a conceptual representation of data
that does not include many of the details of how the data is stored
or how the operations are implemented.
• Informally, a data model is a type of data abstraction that is used to
provide this conceptual representation.
• The data model uses logical concepts, such as objects, their
properties, and their interrelationships, that may be easier for most
users to understand than computer storage concepts. Hence, the
data model hides storage and implementation details that are not
of interest to most database users.
3.Support for Multiple Views of the Data
• A database has many users, each of whom may require a different
view of database
• A View subset of database contains virtual data derived
from database
4.Sharing of knowledge and Multi-user Transaction Processing
 A multiuser DBMS allows multiple users to access the database at the
same time.
• OLTP (Online Transaction Processing)- major part of database
application
• For example, when several reservation clerks try to assign a seat on
an airline flight, the DBMS should ensure that each seat can be
accessed by only one clerk at a time for assignment to a passenger.
These types of applications are generally called on-line transaction
processing (OLTP) applications.
• DBMS must include concurrency control.
• DBMS must enforce several transaction properties:
1. Isolation: 1 agent assigned a seat to passenger, then that seat is
blocked and isolated from other agents.
2. Atomicity: DBMS ensures whether transaction is executed
completely or none at all. If an agent has assigned a seat to
passenger and transaction is struck midway or incomplete then that
seat should be released for other agent for access.
Q.7 Three schema Architecture:
• The three-schema architecture divides the database into three-
level used to create a separation between the physical
database and the user application.
• In simple terms, this architecture hides the details of physical
storage from the user.
• The database administrator’s (DBA) responsibility is to change
the structure of database storage without affecting the user’s
view. It deals with the data, the relationship between them and
the different access methods implemented on the database.
1.Internal or Physical level
• The internal level has an internal schema which describes
the physical storage structure of the database.
• The internal schema is also known as a physical schema.
 • It uses the physical data model. It is used to define that how
the data will be stored in a block.
• The physical level is used to describe complex low-level data
structures in detail.
2.Conceptual or Logical level
• The conceptual schema describes the design of a database at the
conceptual level. Conceptual level is also known as logical level.
• The conceptual schema describes the structure of the whole
database.
• The conceptual level describes what data are to be stored in the
database and also describes what relationship exists among
those data.
• In the conceptual level, internal details such as an implementation
of the data structure are hidden.
• Programmers and database administrators work at this level
3.External/ View level
• This is the highest level of database abstraction. It includes a
number of external schemas or user views.
• This level provides different views of the same database for a
specific user or a group of users.
An external view provides a powerful and flexible security mechanism
by hiding the parts of the database from a particular user.

DBMS Architecture
Data Architecteture
The DBMS design depends upon its architecture.
• The basic client/server architecture is used to deal with a large number of
PCs, web servers, database servers and other components that are
connected with networks.
• The client/server architecture consists of many PCs and a workstation
which are connected via the network.
• DBMS architecture depends upon how users are connected to the
database to get their request done.
1-Tier Architecture
• 1 Tier Architecture in DBMS is the simplest architecture of Database in
which the client, server, and Database all reside on the same machine.
• A simple one tier architecture example would be anytime you install a
Database in your system and access it to practice SQL queries.
• Such architecture is rarely used in production.
• In this architecture, the database is directly available to the user.
• It means the user can directly sit on the DBMS and use 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.
Two- Tier Architecture
 • The 2-Tier architecture is same as basic client-server.
• Runs on application program that runs on client side.
• Clients can directly interact with database.
• In the two-tier architecture, applications on the client end can
directly communicate with the database at the server side.
• For this interaction (Interface), API's like: ODBC (Open Database
Connectivity), JDBC (Java Database Connectivity) are used.
• The user interfaces and application programs are run on the client-
side.
• The server side is responsible to provide the functionalities like:
query processing and transaction management.
• To communicate with the DBMS, client-side application establishes
a connection with the server side.
3-Tier Architecture
• Internet based / website
• The 3-Tier architecture contains another layer between the client
and server.
• In this architecture, client can't directly communicate with the
server.
• The application on the client-end interacts with an application
server which further communicates with the database system.
• 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.
• The 3-Tier architecture is used in case of large web application.
•
Q.2 Describe various types of attributes. →5m
• Composite attribute
It can be divided into smaller sub parts, each sub part can form an
independent attribute. Example- Name
• Simple or Atomic attribute
Attributes that cannot be further subdivided are called atomic
attributes.
Example- Phone Number, Pin code
• Single valued Attribute
Attributes having a single value for a particular item is called a single
valued attribute. Example: Room Number
• Multi-valued Attribute
Attribute having a set of values for a single entity is called a multi-valued
attribute.
For example − e-mail, Tel.No, Hobbies
• Derived Attributes or stored Attributes
- When one attribute value is derived from the other is called a derived
attribute.
For example: Age can be derived from date of birth, where,
Age is the derived attribute.DOB is the stored attribute.
 • Complex Attribute
Nesting of composite and multi-valued attributes forms a complex
attribute.For exampleIf a person has more than one house and each
house has more than one phone. Then, that attribute phone is
represented as a complex attribute.

Q.5 Describe Generalization, Specialization and Aggregation with suitable

example. ->10m
Generalization
• Generalization is a process of generalizing an entity which contains
generalized attributes or properties of generalized entities. The entity
that is created will contain the common features.
• Generalization is a Bottom up process.
• We can have three sub entities as Car, Truck, Motorcycle and these three
entities can be generalized into one general super class as Vehicle.
• It is a form of abstraction that specifies two or more entities (sub class)
having common characters that can be generalized into one single entity
(super class) at higher level hiding all the differences.
2.Specialization
• It is a process of identifying subsets of an entity that shares different
characteristics.
• It breaks an entity into multiple entities from higher level (super class) to
lower level (sub class). The breaking of higher level entity is based on some
distinguishing characteristics of the entities in super class.
• It is a top down approach in which we first define the super class and then
sub class and then their attributes and relationships.
The specialization process allows us to do the following:
• Define a set of subclasses of an entity type.
• Establish additional specific attributes with each subclass.Establish
additional specific relationship types between each subclass and other
entity types or other subclasses.
Aggregration
• Aggregration is a process when relation between two entities is treated as
a single entity.
• Aggregation represents a has-a relationship between entity types, where
one represents the whole and the other the part.
• An example of aggregation is the Car and Engine entities. A car is made up
of an engine. The car is the whole and the engine is the part.
• Aggregation does not represent strong ownership. This means, a part
(engine) can exist on its own without the whole (car).
• There is no stronger ownership between a car and the engine. An engine of
a car can be moved to another car.
• In Aggregation, the dependent object is standalone, and can exists even if
the object of owning class is dead.