Chapter 3 — Database Management System

What is a DBMS?
A Database Management System (DBMS) is software that allows you to store, organize, manage and retrieve large amounts of structured data efficiently. Instead of keeping data in separate files, a DBMS provides a centralized system to define, create, update and query data.

Basic terms

• Database: A collection of related data (tables) stored together.
• Table (Relation): A set of rows (records) and columns (fields/attributes).
• Record (Row): One complete set of related fields — one instance of data (e.g., one student).
• Field (Column/Attribute): One data item in every record (e.g., Name, Roll No.).
• Primary Key: A field (or set of fields) that uniquely identifies each record.
• Foreign Key: A field in one table that refers to the primary key in another table (creates relationships).

Why use a DBMS instead of plain files?

• Reduced data redundancy: Same data is not repeated unnecessarily.
• Data integrity: Rules ensure data is accurate and consistent.
• Concurrent access: Multiple users can access and update data safely.
• Security: Access controls and user privileges protect data.
• Backup & recovery: Built-in mechanisms to recover from failures.
• Efficient querying: Powerful languages (like SQL) to fetch exactly the data needed.

Main functions of a DBMS

• Data definition: create and modify table structures (CREATE TABLE, ALTER TABLE).
• Data manipulation: insert, update, delete and retrieve data (INSERT, UPDATE, DELETE, SELECT).
• Transaction management: group operations so they succeed or fail together.
• Concurrency control: manage access by multiple users without conflicts.
• Security management: define user roles and permissions.
• Backup and recovery: protect and restore data after errors or crashes.

CRUD operations (simple SQL examples)

-- Create (insert)
INSERT INTO Students (RollNo, Name, Class) VALUES (12, 'Asha', 10);

-- Read (select)
SELECT Name, Class FROM Students WHERE RollNo = 12;

-- Update
UPDATE Students SET Name = 'Asha R.' WHERE RollNo = 12;

-- Delete
DELETE FROM Students WHERE RollNo = 12;

Key concepts: Normalization & Relationships
Normalization is the process of organizing data to reduce redundancy and improve integrity. Typical steps are 1NF, 2NF and 3NF. Relationships between tables (one-to-one, one-to-many, many-to-many) are expressed using primary and foreign keys.

ACID properties (for reliable transactions)

• Atomicity: All parts of a transaction succeed or none do.
• Consistency: Transactions take the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere.
• Durability: Once committed, changes persist even after a crash.

Real-life importance
DBMSs are everywhere: schools use them for student records, banks for accounts, hospitals for patient data, e-commerce sites for product catalogs and transactions. They make data reliable, searchable and secure.

Summary
A DBMS is essential software that stores and manages structured data, supports powerful queries and transactions, enforces data integrity and security, and enables multiple users to work with data concurrently and safely.

What is a Database? A database is an organized collection of related data stored so it can be easily accessed, managed and updated. Example: a school's student information stored in tables (name, roll no., class, marks).

What is a DBMS? A Database Management System (DBMS) is software that allows users to create, read, update and delete data in a database. The DBMS acts as an interface between users/applications and the physical data stored on disk.

Components of a Database System

• Data: facts stored (tables, files).
• Hardware: physical storage (disk, memory).
• Software: DBMS (e.g., MySQL, SQLite, MS Access).
• Users: end-users, DB administrators, application programs.

Basic Terminology

• Field/Attribute — one piece of information (e.g., Name, DOB).
• Record/Tuple — a row containing related fields (e.g., one student).
• Table/Relation — collection of records with same fields (e.g., Students table).
• Primary Key — unique identifier for a record (e.g., RollNo).
• Foreign Key — field in one table that links to primary key in another (used for relationships).

Data Models (Class 10 focus)

• Relational Model — data is stored in tables (rows & columns). Most common in school-level DBMS.

Relationships between Tables

• One-to-One (1:1)
• One-to-Many (1:N) — common: one class has many students
• Many-to-Many (M:N) — solved using junction tables (e.g., students and subjects)

Data Integrity & Constraints

• NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY — rules that keep data correct and consistent.

Normalization (brief) — process of organizing tables to reduce redundancy and dependency.

• 1NF: Each column has atomic values; no repeating groups.
• 2NF: 1NF + every non-key attribute fully depends on the primary key.
• 3NF: 2NF + no transitive dependency (non-key attributes not depending on other non-key attributes).

Queries & SQL

