- Course Introduction
Though we may not recognize them in our everyday activities, databases are everywhere. They are hidden behind your online banking profile, airline reservation systems, medical records, and even employment records. This course will provide students with a general overview of databases, introducing you to database history, modern database systems, the different models used to design a database, and Structured Query Language (SQL), which is the standard language used to access and manipulate databases. Many of the principles of database systems carry to other areas in computer science, especially operating systems. Databases are often thought of as one of the core computer science topics, since many other areas in the discipline have been derived from this area.
- Unit 1: Introduction to Modern Database Systems
Different databases serve different purposes; each one is dependent upon both deployment environment and different types of user interactions. In this unit, we will ask a number of questions pertaining to databases: What are some database environments and user types? How can the database management system ensure control over data integrity, avoid data redundancy, and secure data, while at the same allowing interactions with different user types? In answering these questions, we will identify and determine the characteristics of databases, their many deployment environments, and the different categories of users that interact with it.
Completing this unit should take you approximately 3 hours.
- 1.1: Databases: Definition and Characteristics
- 1.2: Database Usage and Environment
- 1.2.1: Data and Databases
- 1.2.2: The Integrated Database Environment
- 1.3: Classification of Database Management Systems
- Unit 2: Database Architecture and Date Languages
In order to properly create and then manage a database, we need to have a thorough understanding of the data it holds. Because data can be seen from different levels, we will introduce different data models and learn how to apply them in order to describe the structure of the database, thereby providing a "view" of the database for the different types of users introduced in the previous section.
Completing this unit should take you approximately 2 hours.
- 2.1: Database and Three-Schema Architecture
- 2.2: Tables, Views, and Indexes
- Unit 3: Database History
Databases have existed for centuries: the maintenance of records and data has evolved from engravings to cards to digital storage. In this unit, we will present the four different models of representing data, discussing the different limits of each.
Completing this unit should take you approximately 1 hour.
- 3.1: Once Upon a Time, before Computers Existed
- 3.2: The Four Revelations
- Unit 4: The Entity Relationship Model
Databases often hold a great amount of data. In order to build a database, we need to understand which entities should hold data and identify the connections that may exist between entities. In this unit, we will learn about the Entity-Relationship model, which will allow us to create a graphical view of the different elements of a database as well as the relationships between them. We will also learn the drawing conventions of the E-R model using a part-to-whole approach, beginning with those conventions used to represent a single entity, and concluding with conventions used to represent all relations in a database.
Completing this unit should take you approximately 9 hours.
- 4.1: Why Use an E-R Model
- 4.2: Elements in the Model and Respective Symbols
- 4.3: The Enhanced ER Model
- Unit 5: The Relational Database Model
The relational database model provides us with a way to understand how data can be perceived. While the E-R model represents the relations between elements of a database, it does not provide a conceptual view of its data. We will use the relational model to solve that problem. The relational model looks at entities as tables and allows operations to be performed on them. In this unit, we will learn how to map ER models into relations.
Completing this unit should take you approximately 12 hours.
- 5.1: The Relational Model at a Glance
- 5.2: Mapping an ER Diagram to a Relation
- Unit 6: Relational Algebra
We have seen that database entities can be viewed as logical tables. While this is useful in its own way, we can learn more from the data if we can perform operations on the tables within a database, as data from one table may not be meaningful without the data from another table. In this unit, we will introduce relation algebra, the mathematical notation used to represent how data retrievals and updates are performed on tables in a database. Understanding relational algebra will serve as a prelude to using the Structure Query Language (SQL).
Completing this unit should take you approximately 5 hours.
- 6.1: Basic Operations
- 6.2: Derived Operations
- 6.3: Set Operations
- 6.4: Aggregate Functions
- 6.5: Assessment
- Unit 7: Intro to Data Normalization
In this course, we have learned that entities in a database can be thought of as logical tables. We will now learn that data in a table must be stored in a normalized way. We will first identify the properties of a normalized table, learning about the process of normalization and its importance to the structure of a database. We will then study the four major steps of normalization and discuss the database anomalies that can result in the absence of normalization.
Completing this unit should take you approximately 17 hours.
- 7.1: Normalization Defined
- 7.2: Anomalies
- 7.3: Functional Dependencies
- 7.4: From 1NF to BCNF
- Unit 8: Introduction to SQL
Structured Query Language (SQL) is the main data definition language used for the creation and maintenance of databases. In this unit, we will look at basic SQL syntax, including some data definition and data manipulation language commands.
Completing this unit should take you approximately 14 hours.
- 8.1: Overview
- 8.1.1: What Is SQL?
- 8.1.2: SQL Syntax
- 8.2: Data Definition Language (DDL) Commands
- 8.2.1: Create Table
- 8.2.2: Drop Table
- 8.2.3: Alter Table
- 8.3: Column and Table Level Constraints
- 8.3.1: Not Null
- 8.3.2: Unique
- 8.3.3: Primary Key
- 8.3.4: Foreign Key
- 8.3.5: Default
- 8.3.6: Check
- 8.4: Data Manipulation Language (DML) Commands
- 8.4.1: Insert Operator and Command Syntax
- 8.4.2: Update Operator and Command Syntax
- 8.4.3: Delete Operator and Command Syntax
- 8.4.4: Select Operator and Command Syntax
- Unit 9: Basic Select Statements
In Unit 8, we introduced the select statement. In this unit, we will learn to build queries using one table. We will take a look at the basic syntax of the select statement along with some basic expressions and the where clause.
Completing this unit should take you approximately 14 hours.
- 9.1: The Distinct Clause
- 9.2: The Where Clause
- 9.3: And/Or Operators
- 9.4: SQL Aggregate Functions
- 9.5: Special Operators
- 9.5.1: Like
- 9.5.2: Between
- 9.5.3: In
- 9.5.4: Wildcards
- 9.5.5: Top
- 9.6: The Order by Clause
- Unit 10: The Join Statement
Programmers frequently join data from a number of different tables in order to obtain more information. They also--perhaps even more frequently--build queries to obtain information from more than one table in order to generate better information. In this section, we will learn about SQL Joins, which allow us to create complex queries, combine data from different tables, and obtain a new result set that can provide us with a better understanding of the data and maximize database flexibility.
Completing this unit should take you approximately 23 hours.
- 10.1: Introduction
- 10.2: Inner Joins
- 10.3: Outer Joins
- 10.3.1: Left Outer Joins
- 10.3.2: Right Outer Joins
- 10.3.3: Full Outer Joins
- 10.4: The Group By Clause
- 10.5: Assessment