Database Management System (DBMS)

1. Introduction to Database Management Systems

A Database is an organized, structured collection of logically related data stored electronically in a computer system. A Database Management System (DBMS) is software that acts as an interface between the user, applications, and the physical database. It enables users to define, create, maintain, query, and control access to data efficiently.

Popular open-source and commercial DBMS applications include LibreOffice Base, MySQL, PostgreSQL, Microsoft Access, and Oracle Database.

2. Data vs. Information & Limitations of File Systems

• Data: Raw, unprocessed facts, figures, characters, or symbols that lack independent context or direct meaning (e.g., 98, "Aarav").
• Information: Processed, structured, and organized data that is meaningful, contextual, and useful for decision-making (e.g., "Aarav scored 98% in Class 10 IT Examination").

Limitations of Traditional File Systems: Prior to DBMS, organizations stored records in flat text files or paper ledgers. This led to serious problems:

• Data Redundancy: Same data duplicated across multiple independent files (e.g., student address stored separately in fee records, library records, and exam records).
• Data Inconsistency: When duplicate data is updated in one file but forgotten in another, conflicting records exist.
• Difficulty in Accessing Data: Extracting custom reports required writing brand-new programs for each query.
• Data Isolation: Data scattered in varied file formats made integration nearly impossible.
• Security & Integrity Issues: Lack of granular access permissions and no validation constraints.

3. Need and Key Advantages of DBMS

4. Flat File vs. Relational Database Model

5. Core RDBMS Concepts (Relations, Tuples, Attributes, Degree, Cardinality)

The Relational model was introduced by Dr. E. F. Codd in 1970. Key architectural terminology includes:

• Relation (Table): A two-dimensional data structure consisting of columns and rows used to store related records.
• Tuple (Record / Row): A single horizontal row in a relation representing a complete set of attributes for one real-world entity (e.g., one specific student).
• Attribute (Field / Column): A single vertical column in a relation representing a specific characteristic or property (e.g., RollNo, StudentName, Marks).
• Domain: The set of permissible, valid values that can be assigned to an attribute (e.g., marks between 0 and 100).
• Degree: The total number of attributes (columns) present in a relation. (e.g., a table with 5 columns has a degree of 5).
• Cardinality: The total number of tuples (rows/records) present in a relation. (e.g., a table with 40 student rows has a cardinality of 40).

6. Keys in Database (Primary, Candidate, Alternate, Foreign Keys)

Keys are columns used to uniquely identify records and establish logical links between tables:

7. Common Data Types in RDBMS

Every attribute must be assigned an appropriate data type to define what values it can store and prevent erroneous inputs:

8. Creating and Modifying Tables in Design View

Design View provides maximum precision and control when designing table structures in LibreOffice Base:

1. Open your .odb database file in LibreOffice Base.
2. In the left panel under Database, click the Tables icon.
3. In the upper Tasks pane, click Create Table in Design View....
4. A grid with three columns appears: Field Name, Field Type, and Description.
5. Type the first field name (e.g., StudentID) and select its data type (e.g., Integer [INTEGER]).
6. In the bottom Field Properties pane, set options such as AutoValue (Yes/No), Length, Default value, and Entry required (Yes/No).
7. Right-click on the row header of StudentID and select Primary Key. A small key icon appears beside the field name.
8. Add other fields (e.g., FullName, Class, DOB, FeesPaid).
9. Click the Save icon (or Ctrl + S), give your table a name (e.g., tbl_Students), and click OK.

9. Creating Tables Using the Table Wizard

The Table Wizard guides beginners through pre-built templates for common database scenarios (e.g., Contacts, Customers, Employees):

1. In the Tables section, click Use Wizard to Create Table....
2. Step 1 (Select Fields): Choose a category (Business or Personal), pick a sample table (e.g., Employees), and move desired fields to the right using the > button.
3. Step 2 (Set Types and Formats): Adjust field properties, lengths, and mandatory flags.
4. Step 3 (Set Primary Key): Choose whether to automatically add a primary key, use an existing field, or define a composite key.
5. Step 4 (Create Table): Enter the table name and choose to insert data immediately or modify the structure in Design View. Click Finish.

10. Setting and Modifying Primary Keys

Every relation must have an attribute that uniquely identifies each record. To set or edit a Primary Key in LibreOffice Base:

• Open the table in Design View (right-click table > select Edit).
• Right-click the grey selector box to the left of the desired field name.
• Click Primary Key. A golden key symbol confirms the primary key.
• To create a Composite Primary Key: Hold the Ctrl key, click to highlight multiple rows, right-click any highlighted selector, and select Primary Key.