Structured Query Language (SQL) is used to interact with relational databases. Basic operations: SELECT (read), INSERT (add), UPDATE (modify), DELETE (remove).

Transactions & ACID Properties — ensure reliable processing:

• Atomicity: all or nothing
• Consistency: database moves from one valid state to another
• Isolation: concurrent transactions don’t interfere
• Durability: once committed, changes persist

Advantages of Using a DBMS

• Eliminates data redundancy
• Improves data consistency and integrity
• Provides efficient data retrieval (queries)
• Access control and security
• Backup and recovery mechanisms

Common Examples & Uses — student records, library systems, banking, inventory management, hospital records, online stores.

Overview
In a relational database, keys uniquely identify tuples (rows) and express relationships between tables. Constraints are rules that maintain correctness and integrity of the data (for example, preventing duplicate or invalid values).

Types of Keys

• Superkey: Any set of attributes that uniquely identifies a tuple in a relation. (May contain extra attributes.)
• Candidate key: A minimal superkey — a superkey with no unnecessary attributes. There can be multiple candidate keys.
• Primary key (PK): A candidate key chosen by the designer to uniquely identify tuples. Cannot be NULL (entity integrity).
• Composite (or compound) key: A key composed of two or more attributes used together to uniquely identify a tuple.
• Foreign key (FK): An attribute or set of attributes in one relation that references the primary key of another relation; expresses relationships between tables.
• Unique key: Enforces uniqueness like a primary key but can allow NULL (depending on DBMS) and is not necessarily the primary identifier.
• Surrogate key: System-generated artificial key (e.g., auto-increment ID) used when no natural key is suitable.

Formal idea: If R is a relation with attributes (A1, A2, ..., An) and X is a set of attributes, X is a key for R if X → A1,A2,...,An (X functionally determines all attributes) and no proper subset of X has that property.

Types of Constraints

• Entity integrity: Primary key values must be unique and NOT NULL. (Prevents unidentified rows.)
• Referential integrity: A foreign key must either be NULL or match an existing primary key value in the referenced table. (Prevents dangling references.)
• Domain constraints: Each attribute value must be of the declared data type and within allowed range (e.g., age >= 0, salary >= 0).
• NOT NULL: Prevents NULL values in an attribute.
• UNIQUE: Ensures all values in a column (or group) are different.
• CHECK: Custom condition that values must satisfy (e.g., CHECK (marks BETWEEN 0 AND 100)).
• ON DELETE / ON UPDATE rules: Actions for referential integrity (CASCADE, SET NULL, RESTRICT/NO ACTION) when referenced rows change.

How keys and constraints are used together
Designers choose a primary key to ensure entity integrity. Foreign keys connect tables and enforce referential integrity. Domain, UNIQUE, and CHECK constraints refine what values are allowed.

SQL examples (short)
CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK (Age >= 5));
CREATE TABLE Enrollment (EnrollID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE);

Design tips

• Prefer simple, stable primary keys (surrogate keys if natural keys change).
• Use composite keys only when the combination truly represents uniqueness (e.g., OrderID + ProductID in OrderItems).
• Apply CHECK and domain constraints close to the column definition to avoid bad data.
• Decide ON DELETE/UPDATE actions based on real-world rules (e.g., deleting a customer might delete their orders or be disallowed).

Why this matters (real-life consequences)
Without keys and constraints, databases can contain duplicate, inconsistent, or orphaned records (e.g., invoices referencing nonexistent customers), making reporting and operations unreliable.

What is a Relationship?
In a Database Management System (DBMS), a relationship describes how two or more entity sets are associated with each other. In an Entity‑Relationship (ER) model a relationship is shown by a diamond (or labeled connector) between entity boxes and can have attributes of its own.

Key concepts

• Cardinality: Describes the numerical mapping between entities (1:1, 1:N, M:N).
• Participation: Can be total (every entity must participate) or partial (some may not participate).
• Attributes of relationship: A relationship can have attributes (e.g., grade in an enrollment relationship).
• Foreign key: In the relational schema, relationships are usually implemented by using primary keys (PK) and foreign keys (FK) to preserve referential integrity.

Types of relationships

• One‑to‑One (1:1): Each entity in A relates to at most one entity in B, and vice versa. Example: Person — Passport.
• One‑to‑Many (1:N): An entity in A can relate to many in B, but an entity in B relates to at most one in A. Example: Department — Employees.
• Many‑to‑Many (M:N): Entities in A can relate to many in B and vice versa. Implemented with an associative (junction) table in relational schema. Example: Student — Course via Enrollment.
• Unary (Recursive): An entity relates to itself (e.g., Employee manages Employee).

