Database Management System - 1, MCA DBMS Material Overview

Characteristics of DBMS
  • Data Integrity
  • Security
  • Consistency
  • Roll-back
  • Data Independence
  • Back-up Procedures
  • Concurrency Control
  • Version Control

DBMS Model
  • Hierarchical Model
  • Network Model
  • Relational Model

Components of DBMS
  • Data
  • Users
    • Report Writing
    • Query Language
    • Spreadsheets
    • End-user Interfaces
  • Hardware
  • Software
    • Command
    • Editing
    • Queries
    • Reporting
    • Analysis

Types of DBMS
  • Operational Databases
  • Analytical Databases
  • Distributed Databases
  • Personal End User Databases
  • Multimedia Databases
  • Special Purpose Databases

Transaction Management
  • Concept of Transaction
  • Transaction Outcomes
  • Transaction Support
  • Properties of Transactions
    • Atomicity
    • Consistency
    • Independence
    • Durability

Primary, Foreign and Candidate Keys
      • Identify and define the primary key attributes for each entity
      • Validate primary keys and relationships
      • Migrate the primary keys to establish foreign keys
·                                                         Define Primary Key attributes
·                                                         Validate Keys and Relationships
·                                                         A foreign key
·                                                         Candidate key
            Uniqueness property – No two distinct tuple of R have the same value for K
            Irreducibility property – No proper subset of K has uniqueness property

Functions of a DBMS
  • Data Definition
  • Data Manipulation
  • Data Security & Integrity
  • Data Recovery & Concurrency
  • Data Dictionary Maintenance
  • Performance

Role of the Database Administrator
  • Defining the Schema
  • Liaising with Users
  • Defining Security & Integrity Checks
  • Defining Backup / Recovery Procedures
  • Monitoring Performance

  • Basic Concepts
  • Functional Dependencies
  • Simple Data
    • S# - supplier identification number (this is the primary key)
    • Status – status code assigned to city
    • City – name of city where supplier is located
    • P# - part number of part supplied
    • Qty> - quantity of parts supplied to date
o   First Normal Form
      • Insert
      • Delete
      • Update
  • Second Normal Form
    • S# - city, status
    • City – status
    • (s#,p#) – qty

The Entity – Relationship Model
  • Entities
  • Attributes
  • Connectivity and Cardinality
    • One to one (1:1)
    • One to many (1:N)
    • Many to many (M:M)
o   ER Notation

DBMS Architecture
o   External – individual user view
o   Conceptual – community user view
o   Internal – physical or storage view

Advantages of DBMS
  • Reduction of redundancies
  • Shared Data
  • Integrity
  • Security
  • Conflict Resolution
  • Data Independence

Disadvantages of DBMS
  • Larger file size
  • Increased complexity
  • Greater impact of failure
  • More difficult recovery

Properties of  DBMS
  • There are no duplicate tuples
  • Tuples are unordered (top to bottom)
  • Attributes are unordered (left to right)
  • All Attribute values are atomic

Codd’s Twelve Rules
  • The information rule
  • Guaranteed access rule
  • Systematic treatment of null values
  • Dynamic online catalog based on the relational model
  • Comprehensive data sub language rule
    • Data definition
    • View definition
    • Data manipulation (interactive and by program)
    • Integrity constraints
    • Authorization
    • Transaction boundaries (begin, commit and rollback)
o   View updating rule
o   High – level insert, update and delete
o   Physical data independence
o   Logical data independence
o   Integrity independence
o   Distribution independence
o   Non subversion rule

DDL (Data Definition Language)
o   Create
o   Drop
o   Alter
o   Truncate
o   DBCC (Database Console Commands)

DML (Data Manipulation Language)
  • 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
  • A DML is a language which enables users to access and manipulate data
Two types of DML
o   Procedural
o   Nonprocedural
o   Easier for user
o   May not generate code as efficient as that produced by procedural languages

DCL (Data Control Language)
  • GRANT to allow specified users to perform specified tasks
  • REVOKE to cancel previously granted or denied permissions

  • CONNENCT allows a user to connect to the database or schema
  • SELECT allows a user to select records
  • INSERT allows a user to insert records
  • UPDATE allows a user to update records
  • DELETE allows a user to delete records
  • USAGE allows a user to use a database object such as a schema or a function

ACID (Atomicity, Consistency, Independence and Durability)


Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More