Management Information System final

chapter3.pdf

Home >Information Systems homework help >Management Information System final

Management Information Systems – MIS 300

Textbook/material required: Haag, S., & Cummings, M. (2009). Management information systems for the information age. 9 th ed. McGraw-Hill, Inc.

Chapter 3: Databases and Data Warehouses

Pages: 66-80/ 83-85

Lesson Content • What is a database?

• Introduction to Relational database.

• Keys: • Primary Keys.

• Foreign Keys.

• Referential Integrity.

• Data warehouse • Multidimensional data warehouse.

• Data Warehouse and OLAP.

• Analytics Life Cycle

What is Database? p. 68

• Remember data from week 1?

• Data  Process  Information

• Database is the place we store our data.

• Defined as: • ‘A collection of data.’ • ‘A large centralized shared data

repository’

• Database = Data + Base.

What is Database? p.68

• Non-Electronic Databases: Billing Receipt, Application form cabinet.

• Electronic Databases: University Student Records, Library Book Records, and Online Search Engines.

What is DBM?: p.72

• DBM stands for Database Management.

• DBMs is a software that manages the database.

• Example: Bank Systems. **

DBM

ATM Cash Machines

Bank Managers

Online Banking

Planning your Database

• Steps in creating a database: • Tables • Queries • Forms • Reports

Tables

Queries

FormsReports 7

Database Terms p.68

• Tables VS Records • Table: A table stands

for a set of records that have the same set of fields.

• Record: Is a set of fields, containing data about a related topic/person/item…etc. • A Record is a row in a

table.

Customer Table

ID Name Address Phone Age

1 Fadi Mahboula 384732 40

2 Ibrahim Salmya 232332 25

Record

Database Terms p.84 • Fields VS Data Type • Field: A field is one single item of

data. A field has a format, or data type. A field is a column in a table: example Address.

• Data type: A Data type is the format in which the data is being stored. Examples of data types could be: Numbers, Text, Date, Characters, Boolean (True/False)...etc.

Customer Table

ID Name Address Phone Age

1 Fadi Mahboula 384732 40

2 Ibrahim Salmya 232332 25

Field

Number(2) Text(20) Text(50) Number(14) Number(2)

ID Name Address Phone Age

Data type

Data Dictionary*

Relational Database p.68

• We said Database is the storage of data in a base, in order to be used by the Database Management System (DBMs)

• Database Vs Relational Database: • A Relational database is just a way of storing data that is a more efficient and

effective. • Stores data better and find information faster.

• In relational databases a ‘table’ are called a ‘relation’.

Relations p.68

• A relation is implemented in a table.

Student( StudentID, Name, City, Course)

1 Samar Hawali MIS

2 Nasser Salmya Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

5 Nabil Kuwait City Engineering 11

Characteristics of Relations p.68

1. The content of the table changes over time (New data item

may be added, updated or deleted from a database).

2. Each attribute must be atomic; i.e. each cell in a relational table must have only a single value.

1 Samar Hawali MIS

2 Nasser Salmya Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

5 Nabil Kuwait City Engineering

Characteristics of Relations p.68

3. All rows must be distinct; i.e. there cannot be two identical rows in one table.

4. There is a field that uniquely identifies each row. Such a field is known as the primary key.

1 Samar Hawali MIS

2 Nasser Salmya Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

5 Nabil Kuwait City Engineering

Primary Key p.70

• When modelling a database, the developer must identify the keys for each table !! • The primary key is usually underlined.

• If a table has only one unique key, then that key is the primary key.

• If we have more than one unique key, we need to decide on one to be our primary key.

Student ( StudentID, Name, Civil_ID, Age, Address, Course)

Primary key

Foreign Keys p.70

A foreign key is an attribute in a relation that is not the primary key in that relation but it is the primary key in another relation.

What do they mean: A foreign key is a field in a table that is not a primary key in that table but it is a primary key in another table!

The reason why we create foreign keys is in order to link tables together and it is the most important part of relational modelling. Maybe it is better if we see it in an example.

Foreign Keys p.70

• Why do we use Foreign keys. Take a look at the following two tables of library system:

Student ( StudentID, Name, Address, Course)

101 01/01/14 4 weeks Nasser Salmya Accounting 01/02/14

102 02/01/14 1 week Khalil Kuwait City Finance 05/01/14

103 02/01/14 1 week Nasser Salmya Accounting 03/01/14

Bookloan(LoanID, Date, Period, Name, Address, Course, return_date)

1 Samar Hawali MIS

2 Nasser Salmya Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Foreign Keys p.70 • Problems with such design:

• Duplication of data.

• Data redundancies.

• Cause of errors*.

Student ( StudentID, Name, Address, Course)

101 01/01/14 4 weeks Nasser Salmya Accounting 01/02/14

102 02/01/14 1 week Khalil Kuwait City Finance 05/01/14