Implementing relationships in tables: For 1:N, add the PK of the "one" side as an FK in the "many" side table. For M:N, create a new table (associative entity) whose PK is formed from the PKs of the two participating entities and which can hold relationship attributes.

Referential integrity: The foreign key value in the child table must match an existing primary key value in the parent table (or be NULL if allowed).

What is ER Model?
The Entity-Relationship (ER) Model is a high-level conceptual data model used to describe the structure of a database. It shows entities (things of interest), their attributes (properties), and relationships (associations) between entities. ER diagrams (ERDs) are visual representations of this model that help in database design before actual implementation.

Core concepts

• Entity: A real-world object or concept that can be distinctly identified (e.g., Student, Book, Customer). Represented as a rectangle.
• Entity set: A collection of similar entities (e.g., all students).
• Attribute: A property of an entity (e.g., Student has name, roll_no). Represented as an oval.
• Key attribute: An attribute (or combination) that uniquely identifies an entity in an entity set (e.g., roll_no). Shown by underlining.
• Relationship: A meaningful association among two or more entities (e.g., Student ENROLLS_IN Course). Represented as a diamond.
• Degree of relationship: Number of entity types involved (binary, ternary, etc.). Most common are binary relationships (between two entities).
• Cardinality: Describes number of instances of one entity that can or must be associated with instances of another. Common types: one-to-one (1:1), one-to-many (1:M), many-to-many (M:N).
• Participation constraint: Whether all entity instances must participate in a relationship (total participation) or only some do (partial participation). Total participation often shown with a double line.
• Weak entity: An entity that cannot be uniquely identified by its own attributes and depends on another (owner) entity. Shown with double rectangle and identifying relationship.

Notation summary (Chen notation)

• Entity: rectangle
• Attribute: oval (composite attributes broken into sub-ovals; multivalued attributes double oval)
• Relationship: diamond
• Key attribute: underline attribute name
• Total participation: double line between entity and relationship (or bold/annotated participation)

Steps to create an ER diagram

1. Identify entities from the problem domain.
2. Determine key attributes for each entity.
3. List other attributes (simple, composite, multivalued, derived).
4. Identify relationships between entities and their cardinalities and participation constraints.
5. Refine model: add weak entities, associative entities (for M:N relationships), and constraints.
6. Review and convert to relational schema if needed.

Advantages

• Provides a clear, visual way to design databases.
• Helps detect missing data or relationships early in design.
• Useful for communication between users and designers.

Limitations

• Conceptual only—does not specify physical storage or performance details.
• Large systems can produce complex ERDs that are hard to read without modularization.

What is normalization? Normalization is a process in database design that organizes data to reduce redundancy and avoid anomalies (insertion, update, deletion). It structures tables and their relationships so that each fact is stored only once and dependencies between data items are clear.

Why normalize? To eliminate duplicate data, make updates safe and simple, save storage, and ensure data integrity. Without normalization, changes must be made in many places and errors can occur.

Key idea — Functional Dependency

• A functional dependency A → B means value of attribute A determines value of attribute B. Example: student_id → student_name.
• Determinant: left side of the dependency (A). If A is a key, it determines all attributes in the table.

Normal forms (basic levels)

1. First Normal Form (1NF): Each column must hold atomic (indivisible) values and each record must be unique. No repeating groups or lists in a single cell.
   • Fix: split repeating values into separate rows or a separate table.
2. Second Normal Form (2NF): Table is in 1NF and every non-key attribute must depend on the whole primary key (no partial dependency). Applies when primary key is composite (more than one attribute).
   • Fix: move attributes that depend on part of a composite key into a new table keyed by that part.
3. Third Normal Form (3NF): Table is in 2NF and there are no transitive dependencies (non-key attribute must not depend on another non-key attribute).
   • Fix: move the dependent non-key attributes into a new table so that non-key attributes depend only on the key.
4. Boyce–Codd Normal Form (BCNF): A stronger version of 3NF. For every functional dependency X → Y, X should be a superkey. BCNF resolves some anomalies that 3NF does not.

Common anomalies explained briefly

