Characteristics of
DBMS
- Data Integrity
- Security
- Consistency
- Roll-back
- Data
- 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
- 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
Normalization
- 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
- 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
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
