Topic Name Description
Course Introduction Page Course Syllabus
Page Course Terms of Use
1.1: Characteristics of Databases File Adirenne Watt and Nelson Eng's Database Design: "Chapter 1: Before the Advent of Database Systems"

Read this chapter, which begins by discussing fundamental data concepts. The study of databases is an extension of the study of data in programming. The study of databases as a discipline was motivated by a variety of factors: the increasing size and complexity of software systems, the need to share data, and the need to be able to maintain and secure data effectively.

Data is as a collection of symbols used to represent numbers, text, pictures, videos, audio, and so on. How this data is represented gives the meaning (or semantics) for the symbols. Additional semantics are provided by the relationships that data has with other data. In programming, the semantics of data are provided by program documentation, as well as the programming language used to create the program. In databases, the semantics of the data is provided by a data model, which includes the representation of the data, relationships among the data, and metadata (which is data that defines other data).

Semantics makes data useful, and we define useful data as information. Sometimes, the terms 'data' and 'information' appear as synonyms. However, they are different: all information is data, but not all data is information. The type and amount of semantics determines the usefulness of some given data. Usefulness is also relative to a given user. Data may not be useful to some users, but very useful to others.

Identifying data and information and organizing them into a data model typically occurs in software requirements analysis and design. Current work in the field of databases addresses techniques that support building and storing data models, and using them to discover meaning (or information) in large volumes of data.

File Adirenne Watt and Nelson Eng's Database Design: "Chapter 2: Fundamental Concepts" and "Chapter 3: Characteristics and Benefits of a Database"

Read these chapters. Chapter 2 explores the fundamental concepts of databases and their properties. Chapter 3 discusses the characteristics of databases that give them their unique advantages. From a database development perspective, these characteristics are known as "requirements". Be sure to complete the short exercises at the end of each chapter.

File James F. Courtney and David B. Paradice's Database Systems for Management: "Chapter 1: Introduction to Database Systems"

Read this chapter, which begins with a description of database management systems (DBMS). DBMSes are software systems that enable and support the use of databases.

1.2: Database Environments Page The Computer Revolution: "Database Models"

Read this article on the types of databases and their functions. The article introduces a data model, which is the conceptual design for a database from a development perspective.

File Adirenne Watt and Nelson Eng's Database Design: "Chapter 4: Types of Data Models" and "Chapter 5: Data Modeling"

These chapters discuss the types of data models, their properties, and their levels of abstraction: external, conceptual, internal, and physical. We will later refer to these levels of abstraction as 'schema levels'.

File James F. Courtney and David B. Paradice's Database Systems for Management: "Chapter 2: Elements of Database Systems"

This chapter continues the top-down discussion on DBMSes. It describes the elements of a DBMS, functional requirements and necessary characteristics (which corresponds to the external view of the 3-level schema architecture perspective), design (which corresponds to the conceptual model view), and implementation (which corresponds to the internal/physical model view). It also gives an overview of database and DBMS processes, including planning, development techniques and methods, roles and responsibilities, stakeholders, and database and DBMS maintenance and configuration control (which is how to control changes to the database or DBMS).

1.3: Classifying Database Management Systems File Adirenne Watt and Nelson Eng's Database Design: "Chapter 6: Classification of Database Management Systems"

Read this chapter, which takes a bottom-up approach, going from data to databases to DBMSes. Previous chapters addressed data and databases, while this chapter discusses DBMSes and classifies them using several criteria.

Page O.S. Tezer's "Comparison Of NoSQL Database Management Systems And Models"

This course primarily covers traditional databases, but this article gives an overview of some non-traditional databases and classifies non-SQL databases according to their operational model. Non-SQL databases are schema-less, and not based on a single data model.

Page Dave Kellogg's "Classifying Database Management Systems: Regular and NoSQL"

Read this article on how to classify traditional and non-traditional databases based on internal and implementation models.

2.1: Databases and the Three-Schema Architecture Page Geographic Information Technology Training Alliance: "Three-Schema Architecture"

This article presents the three-schema architecture, which aims to provide data independence. The three schemas are external, conceptual, and internal. Each provides a level of independence: application independence from the external schema, external schema independence from the conceptual schema, conceptual schema independence from the internal schema, and internal schema independence and thus application independence from the physical data itself.