11. Referential Integrity and Cascade Rules

Referential Integrity is a relational rule that maintains consistency between related tables. It ensures that a foreign key value in a child table must always correspond to an existing, valid primary key value in the parent table. It prevents accidental deletion or modification of records that have dependent child data.

When deleting or updating a record in the primary (parent) table, Base allows four update/delete rules:

• No Action / Restrict: Rejects the deletion or update if matching child records exist. (Default and safest option).
• Cascade Update / Cascade Delete: If a parent record is deleted or updated, all corresponding records in child tables are automatically deleted or updated.
• Set NULL: If the parent record is deleted, foreign key fields in matching child records are automatically set to NULL.
• Set Default: If the parent record is deleted, matching foreign key fields revert to their defined default value.

12. Table Relationships (1:1, 1:Many, Many:Many)

Relationships connect tables using common fields, eliminating redundant data entry:

Creating Relationships in LibreOffice Base: Select Tools > Relationships... > Add tables > Drag the Primary Key field from the parent table and drop it onto the Foreign Key field of the child table > Set update and delete cascade rules > Click OK.

13. Inserting, Editing, and Deleting Table Records

Once table structure is defined, data is entered in Datasheet View:

• Double-click the table name in Base to open it in Datasheet View.
• Insert: Navigate to the blank row at the bottom marked with a *, enter values, and press Tab or Enter to commit the record.
• Edit: Click on any cell and edit the text. A small pencil icon indicates that the current record is being edited.
• Delete: Click the row header selector on the left, press Delete on your keyboard, and confirm the deletion prompt.

14. Sorting and Filtering Data in Tables

• Sorting: Select a column > click the Sort Ascending (A-Z) or Sort Descending (Z-A) icon on the Table Data toolbar. Multi-column sorting can be done via Data > Sort....
• AutoFilter: Click a specific value > click the AutoFilter button to show only records containing that exact value.
• Standard Filter: Click the Standard Filter icon > set field criteria (e.g., Marks >= 75 AND City = 'Delhi') > Click OK.
• Remove Filter: Click the Apply/Remove Filter toggle button to view all records again.

15. Introduction to Structured Query Language (SQL) & Classification

SQL (Structured Query Language) is the universal standard language used to create, query, update, and manage relational databases. SQL statements are case-insensitive, though keywords are conventionally capitalized for readability. Every SQL statement terminates with a semicolon (;).

SQL commands are categorized into two primary divisions:

16. SQL DDL Commands (CREATE, ALTER, DROP)

1. CREATE TABLE: Creates a new relational table with defined column names and constraints.

CREATE TABLE Students (
    RollNo INT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Gender CHAR(1),
    DOB DATE,
    Marks DECIMAL(5,2)
);

2. ALTER TABLE: Modifies an existing table structure (adding a new column, modifying data type, or dropping a column).

-- Add a new column
ALTER TABLE Students ADD Email VARCHAR(100);

-- Drop an unwanted column
ALTER TABLE Students DROP COLUMN Gender;

3. DROP TABLE: Permanently removes an entire table and all its stored records from the database.

DROP TABLE Students;

17. SQL DML Commands (SELECT, INSERT, UPDATE, DELETE)

1. INSERT INTO: Inserts new records into a table.

INSERT INTO Students (RollNo, StudentName, DOB, Marks)
VALUES (101, 'Ananya Sharma', '2008-05-14', 94.50);

2. SELECT: Retrieves data records from one or more tables.

-- Retrieve all columns for all students
SELECT * FROM Students;

-- Retrieve specific columns
SELECT RollNo, StudentName, Marks FROM Students;

3. UPDATE: Modifies existing records based on a conditional expression.

UPDATE Students
SET Marks = 98.00
WHERE RollNo = 101;

Caution: Omitting the WHERE clause in an UPDATE statement updates every single row in the entire table!

4. DELETE: Removes specified records from a table.

DELETE FROM Students
WHERE RollNo = 101;

Caution: Running DELETE FROM Students; without a WHERE clause deletes all records from the table while leaving the table structure intact.

18. Filtering Data in SQL (WHERE, Operators, LIKE, BETWEEN, IN)

The WHERE clause extracts only records that satisfy specified logical conditions:

• Comparison Operators: = (Equal), <> or != (Not Equal), >, <, >=, <=.
  SELECT * FROM Students WHERE Marks >= 80;
• Logical Operators: AND (both true), OR (either true), NOT (negation).
  SELECT * FROM Students WHERE Marks >= 75 AND Class = '10A';
