ISTM 315 Exam 2

SQL

Structured Query Language. The language of relational database systems; if you're interacting with an RDBMS, you're using SQL.

RDBMS

Relational database management system. Manages data as a collection of tables, with all relationships represented by common values in related tables.

How relationships are represented in an RDBMS

As equalities between a primary key in one table and a foreign key in another.

SQL and non-relational systems

Languages for non-relational DBMSs (e.g., Hive, Pig) still build on SQL as a foundation, so SQL knowledge carries over.

Schema

The structure that contains descriptions of user-created objects such as base tables, views, and constraints. Essentially everything you think of as "the database."

CREATE SCHEMA vs CREATE DATABASE (MySQL)

Treated as synonyms; same effect.

Schemas per DBMS

A single DBMS can manage several schemas at once. MySQL Workbench's schema navigator (left panel) lists every database you can access.

Three main segments of SQL

DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language).

DDL (Data Definition Language)

Commands that define/build a database: create, alter, and drop tables, and establish constraints on and between them.

DML (Data Manipulation Language)

Commands that maintain and query a database: INSERT, UPDATE, DELETE, SELECT. Most course time is spent here.

DCL (Data Control Language)

Commands that control the database, including administering privileges and committing data. The DBA's realm; mostly out of scope for this course.

DBA

Database administrator. Responsible for the data itself and for who gets access to it.

CREATE statement

First major DDL statement. Creates objects in a DBMS: a schema/database, a table, a view, etc.

CREATE SCHEMA / CREATE DATABASE

Defines a portion of the database owned by a particular user; the home where tables and their relationships live.

CREATE SCHEMA syntax

CREATE SCHEMA customerdb; (Refresh the schema navigator to see it. It is empty until you add tables.)

Class convention: CREATE SCHEMA or CREATE DATABASE?

Use CREATE SCHEMA.

CREATE TABLE

Defines a new table and its columns.

CREATE VIEW

Defines a logical table from one or more tables or views, or from a SQL query. Not covered in class because views work so much like tables.

Other CREATE statements (not used in class)

CHARACTER SET, COLLATION, TRANSLATION, ASSERTION, DOMAIN.

Steps in creating a table (in order)

1) Identify data types for attributes. 2) Identify columns that can/cannot be null. 3) Identify PK/FK mates. 4) Determine default values. 5) Identify constraints on columns (domain specs, allowable values). 6) Create the table and associated indexes.

Required vs optional fields: default stance

Require most information by default. Making a field optional needs a business reason.

Where PK/FK mates come from

Logical modeling, specifically the relational schema.

Default values: best practice

Use sparingly, only when there's a specific need. Defaults can go wrong.

Good default value example

Order date set to the system date/time when the transaction is recorded.

Column constraints

Domain specifications and restrictions on a column's allowable values.

Six data types for class

BOOLEAN, DATETIME, DECIMAL, INTEGER, CHAR, VARCHAR. Covers most business needs.

Data type decision tree (in order)

1) True/false or yes/no? BOOLEAN. 2) Date or time? DATETIME. 3) Doing math? Needs decimal places: DECIMAL; otherwise INTEGER. 4) Not doing math? Fixed length: CHAR; variable length: VARCHAR.

BOOLEAN

For true/false or yes/no data, stored as 0 or 1. Some systems call it BOOL or TINYINT; same idea.

DATETIME

For anything temporal. Stores date and time together as one value. Exists on almost every DBMS.

DECIMAL

For numbers used in math that need decimal places. Stores exact numbers with specified precision. Fixed-point, not floating-point; the most precise decimal type.

INTEGER

For numbers used in math that don't need decimal places. Exact numbers with predefined precision and a scale of 0.

CHAR

Fixed-length string; can't be bigger or smaller than the declared size. Example: US state code as CHAR(2).

VARCHAR

Variable-length string ("varying character"). Declared size is a maximum; unused space is freed, so memory is allocated efficiently.

Number-like data you don't do math with

Store as a string type (CHAR/VARCHAR), not numeric. Examples: phone numbers, zip codes.

Phone number data type

US only (always 10 digits): CHAR. International (lengths vary): VARCHAR.

Zip code data type

CHAR (5 or 9, depending on what you store). You never average zip codes.

Salary data type

DECIMAL or INTEGER, because you do math with it.

Sizing VARCHAR

Be generous (e.g., 50 for last name) so you never lose or truncate data. Unused space is freed.

Why not make everything VARCHAR(2000)?

1) You'll hit DBMS limits; the DBMS must guarantee it can store the declared size. 2) It's lazy. Ballpark a size and err large.

CREATE TABLE syntax

CREATE TABLE customer ( field definitions, constraints ); Open parentheses after the table name, list field definitions, then constraints, then close.

CREATE TABLE formatting

Technically one statement that could be one line. Put each field definition and constraint on its own line for readability and debugging.

Field definition format

field_name DATA_TYPE NOT NULL e.g., customer_id INTEGER NOT NULL

Why IDs are INTEGER even though you don't do math on them

The DBMS may do math on them: auto increment adds 1 to the prior ID.

NOT NULL

Makes a field required. Leave it off only with a business reason (e.g., middle name, since not everyone has one).

Size on INTEGER

Don't specify one. INTEGER(4) is accepted but imposes no limit beyond the integer type's own size.

VARCHAR size in CREATE TABLE

A maximum. VARCHAR(35) accepts up to 35 characters and frees whatever isn't used.

CHAR size in CREATE TABLE

Exact. CHAR(2) requires exactly 2 characters.

Commas in CREATE TABLE

Every field definition ends with a comma, including the last one, since it must be separated from the constraints.

Primary key constraint syntax

CONSTRAINT customer_pk PRIMARY KEY (customer_id)

Constraint naming convention

tablename_constrainttype, e.g., customer_pk, orders_fk. Makes the system self-documenting.