MIS 10th edition : Module 3 - Data and Business Intelligence

how users, the DBMS, and the database interact:

The user issues a request, and the DBMS searches the database and returns the information to the user.

data hierchy

the structure and organization of data, which involves fields, records, and files.

flat files

data was stored in a series of files that were called "flat files" because they were not arranged in a hierarchy, and there was no relationship among these files

to generate business intelligence (BI), the database component of an information system needs access to two types of data:

internal and external

examples of sources for external data:

Competitors, customers, and suppliers

Distribution networks

Economic indicators (e.g., the consumer price index)

Government regulations

Labor and population statistics

Tax records

What are the applications of the BI reports at the Richmond, Virginia police department?

Business intelligence (BI) is used in law enforcement as well as in the business world. In Richmond, Virginia, data entered into the information system includes crime reports from the past five years, records of 911 phone calls, details about weather patterns, and information about special events. The system generates BI reports that help pinpoint crime patterns and are useful for personnel deployment, among other purposes. The system has increased public safety, reduced 911 calls, and helped management make better use of Richmond's 750 officers.

How might the BI reports impact how the department leverages resources and influences outcomes?

the department refined its reports by separating violent crimes into robberies, rapes, and homicides to help them discover patterns for certain types of crime. For example, the department discovered that Hispanic workers were often robbed on paydays. By entering workers' paydays into the system and looking at robbery patterns, law enforcement officers were able to identify days and locations on which these incidents were likely to occur. Moving additional officers into those areas on paydays has reduced the number of robberies.

In a database, files are accessed by using........ method

a sequential,

random,

or indexed sequential

In a sequential access file structure, records in files are organized and processed in.................order, typically the order in which they were entered.

numerical or sequential order,

In a sequential access file structure, Records are organized based on what is known as a

primary key

This type of access method is effective when a large number of records are processed less frequently, perhaps on a quarterly or yearly basis.

sequential access file structure,

Normally, a sequential file structure is used for .................because they rarely need updating.

backup and archive

In a random access file structure, records can be accessed .............................

in any order, regardless of their physical locations in storage media.

This method of access is fast and very effective when a small number of records needs to be processed daily or weekly

a random access file structure

Because access speed usually is not critical, these records are typically stored on magnetic tape.

sequential access file structure

To achieve this fast speed, these records are often stored on magnetic disks

random access file structure

..............are random access devices, whereas .................. are sequential access devices.

disks - tapes

with the ...................records can be accessed sequentially or randomly, depending on the number being accessed.

indexed sequential access method (ISAM)

This file structure is similar to a book index that lists page numbers where you can find certain topics

indexed sequential access method (ISAM)

The advantage of this method is that both types of access can be used, depending on the situation and the user's needs. For a small number, random access is used, and for a large number, sequential access is used.

indexed sequential access method (ISAM)

ISAM, as the name suggests, uses an index structure and has two parts:

the indexed value and a pointer to the disk location of the record matching the indexed value.

Retrieving a record requires at least two disk accesses, once for...........and once for.................

the index structure- the actual record.

Before designing a database, you need to know the two ways information is viewed in a database:

the physical view and the logical view

the..............involves how data is stored on and retrieved from storage media, such as hard disks or magnetic tapes. For each database, there is only one............. of data

physical view

The physical view involves how data is stored on and retrieved from storage media, such as............. or ...............

hard disk - magnetic tape

the....................... involves how information appears to users and how it can be organized and retrieved.

logical view

There can be more than one .................... of data, , depending on the user.

logical view

example of logical view

For example, marketing executives might want to see data organized by top-selling products in a specific region; the finance officer might need to see data organized by cost of raw materials for each product.

data model

determines how data is created, represented, organized, and maintained. It usually contains data structure, operations, and integrity rules.

A data model usually includes these three components:

data structure, operations, integrity rules

Data structure

Describes how data is organized and the relationship among records

Operations

Describe methods, calculations, and so forth that can be performed on data, such as updating and querying data

Integrity rules

Define the boundaries of a database, such as maximum and minimum values allowed for a field, constraints (limits on what type of data can be stored in a field), and access methods