103 02/01/14 1 week Nasser Salmya Accounting 03/01/14

1 Samar Hawali MIS

2 Nasser Hawali Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Foreign Keys p.70

• Solution: Create a foreign key in BookLoan table that links to the primary key in the Student record table.*

Student ( StudentID, Name, Address, Course)

101 01/01/14 4 weeks 2

102 02/01/14 1 week 3

103 02/01/14 1 week 2

1 Samar Hawali MIS

2 Nasser Hawali Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Bookloan(LoanID, Date, Period,Student_ID)

Foreign Keys p.70

• Try to guess some of these keys: which ones are primary key and which ones are foreign keys.

Student ( StudentID, Name, Civil_ID, Age, Address, Course)

Book ( Title, Author, Subject, Category, ISBN)

Student ( StudentID, Name, Civil_ID, Age, Address, Course)

Book ( Title, Author, Subject, Category, ISBN)

Primary Key

Foreign keys Any Questions?

BookLoan ( LoanID, StudentID, ISBN, Date, Period, Penalty)

Integrity principles p.71

• We have two terms to understand: • Entity Integrity: it is a principle that Primary keys cannot

have repetitions or Null values. • Referential Integrity: relates to the linking between tables

defined by the primary key and foreign key. The idea here is that you cannot have a foreign key that doesn’t exist in the original table.

Referential Integrity: example

Now suppose, Khalil drops out of the University. You need to delete his record what would happen?

101 01/01/14 4 weeks 2

102 02/01/14 1 week 3

103 02/01/14 1 week 2

1 Samar Hawali MIS

2 Nasser Hawali Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Referential Integrity: example

Deleting Khalil would leave loan book table linking to nowhere!!

Referential Integrity option, ensures that if you delete Khalil, it would delete all his records (including those in other tables!!)

????

1 Samar Hawali MIS

2 Nasser Hawali Accounting

4 Samar Salmya Engineering

101 01/01/14 4 weeks 2

102 02/01/14 1 week 3

103 02/01/14 1 week 2

Referential Integrity: example

Before we delete Khalil, the system will warn us that he has records in the library.

Only when the books are returned that we can proceed to deleting him.

This process ensures integrity in links among tables!!

101 01/01/14 4 weeks 2

103 02/01/14 1 week 2

1 Samar Hawali MIS

2 Nasser Hawali Accounting

4 Samar Salmya Engineering

Terms related to Database p.66

• Business Intelligence: Using the information to make important and often strategic decisions*.

• Analytics: the science of fact-based decision making.

• Online Transaction Processing (OLTP): is collecting, processing, and updating information live. • The collection, processing, and updating is immediate.

• Databases support OLTP and therefore are called Operational Databases.

• Online Analytical Processing: Is processing data to produce information that support decision making.

P.67

Data Warehouse p.79 • Data Warehouse is a collection of information gathered from many

different operational databases.

• Helps create Business Intelligence that support: • Business Analysis Activities

• Decision Making.

Data Warehouse p.79

• Data Warehouses are multidimensional: • Databases are two dimensional (Columns and Rows).

• Data Warehouses contain layers of columns and layers of rows.

• Information is stored at several layers as we will see in the next example.

Data Warehouse p.80 • Data Warehouses are multidimensional:

Data Marts p.85 • Data mart – subset of a data warehouse in which only a focused

portion of the data warehouse information is kept

Data Warehouse

• Data Warehouses Support Decision Making: • Data Warehouses do NOT support transaction processing.

• Databases support transaction processing (OLTP).

• Data Warehouses support Online Analytical Processing (OLAP).

• These are key differences between Databases and Data Warehouses.

The Analytics Life Cycle p.84 The process of designing and implementing an Analytic System to support decision makers in an organization. Steps:

1- Interview the decision makers to determine what they need. Including preferences to graphs, tables, and even colors.

2- Find the data (internally and externally). 3-Process this data (Extraction, Transformation, and Loading*) 4- Apply data-mining tools, to generate Key Performance Indicators (KPI)

The Analytics Life Cycle p.84

Extraction, Transformation, and Loading (ETL) p.84

• ETL is a three-step process 1. Extract needed information from its source

2. Transform the data into a standardized format

3. Load the transformed data into a data warehouse

Keywords

• Business Intelligence, Analytics, Online Transaction Processing (OLTP), Operational Database, Online Analytics Processing (OLAP), Database, Relational Database, Relation, Data Dictionary, Tables, Records, Fields, Data Type, Primary Key, Foreign Key, Integrity Constraints, Data Warehouse, Multidimensional Data warehouse, Data Warehouse and OLAP, Databases and OLTP, Analytic Life Cycle, ETL, Data Mart.

• Note: The Tool Set of the Analytics Professional (p.81) & Information Ownership (p.86) not required.