Chapter 8: Database Management System
1. Database Concepts
Data is a collection of raw facts and figures (names, numbers, dates); when data is organised and given meaning it becomes information. A database is an organised collection of related data stored so that it can be easily accessed, managed and updated — for example, a school database of students, a bank database of accounts, or a railway reservation database.
DBMS (Database Management System): software that creates, stores, organises, retrieves and manages data in a database. Examples: LibreOffice Base, MySQL, PostgreSQL, Oracle, Microsoft Access, SQL Server.
Advantages of a DBMS over flat files: reduces data redundancy (duplication), prevents data inconsistency, allows sharing of data by many users, enforces security through passwords and access rights, maintains data integrity (accuracy and validity), and supports backup and recovery.
2. RDBMS — Tables, Fields and Records
A Relational Database Management System (RDBMS) stores data in relations (tables) and links tables to one another through common fields. In a table:
- A field (column/attribute) is one item of information, e.g., RollNo, Name, Marks. Each field has a data type.
- A record (row/tuple) is the complete set of field values for one entity, e.g., all details of one student.
- The number of columns is the degree of the table and the number of rows is its cardinality.
3. Keys in a Database
| Key | Description |
|---|---|
| Primary key | A field (or combination of fields) that uniquely identifies each record; it can never be NULL and never repeats. Example: RollNo, AdmissionNo. |
| Candidate key | Every field that is capable of acting as a primary key. One candidate key is chosen as the primary key. |
| Alternate key | A candidate key that was not chosen as the primary key. |
| Composite key | A primary key made of two or more fields taken together. |
| Foreign key | A field in one table that refers to the primary key of another table; it links the two tables and maintains referential integrity. |
4. LibreOffice Base and its Objects
LibreOffice Base is the free, open-source DBMS of the LibreOffice suite. A Base database file is saved with the extension .odb. Start it with Start > LibreOffice > LibreOffice Base; the Database Wizard offers to create a new database or open an existing one. Base uses the HSQLDB engine internally. It has four main objects:
- Tables: store the actual data in rows and columns. Create them in Design View (define field name, type, properties, set the primary key by right-clicking the field selector) or with the Table Wizard. Press Ctrl+S to save the table design.
- Queries: extract information that answers a question, e.g., "students with marks > 80". Created in Design View, with the Query Wizard, or directly in SQL View (Tools > SQL can also run statements).
- Forms: a friendly front-end screen used to enter, edit and view data one record at a time (Form Wizard: Use Wizard to Create Form).
- Reports: formatted, printable presentation of data from a table or query, e.g., a mark sheet or a bill (Report Wizard).
5. Data Types in Base / SQL
| Category | Type | Used for |
|---|---|---|
| Numeric | INTEGER / INT, SMALLINT, BIGINT | Whole numbers (RollNo, Quantity) |
| Numeric (fractional) | DECIMAL(p,s), NUMERIC, FLOAT, REAL, DOUBLE | Numbers with decimal point (Price, Marks) |
| Alphanumeric | CHAR(n) — fixed length, VARCHAR(n) — variable length | Text (Name, City); VARCHAR saves space |
| Date / Time | DATE, TIME, TIMESTAMP | Dates and times (DOB, AdmissionDate) |
| Binary | BINARY, LONGVARBINARY (Image) | Photos, files |
| Boolean | BOOLEAN (Yes/No) | True/false values |
6. SQL — DDL and DML
SQL (Structured Query Language) is the standard language of RDBMS. Its statements are grouped into:
- DDL (Data Definition Language) — defines the structure:
CREATE,ALTER,DROP. - DML (Data Manipulation Language) — works on the data:
SELECT(retrieval),INSERT,UPDATE,DELETE.
Creating a table (DDL):
CREATE TABLE Student (RollNo INTEGER PRIMARY KEY, Name VARCHAR(30), Class VARCHAR(5), Marks DECIMAL(5,2), DOB DATE);
Manipulating data (DML):
- Insert a record:
INSERT INTO Student VALUES (1, 'Aman Kumar', '10A', 88.50, '2010-05-14'); - Insert into selected fields:
INSERT INTO Student (RollNo, Name) VALUES (2, 'Riya'); - View all data:
SELECT * FROM Student; - Selected columns with condition:
SELECT Name, Marks FROM Student WHERE Marks > 80; - Sorted output:
SELECT * FROM Student ORDER BY Name ASC;(useDESCfor descending) - Change data:
UPDATE Student SET Marks = 92 WHERE RollNo = 1; - Remove records:
DELETE FROM Student WHERE RollNo = 2;(without WHERE, all records are deleted)
Caution: DELETE removes records but keeps the table structure, whereas DROP TABLE Student; removes the entire table with its structure. An UPDATE or DELETE written without a WHERE clause affects every record of the table.
In SELECT queries the WHERE clause supports relational operators (=, <, >, <=, >=, <>) and logical operators (AND, OR, NOT). Text values are written in single quotes and dates in the format 'YYYY-MM-DD'.
7. Menu Paths and Shortcuts at a Glance
| Task | Path / Shortcut |
|---|---|
| Create table in Design View | Tables > Create Table in Design View |
| Set primary key | Right-click the field selector > Primary Key |
| Save table / object | Ctrl+S |
| Run SQL directly | Tools > SQL |
| Create query in SQL View | Queries > Create Query in SQL View |
| Create form / report | Forms > Use Wizard to Create Form / Reports > Use Wizard to Create Report |
| New Base file | Ctrl+N (from Base) — saved as .odb |
Exam Tip: The most repeated questions are: definition of primary key and foreign key, difference between DDL and DML, difference between DELETE and DROP, and writing simple SQL (CREATE TABLE, INSERT, SELECT with WHERE and ORDER BY, UPDATE). Practise one full table-creation example with all data types.