• Insertion anomaly: Cannot add data because other required data is missing (e.g., cannot add a course until a student exists in the same row).
• Update anomaly: Changing a value requires multiple row updates (e.g., change teacher name in many rows).
• Deletion anomaly: Deleting a row removes other important information unintentionally (e.g., deleting last student in a course removes course info).

Steps to normalize a table (practical approach)

1. List attributes and identify primary key candidate(s).
2. Find functional dependencies among attributes.
3. Ensure 1NF: make values atomic.
4. For composite keys, eliminate partial dependencies to reach 2NF by creating new tables.
5. Eliminate transitive dependencies to reach 3NF by creating additional tables.
6. Check BCNF: for every dependency X → Y, ensure X is a superkey; if not, decompose further.

Trade-offs: Normalization reduces redundancy and anomalies but can increase number of tables and require JOINs, which may affect read performance. Denormalization (intentional redundancy) is sometimes used for performance optimization.

What is a table?
A table is a collection of related data organized in rows (records) and columns (fields). In a database management system (DBMS) a table represents an entity (for example Students, Products, Employees) and each column stores one attribute of the entity.

Creating a table - concepts and steps

• Plan the table: identify the entity, list attributes, choose appropriate data types and sizes, decide a primary key, and apply normalization (remove repeating groups and redundancy).
• Using GUI (e.g. MS Access):
  • Open Database > Create > Table Design (or Datasheet View).
  • In Design View, add field names and select data types (Text/Short Text, Number, Date/Time, Currency, Long Text/Memo, Yes/No).
  • Set field properties: Field Size, Format, Input Mask, Default Value, Validation Rule and Validation Text.
  • Choose a Primary Key for unique identification (right-click field > Primary Key).
  • Save the table with a meaningful name.
• Using SQL (CREATE TABLE): write a CREATE TABLE statement specifying columns, types, and constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK).

Common data types and properties

• Text/Short Text: alphanumeric values (names, codes). Use Field Size to limit characters.
• Number: numeric values for calculations (integers, decimals).
• Date/Time: dates, times and date-time values.
• Currency: monetary values with fixed decimals.
• Long Text/Memo: long descriptions or notes.
• Yes/No: boolean values (true/false).
• Field properties: Default Value, Input Mask (for formats like phone numbers), Validation Rule (e.g. >0), Required (NOT NULL).

Primary key and relationships
Primary key uniquely identifies each row (e.g. StudentID). A foreign key is a field in one table that links to the primary key of another table to model relationships (one-to-many, many-to-many via junction table). Enforce referential integrity to prevent orphan records.

Modifying a table

• Structure changes: add columns, delete columns, change data type or size, rename columns, change primary key.
• Data changes: insert, update, delete records.
• Using GUI: open table in Design View to alter fields and properties; use Datasheet View to add/edit records.
• Using SQL (ALTER TABLE): add, modify or drop columns and constraints with ALTER TABLE statements.
• Always backup data before making destructive changes (dropping columns) and ensure data type changes are compatible to avoid data loss.

Validation and integrity
Use constraints to ensure data quality: NOT NULL for required fields, UNIQUE for fields that must be distinct, CHECK to enforce range or pattern rules (e.g. CHECK (Marks BETWEEN 0 AND 100)).

Good practices

• Name tables and fields clearly and consistently (Students, StudentID, FirstName).
• Avoid storing calculated values; compute them with queries or views when needed.
• Normalize up to 3NF for simple databases to reduce redundancy.
• Document field meanings, units, and allowed values.

Example SQL snippets

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50),
  DOB DATE,
  Gender CHAR(1),
  Phone VARCHAR(15)
);

ALTER TABLE Students ADD COLUMN Email VARCHAR(100);
ALTER TABLE Students DROP COLUMN Phone;
-- Update records
UPDATE Students SET Email = 'abc@example.com' WHERE StudentID = 101;

These commands create, modify structure, and update records. Replace types and syntax to match specific DBMS where needed.

What is DML?
Data Manipulation Language (DML) is the part of SQL used to retrieve and change data stored in a database. DML provides commands to perform CRUD operations: Create (insert rows), Read (query rows), Update (modify rows) and Delete (remove rows).

Main DML commands

• SELECT — read and retrieve data.
• INSERT — add new rows to a table.
• UPDATE — change existing rows.
• DELETE — remove existing rows.

Common clauses used with DML

