VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 1
Basic Overview
To define the database system, the following terminologies must be clarified.
File
Database
Database management system (DBMS)
File is a two-dimensional table summarizing the multiple instances of a set of fields of
an entity. The database system is essentially a database management system which is
free from all the drawbacks of the conventional file processing system.
Consider the example of the hospital system which deals with in patients as well as out
patients. A schematic representation of the database using the database approach is
shown in below figure.
Figure: Conceptual data model of hospital system
A database is a logically coherent collection of data with some inherent meaning,
representing some aspect of real world and which is designed, built and populated with
data for a specific purpose.
Database systems are ubiquitous today and most people interact either directly or
indirectly, with database many times every days.
A database is a collection of data, typically describing the activities of one or more
related organizations. For example, a university database might contain information
about the following:
Patients Physician Items
Room
In-patient
Treatments
Out-patient
Treatments
Charge
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 2
Entities such as students, faculty, courses, and classrooms.
Relationships between entities, such as students’ enrollment in courses, faculty
teaching courses, and the use of rooms for courses.
A database management system, or DBMS, is software designed to assist in maintaining
and utilizing large collections of data, and the need for such systems, as to store the data
in files and write application-specific code to manage it.
Unit -1 Introduction
A database-management system (DBMS) is a collection of interrelated data. The
collection of data, usually referred to as the database, contains information relevant to
an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve
database information that is both convenient and efficient.
Unit – 1.2 Database-System Applications
Databases are widely used. Here some representative applications:
Banking: For customer information, accounts, loans and banking transactions.
Airlines: For reservations and schedule information. Airlines were among the first to
use databases in a geographically distributed manner.
Universities: For student information, course registrations, and grades.
Credit card transactions: For purchases on credit card and generation of monthly
statements.
Telecommunication: For keeping records of calls made, generating monthly bills,
maintaining balances on prepaid calling cards, and storing information about the
communication networks.
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
on-line trading by customers and automated trading by the firm.
Sales: For customer, product and purchase information.
On-line retailers: For sales data noted above plus on-line order tracking, generation of
recommendation lists, and maintenance of on-line product evaluations.
Manufacturing: For management of the supply chain and for tracking production of
items in factories, inventories of item in warehouses and stores, and orders for items.
Human resources: For information about employees, salaries, payroll taxes, benefits
and for generation of paychecks.
Unit- 1.1 Drawback of file processing system
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 3
File-processing system has a number of major disadvantages:
1. Data redundancy and inconsistency:
2. Difficulty in accessing data:
3. Data isolation:
4. Integrity problems:
5. Atomicity problems:
6. Concurrent-access anomalies:
7. Security Problems:
Descriptive:
1. Data redundancy and inconsistency:
The same information may be duplicated in several places (files).
For example, the address and telephone number of a particular customer may appear
in a file that consists of savings-account records and in a file that consists of
checking-account records.
This redundancy leads to higher storage and access cost. In addition, it may lead to
data inconsistency; that is, the various copies of the same data may no longer agree.
For example, a changed customer address may be reflected in savings-account
records but not elsewhere in the system.
2. Difficulty in accessing data:
The point here is that conventional file-processing environments do not allow
needed data to be retrieved in a convenient and efficient manner. More
responsive data-retrieval systems are required for general use.
Suppose that one of the bank officers needs to find out the names of all customers
who live within a particular postal-code area. The officer asks the data-processing
department to generate such a list. Because the designers of the original system did
not anticipate this request, there is no application program on hand to meet it. There
is, however, an application program to generate the list of all customers.
The bank officer has now two choices: either obtain the list of all customers and
extract the needed information manually or ask a system programmer to write the
necessary application program. Both alternatives are obviously unsatisfactory.
Suppose that such a program is written, and that, several days later, the same officer
needs to trim that list to include only those customers who have an account balance
of $10,000 or more. As expected, a program to generate such a list does not exist.
Again, the officer has the preceding two options, neither of which is satisfactory.
3. Data isolation:
Because data are scattered in various files, and files may be in different formats,
writing new application programs to retrieve the appropriate data is difficult.
4. Integrity problems:
The data values stored in the database must satisfy certain types of consistency
constraints.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 4
For example, the balance of a bank account may never fall below a prescribed
amount (say, $25). Developers enforce these constraints in the system by adding
appropriate code in the various application programs. However, when new
constraints are added, it is difficult to change the programs to enforce them. The
problem is compounded when constraints involve several data items from different
files.
5. Atomicity problems:
A computer system, like any other mechanical or electrical device, is subject to
failure. In many applications, it is crucial that, if a failure occurs, the data be
restored to the consistent state that existed prior to the failure.
Consider a program to transfer $50 from account A to account B. If a system failure
occurs during the execution of the program, it is possible that the $50 was removed
from account A but was not credited to account B, resulting in an inconsistent
database state. Clearly, it is essential to database consistency that either both the
credit and debit occur, or that neither occur. That is, the funds transfer must be
atomic—it must happen in its entirety or not at all. It is difficult to ensure atomicity
in a conventional file-processing system.
6. Concurrent-access anomalies:
For the sake of overall performance of the system and faster response, many
systems allow multiple users to update the data simultaneously. In such an
environment, interaction of concurrent updates may result in inconsistent data.
Consider bank account A, containing $500. If two customers withdraw funds (say
$50 and $100 respectively) from account A at about the same time, the result of the
concurrent executions may leave the account in an incorrect (or inconsistent) state.
Suppose that the programs executing on behalf of each withdrawal read the old
balance, reduce that value by the amount being withdrawn, and write the result back.
If the two programs run concurrently, they may both read the value $500, and write
back $450 and $400, respectively. Depending on which one writes the value last, the
account may contain $450 or $400, rather than the correct value of $350. To guard
against this possibility, the system must maintain some form of supervision. But
supervision is difficult to provide because data may be accessed by many different
application programs that have not been coordinated previously.
7. Security problems:
Not every user of the database system should be able to access all the data.
For example, in a banking system, payroll personnel need to see only that part of the
database that has information about the various bank employees. They do not need
access to information about customer accounts. But, since application programs are
added to the system in an ad hoc manner, enforcing such security constraints is
difficult.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 5
These difficulties, among others, prompted the development of database systems. In
what follows, we shall see the concepts and algorithms that enable database systems to
solve the problems with file-processing systems. In most of this book, we use a bank
enterprise as a running example of a typical data-processing application found in a
corporation.
How database system is different from DBMS?
Answer: Database systems are designed to manage large bodies of information.
Management of data involves both defining structures for storage of information and
providing mechanisms for the manipulation of information. In addition, the database
system must ensure the safety of the information stored, despite system crashes or
attempts at unauthorized access. If data are to be shared among several users, the system
must avoid possible anomalous results.
Because information is so important in most organizations, computer scientists have
developed a large body of concepts and techniques for managing data.
Advantages of DBMS (Database Management Systems) are followings:
A true DBMS offers several advantages over file processing. The principal advantages
of a DBMS are the followings:
Flexibility: Because programs and data are independent, programs do not have to be
modified when types of unrelated data are added to or deleted from the database, or
when physical storage changes.
Fast response to information requests: Because data are integrated into a single
database, complex requests can be handled much more rapidly then if the data were
located in separate, non-integrated files. In many businesses, faster response means
better customer service.
Multiple access: Database software allows data to be accessed in a variety of ways
(such as through various key fields) and often, by using several programming languages
(both 3GL and nonprocedural 4GL programs).
Lower user training costs: Users often find it easier to learn such systems and training
costs may be reduced. Also, the total time taken to process requests may be shorter,
which would increase user productivity.
Less storage: Theoretically, all occurrences of data items need be stored only once,
thereby eliminating the storage of redundant data. System developers and database
designers often use data normalization to minimize data redundancy.
FILE SYSTEMS VERSUS A DBMS
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 6
To understand the need for a DBMS, let us consider a motivating scenario: A company
has a large collection (say, 500 GB1) of data on employees, departments, products,
sales, and so on. This data is accessed concurrently by several employees. Questions
about the data must be answered quickly, changes made to the data by different users
must be applied consistently, and access to certain parts of the data (e.g., salaries) must
be restricted.
We can try to deal with this data management problem by storing the data in a
collection of operating system files. This approach has many drawbacks, including the
following:
We probably do not have 500 GB of main memory to hold all the data. We must
therefore store data in a storage device such as a disk or tape and bring relevant parts
into main memory for processing as needed.
Even if we have 500 GB of main memory, on computer systems with 32-bit
addressing, we cannot refer directly to more than about 4 GB of data! We have to
program some method of identifying all data items.
We have to write special programs to answer each question that users may want to ask
about the data. These programs are likely to be complex because of the large volume
of data to be searched.
We must protect the data from inconsistent changes made by different users accessing
the data concurrently. If programs that access the data are written with such concurrent
access in mind, this adds greatly to their complexity.
We must ensure that data is restored to a consistent state if the system crashes while
changes are being made.
Operating systems provide only a password mechanism for security. This is not
sufficiently exible to enforce security policies in which different users have permission
to access different subsets of the data.
A DBMS is a piece of software that is designed to make the preceding tasks easier. By
storing data in a DBMS, rather than as a collection of operating system files, we can use
the DBMS’s features to manage the data in a robust and e client manner. As the volume
of data and the number of users grow—hundreds of gigabytes of data and thousands of
users are common in current corporate databases—DBMS support becomes
indispensable.
Advantage of DBMS
Using a DBMS to manage data has many advantages:
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 7
1. Data independence: Application programs should be as independent as possible
from details of data representation and storage. The DBMS can provide an
abstract view of the data to insulate application code from such details.
2. Efficient data access: A DBMS utilizes a variety of sophisticated techniques to
store and retrieve data efficiently. This feature is especially important if the data
is stored on external storage devices.
3. Data integrity and security: If data is always accessed through the DBMS, the
DBMS can enforce integrity constraints on the data. For example, before inserting
salary information for an employee, the DBMS can check that the department
budget is not exceeded. Also, the DBMS can enforce access controls that govern
what data is visible to different classes of users.
4. Data administration: When several users share the data, centralizing the
administration of data can offer significant improvements. Experienced
professionals, who understand the nature of the data being managed, and how
different groups of users use it, can be responsible for organizing the data
representation to minimize redundancy and for fine-tuning the storage of the data
to make retrieval efficient.
5. Concurrent access and crash recovery: A DBMS schedules concurrent
accesses to the data in such a manner that users can think of the data as being
accessed by only one user at a time. Further, the DBMS protects users from the
effects of system failures.
6. Reduced application development time: Clearly, the DBMS supports many
important functions that are common to many applications accessing data stored
in the DBMS. This, in conjunction with the high-level interface to the data,
facilitates quick development of applications. Such applications are also likely to
be more robust than applications developed from scratch because many important
tasks are handled by the DBMS instead of being implemented by the application.
Database Systems: Data Abstraction
The major purpose of a database system is to provide users with an abstract view of the
system. The system hides certain details of how data is stored and maintained
Complexity should be hidden from database users.
There are several levels of abstraction:
(a) Physical Level:
How the data are stored.
E.g. index, B-tree, hashing.
Lowest level of abstraction.
Complex low-level structures described in detail.
(b) Conceptual Level:
Next highest level of abstraction. Data Abstraction Model
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 8
Figure The three levels of data abstraction
Describes what data a restored.
Describes the relationships among data.
Database administrator level.
(c) View Level:
Highest level.
Describes part of the database for a particular group of users.
Can be many different views of a database.
E.g. tellers in a bank get a view of customer accounts, but not of payroll data.
Unit – 1.3 Organization of database (Physical, Conceptual, Logical)
The terms "conceptual", "logical", and "physical" are frequently used in data modeling
to differentiate levels of abstraction versus detail in the model. Although there is no
general agreement, let alone accepted authority, which defines these terms, nevertheless
data modelers generally understand the approximate scope of each.
Conceptual E-R Model
A conceptual entity-relationship model shows how the
business world sees information. It suppresses non-
critical details in order to emphasize business rules and
user objects. It typically includes only significant entities
which have business meaning, along with their
relationships. Many-to-many relationships are
acceptable to represent entity associations.
A conceptual model might discover that there is a need to
house information about each person in an organization.
While considerable thought is given to discovering and
describing the relevant properties of each person, the designers accept implicitly that
each person is distinct and unique.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 9
A conceptual model may include a few significant attributes to augment the definition
and visualization of entities. No effort need be made to inventory the full attribute
population of such a model. A conceptual model may have some identifying concepts
or candidate keys noted but it explicitly does not include a complete scheme of identity,
since identifiers are logical choices made from a deeper context.
Logical E-R Model
A logical entity-relationship model is provable in the
mathematics of data science. Given the current
predominance of relational databases, logical models
generally conform to relational theory. Thus a logical
model contains only fully normalized entities. Some of
these may represent logical domains rather than potential
physical tables.
For a logical data model to be normalized it must include
the full population of attributes to be implemented and
those attributes must be defined in terms of their domains
or logical data types (e.g., character, number, date, picture, etc.).
A logical data model requires a complete scheme of identifiers or candidate keys for
unique identification of each occurrence in every entity. Since there are choices of
identifiers for many entities, the logical model indicates the current selection of identity.
Propagation of identifiers as foreign keys may be explicit or implied.
Since relational storage cannot support many-to-many concepts, a logical data model
resolves all many-to-many relationships into associative entities which may acquire
independent identifiers and possibly other attributes as well.
Physical Database Schema
A physical data model is a single logical model
instantiated in a specific database management product
(e.g., Sybase, Oracle, Informix, etc.) in a specific
installation. The physical data model specifies
implementation details which may be features of a
particular product or version, as well as configuration
choices for that database instance. These include index
construction, alternate key declarations, modes of
referential integrity (declarative or procedural),
constraints, views, and physical storage objects such as
tablespaces.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 10
In Summary
The conceptual model is concerned with the real world view and understanding of data;
the logical model is a generalized formal structure in the rules of information science;
the physical model specifies how this will be executed in a particular DBMS instance.
Various data modeling methodologies and products provide these layers of abstraction
in different ways. Some address only the physical implementation; some model only the
logical structure; others may provide elements of all three but not necessarily in three
separate views. In each case it helps the data modeler to understand the level of
abstraction to which a particular feature or task belongs.
Unit – 1.4 Data Models:
Data models are a collection of conceptual tools for describing data, data relationships,
data semantics and data constraints. There are three different groups:
1. Object-based Logical Models.
2. Record-based Logical Models.
3. Physical Data Models.
Relational
Data Models
Object-based data
model
E-R Model
Entity & Entity Sets
Record-based data
model
Physical data model
Network Hierarchical
Types of
Relationship
Relational
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 11
Unit – 1.4.1
1. Object-base Logical Models:
Describe data at the conceptual and view levels.
Provide fairly flexible structuring capabilities.
Allow one to specify data constraints explicitly.
Over 30 such models, including
Entity-relationship model.
Object-oriented model.
Binary model.
Semantic data model.
Info logical model.
Functional data model.
At this point, we'll take a closer look at the entity-relationship (E-R) and object-oriented
models.
Unit – 1.4.2 & 1.4.3
The E-R Model
1. The entity-relationship model is based on a perception of the world as consisting of a
collection of basic objects (entities) and relationships among these objects.
An entity is a distinguishable object that exists.
Each entity has associated with it a set of attributes describing it.
E.g. number and balance for an account entity.
A relationship is an association among several entities.
E.g. A cust acct relationship associates a customer with each account he or she
has.
The set of all entities or relationships of the same type is called the entity set or
relationship set.
Another essential element of the E-R diagram is the mapping cardinalities, which
express the number of entities to which another entity can be associated via a
relationship set.
We'll see later how well this model works to describe real world situations.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 12
2. The overall logical structure of a database can be expressed graphically by an E-R
diagram:
Rectangles: represent entity sets.
Ellipses: represent attributes.
Diamonds: represent relationships among entity sets.
Lines: link attributes to entity sets and entity sets to relationships.
Double rectangles, which represent weak entity sets
Double ellipses, which represent multi-value attributes
Dashed ellipses, which denote derived attributes
Double lines, which indicate total participation of an entity in a relation- ship set
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 13
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 14
Rectangles represent entity sets.
Diamonds represent relationship sets.
Lines link attributes to entity sets and entity sets to relationship sets.
Ellipses represent attributes
o Double ellipses represent multivalued attributes.
o Dashed ellipses denote derived attributes.
Underline indicates primary key attributes
The Object-Oriented Model
1. The object-oriented model is based on a collection of objects, like the E-R model.
An object contains values stored in instance variables within the object.
Unlike the record-oriented models, these values are themselves objects.
Thus objects contain objects to an arbitrarily deep level of nesting.
An object also contains bodies of code that operate on the object.
These bodies of code are called methods.
Objects that contain the same types of values and the same methods are grouped
into classes.
A class may be viewed as a type definition for objects.
Analogy: the programming language concept of an abstract data type.
The only way in which one object can access the data of another object is by
invoking the method of that other object.
This is called sending a message to the object.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 15
Internal parts of the object, the instance variables and method code, are not visible
externally.
Result is two levels of data abstraction.
For example, consider an object representing a bank account.
The object contains instance variables number and balance.
The object contains a method pay-interest which adds interest to the balance.
Under most data models, changing the interest rate entails changing code in
application programs.
In the object-oriented model, this only entails a change within the pay-interest
method.
2. Unlike entities in the E-R model, each object has its own unique identity, independent
of the values it contains:
Two objects containing the same values are distinct.
Distinction is maintained in physical level by assigning distinct object identifiers.
Unit – 1.4.4 & 1.4.5
Record-based Logical Models
Also describe data at the conceptual and view levels.
Unlike object-oriented models, are used to Specify overall logical structure of the
database, and Provide a higher-level description of the implementation.
Named so because the database is structured in xed-format records of several
types.
Each record type de nes a xed number of elds, or attributes.
Each eld is usually of a xed length (this simpli es the implementation).
Record-based models do not include a mechanism for direct representation of
code in the database.
Separate languages associated with the model are used to express database
queries and updates.
The three most widely-accepted models are the relational, network, and
hierarchical.
This course will concentrate on the relational model.
The network and hierarchical models are covered in appendices in the text.
Relationship & Relationship Sets
A relationship is an association between several entities.
A relationship set is a set of relationships of the same type.
If E1, E2, E3, E4, E5 are entity sets then a relationship set R is a subset of where
{(e1,e2,e3,…e4) e1€ E1, e2€ E2 ,…, en€ En }
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 16
For example, consider the two entity sets customer and account. We define the
relationship CustAcct to denote the association between customers and their
accounts
Mapping Constraints
An E-R scheme may define certain constraints to which the contents of a database must conform.
Mapping Cardinalities
Existence Dependencies
In Brief,
Mapping Cardinalities: express the number of entities to which another entity can be
associated via a relationship. For binary relationship sets between entity sets A and B, the
mapping cardinality must be one of:
1. One-to-One: An entity in A is associated with at most one entity in B, and an entity in
B is associated with at most one entity in A.
One to One
For example: 1-1 Department, Manager
2. One-to-Many: An entity in A is associated with any number in B. An entity in B is
associated with at most one entity in A.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 17
One to Many
For example: 1-Many Department, Employee
3. Many-to-One: An entity in A is associated with at most one entity in B, and an entity
in B is associated with any number in A.
One to One
For example: Many -1 Many, Department
4. Many-to-Many: Entities in A and B are associated with any number from each other.
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 18
One to One
For example: Many - Many Customers, Items
The appropriate mapping cardinality for a particular relationship set depends on the real world
being modeled.(Think about the CustAcct relationship…)
Existence Dependencies: if the existence of entity X depends on the existence of entity Y,
then X is said to be existence dependent on Y. (Or we say that Y is the dominant entity and X is
the subordinate entity.)
For example,
o Consider account and transaction entity sats, and a relationship log between them. o This is one-to-many from account to transaction. o If an account entity is deleted. Its association transaction entities must also be deleted. o Thus account is dominant and transaction is subordinate.
The Relational Model Data and relationships are represented by a collection of tables.
Each table has a number of columns with unique names, e.g. customer, account
Figure 1.3 shows a sample relational database.
name street city number
Lowery Maple Queens 900
Shiver North Bronx 556
Shiver North Bronx 647
Hodges Sidehill Brooklyn 801
Hodges Sidehill Brooklyn 647
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 19
Figure 1.3: A sample relational database.
Figure 1.4: A sample network database
The Network Model
Data are represented by collections of records.
Relationships among data are represented by links.
Organization is that of an arbitrary graph.
Figure 1.4 shows a sample network database that is the equivalent of the
relational database of Figure 1.3.
The Hierarchical Model
Similar to the network model.
Organization of the records is as a collection of trees, rather than arbitrary graphs.
Figure 1.5 shows a sample hierarchical database that is the equivalent of the
relational database of Figure 1.3.
Figure 1.5: A sample hierarchical database
The relational model does not use pointers or links, but relates records by the values
they contain. This allows a formal mathematical foundation to be defined.
Unit – 1.4.6
Database Systems: Physical Data Models
1. Are used to describe data at the lowest level.
2. Very few models, e.g.
Unifying model.
name balance
900 55
556 100000
647 105366
801 10533
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 20
Frame memory.
A physical data model (a.k.a. database design) is a representation of a data design which
takes into account the facilities and constraints of a given database management system.
In the lifecycle of a project it is
typically derived from a logical
data model, though it may be
reverse-engineered from a
given database implementation.
A complete physical data
model will include all the
database artifacts required to
create relationships between
tables or achieve performance
goals, such as indexes,
constraint definitions, linking
tables, partitioned tables or
clusters. The physical data model can usually be used to calculate storage estimates and
may include specific storage allocation details for a given database system.
Database Systems: Instances and Schemes
1. Databases change over time.
2. The information in a database at a particular point in time is called an instance of the
database.
3. The overall design of the database is called the database scheme.
4. Analogy with programming languages:
Data type definition - scheme
Value of a variable - instance
5. There are several schemes, corresponding to levels of abstraction:
Physical scheme
Conceptual scheme
Subschema (can be many)
Database Systems: Data Independence 1. The ability to modify a scheme definition in one level without affecting a
scheme
2. definition in a higher level is called data independence.
2. There are two kinds:
Physical data independence
The ability to modify the physical scheme without causing application programs
to be rewritten
Modifications at this level are usually to improve performance
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 21
Logical data independence
The ability to modify the conceptual scheme without causing application
programs to be rewritten
Usually done when logical structure of database is altered
3. Logical data independence is harder to achieve as the application programs are
usually heavily dependent on the logical structure of the data. An analogy is made to
abstract data types in programming languages.
Unit - 1.5 Query Language (DDL , DML, DCL & TCL )
What are the difference between DDL,DML and DCL statements?
Data Definition Language (DDL): DDL statements are used to define the database
structure or schema.
Command Description
CREATE: to create objects in the database
ALTER: alters the structure of the database
DROP: Delete objects from the database
TRUNCATE: Remove all records from a table, including all spaces allocated for the records are
removed
COMMENT: Add comments to the data dictionary
RENAME: Rename an object
1. Used to specify a database scheme as a set of definitions expressed in a DDL
2. DDL statements are compiled, resulting in a set of tables stored in a special le called a
data
Dictionary or data directory.
3. The data directory contains metadata (data about data)
4. The storage structure and access methods used by the database system are specified
by a set of definitions in a special type of DDL called a data storage and definition
language.
5. Basic idea: hide implementation details of the database schemes from the users.
Data Manipulation Language (DML): DML statements are used for managing data
within schema objects. Some example:
Command Description
SELECT: Retrieve data from the a database
INSERT: Insert data into a table
UPDATE: Update existing data within a table
DELETE: Deletes all records from a table, the space for the records remain
MERGE: UPSERTE operation (insert or update)
CALL: Call a PL/SQL or java subprogram
EXPLAI PLAN: Explain access path or data
LOCK TABLE: Control concurrency
VIDYABHARTI TRUST COLLEGE OF BBA & BCA. UMRAKH
Course: BCA SEM: II
Subject: Database Management System
FACULTY: Ronak Panchal Page 22
1. Data Manipulation is:
retrieval of information from the database
insertion of new information into the database
deletion of information in the database
modification of information in the database
2. A DML is a language which enables users to access and manipulate data.
The goal is to provide efficient human interaction with the system.
3. There are two types of DML:
Procedural: the user specifies what data is needed and how to get it
Nonprocedural: the user only specifies what data is needed
Easier for user
May not generate code as efficient as that produced by procedural languages
4. A query language is a portion of a DML involving information retrieval only. The
terms DML and query language are often used synonymously.
Data Control Language (DCL):
A DCL is used to control access to data in a database.
Examples of DCL commands include:
GRANT to allow specified users to perform specified tasks.
REVOKE to cancel previously granted or denied permissions.
TCL ( Transaction Control Language):
Transaction Control (TCL) statement are used to manage the changes made by DML
statements. It allows statements to be grouped together into logical transactions.
Command Description
COMMENT: Save work done
SAVEPOINT: Identify a point in a transaction to which you can later rollback
ROLLBACK: Restore database to original since the fast COMMIT
SET TRANSACTION: Change transaction options like isolation and what rollback segment to use