Unit 1: DBMS overview
Database Management Systems notes · PTU syllabus (BSIT402/BSBC404)
On this page
Unit summary
A database management system replaces scattered files with one shared, controlled store of data. This unit covers file processing systems versus database systems, the responsibilities of the database administrator, physical and logical data independence, and the three-level architecture — external, conceptual and internal levels.
After this unit you can
- Compare file processing systems with database systems
- Describe the responsibilities of the DBA
- Distinguish physical and logical data independence
- Explain the three-level ANSI/SPARC architecture
PTU syllabus topics
- File processing systems vs database systems
- database administrator responsibilities
- physical and logical data independence
- three-level architecture — external
- conceptual and internal levels
- External level
User views
- Conceptual level
Whole logical structure: tables, relationships
- Internal level
Physical storage on disk
Topic 1
Data, database and DBMS
- Data: raw facts (e.g. "Ana", 85, "BCA").
- Information: processed, meaningful data ("Ana scored 85 in BCA").
- Database: an organised collection of related data stored so it can be accessed easily.
- DBMS: software that creates, stores, manages and controls access to databases. Examples: MySQL, Oracle, PostgreSQL, SQL Server, MongoDB.
Applications: banking (accounts, transactions), airlines and railways (reservations), universities (students, results), hospitals, e-commerce (orders, inventory), telecom and social media.
Topic 2
File processing systems vs database systems
- File processing system: each application keeps its own files (fees office, library, examination branch), written and read by separate programs.
- Data redundancy
- Same student address stored in many files
- Inconsistency
- Address updated in one file but not the others
- Difficult access
- A new report needs a new program
- Data isolation
- Data scattered in different formats
- Integrity problems
- Rules such as marks ≤ 100 buried in program code
- Atomicity problems
- A crash midway leaves a fee transfer half done
- Concurrent access anomalies
- Two clerks update the same record at once
- Security problems
- Hard to give each user only the data they need
Topic 3
File system vs DBMS: comparison
Redundancy
High: same data in many files
Controlled
Consistency
Inconsistent copies are common
Maintained by constraints
Data access
Need a new program for each query
Easy queries with SQL
Security
Weak
User-level access control
Concurrency
Problems with simultaneous users
Managed safely
Backup and recovery
Manual
Built in
Disadvantages of a DBMS: high cost of software and hardware, complexity, need for trained staff, and performance overhead for very small, simple applications.
Topic 4
Responsibilities of the database administrator
| User | Role |
|---|---|
| Database Administrator (DBA) | Manages the whole database: schema, security, backup, performance |
| Database designers | Decide the structure: tables, keys, relationships |
| Application programmers | Write programs that use the database |
| End users (naive and sophisticated) | Use the database through forms or queries |
Responsibilities of the DBA: defining the schema, granting access, backup and recovery, monitoring performance, and maintaining integrity and security.
Schema and storage definition
Create the conceptual schema, decide storage structures and indexes
Security and authorisation
Create users, grant and revoke privileges
Backup, recovery and availability
Schedule backups, restore after failures, plan disaster recovery
Performance and maintenance
Monitor and tune queries, upgrade software, manage space, enforce integrity constraints
Topic 5
Three-level architecture
- The ANSI/SPARC architecture separates how users see data from how it is stored, through three levels of schema.
- External level
Individual user views — what each user or application sees (fees clerk sees fee details only)
- Conceptual level
The whole database for the organisation — all entities, attributes, relationships and constraints, independent of storage
- Internal level
Physical storage — files, records, indexes, access paths, compression
- Mappings: external–conceptual mapping links each view to the conceptual schema; conceptual–internal mapping links the conceptual schema to stored files.
Example
A college database: the student portal (external) shows a student's marks; the conceptual schema defines STUDENT, COURSE and RESULT tables; the internal level stores RESULT as a B+ tree indexed file on disk.
Topic 6
Physical and logical data independence
- Data independence: the ability to change the schema at one level without changing the schema at the next higher level.
Meaning
Change the internal schema without changing the conceptual schema
Change the conceptual schema without changing external views or programs
Examples
New index, different file organisation, moving to SSD
Adding a column or a table, splitting a table
Achieved by
Conceptual–internal mapping
External–conceptual mapping
Difficulty
Easier to achieve
Harder, because programs depend on structure
- Schema vs instance: the schema is the design (changes rarely); the instance is the data at a moment (changes constantly).
Key terms
- Data redundancy
- Unnecessary duplication of data
- DBA
- Person responsible for overall control of the database
- Schema
- Overall design of a database
- Conceptual level
- Community view of the whole database
- Data independence
- Changing one schema level without affecting the next higher level
Quick revision
- Data, information, database, DBMS.
- File system problems: redundancy, inconsistency, isolation, integrity, atomicity, concurrency, security.
- DBA duties: schema, security, backup, performance.
- External, conceptual, internal levels; two mappings.
- Physical vs logical data independence.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.State three drawbacks of file processing systems.
- Q2.List four responsibilities of a DBA.
- Q3.Name the three levels of DBMS architecture.
- Q4.What is physical data independence?
- Q5.Why is logical data independence harder to achieve?
- Q6.Distinguish schema and instance.
Long-answer questions
- Q1.Compare file processing systems with database systems.
- Q2.Explain the role and responsibilities of the database administrator.
- Q3.Explain the three-level architecture of a DBMS with a diagram.
- Q4.Explain physical and logical data independence with examples.
Stuck on this unit?
Message SBS on WhatsApp for help with Database Management Systems, or to ask about studying B.Sc IT at Synetic.