RELATIONAL DATABASE MANAGEMENT SYSTEM I Subject code BCA12

RELATIONAL DATABASE MANAGEMENT SYSTEM - I Subject code : BCA-12 and PGDCA 1

Overview of DBMS Section A 2

Database • Related information when placed in an organized form makes a database. • Examples – Dictionary – Telephone Directory – Student record register – Address book • Two approaches for storing data in computers– File based approach – Database approach 3

Why do we need a database? • Keep records of our: – Clients – Staff – Volunteers • To keep a record of activities and interventions; • Keep sales records; • Develop reports; • Perform research

File based Approach • A group of files storing data of an organization. • Each file is independent from one another. • Each file contains and processes information for one specific function, such as accounting or inventory. • Files are designed using programs written in programming languages such as COBOl, C, C++. • File systems may use a storage device such as hard disk or CD-ROM etc and involve maintaining physical location of the files. 5

Database Approach • A shared collection of logically related data, designed to meet the information needs of an organization. • Database is a single, large repository of data, which can be used simultaneously by many departments and users. Characteristics of Database • Shared • Persistence • Validity/Integrity/Correctness • Security • Consistency • Non-redundancy • Data Independence 6

Database Management System (DBMS) A software system that • Allows users to define, create and maintain a database • Provides controlled access to database • DBMS provides an environment that is both convenient and efficient to use. Examples – Computerized Library systems – Online Examination Application form – Flight reservations systems 7

• Database Applications: – Banking: all transactions – Airlines: reservations, schedules – Universities: registration, grades – Sales: customers, products, purchases – Manufacturing: production, inventory, orders, supply chain – Human resources: employee records, salaries, tax deductions • Commercially available DBMS are – – Dbase Fox. Pro IMS Oracle 8

Purpose of Database System • In the early days, database applications were built on top of file systems • Drawbacks of using file systems to store data: – Data redundancy and inconsistency • Multiple file formats, duplication of information in different files – Difficulty in accessing data • Need to write a new program to carry out each new task – Data isolation — multiple files and formats – Integrity problems • Integrity constraints (e. g. account balance > 0) become part of program code • Hard to add new constraints or change existing ones 9

Purpose of Database Systems (Cont. ) • Drawbacks of using file systems (cont. ) – Atomicity of updates • Failures may leave database in an inconsistent state with partial updates carried out • E. g. transfer of funds from one account to another should either complete or not happen at all – Concurrent access by multiple users • Concurrent accessed needed for performance • Uncontrolled concurrent accesses can lead to inconsistencies. E. g. two people reading a balance and updating it at the same time – Security problems • Database systems offer solutions to all the above problems 10

Major Components of Database System Data Hardware (Computer System) Software (DBMS) Users (Naïve , Online, Sophisticated users, Application Programmers, DBA) • Procedures (Instructions) • • 11

Advantages of DBMS • • Controlling Redundancy Integrity can be enforced Inconsistency can be avoided Data can be shared Standards can be enforced Restricting unauthorized access Providing Backup and recovery 12

Disadvantages of DBMS • • • Complexity Size Performance Higher impact of failure Cost of DBMS Additional Hardware costs 13

Architecture of DBMS • The American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) proposed a three layer model for DBMS. • 3 Levels of DBMS are: – External Level – Conceptual Level – Internal Level • This model was later known as ANSI SPARC architecture of DBMS. 14

Levels of DBMS • External level or User View – Highest level of DBMS – Closest to the user – Concerned with the way data is viewed by individual users – Only those portions of the database that are of concern to the user or application program are included. – Same database can have different views for different users. – Each External view is described by means of a scheme called an external Schema. – The external schema consists of the definition of logical records and the relationships in external view. • Note: -A schema/scheme is an outline or plan that describes the records and relationships existing in the view. 15

Conceptual level or Logical View • Level of indirection between external and internal level. • Describes what data are actually stored in the database and the relationship that exist among the data. • Defined by Conceptual schema which describes all records and relationships included in the database. • There is only one Conceptual Schema per database. • Defined by DBA. 16

Internal level or Physical View • Lowest level of Architecture • Closest to physical Storage i. e. how data is stored in the database. • Describes the data structures and Access methods to be used by the database. • Expressed by Internal Schema which contains the definition of the stored record, the method of representing the data fields and the access aids used. • Defined by DBA. 17

View of Data An architecture for a database system 18

3 Level ANSI Sparc Architecture of DBMS 19

Mapping b/w Views • External/Conceptual Mapping – Each external schema is related to conceptual schema by external /conceptual mapping – It gives the correspondence among records and relationships of external/conceptual views. • Conceptual/Internal Mapping – Conceptual schema is related to Internal Schema by Conceptual / internal mapping – This enables the DBMS to find the actual record or combination of records in physical storage that constitute a logical record in conceptual schema. – Mapping b/w two levels specifies the method of deriving the conceptual record from physical database. 20

