Introduction to Databases

Databases: An Overview

• This unit introduces databases
• It explains why databases are superior to other data storage methods for users and companies.
• Subsequent sessions cover data organization, database access languages, and a brief history.

Session 1: Introduction to Databases

• A database is an electronic system for easy data access, manipulation, and updates.
• A database management system (DBMS) is a collection of interrelated data and access programs.
• Database systems' primary goal is convenient and efficient information storage and retrieval.
• Facebook, with 2.936 billion users, stores user data in a database.

Applications of Database Systems

• Databases are crucial for enterprise IT, enabling structured and controlled information management.

• Uses include organizing, processing, and structured information management.

• Enterprise Information:

  • Accounting uses databases for payments, receipts, account balances, and asset information.
  • Human Resources uses databases for employee information, salaries, payroll taxes, and benefits.
  • Online retailers use databases for sales data, order tracking, recommendations, and product evaluations.

• Banking and Finance:

  • Banking uses databases for customer information, accounts, loans, and transactions.
  • Finance uses databases for holdings, sales, purchases of financial instruments, and real-time market data.

• Universities: Maintain student information, course registrations, and grades in databases.

• Airlines: Utilize databases for reservations and schedule information, pioneering geographically distributed database use.

• Telecommunications: Employ databases for call records, monthly bills, prepaid card balances, and network information.

Types and Examples of Databases

• Popular database types include Distributed, Relational, and NoSQL databases.

• Distributed Databases:

  • Data and processing are spread across multiple physical locations and database nodes.
  • Systems communicate over a network.
  • Examples include Apache Cassandra, HBase, FoundationDB, Couchbase, and Ignite.

• Relational Databases:

  • Data is organized with predefined relationships in tables with rows and columns.
  • Commonly known as SQL databases.
  • Examples include PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database.

• NoSQL Databases:

  • Relationships are not predefined, unlike relational databases.
  • Data is stored as JSON documents instead of columns and rows.

Session 2: Drawbacks of File-Based Systems/Advantages of Database Management Systems

• File-based systems, used in the 1960s, involve storing information in operating system files.
• Programs are developed to allow users to manipulate these files.
• Drawbacks of File-Based Systems:
  • Data redundancy and inconsistency occurs when the same information is duplicated across different files and applications, potentially leading to conflicting data.
  • The same customer information can appear multiple times in separate files like Customer Orders and Customer Invoices.
  • Data isolation: Data is scattered in various files and formats, making it difficult to retrieve appropriate information with new applications.
  • Security problems: Enforcing security constraints is challenging because applications are added to file-processing systems in an ad hoc manner.
  • Concurrency issues: File-based systems struggle with multiple users accessing the same record simultaneously, often locking files and preventing concurrent access.
  • Integrity problems: Maintaining data consistency and enforcing rules is difficult because constraints are embedded in application programs, requiring changes across multiple programs when new rules are added.
• Advantages of Database Systems:
  • Sharing of Data: Controlled access through authorization protocols and remote, simultaneous access for users.
  • Increased productivity: Tools for querying and transforming data enable quick, informed decisions.
  • Increased concurrency: Allows multiple users to read and update records concurrently without data inconsistency.

Session 3: View of Data and Instances and Schemas

• Abstraction means hiding unnecessary details to simplify user interaction.
• Data abstraction hides irrelevant details of data storage and maintenance from users.
• Levels of Data Abstraction simplify user interactions by hiding complexity.
• These levels include:
  • Physical Level/Internal Schema: The lowest level describes how data is stored in hardware and accessed, typically operated by DBAs.
  • Logical Level/Conceptual Level: A level above physical, storing data in entity sets with defined relationships and constraints; used by developers and DBAs.
  • View Level/User Level/External Level: The highest level, showing only part of the database relevant to the user.
• Data independence: Changing the data schema at one level should not affect the schema at the next level.
• Database Schema
• A database schema is the design of a database.
• Divided into three types based on abstraction levels.
  • Physical schema: Data storage in hard disk blocks, easily changed without affecting logical or view schemas with physical data independence.
  • Logical schema: Database design at the logical level, which impacts application programming.
  • View schema: End-user interaction with database systems, with possible subschemas for different views.
• Database Instance
• An instance refers to the data stored in a database at a specific time, which changes as data is added or deleted.
• Schema defines variable declarations, while the instance is the value of these variables at a given moment.

