MGS 351 Exam 2 Databases and Data Warehouses

Question 1

Data Hierarchy

Answer 1

Database
Table (File, relation)
Records ( Rows, Tubles)
Fields (Columns, Attributes)
Bytes
Bits

Question 2

Issues in Traditional File Environment (1950’s)

Answer 2

(1) Data redundancy- no right way of managing data
(2) Data Inconsistency- Bad Data
(3) Data Isolation (some departments have certain info while other do not)
(4) Data Integrity (is the information valid?)
(5) Security
(6) Application/Data Dependence
- Programs written on top of data files were very iffy because they had to follow very specific organizational structures and modification would result in error
- basically they had to rewrite programs to add updates
- towards the end— it was hard to keep up- time consuming

Question 3

Database Approach Advantages

Answer 3

(1) Minimal data redundancy
(2) data consistency
(3) Integration of data
(4) Sharing of data
(5) Uniform security, Privacy, and integrity
(6) ** Data independence (can add fields, tables, etc…)
(7) Centralized Environment

Question 4

Data Definition Language (DDL)

Answer 4

Creates Structure

• Specifies content and structure of database and defines each data element
• You can use the design view

Question 5

Data Manipulation language (DML)

Answer 5

Manipulates data in a database

• Inserting records-
• Deleting records

Question 6

Data Dictionary

Answer 6

Stores definitions of data elements and characteristics
-Keeps track of what the user has built
-Backing up a dictionary creates an empty skeleton
—Having a “blueprint of the structure”
—Does not have the data but it does have the structure of the database.
Example: stores properties and fields.
Does NOT store records

Question 7

Evolution of Databases

Answer 7

(1) Traditional files (1960’s)
(2) Hierarchal (1970’s)
(3) Objective oriented (new) -people creating private ones
(4) Relational (this is what we use now) its more flexible

Question 8

Relational Database represents Two-dimensional tables called

Answer 8

Relations

Question 9

Relational Database relates data across tables based on…

Answer 9

Common data elements (Primary key links to foreign keys)

Examples: DB2, Oracle, MS SQL server

Question 10

Hierarchal Database

Answer 10

Used to break one component into subgroups

Question 11

Network Database

Answer 11

Depicts data logically as many-to-many relationships (no flexibility)
“Hard to get data our of there”

Question 12

Database Design

-Conceptual/Logical Design

Answer 12

Abstract model of database from a business perspective

Question 13

Database design

-Physical design

Answer 13

How data is arranged and optimized on storage devices
-Database administrators can modify how it is stored
Example: C-drive, Flash drive, etc…
-All about performance. Don’t touch the index options(primary key) because its slows inserting, updating data and query are faster

Question 14

Business Intelligence need to have knowledge in…

Answer 14

(1) Customers
(2) Competitors
(3) Partners
(4) Competitive Environment
(5) Internal Operations

Question 15

Database Administrators are responsible for…

Answer 15

Technical operations

Question 16

Data Administrators are responsible for…

Answer 16

Making sure data is stored correctly and consistently

-Defining and Tracking

Question 17

Online Transaction Processing (OLTP)

Answer 17

Gathering of input information, Processing the information, and updating existing information to reflect the gathered and processed information
-Operational database for day-to-day operations
Example: who is in a certain class?

Question 18

Online Analytical Processing (OLAP)

Answer 18

The Manipulation of information to support decision making
-Takes data from OLTP, Summarizes it, and stores it in a data warehouse
Example: How many people in total are in a class?

Question 19

Data Warehouse

Answer 19

Logical collection of information - Gathered from many different operational databases (extract transformation Load(ETL)) - used to create business intelligence that supports business analysis and decision-making tasks

• Analyzing information by zip code not by address
• pre-summarized information (think of the ruby cube digram–also know as slice and dice)
• **Gets any data from many places–>summarizes it–>Uses it for decision making

Question 20

Data Marts

Answer 20

Subset of data warehouse in which only a focused portion of the data warehouse information is kept
-Example SAT, GPA, Major —>Created information

Question 21

Data Mining

Answer 21

Finding hidden patterns and trends in data

Question 22

Requirements for Data Mining tools

Answer 22

Requirements for searching hidden info is…
-Query tools
-Intelligence agents
-Multidimensional analysis (OLAP “Slice and Dice”)
-Statistical analysis
Example: pattern that was identified showed a trend that baby diapers were commonly purchased with beer

Question 23

“Duplicate” in a Distributed Database

Answer 23

Make a copy for every location
Downside: a centralized unit is needed to maintain a master file
Upside: Multiple backup files are created

Question 24

“Partitioned” in a Distributed Database

Answer 24

Split up and distributed to relevant departments

Question 25

The web is used to query Databases…

Answer 25

• Not fast enough for advanced analysis
• Static page: Server provides webpage from file
• Dynamic page: Server connects to a database, queries data, and custom builds the web page on the fly

Example: Ublearns->Go to a template page, enter username, record is found and the page is automatically updated

Question 26

PHP

Answer 26

Open source software

Question 27

ASP

Answer 27

Active server page

Question 28

CFM

Answer 28

Cold fusion