Relational Database Management Systems (RDBMS)

This module presents the most commmly used database managment system in use today: the Relational Database Management System (RDBMS). It begins by exploring the underlying basis for RDBMS systems: the relational model and algebra pioneered by Edgar Codd in the ealy 1970s. It then looks at how the relationa model is represented by relational tables, and operated on by Structured Query Language (SQL) the language of relational databases.

Introduction to Database Concepts

This lecture covers the basic concepts of database management system concepts. It begins by describing the characteristics that make using a database managment systems attractive compared to each applicaion developing its own data management scheme. It also describes three roles that occur in managing a DBMS and what functions each role performs.

Next, we look at the different types of architectures for data-centric applicatins and what role a DBMS plays in each type, including 1-, 2-, and 3-tier architectures. We also introduce two logical models for representing information: the Entity-Relationship (ER) model and the Relational model, and look at how the two are related.

Finally, we learn what a schema is and how it relates to the data stored in the database, and the concept of data independence: how the logical schema are independent of their physical representation.

Readings

Class presentation

Tutorial readings

Relational Algebra

This lecture begins by introducing Edgar Codd's work with relational data models, and his twelve rules for any relational database system. We will also review the elements of the relational data model, and introduce the operations provided by relational algebra that are at the core of a relational dabase mamagement system

Next, we will look at the relational model, and learn how to map E-R models onto relational models using simple transformations between entities/attributes and relations and their table representations.

Finally we will learn the basics of Structured Query Language (SQL) that is universally used to create and query relational databases, and how SQL operations implement the operations in relational algebra. We will also gain hands-on experience with relational algebra and SQL using the RelaX relational algebra calculator to perform query operations on example tables.

Readings

Class presentation

Tutorial readings

Additional readings

SQL Overview

In this lecture, we will begin by introducing Structured Query Lanaguage (SQL), the language used to interact with a relational database system (RDBMS). SQL fulfills Codd's fifth rule as a comprehensive data sub-language that exclisively provides access to a RDBMS for data definition, manipulation and transactions. We will look at the major operations in SQL, what they do, and how they map into expressions in relational algebra.

Next, we will look at the topic of database design, including how to derive entity-relationship (ER) models by analyzing requirements, then how to map ER models onto a series of relationships, and finally how to transform those relationships into normalized forms to ensure that a database structure is suitable for general-purpose querying, and is free of certain undesirable characteristics including insertion, update, and deletion anomalies that could lead to loss of data integrity.

Readings

Class presentation

Additional readings

  • DUE DATE: Thu May 16 by 11:59:59pm

Use the RelaX Relational Algebra Calculator with the "Wikipedia - Relational algebra" dataset to answer these questions. Create a text file "assignment-1.txt" with your solutions in the form of a relation or a relational algebra expression that is executable in the calculator. Include a comment before each answer that identifies the question.

  1. This expression uses a union of two select operations on the Employee relation. Rewrite this as a single select on the Employee relation with compound conditions. 
    (σ DeptName = 'Sales' Employee) ∪ (σ DeptName = 'Executive' Employee)
  2. This expression uses select and cross join. Rewrite it using just a single join of another kind. 
    σ Employee.DeptName = Dept.DeptName (Employee ⨯ Dept)
  3. Is this expession an identity for the Employee or the Dept relation (ignoring order)? 
    (Employee ▷ Dept) ∪ (Employee ⋉ Dept)
  4. Provide an expression for a relation with Employee.Name, Employee.DeptName, and Dept.Manager attributes. If the employee has no manager, the Dept.Manager attribute will be NULL. Form the relation using a specific kind of join of the Employee and Dept relations on the DeptName attribute so that there is a tuple even if there is no matching Dept tuple for an Employee tuple.
  5. Provide an expression for a relation showing the names of employees who have a manager. Form the relation using a specific kind of join of the Employee and Dept relations on the DeptName attribute so the resulting relation has only employees that have managers. Note: a manager cannot manage his or herself.
  6. Provide an expression for a relation showing the names of departments that have no employees. Form the relation using a specific type of join of the Dept and Employee relations.
  7. Provide an expression for a relation showing the names of employees who are not managers. Hint: may require renaming an attribute.
  • DUE DATE: Mon May 20 by 11:59:59pm

Use the RelaX Relational Algebra Calculator with the "Wikipedia - Relational algebra" dataset to answer these questions. Create a text file "assignment-2.sql" with your solutions in the form of SQL statements in the SQL tab of calculator. Include a comment before each answer that identifies the question.

  1. This SQL statement uses a union of two select operations on the Employee relation. Rewrite this as a single select on the Employee relation with compound conditions. 
    (select * from Employee where DeptName = 'Sales') union (select * from Employee where DeptName = 'Executive')
  2. This SQL statement uses a cross join. Rewrite it using a natural join. 
    select * from Employee, Dept where Employee.DeptName = Dept.DeptName
  3. Provide a SQL statement for a relation with Employee.Name, Employee.DeptName, and Dept.Manager attributes. If the employee has no manager, the Dept.Manager attribute will be NULL. Form the relation using a specific kind of join of the Employee and Dept relations on the DeptName attribute so that there is a tuple even if there is no matching Dept tuple for an Employee tuple.
  4. Provide a SQL statement for a relation showing the names of employees who have a manager. Form the relation using a specific kind of join of the Employee and Dept relations on the DeptName attribute so the resulting relation has only employees that have managers. Note: a manager cannot manage his or herself.
  5. Provide a SQL statement for a relation showing the names of departments that have no employees. Form the relation using a specific type of operator on selections of Dept and Employee relations.
  6. Provide a SQL stetement for a relation showing the names of employees who have no manager. One approach is to solve it in terms of a relation showing names of employees who do have a manager.
  7. Provide a SQL statement for a relation showing the names of employees who are not managers.