Download - Class1 FINAL
-
7/28/2019 Class1 FINAL
1/55
Databases and
Database Users
CLASS 1
DBMS-SWE 303
-
7/28/2019 Class1 FINAL
2/55
This sem you will be evaluated based on
QUIZ 1,2&3
ASSIGNMENT 1,2
CAT 1,CAT 2
TERM END EXAMINATION
TEXT BOOK:
Ramez Elmasri & B.Navathe: Fundamentals of Database
Systems, IV Ed., Addison Wesley, 2006 Date C.J: Introduction to Database Systems, Sixth Edition,
Addison-Wesley, 1995.
Data base system concepts by Abraham
Silberschatz,Henry K.Forth,S.Sudarsan 6th edition
-
7/28/2019 Class1 FINAL
3/55
Syllabus UNIT IIntroduction ER Modeling
Databases and database users Database systemconcepts and architecture
data modeling using Entity Relationship model.
RelationalModel.
UNIT II The Relational data model Relational constraints
Relational algebra
Introduction to SQL
Introduction PL/SQL
Relational database standard
ER to relational mapping
E.F. Codd rules.
-
7/28/2019 Class1 FINAL
4/55
UNIT IIINormalization
Functional dependencies
Normalization for relational databases up to BCNF
UNIT IVQuery Optimization and TransactionProcessing
Query Processing
Translating queries into relational algebra Using Heuristics in query optimization
Introduction to Transactions
Single user and multiuser system transactions
Read and write operations
DBMS buffers
Transactions system concepts
Serializability types.
-
7/28/2019 Class1 FINAL
5/55
UNIT VConcurrency, Recovery And Security 2PL
Types of locks
System lock tables
deadlocks Timestamp ordering algorithm
Recovery concepts -ARIES recovery algorithmIntroduction to database security issues
Discretionary access control based on granting andrevoking privileges.
Concluding remarks including database design andimplementation process.
-
7/28/2019 Class1 FINAL
6/55
EXAMPLES OF DB
Bank to deposit or withdraw funds
Hotel or airline reservation
Access a computerized library
Catalog to search for a bibliographic item, or
Buy some item-such as a book, toy, or computer-from anInternet vendor through its Web page
Purchasing items from a supermarket nowadays in manycases involves an automatic update of the database thatkeeps the inventory of supermarket items.
Chances are that our activities will involve someone orsome computer program accessing a database.
Traditional database applications- most of the information
that is stored and accessed is either textual or numeric
l f d b
-
7/28/2019 Class1 FINAL
7/55
Exciting new applications of database
systems Multimedia databases can now store pictures, video
clips, and sound messages. Geographic information systems (CIS) can store and
analyze maps, weather data, and satellite images.
Data warehouses and online analytical processing
(OLAP) systems are used in many companies to
extract and analyze useful information from very
large databases for decision making.
Real-time and active database technology is used incontrolling industrial and manufacturing processes.
And database search techniques are being applied to
the World Wide Web to improve the search for
information that is needed by users browsing the
-
7/28/2019 Class1 FINAL
8/55
INTRODUCTION
A database is a collection of related data. By data mean known facts that can be recorded
and that have implicit meaning.
For example, consider the names, telephonenumbers, and addresses of the people you know.
You may have recorded this data in
an indexed address book, or
hard drive, using a personal computer and software such as
Microsoft Access, or Excel.
This is a collection of related data with an implicit
meaning and hence is a database.
-
7/28/2019 Class1 FINAL
9/55
Implicit properties of DB
A database represents some aspect of the real
world(miniworld) or the universe of discourse
(DoD). Changes to the miniworld are reflected in
the database.
A database is a logically coherent(REALTED)
collection of data with some inherent meaning.
A database is designed, built, and populatedwith data for a specific purpose. It has an
intended group of users and some preconceived
applications in which these users are interested.
-
7/28/2019 Class1 FINAL
10/55
A database generated and maintained
manually or
computerized.
Manually
A library card catalog
Computerized database
created and maintained either by a group of
application programs written specifically for that
task or by a database management system.
-
7/28/2019 Class1 FINAL
11/55
A database management system (DBMS) is a collection of
programs that enables users to create and maintain a database.
Example:
Oracle DB2 (IBM)
MS SQL Server
MS Access
Ingres PostgreSQL
MySQL
The DBMS is hence a general-purpose software system that
facilitates the processes of defining, constructing, manipulating,
and sharing databases among various users and applications.
-
7/28/2019 Class1 FINAL
12/55
Defining a database involves
specifying the data types, structures, and
constraints for the data to be stored in thedatabase.
Students(sid: string, name: string, login:
string, age: integer, gpa:real)
Courses(cid: string, cname:string,
credits:integer)
Enrolled(sid:string, cid:string, grade:string)
-
7/28/2019 Class1 FINAL
13/55
Constructing the database is the
process of storing the data itself on some storage
medium that is controlled by the DBMS.
Manipulating a database includes such functions
as querying the database to retrieve specific data,
updating the database to reflect changes in theminiworld, and generating reports from the data.
Sharing a database allows
multiple users and programs to access the database
concurrently.
-
7/28/2019 Class1 FINAL
14/55
Database + DBMS software
Database system
Other important functions provided by the DBMSinclude
protecting the database and
maintaining it over a long period of time.
Protection includes both
system protectionagainst hardware or softwaremalfunction (or crashes), and
security protection againstunauthorized or maliciousaccess
A typical large database may have a life cycle of manyyears, so the DBMS must be able to maintain thedatabase system by allowing the system to evolve as
requirements change over time.
-
7/28/2019 Class1 FINAL
15/55
Slide 1- 15
DATABASE SYSTEM
Application Program/Queries
DBMS SOFTWARE
Software to Process Queries/Programs
Software to Access Stored Data
Stored Database
Definition Stored Database
Users/Programmers
-
7/28/2019 Class1 FINAL
16/55
Slide 1- 16
DATABASE SYSTEM
Application Program/Queries
DBMS SOFTWARE
Software to Process Queries/Programs
Software to Access Stored Data
Stored Database
Definition Stored Database
Users/Programmers
1. Data
2. Software
3. Users
4. Hardware
-
7/28/2019 Class1 FINAL
17/55
AN EXAMPLE
a UNIVERSITY database for maintaining
information concerning students, courses, and
grades in a university environment.
-
7/28/2019 Class1 FINAL
18/55
Slide 1- 18
Example of a simple database
Name Student_number Class Major
Smith 17 1 CS
Brown 8 2 CS
STUDENT
h d b d f f l h f h h
-
7/28/2019 Class1 FINAL
19/55
The database is organized as five files, each of which stores
data records of the same type.
The STUDENT file stores data on each studentthe COURSE file stores data on each course
the SECTION file stores data on each section of a course, the
GRADE_REPORT file stores the grades that students receive in
the various sections they have completed, andthe PREREQUISITE file stores the prerequisites of each course.
I) To define this database,
specify the structure of the records of each file
different types of data dements to be stored in each
record.
each STUDENT record includes data to represent the
student's Name, StudentNumber, Class and Major
-
7/28/2019 Class1 FINAL
20/55
Each COURSE record includes data to represent
the CourscName ,CourseNumber, CreditHours,
and Department (the department that offers the
course); and so on.
Also specify a data type for each data element
within a record.
For example, we can specify that Name of
STUDENT is a string of alphabetic characters,
StudentNumber of STUDENT is an integer, and
Grade of GRADE..REPORT is a single characterfrom the set lA, B, C, D, F, l}.
) h d b
-
7/28/2019 Class1 FINAL
21/55
II) To construct the UNIVERSITY database, we store
data to represent each student, course, section, grade
report, and prerequisite as a record in the appropriate
file. Notice that records in the various files may be related.
For example, the record for "Smith" in the STUDENT file
is related to two records in the GRADE_REPORT file thatspecify Smith's grades in two sections.
Similarly, each record in the PREREQUISITE file relates
two course records: one representing the course andthe other representing the prerequisite.
Most medium-size and large databases include many
types of records and have many relationships among
the records
) b i l i i l i d
-
7/28/2019 Class1 FINAL
22/55
III) Database manipulation involves querying and
updating.
Examples of queries are
"retrieve the transcript-a list of all courses and grades-
of Smith,"
"list the names of students who took the section of the
Database course offered in fall 1999 and their grades inthat section," and
"what are the prerequisites of the Database course
These informal queries and updates must bespecified in the query language of the DBMS
before they can be processed.
-
7/28/2019 Class1 FINAL
23/55
CHARACTERISTICS OF
THE DATABASE APPROACH
Distinguish the database approach from the
traditional approach of programming with files.
File processing,each user defines and implements
the files needed for a specific software application aspart of programming the application.
Eg. the grade reporting office, may keep a file on
students and their grades. Programs to print a
student's transcript and to enter new grades into the
file are implemented as part of the application.
A second user, the accounting office, maykeep track
of students' fees and their payments.
B h i d i d b d
-
7/28/2019 Class1 FINAL
24/55
Both users interested in data about students,
Each user maintains separate files-and programs to
manipulate these files-because each requires some
data not available from the other user's files.
This redundancy in defining and storing data
results in wasted storage space and in redundant
efforts to maintain common data up to date.
Database approacha single repositoryof data is
maintained that is defined once and then is
accessed by various users
-
7/28/2019 Class1 FINAL
25/55
Database approach versus the file-
processing approach
Self-describing nature of a database 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.
Insulation between programs and data, and dataabstraction
Support of multiple views of the data Sharing of data and multiuser transaction
processing
-
7/28/2019 Class1 FINAL
26/55
Self-Describing Nature of a Database
System
database system contains not only the databaseitself but also a complete definition or descriptionof the database structure and constraints.
This definition is stored in the 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.
The information stored in the catalog is calledmeta-data, and it describes the structure of theprimary database
A general purpose DBMS software package is not
-
7/28/2019 Class1 FINAL
27/55
A general-purpose DBMS software package is notwritten for a specific database application, andhence it must refer to the catalog to know the
structure of the files in a specific database, such as thetype and format of data it will access.
The DBMS software must work equally well withany number of database applications-for example, a
university database, a banking database, or acompany database-as long as the database definition is stored in the catalog
traditional file processing data definition istypically part of the application programsthemselves. Eg. program written in c++ may havestruct or class declarations
DBMS catalog will store the definitions of all the files
-
7/28/2019 Class1 FINAL
28/55
DBMS catalog will store the definitions of all the filesshown.
These definitions are specified by the database
designer prior to creating he actual database and arestored in the catalog.
Whenever a request is made to access, say, the Nameof a STUDENT record, the DBMS software refers to the
catalog to determine the structure of the STUDENT file and the position and
size of the Name data item within a STUDENT record.
By contrast, in a typical file-processing application, thefile structure and, in the extreme case, the exactlocation of Name within a STUDENT record are alreadycoded within each program that accesses this data
item.
-
7/28/2019 Class1 FINAL
29/55
Insulation between Programs and
Data, and Data Abstraction
traditional file processing, the structure of datafiles is embedded in the application programs, soany changes to the structure of a file may requirechanging all programs that access
DBMS access programs do not require suchchanges in most cases.
The structure of data files is stored in the DBMS
catalog separately from the access programs. We call this property program-data
independence.
program operation independence object
-
7/28/2019 Class1 FINAL
30/55
program-operation independence object-oriented and object-relational systems
The characteristic that allows program-data
independence and program-operationindependence is called data abstraction.
A DBMS provides users with a conceptual
representation of data that does not includemany of the details of how the data is stored orhow the operations are implemented.
Informally, a data model is a type of data
abstraction that is used to provide thisconceptual representation.
-
7/28/2019 Class1 FINAL
31/55
Data model
The data model uses logical concepts, such as
objects,
their properties, and
their interrelationships, that may be easier formost users to understand than computer storageconcepts.
Hence, the data model hides storage and
implementation details that are not ofinterest to most database users.
-
7/28/2019 Class1 FINAL
32/55
internal implementation of a file STUDENT record
Typical database user is not concerned with the databaseuser with the location of each data item within a record orits length; rather, the concern is that when a reference is
made to Name of STUDENT, the correct value is returned. Many data models can be used to provide this data
abstraction to database users.
-
7/28/2019 Class1 FINAL
33/55
Support of Multiple Views of the Data
A database typically has many users, each of
whom may require a different perspective or
view of the database.
A view may be a subset of the database or it
may contain virtual data that is derived from
the database files but is not explicitly stored.
-
7/28/2019 Class1 FINAL
34/55
Sharing of Data and Multiuser
Transaction Processing
A multiuser DBMS must allow multiple
users to access the database at the same time.
This is essential if data for multiple
Applications is to be integrated and
maintained in a single database.
-
7/28/2019 Class1 FINAL
35/55
Concurrency control software
The DBMS must include concurrency control softwareto ensure that several users trying to update thesame data do so in a controlled manner so that theresult of the updates is correct.
For example, when several reservation clerks try toassign a seat on an airline flight, the DBMS shouldensure that each seat can be accessed by only oneclerk at a time for assignment to a passenger.
These types of applications are generally calledonline transaction processing (OLTP) applications.
A fundamental role of multiuser DBMS software is toensure that concurrent transactions operatecorrectly.
-
7/28/2019 Class1 FINAL
36/55
Transaction A transaction is an executing program or process that
includes one or more database accesses, such asreading or updating of database records.
Each transaction is supposed to execute a logicallycorrect database access if executed in its entirety
without interference from other transactions. The DBMS must enforce several transaction properties.
The isolation property ensures that each transactionappears to execute in isolation from other transactions,
even though hundreds of transactions may beexecuting concurrently.
The atomicity property ensures that either all thedatabase operations in a transaction are executed ornone are.
-
7/28/2019 Class1 FINAL
37/55
Database Users
Users may be divided into:
those who actually use and control the
content (called Actors on the Scene)
those who enable the database to be
developed and the DBMS software to be
designed and implemented (called Workers
Behind the Scene).
-
7/28/2019 Class1 FINAL
38/55
Database Users 2.
ActorsDatabase administrators: responsible
for access to the database,
for coordinating and monitoring its use, acquiring software/hardware resources, controlling its use and monitoring run-time performance.
DBA is accountable for problems suchas breach of security or poor system response time.
End-users: use the data for queries, reports and some even
-
7/28/2019 Class1 FINAL
39/55
End users: use the data for queries, reports and some evenupdate database content.
categories of end users:
1) Casual end users: occasionally access the database-(middle- or high-level managers)
2) Nave or parametric endusers: Their main job functionrevolves around constantly querying and updating thedatabase, using standard types of queries and updates-
called canned transaction Bank tellers check account balances and post
withdrawals and deposits.
Reservation clerks fur airlines, hotels, and car rental
companies check availability for a given request andmake reservations.
Clerks at receiving stations for courier mail enterpackage identifications via barcodes and descriptiveinformation through buttons to update a central
database of received and in-transit packages.
3) Sophisticated end users include engineers
-
7/28/2019 Class1 FINAL
40/55
3) Sophisticated end users include engineers,
scientists, business analysts, and others who
thoroughly familiarize themselves with the
facilities of the DBMS so as to implement theirapplications to meet their complex requirements
4) Stand-alone users maintain personal databases by
using ready-made program packages that provide
easy-to-use menu-based or graphics-based
interfaces. An example is the user of a tax package
Data ase Designers:
-
7/28/2019 Class1 FINAL
41/55
Data ase Designers:
Database designers are responsible for identifyingthe data to be stored in the database and forchoosing appropriate structures to represent and storethis data.
These tasks are mostly undertaken before the databaseis actually implemented and populated with data.
It is the responsibility of database designers tocommunicate with all prospective database users inorder to understand their requirements, and to comeup with a design that meets these requirements.
responsible to define the content, structure,constraints, and functions or transactions against thedatabase. They communicate with the end-users andunderstand their needs.
-
7/28/2019 Class1 FINAL
42/55
Other DBS Personnel
System analysts and application programmers
Operators and maintenance personnel
Tool developers
DBMS Designers andProgrammers
-
7/28/2019 Class1 FINAL
43/55
advantages of using a DBMS & capabilities
a good DBMS should possess
Controlling Redundancy file processing Each user maintains own files for their applications
For example, consider the UNIVERSITY database; two groups ofusers might be the
course registration personnel and the accounting office.
In the traditional approach, each group independently keeps fileson students.
The accounting office also keeps data on registration and related
billing information, whereas the registration office keeps track ofstudent courses and grades.
Much of the data is stored twice: once in the files of each usergroup.
This redundancy in storing the same data multiple
-
7/28/2019 Class1 FINAL
44/55
This redundancy in storing the same data multiple
times leads to several problems.
There is the need to perform a single logical update-such as
entering data on a new student-multiple times: once for each filewhere student data is recorded.
This leads to duplication of effort.
Second, storage space is wasted
when the same data is storedrepeatedly,
Third, files that represent the same data may becomeinconsistent.
This may happen because an update is applied to some of the files
but not to others. Even if an update-such as adding a new student-is
applied to all the appropriate files, the data concerning the studentmay still be inconsistent because the updates are applied
independently by each user group. For example, one user group may
enter a student's birthdate erroneously as JAN-19-1984, whereas the
other user groups may enter the correct value of JAN-29-1984.
-
7/28/2019 Class1 FINAL
45/55
Ideally, we should have a database design that stores each
-
7/28/2019 Class1 FINAL
46/55
y, glogical data item-such as a student's name or birth date-inonly one place in the database.
Eg: store Student-Name and Course-Number redundantly in a
GRADE_REPORT file (Prev fig) whenever we retrieve aGRADE_REPORT record, we want to retrieve the studentname and course number along with the grade, studentnumber, and section identifier.
By placing all the data together, we do not have to searchmultiple files to collect this data.
In such cases, the DBMS should have the capability to controlthis redundancy so as to prohibit inconsistencies among the
files. Prev Figure (b) shows a GRADE3EPORT record that is
inconsistent with the STUDENT file of Figure , which may beentered erroneously if the redundancy is not controlled.
Restricting Unauthorized Access
-
7/28/2019 Class1 FINAL
47/55
Restricting Unauthorized Access
Financial data is often considered confidential, and
hence only authorized persons are allowed to
access such data.
Some users may be permitted only to retrieve
data, whereas others are allowed both to retrieve
and to update.
users or user groups are given account numbers
protected by passwords, which they can use to gain
access to the database
A DBMS should provide a security and authorization
subsystem, which the DBA uses to create accounts
and to specify account restrictions.
Providing Persistent Storage for Program Objects
-
7/28/2019 Class1 FINAL
48/55
g g g j
Databases can be used to provide persistent storage forprogram objects and data structures.
Programming languages have complex datastructures, such as record types in Pascal or classdefinitions in c++ or Java.
The values of program variables are discarded once
a program terminates, unless the programmerexplicitly stores them in permanent files, whichinvolves converting these complex structures into aformat suitable for file storage.
When the need arises to read this data once more,the programmer must convert from the file format tothe program variable structure.
DBMS software automatically performs any
-
7/28/2019 Class1 FINAL
49/55
DBMS software automatically performs any
necessary conversions.
Hence, a complex object in c++ can be stored
permanently in an object-oriented DBMS. Such an
object is said to be persistent, since it survives the
termination of program execution and can later be
directly retrieved by another c+ + program
Providing Storage Structures for Efficient Query Processing
-
7/28/2019 Class1 FINAL
50/55
Database systems must provide capabilities for efficientlyexecuting queries and updates.
Because the database is typically stored on disk, the DBMSmust provide specialized data structures to speed up disksearch for the desired records.
Auxiliary files called indexes are used for this purpose.
Indexes are typically based on tree data structures or hashdata structures, suitably modified for disk search.
In order to process the database records needed by aparticular query, those records must be copied from disk tomemory.
Hence, the DBMS often has a buffering module thatmaintains parts of the database in main memory buffers.
In other cases, the DBMS may use the operating system to do
the buffering of disk data.
Providing Backup and Recovery
-
7/28/2019 Class1 FINAL
51/55
A DBMS must provide facilities for recovering fromhardware or software failures.
Backup and recovery subsystem of the DBMS is
responsible for recovery Recovery subsystem could ensure that the transaction is
resumed from the point at which it was interrupted sothat its full effect is recorded in the database.
Providing Multiple User Interfaces query languages for casual users, programming language
interfaces for application programmers, forms andcommand codes for parametric users, and menu-driveninterfaces and natural language interfaces for stand-alone
users. Both forms-style interfaces and menu-driven interfaces
are commonly known as graphical user interfaces (GU Is).
Capabilities for providing Web GUl interfaces are common
Representing Complex Relationships among
-
7/28/2019 Class1 FINAL
52/55
Representing Complex Relationships among
Data
A DBMS must have the capability to represent a
variety of complex relationships among the data
as well as to retrieve and update related data
easily and efficiently.
Enforcing Integrity constraints
The simples type of integrity constraintspecifying a
-
7/28/2019 Class1 FINAL
53/55
p yp g y p y gdata type for each data item.
Specify value of the Class data item within each
STUDENT record must be an integer between 1 and 5and that the value of Name must be a string of no morethan 30 alphabetic characters.
A more complex type of constraint frequently
occurs involves specifying that a record in one file mustbe related to records in other files.
For example, in Figure, we can specify that "everysection record must be related to a course record."
Another type of constraint specifies uniqueness ondata item values, such as "every course record musthave a unique value for CourseNumber."
Some database systems provide capabilities for
-
7/28/2019 Class1 FINAL
54/55
y p p
defining deduction rules for inferencing new
information from the stored database facts.
Such systems are called deductive database
systems.
For example, there may be complex rules in the
miniworld application for determining when astudent is on probation.
These can be specified declaratively as rules, which
when compiled and maintained by the DBMS candetermine all students on probation.
HISTORY OF DATABASE
-
7/28/2019 Class1 FINAL
55/55
HISTORY OF DATABASE
ApPlICATIONS
Early Database Applications Using
Hierarchical and Network Systems