File James F. Courtney and David B. Paradice's Database Systems for Management: "Chapter 3: Data Modeling" and "Chapter 4: A Framework for Database System Design"

In chapter 3, the three ANSI/SPARC schemas are presented as phases of database development: the requirements phase, the high-level design phase, the detailed design phase, and the implementation phase. These correspond to the external model, the internal model, and the physical model, respectively. This chapter also describes entity-relationship modeling as a means of representing a conceptual model.

Chapter 4 discusses conceptual modeling for hierarchical, network, and relational databases.

2.2: Tables, Views, and Indexes File Adirenne Watt and Nelson Eng's Database Design: "Chapter 7: The Relational Data Model"

This chapter introduces the relational database model. Relational databases are one of the most commonly-used database models. The chapter includes a mathematical foundation for the model in relational algebra and relational calculus. These are also the basis of the standard database language SQL. Most current database systems support the relational model.

Page Alexander Halavais' "Relational Databases"

You have probably used spreadsheet software like Microsoft Excel or OpenOffice. If so, you have likely used tables, views, and indexes while manipulating your spreadsheet data. These are all concepts used in relational databases. This video explains the concepts of relational databases using Microsoft Excel. A "primary key" is a special type of index that is unique, and the main (or primary) value used to locate an entry in a database.

Page Mark Zellers' "Introduction to Relational Databases"

This lecture explains the basic concepts of relational databases, including tables, columns, primary keys, foreign keys, and referential integrity. It then illustrates them using a customer order database. The latter part of the lecture uses Microsoft Access to implement a relational database.

3.1: Before Computers Page Stephen Fortune's "A Brief History of Databases"

This article makes several key points, and notes how the evolution of database technology reflects the evolution of how we model the world around us and is driven by the need to address the complexities that result from a proliferation of data. The growing demand for data and better data accessibility has led to a surge in the amount and quality of data available to people and organizations, databases have become so common that organizations are structured to reflect the model of their data.

Page Carnegie Mellon University: Andy Pavlo's "History of Databases"

Watch this lecture, which gives an overview of the evolution of both traditional and non-traditional databases.

3.2: The Four Revelations Page Wikipedia: "Database Model"

This article defines database model and discusses many types. The first diagram depicts 5 categories of data models: flat files, early data models (network and tree), relational, and post-relational.

4.1: Why Use an E-R Model? Page Indian Institute of Technology, Bangalore: S. Srinath's "Conceptual Designs"

The lecture explains why entity-relationship (or E-R) models are used, and gives a high-level view of the development process for a database. It describes the database development process as logically parallel to the development process of an application, such as an information system, that uses a database. The development cycle for the application includes requirements analysis, design, and implementation. The development cycle for databases are similar, having a corresponding requirements analysis phase (called the conceptual schema) that is a technical specification of the database requirements. A popular representation of the conceptual schema is an E-R model.

4.2: E-R Elements and Symbols File Adirenne Watt and Nelson Eng's Database Design: "Chapter 8: The Entity Relationship Data Model"

This chapter describes an E-R model. An E-R model is conceptually similar to a relational model: E-R entities correspond to relational tables; entity attributes correspond to table columns. An E-R model can easily be translated to a relational model.

Page Wellesley College: Scott D. Anderson's "Entity-Relationships"

As you are studying databases, strive to read as many database examples as you can. This resource includes an example E-R model for a SQL movie database. Attempt the questions in the green boxes and at the "Quiz Questions on ER Diagrams" link.

Page Gerard Nico's "Data Modeling: Entity-Relationship (E-R)"

This short description of E-R models demonstrates them from the perspective of application types: OLTP (Online Transaction Processing) and DSS (Decision Support Systems). It also relates E-R notations to other notations.

File Massachusetts Institute of Technology: George Kocur's "Data Models"

Review these slides, which give an E-R example in the form of an aircraft database. The notes also give a list of rules for interpreting the model.

Page Brian Finnegan's "Introduction to the Entity-Relationship Model"

These short videos present an E-R model for a music database from the perspective of the database's requirements and design process.

