Educational Materials
OpenLine Consulting provides two comprehensive educational slide sets on relational databases, licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. See below for information on using and licensing this material. The slide sets cover:
- Theory, Practice & Methodology of Relational Database Design and Programming: This is an extensive (~3000 slides) and complete set of PowerPoint slides for an introductory Oracle-centric database course. A detailed list of the features of the slide set can be found below. They were last revised in June 2008. A number of supplementary materials are also available for a fee
- Advanced Database Topics: This is a complete set of PowerPoint slides for an advanced DB course. It includes slides with in-class exercises and separately marked solution slides (which can be easily removed if you make the slides available to students before class). They were last revised in Summer 2005.
All of this material has been thoroughly tested in Computer Science graduate courses at Boston University's Metropolitan College. For more information, see Dr. Cohen's BU home page.
Features of the Introductory DB Course Slides
The slides for the introductory database course are distinguished by the following features:
- There are a very large set of slides (~3000) with many example and diagrams.
- Extensive detailed coverage of many database design and SQL issues. The slides illuminate key points for many students who otherwise have trouble with this material. They also cover material which advanced students find interesting and challenging.
- Numerous slides are included which contain in-class exercises along with specially marked solution slides (which can be easily removed if you make the slide sets available to students before class).
- Includes early lectures on database application design, which provide context for the early course material. In particular, it introduces the concepts of user roles, operations, pre-conditions, and post-conditions.
- The material on SQL is divided up into distinct lectures (Basic SQL, Grouping, Natural Joins, etc.) and spread throughout the first 1/2 of the lecture set, so that students can deepen their understanding and gain mastery over each major SQL area.
- Conceptual Modeling and Relational Mapping are woven together in an incremental, integrated manner. Early lectures discuss modeling and mapping simple 1:M relationships, and their use in the design of moderately sized systems. Later lecures introduce Dependent and Identifying Relationships (i.e. weak entity classes), Composite Attributes, M:N Relationships, Multivalued Attributes, Factored Relationships (a generalization of UML containment), N-ary Relationships, 1-1 Relationships, Subclasses, and Union Subclasses and Disjoint 1:M Relationship, each with a discussion of various relational mapping alternatives and issues.
- Conceptual Modeling is presented using UML, Chen, and the visual ER language Crow Magnum™ as well as the textual ER language ConText:
- Crow Magnum™ is a significant extension of the Crow's Foot notation, designed explicitly for agile database design. It also contains a number of interesting features which illuminate the conceptual underpinnings of complex design choices, including union subclasses, disjoint 1:M relationships, relationship classes, containment, factored relationships, and visual distinctions between dependent relationships (with lifetime dependencies) and identifying relationships (which additionally provide [part of] the primary key).
- ConText, a new textual ER language, was designed in concert with Crow Magnum™. Pragmatically, ConText is meant to support straightforward textual communication of ER designs. Pedagogically, it provides an opportunity for students to understand the relationship between textual and visual languages. ConText constructs are introduced along with the corresponding Crow Magnum™ constructs.
- Redundancy and simple functional dependencies are introduced very early, in an initial lecture on Conceptual Database Design, treating normalization of simple transitive dependencies at the conceptual level as a very practical way to understand or check conceptual level designs (even before introducing relational mapping or the relational model). The more comprehensive material on functional and multi-valued dependencies and relational normalization comes much later, after students are reasonably adept at conceptual design and relational mapping. For each normal form (though the 5th), we also show the corresponding changes that would be made at the conceptual level, which is remarkably illuminating. Rather than formally discussing DKNF, we discuss and illustrate the general principle: that the goal of decomposition is to allow the database to directly enforce constraints which are hard-to-enforce without decomposition.
- The Relational Model is not introduced until after 1:M relationships, when students already have some experience with conceptual design. Relational Modeling is taught using Extended Relational Schema Diagrams (with visual representations for non-null and unique constraint as well as primary key and foreign key constaints, including representation of cascading deletes) along with SQL.
- An entire lecture describes a foundation and methodology for Relational Mapping -- mapping conceptual to relational designs, which could be supported by automatic or semi-automatic relational mapping tools.
- The lecture set has a very strong focus on specification and enforcement of constraints representing business rules.
- The early introductory lecture on database applications describes pre-conditions and post-conditions, and a later lecture on database applications shows how these can be generalized to access constraints and transition constraints, respectively.
- Even before presenting the relational model, an entire lecture discusses conceptual state constraints, invariants which are informally described based on the conceptual model.
- Immediately after introducing the relational model, a series of three lectures discuss relational state constraints, relational state assertions (relational state constraints formally described using boolean SQL expressions), and approaches for mapping conceptual to relational state constraints. Relational asssertions continue to be addressed as part of the introduction of each new SQL feature.
- Many of the later lectures focus on specification and enforcement (both application-based and database-based) of constraints and assertions. Five lectures focus on enforcement (both application-based and using triggers) of state and transition constraints, and three on enforcement of access-constraints (both application-based and using privileges and roles).
- An extension to subqueries (using EACH, SOME and ONE) are introduced which are based on the predicate calculus, and which can be easily mapped into standard SQL. The extensions signficantly simplify the specification of complex state assertions. In addition to being useful in their own right, these extensions lead naturally and effortlessly into a discussion of the relational calculus.
- Privileges and roles are discussed in terms of how they can be used to enforce access constraints. We discuss a variety of ways to enforce access constraints, from the pure application-based approach (used by many web applications these days), to an approach where each application user is connected as a separate database user. The second approach requires that most application code be moved to the data tier, and while more secure, requires that each application user be a separate database user. We also describe an alternative with almost the same level of security, but requiring only two database "users".
- Detailed lectures on the Relational Calculus, the Relational Algebra (using the language REAL), and on Normalization follow the lectures on SQL and Database Design. Experience shows this is the earliest point at which this material can be appreciated and effectively integrated by the majority of students.
- Programming of user operations and stored procedures is taught entirely using basic Oracle PL/SQL, with some comparisons to Java. Use of complex data structures is deferred until a much later lecture; earlier application implementation lectures use temporary tables for simplicity. Separate lectures discusses issues of session state and data communication between the data tier and the middle tier.
- A lecture on transactions focuses mostly on pragmatic issues having to do with atomicity and isolation, including addressing how to ensure serializability in Oracle (whose SERIALIZABLE isolation level isn't). Much more comprehensive material on transactions can be found in the lectures on Advanced Database Topics, described above.
- Indexing and peformance issues are introduced very early, right along with table creation and the SELECT statement. Two more detailed lectures on physical design and optimization provide a more detailed analysis of memory and disk-based database access along with pragmatic query performance issues. It precedes the material on implementation of constraints, which raises many of these performance issues.
Usage and Licensing
These slides are licensed using a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. This allows you to use and to modify the material for non-commercial purposes, with certain restrictions. However, teaching a course for which students pay or for which you are paid is a commercial purpose.
To use the material for commercial purposes, including for use in college and university courses, you MUST obtain our permission.
OpenLine Consulting is happy to grant a free and renewable license for one year for use of the material in classroom (non-online) courses at accredited colleges and universities. Send requests to requests@openlineconsult.com with
- your name, position, and the URL of your home page
- the name of the college or university
- the name of the department (offering the course) and its URL
- the course name and number, when it will be offered, and its URL
- your declaration that this is a classroom course (i.e. not taught online)
- the expected number of students
Your requests will help us keep track of where the material is being used.
For other uses, please contact us at requests@openlineconsult.com to work out details of compensation and terms of use.
In addition to the general attribution requirements spelled out in the Creative Commons license, use the following guidelines if you use portions of the slides in a slide set that you generate.
- If more than 10 slides, or more than 20% of the slides in a slide set you generate are based on these slides, an attribution with Dr. Cohen's name must appear on the header slide. If more than 50% of the slides in the slide set are based on these slides, then the attribution must indicate that fact, and the attribution must be prominent.
- Slides which you have included, and which are unchanged, must include the unchanged copyright notice at the bottom of the slide
- If you have included a slide, but modified it, you must indicate that it has been adapted from these slides with attribution.
A number of supplementary materials are available separately. Contact us for payment and terms of use.
Please let us know about any errors you find in the material, as well other feedback, suggestions and recommendations. We are especially grateful for specific revisions and any additional material you think would be useful to include, which will be appropriately attributed.