assignment 209

Individual Assignment 1 – Homework (10%) – Chapter 3

Start Date: 11 January 2021 – 17:00

Due Date: 17 January 2021 – 23:59

(
Please upload your assessment on Moodle using Individual Assignment 1 submission link
)

Student Name and ID:

Section:

Faculty Name:  

Total Earned Points: /

Individual Assignment 1-Homework Fall 2020 2

Consider the following two relational database tables to all the below questions.

Employee Table

Payment Table

EmployeeID

110

116

110

1) Identify the Primary keys and foreign keys for each table. (30 points)

2) What are the data types for “PaymentID” and “OvertimePayment” in the Payment table? You should use appropriate data type and length. (20 points)

3) Is it possible to add the below record to the Employee table? Explain Why? (15 points)

4) Is it possible to add the below record to the Employee table? Explain Why? (10 points)

Ali

215/10 Hawali

5) Is it possible to add the below record to the Payment table? Explain Why? (10 points)

6) Is it possible to add the below record to the Payment table? Explain Why? (15 points)

110

120 KD

Individual Assignment 1-Homework Fall 2020 1

Management
Information
Systems
MIS

3

00

Textbook/material required: Haag, S., & Cummings, M.
(

20

0

9

). Management information systems for the
information age. 9 th ed. McGraw-Hill, Inc.

Management Information Systems –
MIS

30

0

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:

6

6-80/ 83-8

5

3

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

4

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.

5

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.

6

What is DBM?: p.

7

2

• DBM stands for
Database
Management.

• DBMs is a software
that manages the
database.

• Example: Bank
Systems. **

DBM

ATM Cash
Machines

Bank
Managers

Online
Banking

7

Planning your Database:

• Steps in creating a database:
•

Tables

•

Queries

• Forms
• Reports

Tables
Queries

FormsReports
8

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 3847

32

40

2 Ibrahim Salmya

23

2

33

2

25

Record

9

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*

10

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’.

11

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 12

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

13

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
14

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 15

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.

16

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

17

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

18

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)

19

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)

20

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.

21

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

22

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
23

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

24

25

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.

26

P.67

27

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.

BI

28

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.

29

Data Warehouse: p.80

• Data Warehouses are multidimensional:
30

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

portion of the data warehouse information is kept

31

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.

32

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)

33

The Analytics Life Cycle: p.84

34

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

35

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.

36

The End

37