Multiple table CRUD
Database schema before class: restaurant_inspections.sql
Schema used in book
Slides from class
SQL script from class
Run the following script before next class to create a new schema: day4_create_nyc_inspections_schema.sql
Database notes
CREATE TABLE
SQL command that creates a table's structure using the attribute names and domains in the command. Can be used with a nested SELECT query to load data into table after the table is created. Example: CREATE TABLE (ID INT, UserName VARCHAR(20));
DROP TABLE
SQL command that deletes a table, views, and indexes. Example: DROP TABLE instructor;
TRUNCATE TABLE
SQL command that deletes all rows within a table, but leaves the table's schema inplace. Example: TRUNCATE TABLE instructor;
INSERT
SQL command that adds more or more rows to a table. Can use a subquery to load rows from another table. Example: INSERT INTO instructor (ID, `name`, dept_name, salary) VALUES ('100001','Pierson','Comp. Sci.',0);
UPDATE
SQL command that changes values in one or more rows. Example: UPDATE instructor SET salary = 100 where ID = '100001';
DELETE
SQL command that removes one or more rows from a table. Example: DELETE FROM instructor WHERE ID = '100001';
key
One or more attributes that determine another attribute.
composite key
A key comprised of multiple attributes.
superkey
One or more attributes that uniquely identify each entity in a table.
candidate key
A minimal superkey; that is a key hat does not contain a subset of attributes that is itself a superkey.
primary key
A candidate key selected as a unique entity identifier.
foreign key
One or more attributes in a table whose values match the primary key of another table or whose value is null.
AUTO INCREMENT
A characteristic of an attribute used to uniquely identify rows. Normally starts at 1 and increments by 1 each time a row is inserted.
constraint
A restriction placed on data, usually express in the form of rules. For example, GPA must be between 0 and 4.0.
referential integrity
A condition by which a table's foreign key must have either a null entry or a matching entry in the related table.
Cartesian product
The product of two sets A and B, denoted A × B, is the set of all ordered pairs (a, b) where a is in A and b is in B.
JOIN
In relational algebra, a type of operator used to yield rows from two tables based on criteria provided.