Business Analytics (I)

Question 1

Where does data come from?

Answer 1

1. Transaction processing system (TPS)
2. Enterprise Information Systems (CRM,SCM,ERP)
3. External Sources

Question 2

Problems with the traditional file environement

Answer 2

-Files maintained separately by different departments
-Data redundancy
-Data inconsistency
-Poor security
-Lack of data sharing and availability

Question 3

Problems with data redundancy

Answer 3

1 Extra data-entry time
2 Extra storage space required
3 Data inconsistency

Question 4

What is a databse?

Answer 4

A structure collection of related files containing records on people, places, or things of interest
-A single table or a collection of logically related table

Question 5

4 Core Database Concepts

Answer 5

1. Database
   -Collection of tables
2. Entity
   -Generalised category representing person, place, thing on which we store and maintain information (e.g. Students)
3. Field / Attributes (=columns)
   -Specific characteristics of each entity
4. Record (=row)
   -A collection of fields that describe an instance of an entity

Question 6

Primary Key

Answer 6

-A specific field that uniquely identifies an individual record
-Cannot be duplicates
-Never changing, never null

Question 7

Solution to Data Redundancy

Answer 7

Keep tables separate but define relationships to link tables -> Relational Database

Question 8

How do you create linkage between tables?

Answer 8

-Use common fields between tables to create linkage between them
-This common field must be primary key for one of the tables linked, and is called foreign key for other table

Question 9

Cardinality of Relationships

Answer 9

1. One-to-One
   Each instance in the relationship will have exactly one related instance
   (student v.s. student id)
2. One-to-Many
   An instance on one side of the relationship have many related instances (but an instance on the other side will have a maximum of one related instance)
   (a painter v.s. his paintings)
3. Many-to-Many
   (a student may take many courses and each course may have many students)

Question 10

Basic Operations of a Relationship Database

Answer 10

1 Select (Choose only 7 years old kids)
-> Vertically shrink the table by eliminating unwanted rows

2 Join
Combine two tables (e.g. putting names and snacks together)

3 Project
Pick certain columns (e.g. only keeping names)
-> Horizontally shrink the table

Question 11

Views of Data

Answer 11

-Logical View: how end users view data
-Physical View: how data are actually structured and organised

Question 12

Database Management System (DBMS)

Answer 12

Specific type of software for creating, storing, organising and accessing data from a database

Question 13

Capabilities of DBMS

Answer 13

1 Data definition capabilities
2 Data dictionary
3 Querying and reporting