Database Overview – Comprehensive Study Notes (S511 Session 2, IU-SLIS)

Hardware and Software Components

• Physical component of a computer system is called hardware (examples: CPU, motherboard).
• A(n) software is a set of instructions that tells the computer what to do and is not tangible.
• The core system software that manages hardware and software resources is the operating system (OS).
• A compiler translates high-level programming language into machine language.
• An assembler translates assembly language into machine code.
• A linker combines object files into a single executable file.
• A loader places the executable code into memory for execution.
• An application program performs specific tasks for users (e.g., word processors, browsers).
• A system program supports the running of other programs and interacts closely with the OS.

Key Terms and Fill-ins (from the transcript)

• 1) hardware
• 2) software
• 3) operating system
• 4) compiler
• 5) assembler
• 6) linker
• 7) loader
• 8) application program
• 9) system program

Data and Information; Software Basics

• Data vs Information (conceptual):

  • Data: Raw facts; Information: Processed data (meaningfully organized).

• Data vs Information (from the slide pairings):

  • Data vs Information matchings include: Data = raw form of data; Information = processed data; Software = collection of instructions; Application software = collection of programs; Processed data = Information; Facebook = Application software; Collection of instructions = Software; Collection of programs = Application software; Raw form of data = Data.

• Data vs Information (expanded):

  • Data meaning: Raw facts; Information meaning: Processed facts.
  • Method of collection: Data = random collection; Information = specific collection.
  • Format: Data = unorganized form of collection; Information = systematic form of processed data.
  • Consists of: Data = text and numbers; Information = refined form of data.
  • Decision making: Data makes decision difficult; Information makes decision easy.
  • Dependency: Data is not dependent on information; Information is dependent on data.
  • Based on: Data = records and observation; Information = analysis.
  • Examples: (not explicitly provided in the transcript).

Traditional Data Storage and Flat Files

• Traditional storage methods included application programs for Loan Processing, Fixed Deposit Processing, and Transaction Processing.
• File System with primary memory / secondary memory.
• Sample data files: CustomerDetails.dat, CustomerLoan.dat, CustomerFixedDeposit.dat, Customer_Transaction.dat.

Why Plain Magnetic/Flat Files Fall Short

• Why PM/flat files cannot manage data:
  • Size of primary memory (PM) limits storage and processing.
  • Addressing capability is limited in flat files.
  • No inherent security in flat files.

Drawbacks of Flat Files (1–7)

• Drawback 1: Data redundancy and inconsistency
  • Example: Inconsistent Name, Address, AccNo across records (ABC, Bhiwani, 1002 vs DEF, etc.).
• Drawback 2: Difficulty in accessing data
  • Tasks require writing modules for each new task (e.g., salary lookup, sales details, promotions).
  • Security: need protection/access permission mechanisms.
• Drawback 3: Data Isolation
  • Data scattered in various files; requires additional programs to integrate.
• Drawback 4: Integrity Problems
  • Constraints needed; adding new rules is hard when data is scattered.
• Drawback 5: Atomicity Problems
  • Operations should complete 100% or 0% (no partial updates).
• Drawback 6: Concurrency Anomalies
  • Multiple users accessing data can lead to inconsistent states (example with bank withdrawals).
• Drawback 7: Security
  • Unauthorized access risk; need rules and permissions to control access.

Database and DBMS Basics

• Database: A collection of related data and its metadata organized in a structured format for optimized information management.

  • Example: Student database with fields like Student no, name, Ph_no, Email id.

• DBMS (Database Management System): Software enabling easy creation, access, and modification of databases for efficient management.

  • Example capabilities: Creation of table, Deletion of row, Insertion of row.

• Database System vs DBMS:

  • Database System: An integrated system of hardware, software, people, procedures, and data that defines and regulates collection, storage, management, and use of data within a database environment.

• Database System Environment components:

  • Hardware
  • Software (OS, DBMS, Applications)
  • People
  • Procedures
  • Data

Database Design as an Art

• Key considerations when building a database:
  • What is the use of the database?
  • Different approaches to form a database.
  • Avoid data redundancy.
  • Ensure data integrity; data must be accurate and verifiable.
  • Poorly designed databases generate errors; can lead to bad decisions or organizational failure.

