Database Concepts & DBMS
A school office story
A school keeps student details in separate files: the admission clerk has one spreadsheet, the fee clerk another and the library a third. When Riya moves house, her new address is updated in the admission file only. Next month the fee receipt goes to the old address and the library sends a notice there too. This is the problem databases solve.
File system and its limitations
In a file system, data is stored in separate files managed by individual programs. It works for small tasks but has serious limitations:
Database and DBMS
A database is an organised collection of related data stored so that it can be easily accessed, managed and updated. A Database Management System (DBMS) is the software that creates and manages the database and lets users and programs work with it.
Now Riya's address is stored once in a STUDENT table; the fee and library sections refer to her roll number. Update it once and everyone sees the new address.
Key DBMS terms
| Term | Meaning |
|---|---|
| Database schema | The design or structure of the database — tables, columns, types and relationships. |
| Data constraint | A rule that data must follow, e.g. marks between 0 and 100, roll number cannot be empty. |
| Meta-data (data dictionary) | "Data about data" — column names, data types, constraints. |
| Database instance | The actual data in the database at a particular moment. |
| Query | A request to the database to get or change data (written in SQL). |
| Data manipulation | Inserting, updating, deleting and retrieving data. |
| Database engine | The core part of the DBMS that stores and processes the data. |
Advantages of DBMS
File system
- Data repeated in many files
- Copies may disagree
- New program for each new report
- Weak security, no access control
- Backup is manual
DBMS
- Redundancy controlled – store once
- Consistency – one update everywhere
- Easy queries with SQL
- User accounts and permissions
- Backup, recovery and multi-user control
Where databases are used
Popular DBMS software
Most databases today are relational (store data in tables). Popular RDBMS: MySQL (free, open source — used in this course), PostgreSQL, Oracle, Microsoft SQL Server, SQLite (inside mobile apps) and MariaDB.
Summary & practice
- File systems suffer from redundancy, inconsistency, difficult access, isolation and poor security.
- Database = organised related data; DBMS = software that manages it.
- Terms: schema, constraint, meta-data, instance, query, data manipulation, engine.
- DBMS controls redundancy, keeps consistency, gives security and easy querying.
1) A shop keeps a customer's phone number in the billing file and in the delivery file. Name the two problems that can arise. 2) List four tables a library database might have. 3) What is meta-data?
Answers: 1) Data redundancy and data inconsistency. 2) BOOK, MEMBER, ISSUE, AUTHOR.
