Database Management System Note [ काठमाडौंउपत्यकाखानेपानी लिमिटेड प्राविधिक सेवा, सुचनाप्रविधिसमूह, ५ तह, ओभरसियर(IT) ] Kathmandu Upatyaka Khanepani Limited (KUKL)

    

7.1. Introduction to Database and Database Management System

·         Database
• A collection of organized and related data for a specific purpose
• Stores data in tables (rows and columns)
• Can be computerized or non-computerized
• Allows easy access, management, and updating of data
• Examples: Student records, inventory, employee information

·         Database Management System (DBMS)
• Software used to create, manage, and manipulate databases
• Provides tools to insert, update, delete, and retrieve data
• Ensures data security, consistency, and integrity
• Supports multiple users accessing the data simultaneously
• Examples of DBMS:

o    Microsoft Access

o    MySQL

o    Oracle Database

o    PostgreSQL

o    SQLite

7.2. Database Design and Normalization

Database Design

·         The process of structuring and organizing data in a database

·         Involves creating tables, fields, and relationships between data

·         Goal is to store data efficiently and avoid redundancy

·         Good design improves data retrieval, accuracy, and maintenance

·         Includes defining:
• Entities (tables)
• Attributes (columns/fields)
• Primary keys (unique identifier for each record)
• Relationships between tables (one-to-one, one-to-many, many-to-many)

Normalization

·         A technique to organize data in the database to reduce data redundancy and improve data integrity

·         Divides large tables into smaller related tables

·         Ensures that each table contains data about one subject only

·         Uses rules called Normal Forms (1NF, 2NF, 3NF, etc.) to guide the process

·         Benefits:
• Eliminates duplicate data
• Simplifies data maintenance
• Increases efficiency during data operations

 

7.3. Basic Concept of Tables, Fields, Records, Relationships, and Indexing

Tables

·         A table is a collection of related data organized in rows and columns

·         Represents an entity like students, products, employees

·         Each table stores data in a structured format

Fields

·         Also called columns or attributes

·         Define the categories or types of data stored in a table

·         Example: In a student table, fields can be StudentID, Name, Age, Address

Records

·         Also called rows or tuples

·         Each record is a single entry in a table, representing one entity instance

·         Contains data for all fields in that row

·         Example: One student's complete information

Relationships

·         Define how tables are connected or related to each other

·         Types of relationships:

o    One-to-One (1:1): One record in a table relates to one record in another

o    One-to-Many (1:N): One record relates to many records in another table

o    Many-to-Many (M:N): Many records relate to many records (usually implemented via a junction table)

Indexing

·         A technique to speed up data retrieval from tables

·         Creates a data structure (index) that allows quick search without scanning the entire table

·         Similar to an index in a book to find information faster

·         Helps improve query performance but may slow down data insertion and updates slightly

 

7.4. Introduction to Database Applications

·         Database applications are software programs designed to manage, store, and retrieve data efficiently

·         They allow users to create, read, update, and delete (CRUD) data in databases

·         Commonly used in many fields such as business, education, healthcare, banking, and government

·         Help in organizing large amounts of information for easy access and analysis

·         Examples of database applications:

o    Customer Relationship Management (CRM) systems

o    Inventory Management systems

o    Library Management systems

o    Hospital Management systems

o    Online Banking systems

o    E-commerce platforms

·         Benefits include:

o    Faster data processing

o    Improved data accuracy and security

o    Easy data sharing and reporting

o    Support for decision making through data analysis

 

7.5. Common Data Types, Data Models, Relational Database and Its Design

Common Data Types

·         Integer: Whole numbers (e.g., 1, 25, -10)

·         Float/Decimal: Numbers with decimals (e.g., 3.14, 100.5)

·         Char/String: Text or characters (e.g., "Anil", "Nepal")

·         Date/Time: Date and time values (e.g., 2024-07-28, 10:30 AM)

·         Boolean: True/False values (e.g., Yes/No, 1/0)

·         Blob: Binary large object for storing images, audio, video

Data Models

·         A data model defines how data is organized, stored, and related in a database

·         Common data models include:

o    Hierarchical Model: Data arranged in a tree structure

o    Network Model: More complex relationships using graphs

