CSC403 Database Management Systems

• CSC403 Database Management System is worth 3 credits.

Course Objectives

• Develop an entity-relationship data model.
• Map the entity-relationship data model to a relational model.
• Learn relational algebra and how to formulate SQL queries.
• Apply normalization techniques to normalize a database.
• Understand the concepts of transaction, concurrency control, and recovery techniques.

Course Outcomes

• Recognize the need for a database management system.
• Design Entity Relationship (ER) and Enhanced Entity Relationship (EER) diagrams for real-life applications.
• Construct a relational model and write relational algebra queries.
• Formulate SQL queries.
• Apply the concept of normalization to relational database design.
• Describe the concepts of transaction, concurrency, and recovery.

Module 1: Introduction to Database Concepts

• Introduction to Databases.
• Characteristics of databases.
• Comparison of File systems versus Database systems.
• Data abstraction and data independence.
• DBMS (Database Management System) system architecture.
• Details of a Database Administrator.

Database Management System (DBMS)

• Contains information about an organization.
• A collection of interrelated data.
• A set of programs to access data.
• Provides an environment that is both convenient and efficient for use.

Database Applications

• Banking transactions.
• Airline reservations and schedules.
• University registration and grades.
• Sales data about customers, products, and purchases.
• Online retailers use order tracking and customized recommendations.
• Manufacturing oversees production, inventory, orders, and supply chain.
• Human Resources handles employee records, salaries, and tax deductions.
• Databases can be very large in scale.
• They affect nearly all aspects of life.

University Database Example

• Add new students, instructors, and courses.
• Register students for courses and generate class rosters
• Assign grades to students, calculate grade point averages (GPA), and generate transcripts.
• Database applications were initially built directly on top of file systems.

Drawbacks of Using File Systems to Store Data

• Data redundancy and inconsistency can occur.
• Multiple file formats with duplicated information in different files.
• Difficulty in accessing data.
• Required creation of new programs for each new task.
• Data isolation due to multiple files and formats.
• Integrity problems.
• Integrity constraints (e.g., account balance > 0) are embedded in program code.
• It is hard to add new constraints or change existing ones.
• Atomicity of updates can be compromised.
• Failures may leave the database in an inconsistent state.
• A transfer of funds needs to either fully complete or not occur at all.
• Concurrent access by multiple users must be managed.
• There must be concurrent access performance.
• Uncontrolled concurrent access can lead to inconsistencies.
• If two people are reading a balance (e.g., 100) and updating it by withdrawing money (e.g., 50 each) at the same time, issues can arise.
• Security problems.
• Difficulty in providing user access to some data, but not all.
• Database systems provide solutions to all the listed problems.

Data Models

• Collection of tools used to describe:
• Data
• Data relationships
• Data semantics
• Data constraints
• Relational model
• Entity-Relationship data model (mainly for database design).
• Object-based data models (Object-oriented and Object-relational).
• Semistructured data model (XML)
• Older models:
• Network model
• Hierarchical model

Relational Model

• The relational model is described in Chapter 2.
• It's an example of tabular data.

Data Definition Language (DDL)

• Specification notation for defining the database schema.
• Example: create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
• The DDL compiler generates table templates stored in a data dictionary.
• Data dictionary contains metadata (data about data).
• Database schema defines the structure of the database.
• Integrity constraints maintains data validity.
• Primary key uniquely identifies instructors.
• Referential integrity enforces relationships (references constraint in SQL).
• For example, dept_name value must appear in the deptartment relation.
• Authorization manages user permissions.

SQL

• A widely used non-procedural language.
• Example: Finding an instructor's name with an ID of 22222 with the statement select name from instructor where instructor.ID = '22222'
• Example: Finding the ID and building of instructors in the Physics department with the statement select instructor.ID, department.building from instructor, department where instructor.dept_name = department.dept_name and department.dept_name = 'Physics'
• Application programs generally access databases through:
• Language extensions to allow embedded SQL.
• Application Program Interface (e.g., ODBC/JDBC) which allow SQL queries to be sent to a database.

Areas to consider in Database Design

• Storage Management
• Query Processing
• Parsing and translation
• Optimization
• Evaluation
• Transaction Management
• Handles system failures.
• Handles concurrent updates from multiple users.
• Transaction: Operations performing a single logical function in a database application
• Transaction Management component keeps the database in a logically correct state despite failures.
• Concurrency-control manager manages interactions between concurrent transactions.

Database System Architecture

• The system architecture has different types of users including:
• Naive users
• Application programmers
• Sophisticated users
• Database administrators
• The architecture also has a variety of tools and interfaces to use with the different users

Database Architecture

• The architecture of a database system is greatly influenced by the underlying computer system.
• Types of database architecture include:
  • Centralized
  • Client-server
  • Parallel (multi-processor)
  • Distributed

History of Database Systems

• 1980s:
  • Research relational prototypes evolve into commercial systems
  • SQL becomes an industrial standard
  • Parallel and distributed database systems
  • Object-oriented database systems
• 1990s:
  • Large decision support and data-mining applications.
  • Large multi-terabyte data warehouses.
  • Emergence of Web commerce.
• Early 2000s:
  • XML and XQuery standards.
  • Automated database administration.
• Later 2000s:
  • Giant data storage systems, such as Google BigTable, Yahoo PNuts, and Amazon.