Database Concepts and Types

Data and Databases

Introduction to Databases

• Brief introduction to database concepts.
• Key question: What are the differences between relational and non-relational databases?

What is Data?

• Types of data stored in databases:
  • Numerical: integers, floating-point numbers
  • Textual: strings, documents
  • Visual: images, videos, audios
• Scale of data usage:
  • Small business (e.g., plant store): products, suppliers, staff members
  • Mobile app data: contacts, game status
  • Large scale: e-commerce websites, social media (Facebook, Twitter, Instagram)
• Data access:
  • Free access
  • Public data (government data)
  • Private data (Facebook data)

What is a Database?

• A database is a structured collection of data.
• A file containing data (e.g., donation file with donors, amounts, party) is not necessarily a database without a structured format.
• Historical examples:
  • Ancient clay tablets with inscriptions for calculations related to palaces or temples.
  • Paper-based systems like bank ledgers or library catalogs.
  • Modern digital storage on computers.

Database System

• Access to the database is not direct; it's managed through a Database Management System (DBMS).
• DBMS is software like MySQL, MariaDB, or Microsoft SQL Server.
• Client-server model:
  • Data stored on disks.
  • DBMS (e.g., Microsoft SQL Server) provides a layer for access.
  • Clients (desktop, mobile applications) communicate with the server through the network.
  • Clients send queries to the server.
  • The server retrieves data from the database and sends it back to the client.
• Client-server model examples:
  • Game server and client-side game application.
  • University website with a web server.
  • Email servers.

Database Application

• A database application is software that helps users interact with database systems.
• Types: desktop-based, web-based, mobile applications.
• Examples:
  • Web application: university portal (e.g., "my Waikato") displaying student details.
  • E-commerce website: Trade Me, storing products, users, and purchase history.
  • Mobile applications: contact lists.

Roles in Database Interaction

• Database Designer:
  • Designs the database structure.
  • Defines the structured format.
• Database Administrator:
  • Installs the database server.
  • Manages permissions and security.
  • Monitors server usage.
  • Handles backups and upgrades.
• Database User:
  • Adds or queries data.
  • Presents data for business insights.
• Database Application Developer:
  • Writes code for mobile, desktop, or web applications to interact with the database.

Relational Databases

• Store data in tables with rows and columns.
• Based on relational algebra (set operations like union, difference).
• Three components:
  • Data Structure: Schema defining tables, columns, and data types.
  • Operations: Syntax for creating tables, adding data, and manipulating data.
  • Rules: Governing valid relational databases to ensure data integrity.
• Still dominant, even after 50 years.

Example: Pet Hotel Database

• Scenario: Design a database for a pet hotel to store pet and owner information.
• Requirements: Register new pets/owners, update/delete information.
• Steps:
  • Analyze requirements: store owner and pet information.
  • Design a schema: organize data into tables and columns.
  • Develop applications: allow hotel staff to view, add, update, and delete records.
• Analyzing Database Requirements:
  • Owner information: names, emails, phone numbers, addresses.
  • Pet information: name, age, type, medical conditions.
  • Relationship: Owner owns a pet.
• Considerations:
  • One owner can have multiple pets.
  • Multiple owners for one pet (e.g., primary and secondary contacts).

Entity Relationship Model

• Used as a tool before actual database design.
• Components:
  • Entities: Independent objects or concepts (e.g., owner, pet).
  • Attributes: Properties of entities (e.g., owner: name, email; pet: name, age, type).
  • Relationship: Expressed using a diamond, connecting entities (e.g., "owns").
  • Cardinalities: Number of entities participating in the relationship (e.g., one owner can have multiple pets).
  • Key Attributes: Unique identifiers (e.g., email for owner, pet ID for pet).

Resulting Tables

• Owner table: stores owner information with the phone number as the primary key.
• Pet table: stores pet information.
• Relationship table: connects owners and pets using owner's phone number and pet ID to describe the relationship.
• Each table includes columns and data types (e.g., string, integer).
• Designed to reduce redundancy.

Non-Relational Databases (NoSQL)

• Evolved due to massive volumes of online data (e.g., trades, social media posts).
• Designed for horizontal scaling (adding more servers as needed).
• Good for processing large volumes of data and supporting real-time analysis.
• Examples: MongoDB, Google Firebase.

Data Storage

• Data is stored in:
  • Documents (JSON format).
  • Key-value pairs.
  • Graphs.
  • Wide columns: each row can have a different number of columns or different structures.

Document Example (JSON)

• Data stored in documents with a structured format, including pet IDs, owner information, and arrays of owners.
• Data redundancy is not a primary concern.

Key-Value Pairs

• Data stored with different levels of keys (e.g., pet ID). Values from keys can be quickly retrieved.

Database Operations

Relational Databases

• Operations: create, insert, read, update, delete, query.
• Example SQL query: find the owner's email address of the dog named 'Fan'.
• latex SELECT email FROM owner WHERE pet_name = 'Fan' AND owner.pet_id = pet.pet_id

NoSQL Databases

• Use different syntax for querying, updating, deleting, and inserting.

Key Differences

• Relational databases store data in structured tables with enforced relationships.
• Non-relational databases offer flexible data models like documents and key-value pairs, optimized for scalability and speed.