sql concept review

Advantages of data base over file

Built in multilevel integrity

Logical and physical data independence from database applications

Guaranteed data consistency and accuracy

Easy data retrieval

Disadvantage of database

Redundant date takes space, need administrator to manage

Difference between data and information

Data is what you store; information is what you retrieve.

3 job tasks dba do

Install and upgrading database sever and application tools

Allocating system storage

Modify database structure

Enrol user and maintaining security

Backup, monitor and controll user access

Tuple

The relational model’s equivalent of a record

Attribute

The relational model’s equivalent of a field.

Normalization

Design database, less space, more efficient

First level –find repeating column, put in own table.

Second level –large amount of duplicate data, in own table

Third level – not related to keys, in own table

 

Time—cost—feature

Production database(oltp)

Lots of transaction

Add changes, delete, viewing

Back up frequently

Over few years of data

Use snowflake design (H, T, straight)

Data warehouse (olap)

Only has add transaction

No backups

Has many years(>20years)

RAD

Rapid application development

JAD

Joint application development. Bring users, sponsors, analysts, designer and developers in a location and flush out a  suitable design.

Purpose of sdlc

Structured, standardized set of process for developing and maintaining business solutions through the development life cycle of a project

Difference RAD and Spiral

RAD components of functions are developed in parallel and time boxed. Spiral start small, build incremental.

Sdlc

Planning – people, technology, money.

Analysis – gather information, interview, documents, surveys, job analysis(observe, do job yourself);

Brainstorm, traditional, delph1, phillips66, mind mapping

Design- diagram er, class, chen, Michael Jackson, hipo, flowchart.

Implementation- build(top down, bottom up(class)); test

Unit(table accept all data), integration(database links to other database), system(performance of queries) acceptance(users put data in)

Install, cut over, parallel, phase

Warehouse database model

Ssis to load data from production database, ssas to set up, ssrs to look at it.

Common warehouse model (cwm)

Uncommon (corporate information factory(CIF)) use views

Kimbal model, number not name in warehouse

Microsoft medel, UDM, unified data table, like kimbal no total

 Join tables with queries

Set joins (data on different servers) Union (combine all data) intersect (find common data) minus (differences)

Equi-joins (data on same server) inner(matching data) outer (missing data) left right full  cross (show all combination) data mining cartisian

Nested joins (subqueries) correlated non-corelated.

delete

restrict - can’t delete parent if there is children

cascade delete – when parent deleted, children go away

nullify – when parent deleted, set foreign key to null in all children record, good for listing, keep detail, need programmer to make script

set default – parent deleted, change foreign key to default

deny delete – put D in one column, keep record, programmer prevent further use.

View

Can not add change or delete data

Data view, colums, calculations, totals, selection criteria, sorted,

Aggregated view, totals & keys

Validation view, used for security, restricted rows & columns user can see

Database design form

Table specification (entity)

Field specifications

Table relations (referential)

Business rules

View specification