o    Relational Model: Data organized in tables with rows and columns (most popular)

o    Object-oriented Model: Data stored as objects like in programming languages

Relational Database

·         Stores data in tables (relations) with rows and columns

·         Each table represents an entity (e.g., Students, Products)

·         Uses primary keys to uniquely identify each record

·         Tables can be linked using foreign keys to establish relationships

·         Based on relational algebra and uses SQL (Structured Query Language) for managing data

Relational Database Design

·         Involves creating tables with appropriate fields and data types

·         Defining primary keys for unique identification

·         Establishing relationships between tables using foreign keys

·         Applying normalization to reduce redundancy and improve data integrity

·         Ensuring referential integrity to maintain consistent relationships

 

7.6. Familiarity with SQL Statements for Database Manipulation

Structured Query Language (SQL)

·         SQL is a standard language used to communicate with and manipulate relational databases

·         It allows users to create, modify, and query database objects and data

Common SQL Statements

Statement	Purpose	Example
CREATE	Create database objects like tables	CREATE TABLE Students (ID INT, Name VARCHAR(50));
INSERT	Insert new records into tables	INSERT INTO Students VALUES (1, 'Anil');
SELECT	Retrieve data from tables	SELECT * FROM Students;
UPDATE	Modify existing data	UPDATE Students SET Name = 'Anil Pandit' WHERE ID = 1;
DELETE	Remove records from tables	DELETE FROM Students WHERE ID = 1;
ALTER	Change structure of a table	ALTER TABLE Students ADD Age INT;
DROP	Delete database objects like tables	DROP TABLE Students;

Using SQL to Manipulate Data

·         Query data with SELECT and conditions using WHERE

·         Use aggregate functions like COUNT(), SUM(), AVG() for calculations

·         Sort results with ORDER BY

·         Join multiple tables using JOIN

Example: Basic SQL Query

SELECT Name, Age

FROM Students

WHERE Age > 18

ORDER BY Name;

 

 

7.7. Creating, Modifying, and Deleting Forms/Reports

Forms

·         Forms are user-friendly interfaces that allow users to enter, view, and edit data in a database

·         Used to simplify data input and improve accuracy

·         Forms can include text boxes, dropdown lists, checkboxes, radio buttons, etc.

·         Created using database software like Microsoft Access, Oracle Forms, or web frameworks

Creating Forms:

·         Use form design tools or wizards to select fields from tables

·         Arrange input controls for easy data entry

·         Add validation rules to ensure correct data input

Modifying Forms:

·         Change layout or add/remove fields as needed

·         Update validation or formatting rules

·         Customize form behavior with scripts or macros

Deleting Forms:

·         Remove forms no longer in use from the database system to avoid clutter

Reports

·         Reports are formatted outputs of data from the database for analysis or presentation

·         Used to summarize, group, and print data in a readable format

·         Can include charts, totals, and calculated fields

Creating Reports:

·         Use report wizards or design tools to select data sources

·         Define grouping, sorting, and filters

·         Customize layout, headers, footers, and styling

Modifying Reports:

·         Change report design or data criteria

·         Add or remove fields and formatting elements

Deleting Reports:

·         Delete outdated or unnecessary reports to keep the system organized

7.8. Crash Recovery, Concurrency Control, and Security

Crash Recovery

·         Refers to techniques used to restore a database to a correct state after a failure or crash

·         Ensures no data loss or corruption after unexpected shutdowns

·         Uses methods like transaction logs, backups, and checkpoints

·         Helps maintain data integrity by rolling back incomplete transactions

Concurrency Control

·         Manages simultaneous access to the database by multiple users or processes

·         Prevents conflicts, data inconsistencies, and anomalies when multiple transactions occur at the same time

·         Techniques include:
• Locking mechanisms (shared locks, exclusive locks)
• Timestamp ordering
• Optimistic concurrency control

·         Ensures transaction isolation and consistency of data

Security

·         Protects database from unauthorized access, breaches, and misuse

·         Involves:
• Authentication (verifying user identity)
• Authorization (defining user permissions)
• Encryption of data
• Audit trails to track access and changes
• Backup and recovery plans

·         Important to maintain confidentiality, integrity, and availability of data