Chapter 5: Database Systems and Data Mangement

attribute:

A characteristic of an entity.

record

A collection of attributes about a specific entity.

information:

A collection of data organized and processed so that it has additional value beyond the value of the individual facts. • Collection of organized and processed data • Has additional value beyond the value of the individual facts

Data definition language (DDL)

A collection of instructions and commands used to define and describe data and relationships in a specific database.

file:

A collection of similar entities.

Database Design: Enterprise data model

A data model that identifies the data entities and data attributes of greatest interest to the organization along with their associated standard data definitions, data length and format, domain of valid values, and any business rules for their use.

entity-relationship (Er) diagram:

A data model that uses basic graphical symbols to show the organization of and relationships between data. 1. Data model used to analyze and communicate data needs 2. Works at the individual project or application level 3. Uses graphical symbols • Identify data entities and their associated data attributes • Identify the relationships among the entities of interest 4. Many notation styles exist for drawing an ER diagram

data dictionary:

A detailed description of the data stored in the database. Among other details, the data dictionary contains the following information for each data item: - Name of the data attribute - Aliases or other names that may be used to describe the item - Range of values that can be used (domain) - Type of data (such as alphanumeric or numeric) - Number of bytes of storage needed for the item A data dictionary is a valuable tool for maintaining an efficient database that stores reliable information with no redundancy, and it simplifies the pro- cess of modifying the database when necessary. Data dictionaries also help computer and system programmers who require a detailed description of data elements stored in a database to create the code to access the data.

database management system (DbMS):

A group of programs used to access and manage a database as well as provide an interface between the database and its users and other application programs. - Group of programs provided by the DBMS supplier - Programs used to access and manage a database - Provides an interface between the database and its users and other application programs

entity

A person, place, or thing for which data is collected, stored, and maintained.

the data life cycle

A policy-based approach to manag- ing the flow of an enterprise's data, from its initial acquisition or creation and storage to the time when it becomes outdated and is deleted.

data lifecycle management (DLM):

A policy-based approach to managing the flow of an enterprise's data, from its initial acquisition or creation and storage to the time when it becomes outdated and is deleted. Manages enterprise's data flow • From initial acquisition or creation and storage • Until data becomes outdated and is deleted

relational database model:

A simple but highly useful way to organize data into collections of two- dimensional tables called relations.

Database administrator (DBA)

A skilled and trained IS professional who holds discussions with business users to define their data needs; applies database programming languages to craft a set of databases to meet those needs; tests and evaluates databases; implements changes to improve the performance of databases; and assures that data is secure from unauthorized access. • Skilled and trained IS professional • Holds discussions with business users - Defines their data needs - Applies database programming languages to craft a set of databases to meet those needs - Tests and evaluates databases - Monitors database performance and implements changes to improve response time for user queries • Assures data is secure

SqL:

A special-purpose programing language for accessing and manipulating data stored in a relational database. • SQL databases conform to ACID properties

Database

A well-designed, organized, and carefully managed collection of data. • Should help an organization achieve its goals • Can contribute to organizational success • Provides managers and decision makers with timely, accurate, and relevant information built on data • Most organizations have multiple databases

database approach to data management:

An approach to data management where multiple information systems share a pool of related data.

database as a service(DaaS):

An arrangement wherethe database is stored on a service provider's servers and accessed bythe service subscriber over a network, typically the Internet, with the database administration handled by the service provider. • Database stored on a service provider's servers • Database accessed by service subscriber over the Internet • Database administration handled by the service provider • DaaS advantage • Eliminates the installation, maintenance, and monitoring of in-house databases

foreign key:

An attribute in one table that refers to the primary key in another table.

primary key:

An attribute or set of attributes that uniquely identifies the record.

Data steward

An individual responsible for the managementof critical data elements, including identifying and acquiring new data sources; creating and maintaining consistent reference data and master data definitions; and analyzing data for quality and reconciling data issues. • Typically a non-IS employee • Manages critical data entities or attributes

Data management

An integrated set of functions that defines the processes by which data is obtained, certified fit for use, stored, secured, and processed in such a way as to ensure that the accessibility, reliability, and timeliness of the data meet the needs of the data users within an organization. • Integrated set of functions • Defines the processes by which data is obtained, certified fit for use, stored, secured, and processed • Ensures data accessibility, reliability, and timeliness meet the data users' needs

Improves innovation

Improves worker efficiency, product and/or service quality, or the customer experience

projecting:

Manipulating data to eliminate columns in a table.

selecting:

Manipulating data to eliminate rows according to certain criteria.

ACID properties:

Properties (atomicity, consistency, isolation, durability) that guarantee relational database transactions are processed reliably and ensure the integrity of data in the database.

Database Activities: Providing a User View

Schema: a description that defines the logical and physical structure of the database by identifying the tables, the attributes in each table, and the relationships between attributes and tables. DBMS can reference a schema • To access requested data in relation to another piece of data

Manipulating Data in a relational Database

Selecting, Projecting, Joining, Data Normalization

Adherence to data dictionary standards

makes it easy to share data among various organizations

Nine characteristics of quality information

- accesible - accurate - complete - economical - relevant - reliable - secure - timely - verifiable

Database Activities: Creating and modifying the database

- Data definition language (DDL) - Data dictionary - Adherence to data dictionary standards • Makes it easy to share data among organizations

