OMISDatabase - Technical Foundations of Technical...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Technical Foundations of Technical Information Systems Information Data Resources ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ Data Resources 1 Flat File Data Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ Issues with Traditional or Flat Files Issues …Why Move to RDB …Why – – Unmanaged Data Environment Processing Concerns Data Resources 3 Flat File Data Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 4 Issues with Flat Files – Unmanaged Data Environments • Even in Enterprise Organizations, no one Even person or group was responsible for reviewing data structures. reviewing s s s Each Developer could, more or less, name data Each as they saw fit in their work. as Different names for same data. Inconsistent data type usage. Data Resources ___________________________________ ___________________________________ Flat File Data Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 5 Issues with Flat Files – Processing Concerns Processing • Programs Must Consolidate Data When Programs Needed. Needed. INPUT SELECT PROGRAM PROCESS PROGRAM INPUT OUTPUT CONSOLIDATED FILE INPUT Need to SORT ? Requires Another Step! FILE_2 FILE_1 Data Resources NEXT STEP ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ Database Definition and Terminology – What is a Database Management System What • DBMS - a group of programs and data group structures that collectively allow the standardized creation, maintenance, and access of data. access Data Resources 7 Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ FILES 8 Database Definition and Terminology – What is a Database Management System • Programs and User Tools work through the Programs through the DBMS for Access to the Data DBMS PROGRAMS DBMS USER TOOLS Data Resources ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 9 Database Definition and Terminology – What is a Database Management System What • Provides Added Value: s s s Highly managed data environment…DBAs Select data by column...no file layouts needed Can Join Data From Multiple Tables Referential Integrity of Data with Relationships Normalized Data Philosophy = Data-Driven Driven Systems Systems DBMS s s Data Resources ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 11 Relational Databases...a closer look – Why are RDBs Important to You Why RDBs • Understanding Business Systems Analysis and Design. s Business Rule Implementation in RDB... Decision Support Systems Building Your Own Transaction Systems • • End User Access to Business Data. s Servicing Your Own Business Needs. s Data Resources ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 12 Relational Databases...a closer look – Relational Storage Hierarchy....pyramid Relational metaphor metaphor BIT BYTE COLUMN ROW TABLE Data Resources ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 13 Relational Databases...a closer look – Relational Terms and Concepts • SQL - Structured Query Language, a Structured “standard” query language that is used to converse with a database. converse “SELECT <data column(s)/attribute(s)> SELECT column(s)/attribute(s FROM <db table(s))> FROM table(s WHERE <qualifying value(s)>“ WHERE value(s Data Resources ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 14 Relational Databases...a closer look – Relational Terms and Concepts • Primary Key - Column(s) value(s) in every value(s) database table that is unique and is the index key field for that table. index 1. Every Table must have a 1. Primary Key. Primary 2. Every Table only has 1 2. Primary Key. Primary CUST_MSTR CUST_NO 1111 2222 3333 CUST_DATA XXXXXXXX YYYYYYYY ZZZZZZZZZ PRIMARY KEY Data Resources ___________________________________ ___________________________________ Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ Relational Databases...a closer look – Relational Terms and Concepts • Foreign Key - Column(s) in a table that in is/are the Primary Key of another table. is/are s Foreign Key column(s) are linked to Primary Foreign Key column(s) in RDB relationships. Key SALES_MSTR SALE_NO CUST_NO SALE_DATA 7777 1111 AAAAAAAA 8888 2222 BBBBBBBB 9999 3333 CCCCCCCC PRIMARY KEY FOREIGN KEY 15 CUST_MSTR CUST_NO 1111 2222 3333 CUST_DATA XXXXXXXX YYYYYYYY ZZZZZZZZZ PRIMARY KEY Data Resources Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ Relational Databases...a closer look – Relational Terms and Concepts • RDB Relationship - A RDB linkage between a RDB Primary Key and Foreign Key(s). Primary s s One-to-Many or One-to-One Enforced Referential Integrity SALES_MSTR SALE_NO CUST_NO SALE_DATA 7777 1111 AAAAAAAA 8888 2222 BBBBBBBB 9999 1111 CCCCCCCC CUST_MSTR CUST_NO 1111 2222 3333 CUST_DATA XXXXXXXX YYYYYYYY ZZZZZZZZZ 1 Data Resources 8 16 REFERENTIAL INTEGRITY ENFORCED Database Resources s ___________________________________ ___________________________________ ___________________________________ ___________________________________ ___________________________________ 17 RDBMS and Normalization – Normalization of Data...High-Level Summary q First Normal Form: – – Eliminate Repeating Columns or Groups of Columns Make All Rows the Same Length by Eliminating Repeating Columns...Think Using Generic Names q Second Normal Form: – – All Non-PK Must Describe the PK Evaluate All Columns for Relation to Primary Key. Create new table that shares PK with original table. No Non-PK Column Can Describe Another Non-PK Column Evaluate All Columns for Relation to Other Columns. Create new table that shares PK with Original Table q Third Normal Form: – – Data Resources ___________________________________ ___________________________________ ...
View Full Document

This note was uploaded on 02/28/2011 for the course OMIS 425 taught by Professor Yoke during the Spring '11 term at Northern Illinois University.

Ask a homework question - tutors are online