Courses/Computer Science/CPSC 203/CPSC 203 2007Fall L04/CPSC 203 2007Fall L04 Lectures/Lecture 9

Jump to: navigation, search

Lecture 9

The purpose of this lecture is to introduce you to the relational database model in a visual fashion. We will cover the Relational Database MetaModel and the concepts you need to understand it, particularly the notion of sets and relationships.

The objectives of today's class are:

  • House Keeping
    • Text Reading -- Chapter 11
    • Labs Update
    • Mid Term Questions
    • Quick Lookup, If ... Then and Pivot Tables example.
  • Topics
    • Relational DB Origins
    • Relational DB Meta Model
    • Set and Relations concepts needed to understand the meta-model
    • The Relational DB Meta Model is "tables all the way down".

Lecture Glossary

  • Set - A set is a collection of unique objects. In databases, everything is in sets and subsets of information. Sets, Subsets, Intersection sets and Union sets were introduced in class via "Venn Diagrams":
  • Table - A row (case) by column (variable) display. An entity that has a group of related records. In Relational Databases, a table if often called a "Relation". It is also called an "Entity".
    • Parent: A Parent-Table or Entity has it's primary key linked to one to many foreign keys in the Child table.
    • Child: A Child-Table or Entity has a foreign key(s) that link it back to one or more Parent Tables. The child tables foreign keys are primary keys in the parent tables.
  • Query – A way of asking questions of a database. In general a query of a table(s) returns a subset of the table(s) for which the query parameters are true. For example, which people in the class listed Google as their most frequently visited website. More formally, a query is a proposition about data extracting a subset of information based on specific requirements using the primary and foreign keys. A query language is used to set up these requests and the query can be repeated at different times for updated information
  • Domain – “Data Type” = accepted values and operations. A set of values of a specific type with allowable operations that can apply to many attributes. Every field or column must be assigned a data type which is a domain (with specific rules) such as:
    • Text
    • Numeric
    • Integer
    • Date
    • Hyperlink
  • Attribute – a feature of an entity (object, variables)
  • Entity – an object in the world, which can have many relationships with other entities
  • Relationship – Intersection set of keys for 2 tables. A link between two entities.
  • Join – the relations between entities or Parent Table (on primary key) and Child Table (on foreign key)
    • One-to-One: Ex: a student can only borrow one book
    • One-to-Many: Ex a student can take many courses
    • Many-to-Many: Ex: many students can take many courses. Joins can break down into multiple One-to-Many joins.
  • Key – a unique identifier: data value that makes the row of information completely different from any other row
    • Primary: primary unique id of an instance (case)
    • Foreign: key linked to Primary key of parent table
  • Index - A look up table for a column with repeating values that makes queries on this column more efficient.
  • Referential Integrity -- Put simply, the restriction that no entry in a child table can be made unless the foreign key for that entry matches an existing primary key in the parent table(s). See Wikipedia:
  • Entity Relationship Diagram -- A diagramatic summary of the tables in a database and their relationships.

Lecture Concepts

We will cover the following concepts, largely using visual models.

  • A bit of History and Motivation
    • How did the Relational DB model come about
    • Why did it "win" over other models.
  • The Relational Database Meta-Model (as a dots and Edges diagram).
  • Sets and Relations -- the basic idea behind relational databases.
    • Forest Example
    • Ancestry Example
  • Return to the Relational Database Meta-Model as a set of tables.


TIA 3rd Edn: Chapter 11. 462 -- 503

TIA 4th Edn: Chapter 11. pp 484 -- 525


E.F. Codd's original proposal for the Relational Data Model can be found online at: "A Relational Model of Data For Large Shared Data Banks" created the theory behind the modern databases used in large organizations today. While technical -- the introductory parts of the article are accessible to the general lay reader and provide a good introduction to the thinking style needed to "Grok" databases.

  • The Database Relational Model. A Retrospective Review and Analysis. 2001. By C.J. Date
  • Practical Issue in Database Management -- A Reference for the thinking Practioner.2000. By Fabian Pascal
  • The Essence of SQL. A Guide to Learning the Most SQL in the Least Amount of Time. 1996. By David Rozenshtein
  • SQL Visual Quickstart Guide. 2005. By Chris Fehily.