Online Mind Mapping and Brainstorming

Create your own awesome maps

Online Mind Mapping and Brainstorming

Even on the go

with our free apps for iPhone, iPad and Android

Get Started

Already have an account? Log In

Database Implementation Module 5 (DSS 5525) by Mind Map: Database Implementation
Module 5 (DSS 5525)
0.0 stars - reviews range from 0 to 5

Database Implementation Module 5 (DSS 5525)

Additional Join Forms


SELECT FROM Table1 JOIN Table2 ON table1.colum = table2.column WHERE criteria ORDER BY column;

use alias to simplify


use to show all rows including those with out matching row

left - all rows in the left table will appear

right - all rows in the left table will appear

SQL Data Manipulation Language (DML)


INSERT VALUES (insert one row at a time)

delete restricted - eg. Artist cannot be deleted if there is work for that person

insert: default policy -eg. on insert of artist, a default policy is to be used for finding an artist

Other version accepts SELECT statements to copy data in from other tables


similar to INSERT bulk

us with WHERE clause to specificy which rows

UPDATE MyTable SET (col1 = val1, col2 = val2, etc) WHERE this is true


DELETE FROM MyTable WHERE this is true

SQL Data Definition Language (DDL)


column name

data type, INTEGER, SMAILLINT, Decimal(p,q), CHAR(m), DATE

optional constraints, PRIMARY KEY, defines PK for the table, UNIQUE, indicates no repeated values for column, NULL/NOT NULL, indicates if values are required, primary key columns must be NOT NULL, candidate keys can be either, FOREIGN KEY, defines referential integrity constraints, clauses, ON UPDATE NO ACTION, updates to the pk for a table with children should be prohibited (default), ON UPDATE CASCADE, updates to the pk for a table with children should cascade to the child table, ON DELETE NO ACTION, deletions of rows that have children should be prohibited, ON DELETE CASCADE, deletion of a row with children will cascade and delete child table rows, CHECK, defines data constraints, IN keyword provides the list, LIKE keyword, eg. CONSTRAINT NationalityValues CHECK (Nationality NI ("Canadian', English', etc.), format, CONSTRAINT, constraint name (eg. ArtistPK), the constraint (eg. PRIMARY KEY), the column(s) in parens eg. PRIMARY KEY(ArtistID), CONSTRAINT ArtistPK PRIMARY KEY(ArtistID), special constraints, INTRArelation constraint, within the same table, INTERrealtion constraint, between tables, triggers must be used when there are no INTERrelation constraints

other, DEFAULT, a keyword, not constraint, populate surrogate keys, INDENTITY ({start value},{increment value} - SQL Server, SEQUENCE - Oracle

typical restrictions, name no longer than 18 characters, name must start with a letter, name can contain letters, numbers, underscores, name cannot contain spaces

implementing cardinalities, 1:N, parent optional, specify FOREIGN KEY constraint, specify foreign key NULL, 1:N, parent required, specify FOREIGN KEY constraint, Set foreign key UNIQUE constraint, Set foreign key NULL, 1:1, parent optional, specify FOREIGN KEY constraint, specify foreign key UNIQUE constraint, set foreignkey NULL, 1:1, parent required, specify FOREIGN KEY constraint, specify foreign key UNIQUE constraint, set foreign key NOT NULL, casual relationship, create a foreign key, do not specify FOREIGN KEY constraint, if 1:1, specify foreign key UNIQUE


add, remove, change columns and constraints



ALTER TABLE MyTable ADD MyColumn....



deletes all data and the structure

will not drop a table that is a parent in a 1:N, in a FOREIGN KEY constraint, must drop constraints first

database design process

1) Information-level design

user requirements, functional requirements, reports to be produced, inquiries to be supported, outputs sent to other systems, update transactions, calculations to be performed, restrictions to enforce, synonyms used for each attribute, physical constraints, volume measures, performance measures, examples, occurences of entities, frequency of reports, response time, query, update transactions, special security concernts., length of reports

goals, complete, design should enforce requirements - not force programs, 3NF or 4NF, List of requirements the program will enforce, efficient is NOT a primary goal, clean, redundancy free design

2) Physical-level design

specific DBMS

characteristics of DBMS

performance of the system, space, processing time, response time

goal can conflict with goals of info-level design, can introduce redundancy for speed, may not be able to 3NF with required efficiency

controlled changes, deviations documented, list of all restrictions

3) Final database structure



physical view, internal schema, hierarchical manner in which data is stored on disk

logical view, conceptual schema, overall description of entire database

external view, user view, describes the way an individual user views appoication

Basic database design methodology

1) represent user view as collection of relations, 1) determine the entities involved / create separate relation for each type of entity, 2) determine the primary key for each relation, 3) determine the properties for each relation, 4) determine the relationship among the entities, 1:1, 1:M, primary key on one side as foreign key in many side, N:M, relationship between a & b, create new relationship C, primary key of C is a combo of primary key of A & B, attributes associated with C become Non-key attributes

normalize the relations, 3NF - typical result, 4NF - typical target, analysis early eliminates issues so 3NF is ok

represent all keys, primary, alternative, secondary, foreign, nulls, allowing nulls, referential integrity, updating, forbid update is restricted, allow update -- update cascade, allow update -- update nullifies, deleting, forbid - delete is restricted, allow delete - delete cascades, allow delete - delete nullifies, surrogate

merge the result into design, add the relations, combine relations that have same primary key, new relation, same primary key, attributes from both relations, remove dublicates, 3NF, merge special restrictions

Oracle statements

Oracle does not support CASCADE UPDATE constraint

Money or currency in Oracle is Numeric

Oracle sequences must be used for surrogate keys



doesn't guarantee valid surrogate key values


The DESCRIBE or DESC command is used to view table status

data types





NUMBER (n,d)



General Info

Why use SQL instead of graphical tool for DBMS management task?

creating tables and relationships is faster with SQL

repeatable for apps that require same tables, reports, etc.

Some apps require temp tables to be created during the app

SQL DDL is standardized and DBMS independent (transferable)

procedural language extensions

Transact-SQL (T-SQL) - SQL Server

Procedural Language/SQL (PL/SQL) - Oracle