CS 320: Database Management Systems

Introduction, Syllabus

Ge (Frank) Xia, CS, Lafayette College

What is a DBMS

• • Wikipedia: A database management system (DBMS) is a computer program (or more typically, a suite of them) designed to manage a database (a large set of structured data), and run operations on the data requested by numerous clients
  • » It is not a spread sheet (Excel)
  • » It is not a chunk of data; Rather, it is a tool to store and use data
  • » It is primarily used in business and corporations
  • » DBMS and its derivatives have been the force behind the development of computer technologies for long time
    

Traditional Database Application

• • Suppose we are building a system for the registrar to store the information about:
  • » students
  • » courses
  • » professors
  • » who takes what, who teaches what
  • » evaluations
• • Why use a DBMS ?
  
  

What we need from a database

• • Store the data for a long period of time
• » Large amounts (100s of GB)
  
• » Protect against crashes
  
• » Protect against unauthorized use
  
• • Allow users to query/update:
  
• » Who teaches “CS102”
  
• » Enroll “Mary” in “CS320”
  
• » What do students think of Professor Schmoo

What we need from a database

• • Allow several (100s, 1000s) users to access the data simultaneously
• • allow administrators to change the schema
  
• • We want the database to allow us to focus on the application logic!
• • Why Direct Implementation Won’t Work
  

Trying Without a DBMS

• • Storing data: file system is limited
  
• » size less than 4GB (on 32 bits machines)
  
• » when system crashes we may loose data
  
• » password-based authorization insufficient
  
• • Query/update:
  
• » need to write a new C++/Java program for every new query
  
• » need to worry about performance
  
  

Trying Without a DBMS

• • Concurrency: limited protection
  
• » need to worry about interfering with other users
  
• » need to offer different views to different users (e.g. registrar, students, professors)
  
• • Schema change:
  
• » need to rewrite virtually all applications
  
  

Functionality of a DBMS

• • Data Definition Language - DDL
  
• • Data Manipulation Language - DML
  
• » query language
  
• • Storage management
  
• • Transaction Management
  
• » concurrency control
  
• » recovery
  
  

Building Application with DBMS

• • Requirements modeling (conceptual, pictures)
  
• » Decide what entities should be part of the application and how they should be linked.
  
• • Schema design and implementation
  
• » Decide on a set of tables, attributes.
  
• » Define the tables in the database system.
  
• » Populate database (insert tuples).
  
• • Write application programs using the DBMS
  
• » Way easier now that the data management is taken care of
  
  

Conceptual Modeling

Schema Design & Implementation

• • Table: students

• SSN	Name	Category
  123-45-6789	Charles	Undergrad
  234-56-7890	Dan	Grad
  	...	...

  
  

Schema Design & Implementation

• • Table: takes

• SSN	CID
  123-45-6789	CS444
  234-56-7890	CS412
  345-67-8901	CS320
  	...

  
  
  

Schema Design & Implementation

• • Table: courses

• CID	Name	Semester
  CS444	Operating Systems	Fall
  CS412	Algorithms	Fall
  CS320	Database Systems	Spring
  ...

  
  
  

Querying a Database

• • Find all courses that “Mary” takes
  
• • Use Structured Query Language (SQL)
  
SELECT C.name
FROM courses C, takes T, students S
WHERE S.name = "Mary"
AND S.SSN = T.SSN AND T.CID = C.CID;

  
• • Query processor figures out how to answer the query efficiently
  
  

Data Management

• • Traditional Data Management:
  
• » relational data for enterprise applications storage
  
• » query processing/optimization
  
• » transaction processing
  
• • Novel Data Management:
  
• » Integration of data from multiple databases, warehousing.
  
• » Data management for decision support, data mining.
  
• » Exchange of data on the web: XML
  
  

Database Industry

• • Relational databases are a great success of theoretical ideas.
  
• • Big DBMS companies are among the largest software companies in the world.
  
• » Oracle
  
• » IBM (with DB2)
  
• » Microsoft (SQL Server, Microsoft Access)
  
• » Sybase (recently acquired by SAP)
  
• • $20B industry.
  
  

The Study of DBMS

• • Several aspects:
  
• » Modeling and design of databases
  
• » Database programming: querying and update operations
  
• » Database implementation
  
• • DBMS study cuts across many fields of Computer Science: OS, languages, AI, Logic, multimedia, theory...
  
  

Course (Rough) Outline

• • Database design:
  
• » Entity Relationship diagrams
  
• » Modeling constraints
  
• • The relational model:
  
• » Relational algebra
  
• » Transforming E/R models to relational schemas
  
• • XML: a data format
  
  

Outline (Continued)

• • SQL
  
• » Views and triggers
  
• • Storage and indexing
• • Query optimization
• • Transaction processing and recovery
• • Advanced topics

The Project

• • Goal: Design and implement a substantial, end-to-end database application for a real-world scenario
  

The Project

• • Tasks include:
  
• » Design a relational schema for the database
  
• » Create an actual database using a relational database management system
• » Populate the database with sample data
• » Write interactive queries and modifications on the database
• » Develop web applications and user interfaces for manipulating the database