Uses and Capabilities of Databases

• Storing data
• Calculations (mathematical and statistical)
• Searching
• Sorting
• Analysis of data
• Report generation (including graphical representations)

Review: Purpose, Functions, and Design Importance

• Purpose of Database: Optimizes data management; transforms data into information.
• Functions of DBMS/Database System: Stores data; generates reports; enforces data security (access, privacy, backup & restoration).
• Importance of database design: Defines expected use; different database types require different design approaches; avoid redundancy and ensure integrity; poorly designed databases cause errors.

Database Design Process and Planning

• Planning & Analysis:
  • Assess goal of the organization and the database environment (existing hardware, software, raw data, data processing procedures).
  • Identify database needs and what the database can do to support organizational goals.
  • Identify user needs and characteristics (who will use it, what they want to do, how they will do it).
  • Determine database system requirements.
• Design: From conceptual design to a detailed system specification.
• Implementation: Create the database.
• Maintenance: Troubleshoot, update, and streamline the database.

Business Rules in Database Design

• What: Brief, precise, and unambiguous descriptions of operations in an organization; based on policies, procedures, or principles; apply to any organization that stores data to generate information.
• Why: Improve understanding and communication; standardize data definitions across the organization; serve as a communication tool between users and designers; promote accurate data modeling.
• How (sources): Interviews with managers, policy makers, department managers, end users; written documentation (procedures, standards, operation manuals); observation of business operations.

User-Centered Database Design

• Perspective: The user is always right; system should serve the user, not vice versa.
• Compliance: Users deserve a system that performs as promised.
• Instruction: Easy-to-use guides, contextual help, and meaningful error messages.
• Usability: Interfaces should be intuitive; users should feel in control of software and hardware technology.

What Constitutes Good Database Design?

• Divide information into subject-based tables to reduce redundancy.
• Provide access to data required to join tables when needed.
• Support accuracy and integrity of information.
• Accommodate data processing and reporting needs.

The Design Process (Steps)

• Determine the purpose of the database.
• Find and organize required information.
• Divide information into tables.
• Turn information items into columns.
• Specify primary keys.
• Set up table relationships.
• Refine the design.

Advantages of DBMS

• Controlling data redundancy: Data stored in one place; no duplication.
• Data consistency: A data item appears once with updated value accessible to all users.
• Control over concurrency: Prevents overwriting by concurrent updates.
• Backup and recovery procedures: Automated backup and restore capabilities.
• Data Independence: Separation of data structure from applications that use the data.
• Database Administration: Manage access permissions, grants, and related tasks.

Database Architecture Levels

• Physical Level: File structures, bits consumed, and data structures.
• Logical Level: Data storage and relationships; used by the DBA.
• View Level: What different users see; describes parts of the database.

Database Languages and Interfaces

• Data-Definition Language (DDL): Commands to define data structures (e.g., Create Table).
• Data-Manipulation Language (DML): Commands to manipulate data (e.g., Insert, Update, Delete).
• Data-Control Language (DCL): Commands to control access (e.g., GRANT).
• Data Query Language (DQL)/Query: Commands to query data (e.g., SELECT).
• Example snippet concepts: Create Table, Update, Insert, GRANT, Query.

Database Users and Roles

• Database Users include:
  • Application Programmers (use DML; integrate application programs with the database; interact via application programs).
• System Analyst: Business technology analyst; designs and oversees database architectures; manages projects and teams.
• Temporary/Casual Users: High-level management or short-term users with limited DBMS knowledge.
• Sophisticated/Specialized Users: Business analysts, scientists, engineers; interact with the system through query language without writing traditional programs; may use specialized database applications (CAD, multimedia databases).
• DBA (Database Administrator): Defines the schema; manages all three levels of the database architecture; monitors recovery and backup; has a superuser account; repairs damage from failures.

Case Study: School Management System Modules

• Student Information System
• Attendance Management
• Examination and Grading System
• Fee Management
• Library Management
• Staff Management
• Time Table Management

Class Activity (Exercise)

• Find key modules for the following projects:
  • Hotel management
  • Health monitoring
  • Gym management
  • Alumni Database Management