Courses/Computer Science/CPSC 203/CPSC 203 2007Summer L60/CPSC 203 2007Summer L60 Lectures/Lecture 4

Lecture 4

• Introduction to databases - text ch. 11
• If then statements: =IF(logical_test,value_if_true,value_if_false)

Logical_test: is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE

• Absolute reference:

An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

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": http://en.wikipedia.org/wiki/Venn_diagram
• Table - A row (case) by column (variable) display. An entity that has a group of related records
  • Parent: A Parent-Table or Entity has it's primary key linked to one to many foreign keys in the Child table.
  • Child: A Chuild-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 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: http://en.wikipedia.org/wiki/Referential_Integrity

Lecture Resources

• 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.