CPSC 304 Introduction to Relational Databases

Winter 2014 Term 2 (January -- April 2015)

Course Description

Overview of relational database systems, logical database design, E-R models, formal relational query
languages, SQL, functional dependencies, normalization, formal relational query languages (Relational
Algebra and Datalog), Commercial Query Language (SQL), data warehouses, special topics.

Prerequisite

(a) CPSC 221 or
(b) CPSC 260, EECE 320 and one of CPSC 210, EECE 210, EECE 309.

Learning Outcomes

At the end of the course you will be able to :

Describe how databases store and retrieve information using the basic concepts and terminology of relational databases
Create a non-trivial E/R diagram (semantic model) about an enterprise (e.g., retail establishment, airport, school, library) that correctly describes the entities, attributes, and relationships among the entities, for some of its major business functions;
Create a logical data model from an E/R diagram to design a set of DB tables;
Normalize a set of attributes to eliminate update anomalies or redundancies from a set of tables;
Implement a logical data model using a DBMS;
Write queries using formal query languages such as relational algebra and Datalog;
Write SQL statements to query a set of tables in a DBMS involving multiple conditions, aggregate functions, grouping and group selection (using GROUP BY and HAVING clauses);
Write SQL statements to insert, delete and update a set of tables in a DBMS;
Develop a data-centric application with complete set of business transactions and appropriate user interface using a popular programming language and a popular database management system;
Explain the purpose of a data warehouse and the operations that can be performed on it; design basic star schemas and snowflake schemas; compare and contrast data warehouses / OLAP (online analytical processing) with traditional DBMSs / OLTP (online transaction processing).

Course Content

CPSC 304 is an introductory course to database design and management. The course focus is primarily on
relational databases, and presents the concepts related to the design of a relational database, the most
popular relational database languages and the concepts related to data warehousing and online analytical
processing.

We will begin with a general overview of the database systems and their common features. Then, we will
examine the issues of the logical design of a relational database for an enterprise. We'll study in depth the
Entity-Relationship model as the primary method for representing the overall logical structure of the data,
and how this can be combined with the integrity constrains and used to design a database in an appropriate
normal form which avoids redundancies and allows efficient access of the data.

After that, we will study the popular relational languages. We'll start our study with relational algebra and Datalog on
the formal side and then move to SQL, a popular database language used by most industry strength relational database
systems. In combination with the course assignments, this part of the course will provide you with the
knowledge and skills you need to work on any relational database project.

At the end of the course we will examine systems which allow organizations to create summaries from
multiple databases and provide tools for analyzing the data in order to support high-level decision making.
This type of data transformation is called data warehousing and the data processing that involves data
analysis is known as online analytic processing (OLAP). In this part of the course we’ll discuss the basic
concepts of warehousing, like basic models for storing this type of data and basic OLAP queries.
If time permits we may discuss the basic concepts of other type of databases, like Information Retrieval Systems
and XML-based databases.

CPSC 304 is an introductory course to database design and management. The course focus is primarily on
relational databases, and presents the concepts related to the design of a relational database, the most
popular relational database languages and the concepts related to the transaction processing layer (top
layer) of a database system. The actual topics of transaction management, concurrency control, and crash
recovery are dealt with in detail in CPSC 404.

Textbook

Raghu Ramakrishnan and Johannes Gehrke. Database Management Systems, 3rd Edition, McGraw-Hill,
2003.

Additional References

Hector Garcia-Molina, Jeffrey Ullman, and Jennifer Widom, Database Systems: The Complete
Book, Second Edition, Prentice Hall, 2009.
Abraham Silberschatz, Henry Korth & S. Sudarshan, Database System Concepts, McGraw-Hill, 2006 or newer edition.
R. Elmasri & S. Navathe, Fundamentals of Database Systems, Addison-Wesley, 2006 or newer edition.
Any book on databases dated 2006 or later

Course Material On-Line

Most of the materials we will use in this course will be available on-line at the following URLs:

Course home page at http://www.cs.ubc.ca/~laks/cpsc304/304home.html
Connect course site at https://www.connect.ubc.ca
Piazza page for signing up: https://piazza.com/ubc.ca/winterterm22014/cpsc304
Piazza page for the course: https://piazza.com/ubc.ca/winterterm22014/cpsc304/home

You came here from the course home page which has links to the course notes. As mentioned elsewhere, we will be extensively using the Piazza system (essentially exclusively) for all online discussion related to this course.