Data Abstraction • Major purpose of DBMS is to provide an abstract view of data • i. e. system hides certain details of how data is stored and maintained. • Since many users are not computer trained complexity is hidden from them through several levels of abstraction. • 3 levels of abstraction – Internal Level or Physical Level – Conceptual Level or Logical Level – External Level or View Level 21

Levels of Abstraction • Physical level describes how a record (e. g. , customer) is stored. • Logical level: describes data stored in database, and the relationships among the data. type customer = record name : string; street : string; city : integer; end; • View level: application programs hide details of data types. 22

Instances and Schemas • Schema – the logical structure of the database or overall description of the database. – e. g. , the database consists of information about a set of customers and accounts and the relationship between them) – Analogous to type information of a variable in a program – Physical schema: database design at the physical level – Logical schema: database design at the logical level – Subschema: External view is defined by a subschema • Instance – the actual content of the database at a particular point in time – Analogous to the value of a variable 23

DATA INDEPENDENCE • Ability to modify schema definition in one level without effecting a schema definition in next higher level is Data Independence. • Physical Data Independence – the ability to modify the physical schema without changing the logical schema – Applications depend on the logical schema – In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others. • Logical Data Independence – indicates that conceptual schema can be changed without affecting existing external schema 24

Database Administrator(DBA) • A person or group of persons responsible for overall control of database systems. • Main responsibilities of DBA are: – Deciding the information content of the database – Deciding the storage structures and access strategy – Deciding authorization checks and validation procedures – Granting of authorization for data access – Defining a strategy for backup and recovery – Monitoring performance and responding to changes in requirements. 25

Data Dictionary • A metadata (Data Dictionary) is the data about the data. • Also known as system catalog. • DD is used to control the database operation, data integrity and accuracy. • Contains information regarding database • Provides the name of the data element, its description and data structure in which it may be found. • Provides great assistance in producing a report of where a data element is used in all programs that mention it. 26

Database Languages • Data Definition Languages (DDL) – It is a language that allows the users to define data and their relationship to other types of data. – It is mainly used to create files, databases , data dictionary and tables within databases. • Data Manipulation Languages (DML) – It is a language that provides a set of operations to support the – • • basic data manipulation operations on the data held in the databases. By manipulation we mean: to retrieve information stored in the database To insert new information in the database To delete unwanted information from the database To modify existing data in the database • Data Control Language (DCL) – DCL helps to control access to data and the database using 27 statements grant and revoke

Types of Database Systems • On the basis of no. of users – Single user DBMS – Multi user DBMS • On the basis of Site Location – Centralized DBMS – Parallel DBMS – Distributed DBMS – Client/Server DBMS 28

Data Models • A model is a representation of reality, ' real world’ objects and events, and their associations. • An integrated collection of concepts for describing and manipulating – data relationships – data semantics – data constraints • A data model comprises of 3 components – A structural part – A manipulative part – A set of integrity rules 29

Types of Data Models • Basically data models fall into 3 broad categories – Object based Data Models • Entity-Relationship model • Object Oriented Model – Physical Data Model – Record based Data Model • Hierarchical Model • Network Model • Relational Model 30

Entity-Relationship Model Example of schema in the entity-relationship model 31

Entity Relationship Model (Cont. ) • E-R model of real world – Entities (objects) • E. g. customers, accounts, bank branch – Relationships between entities • E. g. Account A-101 is held by customer Johnson • Relationship set depositor associates customers with accounts • Widely used for database design – Database design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing 32

Relational Model Attributes • Example of tabular data in the relational model Customerid customername customerstreet customercity accountnumber 192 -83 -7465 Johnson South Palo Alto A-101 019 -28 -3746 Smith North Rye A-215 192 -83 -7465 Johnson South Palo Alto A-201 321 -12 -3123 Jones West Harrison A-217 019 -28 -3746 Smith North Rye A-201 33

A Sample Relational Database 34

Data Definition Language (DDL) • Specification notation for defining the database schema – E. g. create table account ( account-number char(10), balance integer) • DDL compiler generates a set of tables stored in a data dictionary • Data dictionary contains metadata (i. e. , data about data) – database schema – Data storage and definition language • language in which the storage structure and access methods used by the database system are specified • Usually an extension of the data definition language 35

Data Manipulation Language (DML) • Language for accessing and manipulating the data organized by the appropriate data model – DML also known as query language • Two classes of languages – Procedural – user specifies what data is required and how to get those data – Nonprocedural – user specifies what data is required without specifying how to get those data • SQL is the most widely used query language 36