Database Implementation Module 5 (DSS 5525)

Get Started. It's Free
or sign up with your email address
Rocket clouds
Database Implementation Module 5 (DSS 5525) by Mind Map: Database Implementation Module 5 (DSS 5525)

1. Additional Join Forms

1.1. Inner

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

1.1.2. use alias to simplify

1.2. Outer

1.2.1. use to show all rows including those with out matching row

1.2.2. left - all rows in the left table will appear

1.2.3. right - all rows in the left table will appear

2. SQL Data Manipulation Language (DML)


2.1.1. INSERT VALUES (insert one row at a time)

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

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

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


2.2.1. similar to INSERT bulk

2.2.2. us with WHERE clause to specificy which rows

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


2.3.1. DELETE FROM MyTable WHERE this is true

3. SQL Data Definition Language (DDL)


3.1.1. column name

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

3.1.3. 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 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 INTERrealtion constraint

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

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

3.1.6. 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


3.2.1. add, remove, change columns and constraints



3.2.4. ALTER TABLE MyTable ADD MyColumn....



3.3.1. deletes all data and the structure

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

4. database design process

4.1. 1) Information-level design

4.1.1. 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

4.1.2. 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

4.2. 2) Physical-level design

4.2.1. specific DBMS

4.2.2. characteristics of DBMS

4.2.3. performance of the system space processing time response time

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

4.2.5. controlled changes deviations documented list of all restrictions

4.3. 3) Final database structure

5. schemas

5.1. types

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

5.1.2. logical view conceptual schema overall description of entire database

5.1.3. external view user view describes the way an individual user views appoication

5.2. Basic database design methodology

5.2.1. 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 N:M

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

5.2.3. represent all keys primary alternative secondary foreign nulls updating deleting surrogate

5.2.4. 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

6. Oracle statements

6.1. Oracle does not support CASCADE UPDATE constraint

6.2. Money or currency in Oracle is Numeric

6.3. Oracle sequences must be used for surrogate keys

6.3.1. NextVal

6.3.2. CurrVal

6.3.3. doesn't guarantee valid surrogate key values


6.4. The DESCRIBE or DESC command is used to view table status

6.5. data types

6.5.1. BLOB

6.5.2. CHAR(n)

6.5.3. DATE

6.5.4. INT

6.5.5. NUMBER (n,d)

6.5.6. VARCHAR(n)

6.5.7. VARCHAR2(n)

7. General Info

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

7.1.1. creating tables and relationships is faster with SQL

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

7.1.3. Some apps require temp tables to be created during the app

7.1.4. SQL DDL is standardized and DBMS independent (transferable)

7.2. procedural language extensions

7.2.1. Transact-SQL (T-SQL) - SQL Server

7.2.2. Procedural Language/SQL (PL/SQL) - Oracle