Introduction to Database Systems

Introduction to Database Systems

Course Information

• CMPSC363
• Lecture #1

Course Overview

• Background and History
• Data Models:
  • E/R Model
  • Relational Model
  • Object Oriented Model
  • XML and Database
• Database Programming:
  • Relational Algebra
  • SQL
  • Object Oriented

Recommended Textbooks

• Primary Textbooks:
  • Jeffrey D. Ullman and Jennifer Widom, "A First Course in Database Systems", Third Edition, Prentice Hall, 2007
  • Hector Garcia-Molina, Jeffrey D. Ullman and Jennifer Widom, "Database Systems: The Complete Book", Second Edition, Prentice Hall, 2008
• Other Useful Textbooks:
  • Fundamentals of Database Systems (Elmasri and Navathe)
  • An Introduction to Database Systems (C. J. Date)
  • Database Management Systems (Ramakrishnan)

Course Prerequisites

• Data Structures
• Algorithm
• Any Programming Language
• Discrete Math

Tentative Class Schedule

• Introduction (1-2 classes)
• E/R Model (2-3 classes)
• Relational Model (3-4 classes)
• Other Models (2-3 classes)
• Relational Algebra (3-4 classes)
• SQL (4-5 classes)
• Constraints and Triggers (1-2 classes)
• System Aspects of SQL (3-4 classes)

Course Load

• 8–10 Homework Assignments
• Multiple In-Class Quizzes
• 1 Project
• A Midterm Exam
• A Final Exam
• Submission Policy: Homework and project assignments are due at the beginning of class one or two weeks that follow the assignment. Late homework will not be accepted, and a score of zero will be assigned for that assignment (strictly enforced).

Course Policy

• Exams:
  • No access to any material nor discussion with anyone (except the instructor) is allowed.
• Assignments (homework and quizzes):
  • Solutions must be developed independently. Stealing, giving, or receiving any code, drawings, diagrams, text, or designs from another person is not allowed.
• Penalties:
  • Maximum penalty for academic dishonesty is an F in the course; incidents will be reported to the university.

Grading Policy

• Grades calculated using the following weights:
  • Homework: 25%
  • Quizzes: 15%
  • Project: 10%
  • Mid-term Exam: 20%
  • Final Exam: 30%
• Grading Scale:
  • A: 90+
  • B: 80+
  • C: 70+
  • D: 60+
  • F: <60

Introduction to Databases

What is a Database?

• A database is a set of data or a collection of information. These data are related.
• Examples:
  • Student Information
  • Human Genome Sequence
  • Company Product Information

More Examples

• Airline Reservation Systems:
  • Reservations by a customer for a single flight, including information such as assigned seat and meal preference.
  • Information about the flight including airports, departure and arrival times, ticket prices, requirements, and availability.
• Banking Systems:
  • Management of customers, accounts, loans, balances, as well as deposit and withdrawal functionalities.

Database Management System (DBMS)

What is DBMS?

• A Database Management System (DBMS) is a powerful tool for creating and managing large amounts of data efficiently and allows it to persist over long periods of time while safely supporting multiple user access. It is used to manage databases.

DBMS Capabilities

• Persistent Storage:
  • Supports the long-term storage of data.
• Programming Interface:
  • Provides a way to interact with the database programmatically.
• Transaction Management:
  • Maintains the integrity of the database during transactions.
• Allows users to create databases and specify their schema using data-definition language.
• Facilitates querying and modifying the data using data-manipulation language.
• Ensures data consistency and failure recovery.

Why Not Use a File System?

• File systems can store data for long periods, but they present several problems:
  • No query language for efficient access to data.
  • Limited creation support for databases.
  • Inadequate consistency control.
  • Management of data becomes complex.

DBMS Evolution

• Historical Progression:
  • File Systems
  • Hierarchical Model (Tree-based)
  • Network Model (Graph-based)
  • Relational Model
  • Object Oriented Model
  • Object/Relational Model

Relational Database System

• Founded by Ted Codd in 1970.
• The view of data is expressed in the form of relations, tuples, and attributes - typically represented as tables, rows, and columns.
• Queries can be composed in a high-level language, which is simple and efficient.
• This model is widely used in most commercial systems.

Example of Relational Structure

Example 1.1: Bank Account Information

Example Queries

• To check the balance:
  ```sql
  SELECT balance
  FROM Accounts
  WHERE accountNo=67890;

- To check account numbers:  

sql
SELECT accountNo
FROM Accounts
WHERE type='savings' AND balance < 0;
```

Major DBMS Products

• Oracle
• IBM: DB2, Informix
• Microsoft: SQL Server, Access
• Sybase
• MySQL
• Postgres
• These are all "relational" or "object-relational" database systems at their core.

Schema versus Data

• Schema:
  • Describes how data is structured; defined at set-up time and rarely changed. It is a part of the "metadata."
• Data:
  • Actual instance of the database, which may change rapidly.
• Comparably resembles types and variables in programming languages.

Data Definition Language (DDL)

• DDL consists of commands used for setting up the schema of a database.
• The process of designing a schema can be complex and may utilize design methodologies and/or tools.

Data Manipulation Language (DML)

• DML consists of commands for manipulating data in a database, including:
  • RETRIEVE
  • INSERT
  • DELETE
  • MODIFY
• Also referred to as "query language."

Roles in DBMS

• DBMS Implementer: Builds the system.
• Database Designer: Establishes the schema of the database.
• Database Administrator (DBA): Loads data and ensures system is operational.
• Database User: Queries and modifies the data.

DBMS Evolution Trends

• Size:
  • Trends toward smaller systems (personal computers) and larger systems (terabytes or petabytes of data using tertiary storage and parallel computing techniques).
• Architecture:
  • Client-server (Two-Tier) architecture.
  • Multi-Tier architecture: combines client, application server, and database server.
• Versatile Data Type:
  • Supports various data types from integers and characters (small storage requirement) to videos and audios (large storage requirement).
• Integration:
  • Data warehouses and data mining trends.

Components of Database Management System

• User/Application:
  • Executes transactions through queries and updates.
• Query Compiler:
  • Compiles queries for execution.
• Execution Engine:
  • Executes the compiled commands.
• Index/File/Record Manager:
  • Manages storage access.
• Transaction Manager:
  • Handles logging, concurrency control, and deadlock resolution.

Overview of DBMS Processes

• DDL Commands:
  • Processed by the DBA, going through the DDL Compiler and the Execution Engine.
• Query Processing:
  • Involves the User, Query Compiler, and Execution Engine for responding to queries.
• Transaction Processing:
  • Managed by the Transaction Manager.

Storage and Buffer Management

• Buffer Manager:
• Storage Manager:
• Data: The contents of the database.
• Metadata (Schema): The structure of the database.
• Statistics: Properties of data.
• Indexes: Used to efficiently access data.

Transaction Processing

• Transaction Manager Functions:
  • Logging
  • Concurrency Control
  • Deadlock Resolution

Query Processor

• Components:
  • Query Complier
  • Query Parser
  • Query Preprocessor
  • Query Optimizer
  • Execution Engine

Outline of Database Systems

• Database Design: Determine the database structure.
• Database Programming: Perform database operations.
• Database System Implementation: Build the database.

Next Class

• E/R Concepts