Chapter 15: Database Management Systems

loyalty cards

- Use one of these cards and you're in effect giving up information about yourself in exchange for some kind of financial incentive. The explosion in retailer cards is directly related to each firm's desire to learn more about you and to turn you into a more loyal and satisfied customer. -increases switching costs for customer

Data Manipulation Language (DML)

- used to add, change, delete, retrieve data from database - Structured query language (SQL) - used to create and manipulate databases; it is non- procedural languages; it is declarative language - CRUD (create, read, update, delete)

Entities

-An entity is a person, place, object, event or concept for which data has to be collected -Entity instance-person, place, object, event, concept (often corresponds to a row in a table)

What does the E-R model construct

-Entities -Relationships -Attributes

Relationships

-Relationship instance-link between entities (corresponds to primary key-foreign key equivalencies in related tables) -Relationship type-category of relationship...link between entity types

Relational DBMS

-Represent data as two-dimensional tables -Each table contains data on entity and attributes -most common used with PC's Servers and mainframes

Entity Relationship Diagram

-Used by database designers to document the data model -llustrates relationships between entities -Technique used by database designers in modeling a structure of data

The Data hierarchy-Record

-can be collected to form a file, and related fields into a database

The Data hierarchy-field

-can be grouped to form a record

Fields

-columns -represents attribute for entity

Data Security

-controversial -big target for identity thieves, spreading potentially incorrect data and raise privacy concerns

The Data hierarchy-database

-course file, financial file, personal file

Where businesses are leveraging data mining

-customer segmentation -marketing targeting -market basket analysis -collaborative filtering -customer churn -fraud detection -financial modeling -hiring and promotion

problems with the traditional file environment

-data redundancy -data inconsistency -poor security -lack of data sharing and availability

Techniques to improve business performance and decision making

-data warehousing -data mining

Enterprise Software

-enterprise software tends to be more integrated and standardized than the prior era of proprietary systems that many firms developed themselves. This integration helps in combining data across business units and functions, and in getting that data into a form where it can be turned into information

Text Mining

-extracts key elements from large unstructured data sets (stored emails)

Table

-grid of columns and rows

Database Management System (DBMS)

-interfaces between applications and physical data files -seperates logical and physical views of data -solves problems of traditional file environment -uncouples programs and data -enables organization to centrally manage data and data security

The Data hierarchy- Bit

-represents a 0 or a 1 -can be grouped to form a byte

The Data hierarchy-Byte

-represents one character, number or symbol -can be grouped to form a field

data clensing

-software to detect and correct data that are incorrect, incomplete, improperly formatted or redundant -enforces consistency among different sets of data from separate information systems

Data quality audit

-structured survey of the accuracy and level of completeness of the data in an information system -survey samples from data files -survey end users for perceptions of quality

Rows

-tuples -records for different entities

Access Query

A query is a request for data results, and for action on data. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data.

Relational Database Tables

A relational database organizes data in the form of two-dimensional tables. Illustrated here are tables for the entities SUPPLIER and PART showing how they represent each entity and its attributes. Supplier Number is a primary key for the SUPPLIER table and a foreign key for the PART table.

Human Resources DataBase with Multiple Views

A single human resources database provides many different views of data, depending on the information requirements of the user. Illustrated here are two possible views, one of interest to a benefits specialist and one of interest to a member of the company's payroll department.

Database

A single table or a collection of related tables.

one to many

An instance on one side of the relationship can have many related instances, but an instance on the other side will have a maximum of one related instance

one to one

Each instance in the relationship will have exactly one related instance

Designing a Database

Entity-Relationship Diagram

Microsoft Access Data Dictionary Features

For the field "Supplier Name" selected in the top pane, definitions can be configured in the General tab in the bottom pane. These General characteristics are Fields Size, Format, Input Mask, Caption, Default Value, Validation Rule, Validation Text, Required, Allow Zero Length, Indexed, Unicode Compression, IME mode, IME Sentence Mode, and Smart Tags.

Before a new database is put in place you need to

Identify and correct faulty data Establish better routines for editing data once database in operation

Foreign Key

If a key can occur many times over multiple records in a table but relates back to a primary key in another table

many to many

Instances on both sides of the relationship can have many related instances on the other side

Data mining

More discovery driven Finds hidden patterns, relationships in large databases and infers rules to predict future behavior E.g., Finding patterns in customer data for one-to-one marketing campaigns or to identify profitable customers.

Ensuring Data Quality

More than 25% of critical data in Fortune 1000 company databases are inaccurate or incomplete Most data quality problems stem from faulty input (GIGO)

Data Definition Language (DDL)

Specifies structure of database content, used to create tables and define characteristics of fields

3 Capabilities of Database Management Systems

The three main capabilities of a DBMS are data definition capability, the data dictionary, and a data manipulation language.

Why do Very large databases and systems require special capabilities, tools?

To analyze large quantities of data To access data from multiple systems

TPS

Transaction Processing Systems that represent a fountain of potentially insightful data. Every time a consumer uses a point-of-sale system, an ATM, or a service desk, there's a transactionSome kind of business exchange. (some kind of business exchange) occurring, representing an event that's likely worth tracking.

Predictive Analysis

Uses data mining techniques, historical data, and assumptions about future conditions to predict outcomes of events E.g., Probability a customer will respond to an offer

Data Dictionary

automated or manual file storing definitions of data elements and their characteristics

File Organization Concepts

database, file, record, field

attribute

each characteristic or quality describing entity, attributes data or grade belong to entity COURSE

Primary Key

field in table used to uniquely identify a table

field

group of characters as words or number, describes an entity and attribute

file

group of records of the same type

record

group of related fields

student registers for course

many to many

product line contains product

one to many

employee is assigned to parking space

one to one

Cardinality of Relationships

one to one, one to many, many to many

Neural Networks

or other advanced algorithms and statistical techniques to hunt down and expose patterns and build models to exploit findings. And massive amounts of data play a role here. Google leverages data it collects when users talk to and type into its search engine to improve its speech recognition algorithms, cutting errors by 25 percent in a single rollout

entity

person, place, thing on which we store data

NoSQL technologies

popular with Internet firms that rely on massive, unwieldy, and disparately structured data; and this technology is often at the heart of what are often characterized as "big data" efforts.

Attribute

property or characteristic of an entity or relationship type (often corresponds to a field in a table)

Database

serves many applications by centralizing data and controlling redundant data

Database Management System (DBMS)

software for creating, storing, maintaining, and accessing database files; makes using databases more efficient, manipulate data

SQL (Structured Query Language)

used to create and manipulate databases, non-procedural language, declarative language