BBA(IT) Server-Side Web Technology: Introduction to Database Systems Study Guide

Introduction to Data and Databases

  • Definition of Data:     * Data refers to raw, unorganized facts and figures.     * Types include numbers, text, images, or symbols.     * Purpose: Processed and analyzed to extract meaningful information.     * Forms:         * Raw form: Unorganized.         * Processed form: Organized and meaningful.

  • Definition of a Database:     * A structured collection of data designed for efficient storage, retrieval, and manipulation.     * Function: Serves as a centralized repository allowing data access, management, and updates by multiple users or applications.     * Example: A Customer database containing fields like         1. cname         2. cno         3. ccity

The Three Levels of Viewing a Database

  • Level 1: Literal Meaning (Storage):     * Defined as "Data + Base"; the actual storage place of all interested information.     * Data collection involves the physical data files stored through a DBMS.     * Examples: Data files for MySQL, SQL Server, Oracle, or PostgreSQL.

  • Level 2: Database Management System (DBMS):     * The software tool package that acts as a gatekeeper to manage data storage, access, and maintenance.     * Scopes:         * Personal usage: MS Access, SQLite.         * Enterprise level: Oracle, MySQL, MS SQL.

  • Level 3: Database Application:     * Refers to all possible applications built upon stored data.     * Examples: Websites, Business Intelligence (BI) applications, Enterprise Resource Planning (ERP) systems, e-commerce platforms, online banking, and registration systems.

Database Systems and DBMS Characteristics

  • Database (General):     * A collection of interrelated data, often stored in tables.     * Can vary in size and complexity.     * May be manual or computerized.

  • Database System:     * A computerized system designed to maintain information and make it available on demand.     * Advantages of Database Systems:         * Reduced redundancy.         * Avoidance of inconsistency.         * Data sharing availability.         * Enforcement of standards.         * Application of security restrictions.         * Maintenance of integrity.         * Possibility of data gathering.         * Balanced requirements.

  • DBMS (Software Definition):     * A collection of programs enabling users to create and maintain a database.     * General-purpose software providing processes for defining, constructing, and manipulating databases for various applications.     * DBMS Advantages:         * Data Independence.         * Efficient Data Access.         * Data Integrity and Security.         * Data Administration.         * Concurrent access and Crash recovery.         * Reduced Application Development Time.

File Processing vs. Database Applications

  • Disadvantages of File Processing:     * Data redundancy and inconsistency.     * Difficulty in accessing data.     * Data isolation.     * Data integrity issues.     * Lack of concurrent access.     * Security problems.

  • Industry Applications of Databases:     * Banking: All transactions.     * Airlines: Reservations and schedules.     * Universities: Registration and grades.     * Sales: Customers, products, and purchases.     * Online Retailers: Order tracking and customized recommendations.     * Manufacturing: Production, inventory, orders, and supply chain.     * Human Resources: Employee records, salaries, and tax deductions.

Professionals in the Database Environment

  • Actors on the Scene (Day-to-day use):     1. Database Administrators (DBA):         * Responsible for authorizing access, coordinating/monitoring use, and acquiring hardware/software resources.         * Design of conceptual and physical schemas: DBA interacts with users to understand data needs, creates definitions stored in the 'Data Dictionary'.         * Security and Authorization: Regulating which parts of the database users can access.         * Storage structure and Access method definition: Writing definitions translated by the DDL compiler.         * Data Availability and Recovery: Restoration of data to a consistent state after failure.         * Database Tuning: Modifying the database for performance as requirements change.         * Integrity Constraint Specification: Maintaining special system structures for updates.     2. Database Designers:         * Identify data to be stored and choose structures to represent it.     3. End Users:         * Casual End Users: Occasional access requiring different information each time.         * Naive/Parametric End Users: Constant querying/updating using standard query types.         * Sophisticated End Users: Engineers, Scientists, and Business Analysts who implement complex requirements.         * Standalone End Users: Maintain personal databases via ready-made menu-based packages.     4. System Analyst:         * Determine end-user requirements and develop specifications for transactions.     5. Application Programmers (Software Engineers):         * Test, debug, document, and maintain transactions.

  • Workers Behind the Scene:     * Database Designers and Implementers: Design/implement DBMS modules and interfaces.     * Tool Developers: Create packages for design, performance monitoring, and testing.     * Operators and Maintenance Personnel: Responsible for the hardware and software environment.

Components of DBMS Applications

  • Hardware: The computer system (Hard disks, RAM, network devices).

  • Software: The DBMS between physical data and users (MySQL, Oracle, PostgreSQL), including the DB engine, OS, and application tools.

  • Data:     * Operational Data: Actual user info (name, age).     * Metadata: Data about data (storage size, type, timestamps).

  • Procedures: Instructions/rules for setup, login/logout, data validation, backup, and access control.

  • Database Access Language: Tools for CRUD (Create, Read, Update, Delete) operations, such as SQL, MyAccess, or PL/SQL.

  • People: DBAs, Developers, and End Users.

