1. SQL Data Manipulation Language (DML)
1.1. INSERT
1.1.1. INSERT VALUES (insert one row at a time)
1.1.2. delete restricted - eg. Artist cannot be deleted if there is work for that person
1.1.3. insert: default policy -eg. on insert of artist, a default policy is to be used for finding an artist
1.1.4. Other version accepts SELECT statements to copy data in from other tables
1.2. UPDATE
1.2.1. similar to INSERT bulk
1.2.2. us with WHERE clause to specificy which rows
1.2.3. UPDATE MyTable SET (col1 = val1, col2 = val2, etc) WHERE this is true
1.3. DELETE
1.3.1. DELETE FROM MyTable WHERE this is true
2. SQL Data Definition Language (DDL)
2.1. CREATE TABLE
2.1.1. column name
2.1.2. data type
2.1.2.1. INTEGER
2.1.2.2. SMAILLINT
2.1.2.3. Decimal(p,q)
2.1.2.4. CHAR(m)
2.1.2.5. DATE
2.1.3. optional constraints
2.1.3.1. PRIMARY KEY
2.1.3.1.1. defines PK for the table
2.1.3.2. UNIQUE
2.1.3.2.1. indicates no repeated values for column
2.1.3.3. NULL/NOT NULL
2.1.3.3.1. indicates if values are required
2.1.3.3.2. primary key columns must be NOT NULL
2.1.3.3.3. candidate keys can be either
2.1.3.4. FOREIGN KEY
2.1.3.4.1. defines referential integrity constraints
2.1.3.4.2. clauses
2.1.3.5. CHECK
2.1.3.5.1. defines data constraints
2.1.3.5.2. IN keyword provides the list
2.1.3.5.3. LIKE keyword
2.1.3.5.4. eg. CONSTRAINT NationalityValues CHECK (Nationality NI ("Canadian', English', etc.)
2.1.3.6. format
2.1.3.6.1. CONSTRAINT
2.1.3.6.2. constraint name (eg. ArtistPK)
2.1.3.6.3. the constraint (eg. PRIMARY KEY)
2.1.3.6.4. the column(s) in parens eg. PRIMARY KEY(ArtistID)
2.1.3.6.5. CONSTRAINT ArtistPK PRIMARY KEY(ArtistID)
2.1.3.7. special constraints
2.1.3.7.1. INTRArelation constraint
2.1.3.7.2. INTERrealtion constraint
2.1.4. other
2.1.4.1. DEFAULT
2.1.4.1.1. a keyword, not constraint
2.1.4.2. populate surrogate keys
2.1.4.2.1. INDENTITY ({start value},{increment value} - SQL Server
2.1.4.2.2. SEQUENCE - Oracle
2.1.5. typical restrictions
2.1.5.1. name no longer than 18 characters
2.1.5.2. name must start with a letter
2.1.5.3. name can contain letters, numbers, underscores
2.1.5.4. name cannot contain spaces
2.1.6. implementing cardinalities
2.1.6.1. 1:N, parent optional
2.1.6.1.1. specify FOREIGN KEY constraint
2.1.6.1.2. specify foreign key NULL
2.1.6.2. 1:N, parent required
2.1.6.2.1. specify FOREIGN KEY constraint
2.1.6.2.2. Set foreign key UNIQUE constraint
2.1.6.2.3. Set foreign key NULL
2.1.6.3. 1:1, parent optional
2.1.6.3.1. specify FOREIGN KEY constraint
2.1.6.3.2. specify foreign key UNIQUE constraint
2.1.6.3.3. set foreignkey NULL
2.1.6.4. 1:1, parent required
2.1.6.4.1. specify FOREIGN KEY constraint
2.1.6.4.2. specify foreign key UNIQUE constraint
2.1.6.4.3. set foreign key NOT NULL
2.1.6.5. casual relationship
2.1.6.5.1. create a foreign key
2.1.6.5.2. do not specify FOREIGN KEY constraint
2.1.6.5.3. if 1:1, specify foreign key UNIQUE
2.2. ALTER TABLE
2.2.1. add, remove, change columns and constraints
2.2.2. ON UPDATE CASCADE (or NO ACTION)
2.2.3. ON DELETE CASCADE (or NO ACTION)
2.2.4. ALTER TABLE MyTable ADD MyColumn....
2.2.5. ALTER TABLE MyTable DROP COLUMN MyColumn
2.3. DROP TABLE
2.3.1. deletes all data and the structure
2.3.2. will not drop a table that is a parent in a 1:N, in a FOREIGN KEY constraint
2.3.2.1. must drop constraints first
3. Oracle statements
3.1. Oracle does not support CASCADE UPDATE constraint
3.2. Money or currency in Oracle is Numeric
3.3. Oracle sequences must be used for surrogate keys
3.3.1. NextVal
3.3.2. CurrVal
3.3.3. doesn't guarantee valid surrogate key values
3.3.4. CREATE SEQUENCE CustID INCREMENT BY 1 START WITH 1000;
3.4. The DESCRIBE or DESC command is used to view table status
3.5. data types
3.5.1. BLOB
3.5.2. CHAR(n)
3.5.3. DATE
3.5.4. INT
3.5.5. NUMBER (n,d)
3.5.6. VARCHAR(n)
3.5.7. VARCHAR2(n)
4. General Info
4.1. Why use SQL instead of graphical tool for DBMS management task?
4.1.1. creating tables and relationships is faster with SQL
4.1.2. repeatable for apps that require same tables, reports, etc.
4.1.3. Some apps require temp tables to be created during the app
4.1.4. SQL DDL is standardized and DBMS independent (transferable)
4.2. procedural language extensions
4.2.1. Transact-SQL (T-SQL) - SQL Server
4.2.2. Procedural Language/SQL (PL/SQL) - Oracle
5. Additional Join Forms
5.1. Inner
5.1.1. SELECT FROM Table1 JOIN Table2 ON table1.colum = table2.column WHERE criteria ORDER BY column;
5.1.2. use alias to simplify
5.2. Outer
5.2.1. use to show all rows including those with out matching row
5.2.2. left - all rows in the left table will appear
5.2.3. right - all rows in the left table will appear
6. database design process
6.1. 1) Information-level design
6.1.1. user requirements
6.1.1.1. functional requirements
6.1.1.1.1. reports to be produced
6.1.1.1.2. inquiries to be supported
6.1.1.1.3. outputs sent to other systems
6.1.1.1.4. update transactions
6.1.1.1.5. calculations to be performed
6.1.1.1.6. restrictions to enforce
6.1.1.1.7. synonyms used for each attribute
6.1.1.2. physical constraints
6.1.1.2.1. volume measures
6.1.1.2.2. performance measures
6.1.1.2.3. examples
6.1.2. goals
6.1.2.1. complete
6.1.2.2. design should enforce requirements - not force programs
6.1.2.3. 3NF or 4NF
6.1.2.4. List of requirements the program will enforce
6.1.2.5. efficient is NOT a primary goal
6.1.2.6. clean, redundancy free design
6.2. 2) Physical-level design
6.2.1. specific DBMS
6.2.2. characteristics of DBMS
6.2.3. performance of the system
6.2.3.1. space
6.2.3.2. processing time
6.2.3.3. response time
6.2.4. goal can conflict with goals of info-level design
6.2.4.1. can introduce redundancy for speed
6.2.4.2. may not be able to 3NF with required efficiency
6.2.5. controlled changes
6.2.5.1. deviations documented
6.2.5.2. list of all restrictions
6.3. 3) Final database structure
7. schemas
7.1. types
7.1.1. physical view
7.1.1.1. internal schema
7.1.1.2. hierarchical manner in which data is stored on disk
7.1.2. logical view
7.1.2.1. conceptual schema
7.1.2.2. overall description of entire database
7.1.3. external view
7.1.3.1. user view
7.1.3.2. describes the way an individual user views appoication
7.2. Basic database design methodology
7.2.1. 1) represent user view as collection of relations
7.2.1.1. 1) determine the entities involved / create separate relation for each type of entity
7.2.1.2. 2) determine the primary key for each relation
7.2.1.3. 3) determine the properties for each relation
7.2.1.4. 4) determine the relationship among the entities
7.2.1.4.1. 1:1
7.2.1.4.2. 1:M
7.2.1.4.3. N:M
7.2.2. normalize the relations
7.2.2.1. 3NF - typical result
7.2.2.2. 4NF - typical target
7.2.2.3. analysis early eliminates issues so 3NF is ok
7.2.3. represent all keys
7.2.3.1. primary
7.2.3.2. alternative
7.2.3.3. secondary
7.2.3.4. foreign
7.2.3.4.1. nulls
7.2.3.4.2. updating
7.2.3.4.3. deleting
7.2.3.5. surrogate
7.2.4. merge the result into design
7.2.4.1. add the relations
7.2.4.2. combine relations that have same primary key
7.2.4.3. new relation
7.2.4.3.1. same primary key
7.2.4.3.2. attributes from both relations
7.2.4.3.3. remove dublicates
7.2.4.3.4. 3NF
7.2.4.3.5. merge special restrictions