Database Management System Essentials

Database Management System Unit I By Dharani.k

Database Architecture Database system Operational data Storage structure Data structures and corresponding operators

DBMS Application program End-user

Major components of a database system: Data: Integrated shared. Hardware: consists of secondary storage volumes. disk, CPU, Main Memory, ... Software: Between the physical data itself and the users of the system is a layer of software , usually called DBMS. Users: 1. Application programmers 2. End users 3. Database administrator (DBA)

A database is a collection of stored operational data used by the application systems of some particular enterprise. Example Manufacturing company - Product data Bank - Account data Hospital - Patient data University - Student data Government Department - Planning data Entity Association Relationships

Redundancy can be reduced Inconsistency can be avoided The data can be shared Standards can be enforced Security restrictions can be applied Integrity can be maintained Conflicting requirement can be balanced

Three levels of architecture Internal Physical storage Conceptual- Users-viewed by individual users External level of indirection between others two External view Conceptual view Internal view

Each user should be able to access the same data, but have a different customized view of the data. Each user should be able to change the way he or she views the data, and this change should not affect other users. Users should not have to deal directly with physical database storage details, such as indexing or hashing interaction with the database should be independent of storage considerations. The Database Administrator (DBA) should be able to change the database storage structures without The internal structure of the database should be unaffected by changes to the physical aspects of storage, such as the changeover to a new storage device.

The DBA should be able to change the conceptual structure of the database without affecting all users. External Level or View level This level describes that part of the database that is relevant to each user. External level is the one which is closest to the end users. This level deals with the way in which individual users vie\v data. Individual users are given different views according to the user requirement. Conceptual Level or Logical level It is the community view of the database. This level describes what data is stored in the database and the relationships among the data. The middle level in the three level architecture is the conceptual level. This level contains the logical structure of the entire database as seen by the DBA. It is a complete view of the data requirements of the organization that is independent of any storage considerations.

Data definition language (DDL) Defines conceptual schema Storage definition language (SDL) Specifies the internal schema Data manipulation language (DML) Allows retrieval, insertion, deletion, modification Low-level or procedural DML Must be embedded in a general-purpose programming language Record-at-a-time High-level or non-procedural DML Can be used on its own to specify complex database operations concisely Set-at-a-time or set-oriented

User A1 User A2 User B1 User B2 User B3 Host Language + DSL Host Language + DSL Host Language + DSL Host Language + DSL Host Language + DSL (Data Sub Language) e.g. SQL, QUEL External View B External View @ # A & External schema A External schema B External/conceptual mapping B External/conceptual mapping A Database management system (DBMS) Conceptual View @ Conceptual schema < & #A Conceptual/internal mapping DBA (Build and maintain schemas and mappings) Storage structure definition (Internal schema) Stored database (Internal View) # @ &

The stored record Interface user User interface DBMS DBMS Stored record interface Access method Physical record interface

Three approaches are The relational it represent relational view of the data. The hierarchical it represented by a tree structures. The network - data is represented by records and links. - it allow us to model many to many correspondence - connector

S# S# P# P# Qty Qty S# s1 S# sname smith sname status 20 status city city Lond on Paris paris SP S s1 p1 300 s2 s3 jones blake 10 30 s1 p2 200 s1 p3 400 P# P# pnam e e nut pnam color color weigh t t 12 weigh city city P s2 p1 300 p1 red londo n paris rome p2 p3 bolt screw green blue 17 17 s2 p2 400 s3 p2 200

P1 P1 Nut Nut Red Red 12 12 london london S2 S2 Jones Smit h h Jones Smit 10 London 10 London Pairs 300 Pairs 300 300 300 S1 S1 20 20 p3 p3 screw screw blue blue 17 17 rome rome s1 s1 smith smith 20 20 london london 400 400