• WHERE — filter rows (applies to SELECT, UPDATE, DELETE).
• ORDER BY — sort results.
• GROUP BY — group rows for aggregate functions.
• HAVING — filter groups (used with GROUP BY).
• JOIN — combine rows from two or more tables.

Why DML matters
DML is how applications and users interact with the actual information in a database—for example, adding a student record, showing a student’s marks, updating attendance, or deleting an obsolete inventory item. Proper use of DML ensures data integrity and supports reporting and analytics.

Important notes

• DML statements modify table data but do not change table structure (that is DDL).
• Changes are usually part of transactions: they can be committed (made permanent) or rolled back (undone) using transaction control commands.
• Always use WHERE with UPDATE and DELETE to avoid accidental full-table modification or removal.

Example SQL snippets

-- INSERT: add a student
INSERT INTO Students (StudentID, Name, Class, Age) VALUES (101, 'Riya', '10A', 15);

-- SELECT: get details
SELECT StudentID, Name, Class FROM Students WHERE Class = '10A' ORDER BY Name;

-- UPDATE: change class for a student
UPDATE Students SET Class = '10B' WHERE StudentID = 101;

-- DELETE: remove a student's record
DELETE FROM Students WHERE StudentID = 101;

-- AGGREGATE: average marks and count by subject
SELECT Subject, AVG(Marks) AS AvgMarks, COUNT(*) AS NumStudents
FROM Marks
GROUP BY Subject
HAVING AVG(Marks) >= 50;

-- JOIN: student names with their marks
SELECT s.Name, m.Subject, m.Marks
FROM Students s JOIN Marks m ON s.StudentID = m.StudentID
WHERE m.Marks >= 80;

What is a Query?
A query is a question asked to a database to retrieve or manipulate data. The most common query in relational databases is the SELECT query which fetches rows and columns from one or more tables. Basic parts of a SELECT query are SELECT (which columns), FROM (which table), and optional clauses like WHERE (filter), ORDER BY (sort) and GROUP BY (aggregate).

Basic SELECT syntax

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

What is a Join?
A join combines rows from two or more tables based on a related column between them. Joins avoid duplication of data by keeping related facts in separate tables (normalization) and combining them when needed.

Why joins are needed (intuitive): Consider a Students table (student details) and a Scores table (marks). To get each student's marks, you combine the two tables on student ID using a join.

Types of Joins (with short explanation):

• Inner Join: Returns only rows that have matching values in both tables. Think of the overlap of two sets.
• Left (Outer) Join: Returns all rows from the left table and matched rows from the right table. If there is no match, right-side columns are NULL.
• Right (Outer) Join: Returns all rows from the right table and matched rows from the left table; left-side columns are NULL if no match.
• Full (Outer) Join: Returns rows when there is a match in one of the tables. It is the union of left and right outer joins (available in some DBMS).
• Cross Join (Cartesian Product): Returns every combination of rows from the two tables. Use with care; result size = rows(A) × rows(B).
• Self Join: A table joined with itself to compare rows within the same table (useful for manager–employee relationships).
• Natural Join: An automatic join on columns with the same names in both tables (use carefully — explicit ON is clearer).

How joins work technically (simple):
A join takes the Cartesian product of the tables and then filters rows where the join condition is true (for inner joins). Outer joins keep unmatched rows from one side and fill the other side with NULLs.

Example SQL patterns

-- Inner join
SELECT A.col1, B.col2
FROM A
INNER JOIN B ON A.key = B.key;

-- Left outer join
SELECT A.col1, B.col2
FROM A
LEFT JOIN B ON A.key = B.key;

-- Cross join
SELECT A.col1, B.col2
FROM A
CROSS JOIN B;

Notes for Class 10: Focus on SELECT queries and the visual idea of combining tables. Practice inner and left joins first. Understand NULLs produced by outer joins and why cross join results grow very quickly.

Overview: In a Database Management System (DBMS), Forms and Reports are two important user-facing objects. Forms are interactive screens used to enter, view or edit data. Reports are formatted, printable representations of data used for analysis and presentation.

Forms

• Purpose: Simplify data entry and ensure data integrity by providing a controlled user interface.
• Components/Controls: text boxes, combo boxes (drop-downs), radio buttons, check boxes, command/button controls (Save, Delete, Next), labels, date pickers, subforms (for related records).
• Data binding: Each control is bound to a table or query field so that user actions read/write database values.
• Validation and rules: Required fields, data type checks (number, date), range checks (e.g., Age between 5 and 18), format masks (phone, email) and custom validation expressions.
• Layout types: Single Record Form (one record at a time), Continuous Form (list style), Split Form (form + datasheet), Modal dialogs for focused tasks.
• Design tips: logically group fields, use labels and tab order, provide clear navigation and error messages, minimize required keystrokes.

