Database Design Test 3 - John Stone

Alternate Join Type

Left and Right Join

Create Table Statements

Used for creating relations

Create Table Statement example

CREATE TABLE (

three-part column definition,

three-part column definition,

...

optional table constraint

...

);

3 parts of a column description

1. Column Name

2. Data Type

3. Optional Constraint

Table Constraints

NOT NULL/NULL

UNIQUE

DEFAULT

CHECK

PRIMARY KEY

FOREIGN KEY

Most common SQL server data types

Int, Char(n), Varchar(n), Numeric(digits before decimal, digits after decimal), Money, Date

Implementation of 1:1 table relationships

Parent required

specify foreign key constraint, specify foreign key unique, specify foreign key NOT NULL

Implementation of 1:N table relationship

Parent required

specify foreign key constraint, set foreign key NOT NULL

Alter Statement (and caveats)

Add, remove, change columns or constraints in an existing table

(there is no undo button)

Alter table statement example

ALTER TABLE CUSTOMER

ADD MyColumn Char(5) NULL;

Drop statements (and cavets)

Used to drop an existing column or constraint in an existing table

(must drop child before parent)

Drop statement example

ALTER TABLE CUSTOMER

DROP COLUMN MyColumn;

Insert statements (and caveats)

Insert rows of data into a table that exists

Insert statement example

INSERT INTO ARTIST

(LastName, FirstName, Nationality, DateOfBirth, DateDeceased)

VALUES ('Miro', 'Joan', 'Spanish', 1893, 1983);

Bulk insert statement example

INSERT INTO ARTIST

(LastName, FirstName, Nationality, DateOfBirth, DateDeceased)

SELECT LastName, FirstName, Nationality,

DateOfBirth, DateDeceased

FROM IMPORTED_ARTIST;

Update statements (and caveats)

used to change values of existing rows

(need a where clause otherwise every row gets changed)

Update statement example

UPDATE CUSTOMER

SET City = 'New York City', State = 'NY'

WHERE CustomerID = 1000;

Delete Statement (and caveats)

Deletes rows from existing tables

(need a where clause otherwise you delete the whole table, DELETE does not reset surrogate key values)

LEFT Join

EVEN IF, REGARDLESS WHETHER OR NOT

Select *

FROM suppliers LEFT JOIN Products

ON supplier.supplierid = products.supplierid ;

RIGHT JOIN

EVEN IF, REGARDLESS WHETHER OR NOT

select *

FROM products RIGHT JOIN suppliers

ON product.supplierid = suppliers.supplierid

SQL views

a virtual table constructed from other Table's and views

(good for presenting information from the database)

Triggers

A stored program executed by the DBMS whenever a specified event occurs

3 types of triggers

BEFORE

INSTEAD OF

AFTER

how many triggers does oracle have

9 (all 3 types and they all have update, insert, delete)

how many triggers does SQL server have

6 (INSTEAD OF, AFTER both have update, insert, delete)

how many triggers does MySQL have

6 (BEFORE, AFTER both have insert, update, delete)

stored procedures

a program stored within a database and is compiled when used

correlated subquery

looks like a normal subquery

processing is nested and it executes simultaneously

used to check functional dependencies

EXISTS

comparison operator in a subquery that returns a non-empty set of values if all row in the subquery that meets a specified condition

NOT EXISTS

comparison operator in a subquery that returns an EMPTY set of values if all row in the subquery fail to meet a specified condition

Double NOT EXISTS

double nested

checks for more comparisons in more tables

(used often to check SQL knowledge)

full backup

backup of all data and structure that exists in that moment

differential backups

backup of all changes since the last full backup

3 principles of Database Redesign

1. Measure twice cut once

2. Test new changes before changing the actual database

3. Create a complete backup of the operational database before making changes