Intro to the relational model
Database schema: restaurant_inspections.sql
Slides from class
SQL script from class (posted after class)
Database notes
database
A shared, integrated computer structure that houses a collection of related data. A database contains two types of data: raw facts (stored in relations or tables) and metadata about those facts.
relation (or table)
A logical construct perceived to be a two-dimensional structure composed of intersecting rows and columns that represents an entity set in the relational model.
entity
A person, place, thing, concept, or event for which data can be stored.
entity set
A collection of entities.
attribute
A characteristic of an entity. An attribute has a name and a data type.
atomic attribute
An attribute that cannot be further subdivided to produce meaningful components (e.g., a person's last name is atomic, a person's full name is not atomic).
attribute domain
Set of possible values an attribute can take on.
NULL
The absence of a value/value is unknown. Not a blank!
schema (logical schema)
A logical grouping of database objects. Like a blueprint of how data is arranged. Logical schema is independent of how the data is actually stored on disk; abstracts details of how data is stored from user.
relational algebra
A set of mathematical principles that form the basis for manipulating relational table contents. We will focus on SELECT, PROJECT, JOIN, and UNION.
SELECT
In relational algebra, an operator used to select a subset of rows (sometimes called a RESTRICT) from a relation (table). Also a SQL command that returns all rows or a subset of rows in a table. SQL command normally has the form: SELECT A1,A2,...An FROM r1,r2,...rn, WHERE p where A1...An are attributes (columns) or relations r1...rn, and p is a condition that rows must meet to be returned.
PROJECT
In relational algebra, an operator used to select a subset of attributes (columns) from a relation (table).
FROM
A SQL clause that specifies one or more tables from which to retrieve data.
WHERE
A SQL clause that adds conditional restrictions to a SELECT statement, limiting the number of rows returned to those that meet a specified condition (e.g., WHERE sales > 100).
LIKE
A SQL comparison operator used to check if an attribute's text matches a specified string pattern.
AS
SQL keyword that allows renaming (e.g., SELECT CAMIS AS RestaurantID FROM table, CAMIS column will now be called RestaurantID).
NYC Open Data
Data provided by the city of New York (for free!) about city government operations such as restaurant health inspections. Other organizations (not just city governments) also provide this type of free data.