4.3: The Enhanced E-R Model Page Lakshmi S's "The Enhanced Entity-Relationship Model"

For some complex applications, an E-R model may not be able to accurately represent the data requirements. To do so, extensions such as specialization, generalization, and inheritance have to be defined. The resulting models are called "enhanced E-R models".

Page University of Michigan: Scott Moore's "Advanced Topics in E-R Modeling"
This tutorial goes into some detail on the enhanced E-R modeling concepts that will be helpful when we transition to relational modeling.
5.1: The Relational Model at a Glance File Adirenne Watt and Nelson Eng's Database Design: "Chapter 7: The Relational Data Model"

Review this chapter, which we looked at previously in Unit 2. The relational model uses tables for relations, table columns for attributes, and table rows for records. Some references may refer to the product of data analysis as a logical model. Here we refer to the product of data analysis as a conceptual model and the product of database design as a logical model. In either case, the database design model addresses how data is used. It is more detailed than the data analysis model, which is concerned with the meaning of the data and its structure.

File James F. Courtney and David B. Paradice's Database Systems for Management: "Chapter 6: The Relational Database Model"

Read this chapter, which supplements the chapter you just reviewed.

5.2: Mapping an E-R Diagram to a Relation Page Indian Institute of Technology, Bangalore: S. Srinath's "ER Model to Relational Mapping"

For software development, it is generally desirable to use methods and models for each phase that are compatible, which eases the transition from one phase to another. So too with databases. This video maps an E-R model to a relational model that supports the requirements analysis and design of a database, and covers the transition between the two.

File Adirenne Watt and Nelson Eng's Database Design: "Chapter 9: Integrity Rules and Constraints"

Read this chapter, which presents constraints on attributes or tables. A constraint is a rule for incorporating semantics of the application data into the relational model. There are constraints for database integrity, application semantics, business rules, 

6.1: Basic Operations URL Gordon Russell's "Relational Algebra"

Read this section.

URL Gordon Russell's "Relational Algebra Example"

Read this section.

6.2: Derived Operations URL Marquette University: Charles Corliss' "Relational Algebra and the Relational Calculus"

Read this page.

Page University of Houston at Clear Lake: Gary Boetticher's "Relational Algebra: Set Operations"

Watch this lecture.

6.3: Set Operations Page Stanford University: "Table Variables and Set Operators"

Watch this lecture.

6.4: Aggregate Functions URL City University of New York: Richard Holowczak's "Aggregate Functions”

Read the "Aggregate Functions" and "Aggregate Functions Examples" sections.

Unit 7: Intro to Data Normalization Page Unit 7 Learning Outcomes
7.1: Normalization Defined URL Gordon Russell's "Normalization"

Read this chapter.

Page WingsLive: "What is Database Normalization?"

Watch this video.

Page Art Langer's "Logic Data Modeling"

Watch these videos.

7.2: Anomalies URL Database Management: "Data Anomalies"

Read this article.

7.3: Functional Dependencies Page Stanford University: "Functional Dependencies"

Watch this lecture.

7.4: From 1NF to BCNF URL Gordon Russell's "Normalisation - BCNF"

Read this chapter.

URL Gordon Russell's "Normalization Quiz"

Answer questions 1-14.

Page Art Langer's "Normal Form"

Watch these videos.

Unit 8: Introduction to SQL Page Unit 8 Learning Outcomes
8.1: Overview Page Stanford University: "Introduction to SQL"

Watch this lecture.

8.1.1: What Is SQL? URL W3Schools: "Introduction to SQL"

Read this page for an introduction to the definition and function of Structured Query Language (SQL).

URL Vrije Universiteit Brussel: Beat Signer's "Introduction to Databases"

Read these slides.

8.1.2: SQL Syntax URL W3Schools: "SQL Syntax"

Read this page.

8.2: Data Definition Language (DDL) Commands Page ProgrammingVideos: "Database, Table, Index"

Watch this video.

8.2.1: Create Table URL SQL Tutorial: "SQL CREATE TABLE"

Read this tutorial to learn how to use SQL to create a database table.

8.2.2: Drop Table URL SQL Tutorial: "SQL DROP TABLE"

