MIS 3500 Database Systems

Course Summary

1

Updated 2015

DB

SYSTEMS

What We Have done

• Explored Database Systems (Database, Database Management System)

• Class Diagram (continuing on systems design & analysis course)

• Schema (normalized tables diagram, 3NF) - new

- in depth look at data modeling

- data normalization, primary key design,

PK-FK relationship

DB

SYSTEMS

Databases are the kingin the IS world!

3

MESSAGES/TOPICS

DB

SYSTEMS

4

• Topic #1: Normalization

• What it is

• Normal Forms

• How to use in data modeling

• Educational goals:

A) Being able to read/improve schemas

B) Create normalized schema from system requirements

MESSAGES/TOPICS

DB

SYSTEMS

5

• Topic #2: Querying

• Read/Write SQL

• Exercise: class, homework, labs

• Use: Project on system development

Know your

data!Know your

data!

MESSAGES/TOPICS

DB

SYSTEMS

Central Fact

PK DIMID_PRODUCTPK DIMID_CUSTOMERPK DIMID_TIMEPK DIMID_COPATIMEPK DIMID_ORGANIZATIONPK DIMID_DISTRIBUTION

VV100-CASES VV101-UNIT OF MEASURE VV102-GROSS SALES VV105-PRODUCT MFG VV301-GEN DIV ADMIN COST VV403-ADVERTISING VV407-GUARANTEED LOANS VV409-INTERCO COGS VV412-INTERCO COST ADJ VV424-PRODUCT RELAT EXP VV426-SALES RELATED EXP VV433-PRICING PROMO VVA01-SURCHARGE VVA02-OFF INVOICE DISC VVA04-BILLBACK VVA06-SALES ADJUST VVA07-PRIMARY BROKERAGE VVA10-SGML EXPENSE VVA11-SHUTTLE FRT VVA13-OUTSIDE FREEZR COST VVA14-TYSN FREEZR CHARGE VVA15-DIRECT MAILING VVA16-SALES & MKT EXP VVA18-GEN AND ADMIN VVA19-G AND a VVB01--ACCRUED ADVERTISING VVB02-ACCRUED MKT VVB09-INTERCO SALES VVB10-STEVEDORING VVB12--SALES ACCRUAL VVB19--FREIGHT FWD VVB20-INCENTIVE BROKERAGE VVB32-GENERAL VVB34-R AND D ACCRUAL VVB35-REGULAR COOP VVB39-SAMS UPCHARGE VVB41-SPECIAL COOP CHARGES VVB42-SLOTTING VVB47-AD HOC PROGRAMS VVJ13-INVENTORY ADJUSTMENT VVJ17-CONTRACT COST DISCOUNT Central FactCol1

PRODUCT DIMENSION

PK DIMID_PRODUCT

SID_PRODUCT WWML3-MINOR PRODUCT LINE *NOTE-- SEE DRAWING A

CUSTOMER DIMENSION

PK DIMID_CUSTOMER

SID_CUSTOMER **NOTE-- SEE DRAWING B

COPATIME

PK DIMID_COPATIME

SID_COPATIME WWFWK-FISC WK WWFWE-FISC WK END

ORGANIZATION DIMENSION

PK DIMID_ORGANIZATION

SID_ORGANIZATION PRCTR- PROFIT CENTER WWPRS-PRICING SEGMENT WWMLG-BUSINESS DIVISION

TIME DIMENSION

PK DIMID_TIME

SID_TIME 0CALMONTH 0CALQUART1 0CALYEAR 0FISCPER 0FISCVARNT 0CALWEEK

DISTRIBUTION DIMENSION

PK DIMID_DISTRIBUTION

SID_DISTRIBUTION VKBUR-SALES PERSON ZZSBK- SECONDARY BROKER ZZPBK- PRIMARY BROKER BZIRK- SALES DISTRICT

TYSON FROZEN FOODSCENTRAL FACT AND DIMENSIONS

AB

6

Other specific topics: • Database Transactions & Integrity

• File Types and Access Methods • Forms and Reports

• Transactions, Concurrent Access

• DB System Security

• OLAP, Data Warehousing, Business Intelligence with SAP

TOPICS

DB

SYSTEMS

7

• Class exercises (Read/Write, Analyze/Create):

• Class Diagram & Schema

• Indexes

• Hands-on study (4 labs):

• Normalization, Querying

• System development

• SAP Lumira (Business Intelligence/Analytics)

STUDY METHODS

DB

SYSTEMS

8

• Terry Bunio, DB/system expert from Protegra: • Comparison of relational DB and non-structured technologies

(“dimensional”)• Addressed almost all tech. we mentioned in the class

• Chuck Cohen, CIO from Richardson: • Relational DB is the king• Analytics (Cognos) as help for casual. SQL-ignorant users

• Both speakers stressed the lasting importance of

relations DB and that OLAP/Analytics is useful.

GUEST SPEAKERS

DB

SYSTEMS

9

With MIS 3500 completed, you should have a solid

basis for various IS-related jobs,

to build on and expand in specific directions

as your job will demand.

Good luck with your further study and career (and on the final exam )!