Database Languages

  • 1. Data Definition Language (DDL):     * Focuses on schemas and structures.     * Commands: CREATE (database/objects), ALTER (structure), DROP (deletion), TRUNCATE (remove records and space), COMMENT (data dictionary notes), RENAME (rename objects).

  • 2. Data Manipulation Language (DML):     * Focuses on manipulating stored data.     * Commands: INSERT, UPDATE, DELETE, MERGE (UPSERT), CALL (subprograms), EXPLAIN PLAN (access paths), LOCK TABLE (concurrency).

  • 3. Data Control Language (DCL):     * Manages permissions.     * Commands: GRANT (privileges like SELECT), REVOKE (remove permissions).

  • 4. Transaction Control Language (TCL):     * Oversees consistency and atomicity.     * Commands: ROLLBACK (undo changes), COMMIT (save changes), SAVEPOINT (set undo point).

  • 5. Data Query Language (DQL):     * Subset of SQL for retrieving data.     * Primary command: SELECT.

ACID Properties

  • Atomicity: Transactions complete fully or not at all.

  • Consistency: Database moves from one valid state to another.

  • Isolation: Multiple concurrent transactions do not affect each other.

  • Durability: Changes are saved permanently once completed.

Normalization

  • Definition: Structuring data and attributes logically to avoid data redundancy (storing the same data in multiple places).

  • Goal: Remove anomalies and instill consistency by dividing large tables into smaller ones linked by relationships.

  • First Normal Form (1NF):     * Requires atomic values.     * Every attribute must contain a single-valued attribute; no multi-valued or composite attributes.     * Example: A table mapping Fruit ID to States must have separate rows if a fruit like "Mango" comes from multiple states (Uttar Pradesh, Andhra Pradesh, Karnataka).

  • Second Normal Form (2NF):     * Must be in 1NF.     * Must not contain partial dependency (non-prime attributes must be fully functionally dependent on the primary key).     * Example: If a Customer ID determines Customer Name and a Plan ID determines Plan Name, they should be moved to separate tables (CustomerInfo, PlanDetail, and a mapping table CustomerPlan).

Practical SQL Examples

Example 1: Book Management

  • Table Composition: BOOK(Book_Id, Book_Name, Author, Date_Purchased, Publisher, Price)

  • Schema Creation: sql CREATE TABLE BOOK ( Book_Id INT PRIMARY KEY, Book_Name VARCHAR(255), Author VARCHAR(255), Date_Purchased DATE, Publisher VARCHAR(255), Price DECIMAL(10, 2) );     

  • Specific Queries:     * List authors from "Himalaya" publications: SELECT DISTINCT Author FROM BOOK WHERE Publisher = 'Himalaya';     * Rename column: ALTER TABLE BOOK RENAME COLUMN Publisher TO Publications;     * Create index: CREATE INDEX idx_book_name_author ON BOOK (Book_Name, Author);     * Author name contains "Sharma": SELECT * FROM BOOK WHERE Author LIKE '%Sharma%';

Example 2: Dept and Emp Tables

  • Schema Creation: sql CREATE TABLE Dept ( deptno INT PRIMARY KEY, dname VARCHAR(50), loc VARCHAR(50) ); CREATE TABLE Emp ( empno INT PRIMARY KEY, ename VARCHAR(50), job VARCHAR(50), hiredate DATE, salary DECIMAL(10, 2), deptno INT, FOREIGN KEY (deptno) REFERENCES Dept(deptno) );     

  • Specific Queries:     * Employees in Computer Department: sql SELECT e.ename, e.job FROM Emp e JOIN Dept d ON e.deptno = d.deptno WHERE d.dname = 'Computer';              * Average Salary: sql SELECT AVG(salary) AS AverageSalary FROM Emp;              * Employees in Pune: sql SELECT e.ename, e.job, d.loc FROM Emp e JOIN Dept d ON e.deptno = d.deptno WHERE d.loc = 'Pune';         

Example 3: Student Table Aggregate Queries

  • Table: STUDENT(Roll, Name, Address, Phone, Age)

  • Queries:     * Retrieve all Roll and Name: SELECT roll, name FROM STUDENT;     * Ascending order by Age: SELECT * FROM STUDENT ORDER BY age;     * Distinct addresses: SELECT DISTINCT address FROM STUDENT;     * Count total addresses: SELECT COUNT(Address) FROM STUDENT;     * Sum of all ages: SELECT SUM(age) from STUDENT;     * Sum of ages by city: SELECT Address, SUM(age) FROM STUDENT GROUP BY (address);     * Filter sum of ages > 30 by city: sql SELECT Address, SUM(age) AS total FROM STUDENT GROUP BY address HAVING total > 30;