• BETWEEN ... AND: Filters values within an inclusive range.
  SELECT * FROM Students WHERE Marks BETWEEN 80 AND 95;
• IN: Matches any value from a specified list.
  SELECT * FROM Students WHERE City IN ('Delhi', 'Mumbai', 'Jaipur');
• LIKE: Pattern matching with wildcards: % (zero or more characters), _ (exactly one character).
  SELECT * FROM Students WHERE StudentName LIKE 'A%'; (Names starting with 'A').
• IS NULL / IS NOT NULL: Checks for missing or undefined values.
  SELECT * FROM Students WHERE Email IS NULL;

19. Sorting and Aggregate Functions in SQL

Sorting with ORDER BY: Sorts query output in ascending (ASC, default) or descending (DESC) order:

SELECT StudentName, Marks FROM Students ORDER BY Marks DESC;

SQL Aggregate Functions: Compute summary values across multiple rows:

20. Creating Queries in Query Design View

For users who prefer visual tools over manual SQL syntax, LibreOffice Base provides Query Design View:

1. In the main Base window, click the Queries icon > click Create Query in Design View....
2. In the Add Table or Query dialog, select the required table(s) (e.g., Students) and click Add, then close the dialog.
3. The upper pane shows the table boxes with their fields; the lower pane contains the Query Design Grid.
4. In the grid, configure the rows:
   • Field: Double-click field names from the table box or choose from the dropdown.
   • Table: Shows the source table name.
   • Sort: Set to ascending, descending, or leave empty.
   • Visible: Check or uncheck whether this column should appear in the final query result.
   • Criterion: Enter filtering conditions (e.g., >= 75).
   • Or: Enter alternate conditions on subsequent rows.
5. Click the Run Query (F5) icon on the toolbar to preview output. Save the query (e.g., qry_Toppers).

21. Forms in DBMS (Definition, Purpose & Form Wizard)

A Form is a user-friendly graphical window designed to make data viewing, entry, and modification intuitive and error-free. Instead of navigating wide raw spreadsheets, users see one record at a time with clear labels and styled controls.

Creating a Form Using the Form Wizard:

1. In Base, click the Forms icon > select Use Wizard to Create Form....
2. Step 1 (Field Selection): Choose the table (e.g., Students) and move fields to the right.
3. Step 2 (Set up a subform): Optional subform for related child tables (e.g., Student's borrowed books).
4. Step 3 (Arrange controls): Choose a layout style (Columnar - Labels Left, Columnar - Labels on Top, As Data Sheet, In Blocks).
5. Step 4 (Set data entry mode): Specify whether the form allows modifications or is restricted to new entries only.
6. Step 5 (Apply styles): Pick a color scheme and border style.
7. Step 6 (Set name): Give the form a title and click Finish.

Common Form Controls: Labels, Text Boxes, List Boxes (dropdown), Combo Boxes, Check Boxes, Radio/Option Buttons, and Push Action Buttons (Next, Previous, Save, Delete).

22. Reports in DBMS (Definition, Purpose & Report Wizard)

A Report is formatted, printable output generated from tables or queries designed specifically for printing, distribution, and executive decision-making. Unlike forms, reports are typically read-only and present aggregated summaries.

Creating a Report Using the Report Wizard:

1. In Base, click the Reports icon > click Use Wizard to Create Report....
2. Step 1: Select the source table or query and choose the fields to display.
3. Step 2: Customize field labels for print presentation.
4. Step 3 (Grouping): Group records by category (e.g., group students by Class or Section).
5. Step 4 (Sort Options): Sort records within each group (e.g., by StudentName ascending).
6. Step 5 (Choose Layout): Select page orientation (Portrait / Landscape) and visual formatting.
7. Step 6 (Create Report): Choose Static Report (snapshot) or Dynamic Report (updates with new data). Click Finish.

23. Practical Database Case Study (School Database)

Consider a school maintaining student records, library loans, and fee payments:

• Parent Table: tbl_Students (StudentID [PK], StudentName, Class, Section, Mobile)
• Child Table 1: tbl_LibraryLoans (LoanID [PK], StudentID [FK], BookISBN, IssueDate, ReturnDate)
• Child Table 2: tbl_Fees (ReceiptNo [PK], StudentID [FK], AmountPaid, PaymentDate, Mode)

By connecting StudentID as a foreign key, the school avoids repeating the student's name, class, and phone number in every loan and fee receipt. When a student changes phone numbers, updating it once in tbl_Students reflects across all library and fee records instantly.

24. Important Terms & Definitions