hierarchical model

the relationships between records form a treelike structure (hierarchy). Records are called nodes, and relationships between records are called branches. The node at the top is called the root, and every other node (called a child) has a parent. Nodes with the same parents are called twins or siblings.

network model

similar to the hierarchical model, but records are organized differently. Unlike the hierarchical model, each record in the network model can have multiple parent and child records.

relational model

uses a two-dimensional table of rows and columns of data. Rows are records (also called tuples), and columns are fields (also referred to as attributes).

data dictionary

stores definitions, such as data types for fields, default values, and validation rules for data in each field.

example of data dictionary

Field name—Student name, admission date, age, and major

Field data type—Character (text), date, and number

Default value—The value entered if none is available; for example, if no major is declared, the value is “undecided.”

Validation rule—A rule determining whether a value is valid; for example, a student’s age cannot be a negative number.

primary key

uniquely identifies every record in a relational database. Examples include student ID numbers, account numbers, Social Security numbers, and invoice numbers.

foreign key

a field in a relational table that matches the primary key column of another table. It can be used to cross-reference tables.

normalization

improves database efficiency by eliminating redundant data and ensuring that only related data is stored in a table.

Normalization can go through several stages, from...............to ...............

first normal form (1NF)- fifth normal form (5NF)

the following tasks are performed in the 1NF stage:

-Eliminate duplicated fields from the same table.

-Create separate tables for each group of related data.

-Identify each record with a unique field (the primary key).

There are several operations, such as

select, project, join,

intersect, union, and difference. (first 3 most commonly used)

...................searches data in a table and retrieves records based on certain criteria (also called conditions).

a select operation

Example of select operation

Table 3.1 shows data stored in the Students table. Using the select operation "Major=MIS," you can generate a list of only the students majoring in MIS, as Table 3.2 shows.

.............pares down a table by eliminating columns (fields) according to certain criteria.

A project operation

example of project operation

For example, say you need a list of students but do not want to include their ages. Using the project operation "PROJECT Student ID#, Name, Major, GPA (Table 3.1)," you can retrieve the data shown in Table 3.3. The "(Table 3.1)" in this statement means to use the data in Table 3.1.

..............combines two tables based on a common field (e.g., the primary key in the first table and the foreign key in the second table).

A join operation

Example of a join operation

Table 3.4 shows data in the Customers table, and Table 3.5 shows data in the Invoices table. The Customer# is the primary key for the Customers table and is a foreign key in the Invoices table; the Invoice# is the primary key for the Invoices table. Table 3.6 shows the table resulting from joining these two tables.

DBMS software includes these components:

-database engine

-data definition

-data manipulation

-application generation

-data administration

A database engine

the heart of DBMS software, is responsible for data storage, manipulation, and retrieval. It converts logical requests from users into their physical equivalents (e.g., reports) by interacting with other components of the DBMS (usually the data manipulation component).

a database engine converts........ from users into their......... (e.g., reports) by interacting with other components of the DBMS (usually the data manipulation component).

logical requests-

physical equivalents

example of a database engine

For example, say a marketing manager wants to see a list of the top three salespeople in the Southeast region (a logical request). The database engine interacts with the data manipulation component to find where these three names are stored and displays them on screen or in a printout (the physical equivalent).

Because more than one logical view of data is possible, the database engine can retrieve and return data to users in many ......... ways.

different

1 multiple choice option

The data definition component

is used to create and maintain the data dictionary and define the structure of files in a database.

Any changes to a database's structure, such as adding a field, deleting a field, changing a field's size, or changing the data type stored in a field, are made with this component.

data definition

3 multiple choice options

The data manipulation component

is used to add, delete, modify, and retrieve records from a database.

Typically, a query language is used for this component.

Data manipulation

Many query languages are available, but ................. and ............. are two of the most widely used.

Structured Query Language (SQL) -

query by example (QBE)

Structured Query Language (SQL)

a standard fourth-generation query language used by many DBMS packages, such as Oracle 12c and Microsoft SQL Server. SQL consists of several keywords specifying actions to take.

