Infs 3220 systems analysis design
This presentation is the property of its rightful owner.
Sponsored Links
1 / 12

INFS 3220 Systems Analysis & Design PowerPoint PPT Presentation


  • 46 Views
  • Uploaded on
  • Presentation posted in: General

INFS 3220 Systems Analysis & Design. Transactional DBs vs. Data Warehouses. Relational Databases (RDBMS). Collection of linked tables Tables linked by Primary Key / Foreign Key relationships (Referential Integrity) Primary Key – column whose values make each record unique

Download Presentation

INFS 3220 Systems Analysis & Design

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Infs 3220 systems analysis design

INFS 3220 Systems Analysis & Design

Transactional DBs vs.Data Warehouses


Relational databases rdbms

Relational Databases (RDBMS)

  • Collection of linked tables

  • Tables linked by Primary Key / Foreign Key relationships (Referential Integrity)

  • Primary Key – column whose values make each record unique

  • Foreign Key – value in column that links to Primary Key in another table

  • SQL – Structured Query Language (language to access data in relational tables)


Relational db example

Relational DB Example

Cust #Cust Name

100Bob

101Sue

102Juan

Order #Prod#QtyCust#

1QR221100

2QR2225100

3SB563102

CUSTOMER TABLE

ORDER TABLE

Foreign Key

Primary Key


Database structure design

Database Structure & Design

  • 2 Approaches:

Conflict

1. Optimize for

Data Capture

i.e., CapturingTransactions

2. Optimize for

Data Access

i.e., Queries & Reporting


Approach 1 optimize for data capture

Approach #1: Optimize for Data Capture

  • To optimize for data storage, you must:

    • Eliminate redundancy of data (or else wasted space & processing occurs)

    • Ensure data integrity (or else data anomalies)

    • Ensure that changes in data (modifications, deletions, etc. only have to happen in one place)

  • Normalization – process in which a DBMS is optimized for data storage

    • All data “redundancy” is removed from Database

    • Has multiple forms (0, 1st, 2nd, 3rd, et al.)


Infs 3220 systems analysis design

Moving from 0NF to 1NFRule: Make a separate table for each set of related attributes, and give each table a primary key of unique values.

Cust # CustName

100, 100, 101Bob, Sue, Juan

CUSTOMER TABLE

ONF

Cust #Cust Name

100Bob

101Sue

102Juan

CUSTOMER TABLE

1NF

Primary Key Created with Unique values


Infs 3220 systems analysis design

Dependency on Primary Key

100Bob

100Bob

Moving from 1NF to 2NFRule: Eliminate any repeating values caused by a dependency on a “keyed” column (i.e., either Primary or Foreign)

Cust #Cust NameOrder#

100Bob1

100Bob2

101Sue3

TABLE X

1NF

Cust #Cust Name

100Bob

101Sue

Order #Cust#

1100

2100

3101

CUSTOMER TABLE

ORDER TABLE

2NF


Infs 3220 systems analysis design

Dependency b/t 2 non-key columns

PGH2 days

PGH2 days

Moving from 2NF to 3NFRule: Eliminate any repeating values caused by a dependency on a “non-keyed” column (i.e., dependency on ANY column)

Cust #CityOrder#ShipTime

100PGH12 days

101PGH22 days

102LA35 days

TABLE X

2NF

City #CityShipTime

10PGH2 days

20LA5 days

Cust #City#

10010

10110

10220

SHIP TIME TABLE

CUSTOMER TABLE

3NF


Normalized db example

Normalized DB Example


Approach 2 optimize for data access in a separate read only data warehouse

Approach #2: Optimize for Data Access(in a separate, read-only Data Warehouse)

  • To optimize for data access, you must:

    • Allow data redundancy

    • Reduce the number of table joins (links among tables)

  • Denormalizing – Adding redundancy & reducing joins in a DBMS


Denormalizing most common approach

Denormalizing – Most Common Approach

  • Star Schema (Clustering)

    • Fact (core or transaction) Tables in middle of star

    • Dimensional (structural or “lookup”) Tables around “points” of star

Cust #CustName

100Bob

101Sue

102Juan

Prod #ProdName

QR22Rake

SR56Spade

TW43Mulch

CUSTOMER TABLE

PRODUCT TABLE

Order #DateCust#Prod#Rep#

106/15/XX100QR221000

207/19/XX100QR221000

308/30/XX101SR562000

ORDER TABLE

Rep #RepName

1000Lee

2000James

3000Natasha

DateQuarter

06/29/XX2Bob

06/30/XX2Sue

07/01/XX3Juan

REP TABLE

DATE/TIME


Denormalizing continued stars are linked via common i e conformed dimensions to form data warehouse

Denormalizing (continued)• Stars are linked via common (i.e., Conformed) Dimensions to form Data Warehouse

Cust #CustName

100Bob

101Sue

102Juan

CUSTOMER TABLE

Order #DateCust#Prod#Rep#

106/15/XX100QR221000

207/19/XX100QR221000

308/30/XX101SR562000

ORDER TABLE

ORDER TABLE

Prod #ProdName

QR22Rake

SR56Spade

TW43Mulch

PRODUCT TABLE

DateQuarter

06/29/XX2Bob

06/30/XX2Sue

07/01/XX3Juan

CUSTOMER TABLE

TIME

DATE/TIME

Rep #RepName

1000Lee

2000James

3000Natasha

REP TABLE

Prod#ProdName Stock DateUnits

QR22Rake 03/23/XX 150

TW43Mulch 04/15/XX 1452

SR56Spade 05/01/XX 997

INVENTORY TABLE


  • Login