Read this tutorial to learn how to use SQL to delete data from a table and to remove the structure of the table.

Page ProgrammingVideos: "Alter Drop"

Watch this video.

8.2.3: Alter Table URL SQL Tutorial: "SQL ALTER TABLE"

Read this tutorial to learn how to use SQL to modify a database table.

8.3.1: Not Null URL W3Schools: "SQL NOT NULL Constraint"

Read this page.

8.3.2: Unique URL W3Schools: "SQL UNIQUE Constraint"

Read this page.

8.3.3: Primary Key URL W3Schools: "SQL PRIMARY KEY Constraint"

Read this page.

8.3.4: Foreign Key URL W3Schools: "SQL FOREIGN KEY Constraint"

Read this page.

8.3.5: Default URL W3Schools: "SQL DEFAULT Constraint"

Read this page.

8.3.6: Check URL W3Schools: "SQL CHECK Constraint"

Read this page.

8.4.1: Insert Operator and Command Syntax URL W3Schools: "SQL INSERT INTO Statement"

Read this page.

Page Stanford University: "Data Modification Statements"

Watch this video.

Page ProgrammingVideos: "Insert, Update, Delete"

Watch this video.

8.4.2: Update Operator and Command Syntax URL W3Schools: "SQL UPDATE Statement"

Read this page.

8.4.3: Delete Operator and Command Syntax URL W3Schools: "SQL DELETE Statement"

Read this page.

8.4.4: Select Operator and Command Syntax URL W3Schools: "SQL SELECT Statement"

Read this page.

Unit 9: Basic Select Statements Page Unit 9 Learning Outcomes
9.1: The Distinct Clause Page Stanford University: "SQL: Basic Select Statement"

Watch this video.

URL W3Schools: "SQL SELECT DISTINCT Statement"

Read this page.

9.2: The Where Clause URL W3Schools: "SQL WHERE Clause"

Read this page.

9.3: And/Or Operators URL's "SQL AND and OR Operators”

Read this page.

9.4: SQL Aggregate Functions URL W3Schools: "SQL Functions"

Read this page.

9.5.1: Like URL W3Schools: "SQL LIKE Operator"

Read this page.

9.5.2: Between URL W3Schools: "SQL BETWEEN Operator"

Read this page.

9.5.3: In URL W3Schools: "SQL IN Operator"

Read this page.

9.5.4: Wildcards URL W3Schools: "SQL Wildcards"

Read this page.

9.5.5: Top URL W3Schools: "SQL SELECT TOP Clause"

Read this page.

9.6: The Order by Clause URL W3Schools: "SQL ORDER BY Keyword"

Read this page.

Unit 10: The Join Statement Page Unit 10 Learning Outcomes
10.1: Introduction Page Michael Fudge's "Understanding the JOIN Clause in the SELECT Statement

Watch this video.

URL W3Schools: "SQL Joins"

Read this page.

10.2: Inner Joins URL W3Schools: "SQL INNER JOIN Keyword"

Read this page.

10.3.1: Left Outer Joins URL W3Schools: "SQL LEFT JOIN Keyword"

Read this page.

10.3.2: Right Outer Joins URL W3Schools: "SQL RIGHT JOIN Keyword"

Read this page.

10.3.3: Full Outer Joins URL W3Schools: "SQL FULL JOIN Keyword"

Read this page.

10.4: The Group By Clause Page Stanford University: "Aggregation"

Watch this video.

URL W3Schools: "SQL GROUP BY Statement"

Read this page.

10.5: Assessment URL W3Schools: "SQL Quiz"

Take this quiz to assess your understanding of the materials presented in this unit.

Optional Course Evaluation Survey URL Optional Course Evaluation Survey

Please take a few moments to provide some feedback about this course. Consider completing the survey whether you have completed the course, you are nearly at that point, or you have just come to study one unit or a few units of this course.

Your feedback will focus our efforts to continually improve our course design, content, technology, and general ease-of-use. Additionally, your input will be considered alongside our consulting professors' evaluation of the course during its next round of peer review. As always, please report urgent course experience concerns to [email protected] and/or our discussion forums.