The basic format of an SQL query is as follows:

After the SELECT keyword, you list the fields you want to retrieve. After FROM, you list the tables or files from where the data is retrieved, and after WHERE, you list conditions (criteria) for retrieving the data

Example of SQL Query

The following example retrieves the name, Social Security number, title, gender, and salary from the Employee and Payroll tables for all employees with the job title of engineer:

-This query means that data in the NAME, SSN, TITLE, GENDER, and SALARY fields from the two tables EMPLOYEE and PAYROLL should be retrieved.

-Line 3 indicates on which field the EMPLOYEE and PAYROLL tables are linked (the SSN field) and specifies a condition for displaying data: only employees who are engineers.

-You can add many other conditions to SQL statements by using AND, OR, and NOT operators (discussed next).

query by example (QBE)

you request data from a database by constructing a statement made up of query forms. With current graphical databases, you simply click to select query forms instead of having to remember keywords, as you do with SQL. You can add AND, OR, and NOT operators to the QBE form to fine-tune the query.

Means that all conditions must be met.

AND—

For example, “Major =MIS AND GPA> 3.8” means a student must be majoring in MIS and have a GPA higher than 3.8 to be retrieved.

Means only one of the conditions must be met.

OR—

For example, “Major=MIS OR GPA> 3.8” means that if a student has a GPA higher than 3.8, he can be majoring in another field, such as accounting. Alternatively, if a student is majoring in MIS, she can have a GPA of 3.8 or lower.

Searches for records that do not meet the condition.

NOT—

For example, “Major NOT ACC” retrieves all students except accounting majors.

The application generation component

is used to design elements of an application using a database, such as data entry screens, interactive menus, and interfaces with other programming languages. These applications, for example, might be used to create a form or generate a report.

If you are designing an order entry application for users, you could use the .................. component to create a menu system that makes the application easier to use. Typically, IT professionals and database administrators use this component.

application generation

3 multiple choice options

The data administration component

also used by IT professionals and database administrators, is used for tasks such as backup and recovery, security, and change management.

create, read, update, and delete (CRUD)

refers to the range of functions that data administrators determine who has permission to perform certain functions.

database administrator (DBA)

found in large organizations, design and set up databases, establish security measures, develop recovery procedures, evaluate database performance, and add and fine-tune database functions.

The DBA’s responsibilities include:

-Designing and setting up a database

-Establishing security measures to determine users’ access rights

-Developing recovery procedures in case data is lost or corrupted

-Evaluating database performance

-Adding and fine-tuning database functions

graph database

a database that uses graph structures for query operation with nodes, edges, and properties to represent and store data.

What is the difference between a relational database and a graph database?

A typical relational database stores entities and their properties in tables, whereas a graph database in addition stores relations between entities. It focuses on connections between entities and navigates and manages connected data. This enables database operations across different but related entities.

Which businesses benefit the most from graph databases?

In the healthcare industry in particular this has proven to be very helpful as doctors may belong to multiple healthcare providers, diseases may have multiple symptoms, and there may be multiple relationships among organizations such as insurance companies, hospitals, and different employers. Healthcare providers such as Curaspan Health Group, GoodStart Genetics, SharePractice, and Janssen Pharmaceuticals use graph databases for patient management, drug research, genomics, clinical trials, and marketing

providers of graph databases

Neo Technology, GraphBase, HyperGraphDB, and Oracle Spatial and Graph

Recent trends in database design and use include

data-driven Web sites, natural language processing, distributed databases, and object-oriented databases.

a "static" Web site.

Without data-driven Web site, designers must edit the HTML code every time a Web site's data contents change

A data-driven Web site 's benefit

improves access to information so users' experiences are more interactive, and it reduces the support and overhead needed to maintain static Web sites.

Data-driven Web sites are useful for the following applications, among others:

-E-commerce sites that need frequent updates.

-News sites that need regular updating of content.

-Forums and discussion groups.

-Subscription services, such as newsletters.

distributed database management system (DDBMS)

stores data on multiple servers throughout an organization.