Reports

• Purpose: Present data in a clean, printable format for review, decision-making, or record-keeping.
• Elements: Report header/footer (title, date), Page header/footer (page numbers), Group headers/footers (for categories), Detail section (record rows), Summary sections (totals, averages).
• Grouping and sorting: Reports often group records (e.g., by class, by region) and sort within groups to make patterns visible.
• Calculated fields and summaries: Use expressions to compute totals, averages, percentages, and running totals.
• Types of reports: Tabular (detailed rows), Summary (aggregated values), Grouped/Hierarchical, Mailing labels, Invoices/Bills/Receipts, Dashboards with charts.
• Output options: Print, PDF export, Excel/CSV export, or on-screen preview. Reports can be generated from tables or saved queries.

How Forms and Reports work together: Data is entered via Forms (or imported). Queries extract or aggregate the data and Reports present it. A well-designed form reduces data errors, improving report accuracy.

Best practices: keep forms simple, validate at entry, use default values where appropriate; design reports around user questions (who, what, when, where), use grouping and summaries for clarity, and include date/page information on every printed report.

Overview: Data validation, integrity and security ensure that a database stores correct, consistent and safe information. Validation checks input for correctness; integrity enforces rules so relationships and values remain meaningful; security protects data from unauthorized access, loss or tampering.

1. Data Validation

• Purpose: Stop incorrect or malformed data entering the database.
• Common validation types:
  • Type checks: ensure data type (integer, date, text).
  • Range checks: values fall within allowed limits (e.g., 0–100 for marks).
  • Format checks: pattern matching (e.g., email format, phone numbers).
  • Presence/NOT NULL: required fields must be provided.
  • Length checks: maximum/minimum characters for text fields.
  • Uniqueness checks: prevent duplicates where needed (e.g., registration number).
• Where validation happens: client-side UI (quick feedback), application/server-side (trusted), and at the database level (constraints, triggers).

2. Data Integrity

• Purpose: Maintain correctness and consistency of data across the database over time.
• Types of integrity constraints:
  • Entity integrity: Primary key must uniquely identify a record and cannot be NULL.
  • Referential integrity: Foreign keys must refer to existing primary key values or be NULL.
  • Domain integrity: Column values must be from a defined domain/type range.
  • Unique constraints: Prevent duplicate values in specified columns.
  • Check constraints & business rules: Custom rules (e.g., salary >= minimum wage).
• Enforcement: SQL constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK), triggers, stored procedures.

3. Data Security

• Purpose: Protect data confidentiality, integrity and availability (the CIA triad).
• Key measures:
  • Authentication: Verify user identity (passwords, multi-factor).
  • Authorization / Access control: Grant privileges by role (GRANT/REVOKE), least privilege principle.
  • Encryption: Protect data at rest and in transit (SSL/TLS, AES for stored data).
  • Backups & recovery: Regular backups, tested restore procedures, define RPO/RTO goals.
  • Audit logging & monitoring: Record changes, login attempts, and suspicious activity for review.
  • Physical security: Secure servers, controlled access to data centers.

4. Best practices (quick):

• Validate at multiple layers: UI + server + DB.
• Use parameterized queries or prepared statements to prevent SQL injection.
• Encrypt sensitive columns (e.g., passwords hashed, credit cards masked).
• Apply principle of least privilege and role-based access control.
• Keep regular automated backups and periodically test restores.
• Document constraints and business rules in the data dictionary.

5. Example SQL constraint snippets:

CREATE TABLE Student (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Email VARCHAR(100) UNIQUE,
  Marks INT CHECK (Marks BETWEEN 0 AND 100)
);

CREATE TABLE Enrollment (
  EnrollID INT PRIMARY KEY,
  StudentID INT,
  CourseID INT,
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

6. Typical problems solved: Prevent out-of-range marks, stop deletion of a parent record that would orphan child records, ensure only authorized staff can view salaries, guarantee unique roll numbers.

Conclusion: Data validation catches bad input early, integrity constraints keep the database consistent, and security measures protect data from misuse or loss. Together they make data reliable and trustworthy for decision making.