Session 4: Data Models

• A data model represents real-world objects, events, and associations.

• It includes conceptual tools for describing data, relationships, semantics, and consistency constraints.

• Data models describe database design at physical, logical, and view levels.

• Types of Data Models:

  • Relational Model:
    • This is the most widely used model which Stores information in rows and columns.
    • Tables are called relations.
    • Each table has multiple columns with unique names.
    • Each row is a tuple with instance information.
    • Attributes or fields define the table.
  • Entity-Relationship (ER) Model:
    • It is used for database design.
    • Uses entities (real-world objects) and their relationships.
    • Entities have attributes (characteristics).
    • Relationships describe how attributes relate.
  • Object-Oriented Model:
    • Inspired by object-oriented programming languages.
    • Data and relationships are in a single structure.
  • Semistructured Data Model (XML):
    • Extension of the relational model with flexible structure.
    • Items may have missing or extra attributes.
    • No differentiation between data and schema.
    • XML and JSON are used.

• Past Models

  • Network and Hierarchical models.

Session 5: Database Languages

• Database languages define, store, and access data in a database.

• These languages include Data Definition Languages (DDL) and Data Manipulation Languages (DML).

• Data Definition Languages (DDL):

  • DDL defines the database structure or schema at physical, logical, and view levels.
  • Includes Storage Definition Languages (SDL) for defining storage structures and access methods.
  • DDL and SDL statements permanently alter the database structure and are typically used by database administrators.
  • Common DDL statements include:
    • Create: Constructs a relation (table or entity).
    • Alter: Modifies the structure of a relation.
    • Rename: Renames a relation.
    • Drop: Deletes a relation or database.
    • Truncate: Deletes all entries from a relation while keeping the structure.

• Key Concept: Database management systems use consistency constraints.

• Requirements for data values before saving in the database.

  • Domain Constraints: - Setting a domain for an attribute.
  • Referential Integrity: - Requiring that values in one relation appear in another.
  • Assertion Constraint: - Conditions that the database must always satisfy.
  • Authorization: - Allowing users to perform certain operations based on assigned access types.

• Data Manipulation Languages (DML):

  • A specific set of statements or commands for accessing and manipulating data.
  • These commands include adding, updating, deleting, and retrieving data.
  • Data Query Languages are DML commands focused on retrieving (querying) data.

• Types of Data Manipulation Languages:

  • Procedural DMLs is Low-level languages where users define what data is needed and how to get it. Low-level languages retrieve and process records one at a time.
  • Non-Procedural high-level languages is where users declare what data is needed without specifying how to get it. Also known as set-at-a-time DMLs, and can retrieve multiple records with a single command.

Session 6: History of Databases

• The concept of databases existed long before the invention of computers.
• The history of databases include:
  • 1950s-early 1960s: Punch Cards and Magnetic Tapes
    • Automation of tasks such as payroll; data was stored on tapes, which could be read and processed sequentially.
    • Single role could hold large amounts of data (10,000 punch cards).
    • Could be read only sequentially.
    • Standard punch card was first used for vital statistics tabulation in the 1890 US census.
  • Early 1960s and 1970s: Hard Disks, Navigational and Relational Models
    • History of Databases began in the early 1960s.
    • Charles Bachman designed the Integrated Data Store (IDS). IDS was developed to solve 2 problems: enable sharing of data files between applications and also allow ordinary programmers to develop random access applications using high-level languages.
    • Was used to solve two problems: enabling data sharing between applications and allowing random access applications with high-level languages.
    • The Information Management System (IMS) was shortly created IBM. Both databases were forerunners of the navigational database. -Edgar F. Codd proposed the relational model in 1970, disconnecting logical organization from physical storage.
    • 1974 - 1977; Ingres (UBC) & System R (IBM). The latter contributed to SQL
    • Chen then proposed the Entity-Relationship Model (1976)
  • 1980s - Growth of Relational Databases. -Relational DB model rose, & the commercialisation of relational systems saw this type of database rise in use and popularity.
    • SQL became standard language
  • 1990s - The Internet
    • Object-oriented DB grew in the 90's
    • The WWW saw huge growth
  • 2000s to current day
    • XML & XQuery emerged
    • Open-source began its growth. (PostGresSQL/MySQL),
    • 1998 saw NoSQL emerge, allowing for faster processing of large, varied datasets.