Week2Lecture_e519faf9-dadd-4d76-8f19-1b0934588fe5_82787_

Introduction to Databases

  • Lecture by Hrishav Tandukar, Islington College

  • Course Code: CC4057 - Introduction to Information Systems

Basic Terminologies

  • Data: Raw collection of facts and figures (numbers, words, measurements).

  • Information: Processed data presented in a human-readable format.

  • Data is transformed into information through processing.

Data Lifecycle

  • Phase 1: Creation of structured/unstructured data.

  • Phase 2: Data is stored.

  • Phase 3: Data can be viewed, processed, modified, and saved.

  • Phase 4: Data is accessed by various owners from different devices.

  • Phase 5: Data may be archived.

  • Phase 6: Managing archived data over time becomes increasingly complex.

Data Lifespan

  • Temporary Data: Used briefly during program execution.

  • Transient Data: Temporary parameters during program invocation.

  • Persistent Data: Maintains information relevant before, during, and after program execution.

File-Based Approach

  • Traditional methods of information system design focus on individual departmental data processing needs.

  • Often leads to extensive programming needs, requiring deep familiarity with file structure.

  • Common file types include Student.txt, Book.txt, etc.

Problems with File System Data Management

  • Time-consuming and costly to maintain,

  • Data Redundancy: Repeated unnecessary data entries.

  • Data Inconsistency: Mismatched data.

  • Difficult to access and share data.

  • Integrity issues and security problems.

Advantages of Database Systems

  • Structured organization for easy access, management, and updates.

  • Avoids unnecessary data duplication and promotes data usage across departments.

  • Efficient application program development and cost-effectiveness.

Database Management System (DBMS)

  • Software for creating and managing databases.

  • Facilitates systematic data creation, retrieval, updating, and management.

  • Provides security controls, concurrency handling, data integrity, backup, and recovery services.

Types of DBMS

  • Relational Database Management System (RDBMS): MySQL, Access, Oracle.

  • Hierarchical Database Management System (HDBMS): Legacy systems.

  • Distributed Database Management System (DDBMS): Apache Hadoop.

  • Object-Oriented Database Management System (OODBMS): Postgres.

Relational Data Model

  • A relational database consists of tables:

    • Relation: A table.

    • Tuple: A row in the table.

    • Attribute: A column in the table.

  • Tables within the database interrelate to maintain relationships.

Structured Query Language (SQL)

  • Standard language for querying relational databases.

  • Used for operations like inserting, searching, updating, and deleting records.

MySQL

  • Popular open-source database solution.

  • Handles large databases efficiently and has fast performance.

  • Free to use; students can install MySQL locally.

  • Easily integrates with programming languages like PHP, Java, and Python.

XAMPP

  • A free and open-source web server solution package.

  • Includes Apache server, MariaDB (MySQL version), PHP, and Perl.

  • Provides a ready-to-use database environment.

Connecting to MySQL

  • XAMPP command line used for connecting:

    • mysql -u root -h localhost

  • SQL statements must end with a semicolon and can contain multiple lines.

Basic SQL Commands

  • CREATE DATABASE [DB NAME];: Create new database.

  • SHOW DATABASES;: List all databases.

  • USE [DB NAME];: Select active database.

  • CREATE TABLE [TABLE NAME] ([COLUMN DETAILS]);: Create new table.

  • INSERT INTO [TABLE NAME] VALUES (...);: Insert data into table.

Summary of Database Structure

  • A database organizes and stores information for easy access and management.

  • Comprised of interrelated tables with rows (tuples) and columns (attributes).

Conclusion

  • The lecture concludes with an invitation for questions, reinforcing the knowledge of databases and their importance in information systems.

  • End of Lecture 2.