Database Activities: Backup Recovery

1. Backup management • Make backup copies of the database • Backup copies can be used to restore the database

When designing a database, an organization must carefully consider the following questions:

1. Content. What data should be collected and at what cost? 2. Access. What data should be provided to which users and when? 3. Logical structure. How should data be arranged so that it makes sense to a given user? 4. Physical organization. Where should data be physically located? 5. Response time. How quickly must the data be updated and retrieved so it can be viewed by the users? 6. Archiving. How long must this data be stored? 7. Security. How can this data be protected from unauthorized access?

Database Activities: Storing and retrieving data

1. DBMS function • Interface between application program and database 2. To obtain database data • Request it through the DBMS Concurrent Control: A method of dealing with a situation in which two or more users or applications need to access the same record at the same time.

A relational database has six fundamental characteristics:

1. Data is organized into collections of two-dimensional tables called relations. 2. Each row in the table represents an entity and each column represents an attribute of that entity. 3. Each row in a table is uniquely identified by a primary key. 4. The type of data a table column can contain can be specified as integer number, decimal number, date, text, etc. 5. The data in a table column can be constrained to be of a certain type (integer, decimal number, data, character, etc.), a certain length, or to have a value between two limits. 6. Primary and foreign keys enable relationships between the tables to be defined. 7. User queries are used to perform operations on the database like adding, changing, or deleting data and selecting, projecting, and joining existing data in existing tables.

Why Learn about Database Systems and Data Management?

1. Databases capture data about changes - For analysis and decision making - To recognize new challenges and opportunities - To track progress toward meeting key goals - To identify when a change in tactics or strategy is needed 2. Need to understand database systems and data management

Database Activities: Security management

1. Ensures data is protected against access by: - Unauthorized users, physical damage, operating system failure, and simultaneous updating 2. Defines and enforces user access privileges 3. Grants users minimum privileges to do their jobs

Benefits Gained Through Use of High-Quality Data

1. Improves decision making 2. Increases customer satisfaction 3. Increases sales 4. Improves innovation 5. Raises productivity 6. Ensures compliance • Ensures compliance with requirements

Database Activities: Manipulating Data and Generating Reports

1. Manipulating data and generating reports • Database provides reports and important information • Company uses DBMS to manage database • Databases can use Query by Example (QBE) - Visual approach to developing database queries or requests Data manipulation language (DML): A specific language, provided with a DBMS, which allows users to access and modify the data, to make queries, and to generate reports.

Database Design

Because today's organizations must keep track of and analyze so much data, it is necessary to keep the data well organized so that it can be used effectively. A database should be designed to store all data relevant to the business and to provide quick access and easy modification.

Database approach to data management

Databases are used to: - provide a user view of the database - to add and modify data - to store and retrieve data - to manipulate the data and generate reports - to provide security management - to provide database backup and recovery services.

Data normalization:

Eliminates data redundancy The process of organizing the data in a relational database to eliminate data redundancy (all data is stored in only one place) and ensure data dependencies make sense (only storing related data in a table).

Knowledge:

The awareness and understanding of a set of information and the ways that information can be made useful to support a specific task or reach a decision. • Provides awareness and understanding of a set of information • Shows how information can support a specific task or be used to reach a decision

joining:

The combining of two or more tables through common data attributes to form a new table with only the unique data attributes.

data governance:

The core component of data management; it defines the roles, responsibilities,and processes for ensuring that data can be trusted and used by the entire organization, with people identified and in place who are responsible for fixing and preventing issues with data. • Defines roles, responsibilities, and processes • Ensures data can be trusted and used by the entire organization • Ensures people identified and in place who are responsible for fixing and preventing issues with data

Data Cleansing

The process of detecting and then correcting or deleting incomplete, incorrect, inaccurate, or irrelevant records that reside in a database. 1. Improves quality of the data used in decision making 2. Different from data validation 3. Methods • Cross-checking data against a validated data set • Using data enhancement to augment the data in a database by adding related information

domain

The range of allowable values for a data attribute.

data item

The specific value of an attribute

linking data tables to answer an inquiry

To find the name and hire date of the manager working on the sales manual project, the president needs three tables: project, department, and manager. The project description (sales manual) leads to the department number (598) in the project table, which leads to the manager's social security number (098-40-1370) in the department table, which leads to the manager's last name (fiske) and hire date (01-05-2001) in the manager table.

Data

raw facts - raw facts such as an employee number or total hours worked in a week.

Database Design: Considerations

• Content and access • Logical structure and physical organization • Response time, archiving, security

the Value of Information

• Directly linked to how it helps decision makers achieve their organization's goals • Helps people perform tasks efficiently and effectively

Raises productivity

• Employees can focus on the core mission • No need to correct data errors

Ensures compliance

• Ensures compliance with requirements

Increases customer satisfaction

• Leads to high customer satisfaction • Bad data causes unfavorable data errors

Increases sales

• Provides accurate consumer targeting and communications • Enables successful up-sell and cross-sell suggestions

Database design

• Store all relevant data • Provide quick access and easy modification • Reflect organization's business processes

Database Design: • Data modelling

• Tool used to design a database • Occurs at organizational level • Called enterprise data modelling • Occurs at specific business application level

Improves decision making

• Using high-quality data in decision making - Removes guesswork and risk taking