Career tip

1
Career tip 17 treat work as play

• It should be fun, involving, and rewarding
• If youre generally not having fun most of the
  time then something is wrong and needs to be
  adjusted, such as
• Your attitude and expectations
• The industry youre working in
• The requisite knowledge you need (training)
• Your network and social connections at work
• The amount of travel you are doing
• Your responsibilities (e.g. do you like to
  manage?)
• Life balance (do you have a life outside of
  work?!)
• The culture of your company may be a poor fit
• Try the simpler adjustments (e.g. attitude and
  training) before making big adjustments (e.g.
  leaving your company or chosen field)

2
Databases and Database Management Systems
3
Learning Objectives For Entire Database Module
(4 modules)

• Definition of a Database
• Data Building blocks
• Fields, Records, Tables, and Relationships
• Database Management Systems (DBMS)
• Capabilities Provided by DBMS
• Data Organization
• How do you organize data so
• its usefulness is maximized
• It is easy to maintain?
• Practice With a DMBS
• Creating Tables, Forms, Relationships, and Queries

4
Basic Data Management Concepts

• Database
• A collection of data organized to meet users
  needs
• Database management system (DBMS)
• Software used to access database
• Database system
• Comprised of database, DBMS, and application
  programs

5
Data Management for Individuals and Organizations

• Individuals use databases to
• Develop monthly budgets
• Store phone numbers and addresses
• Keep track of important dates
• Keep track of valuables for possible insurance
  claim
• Get information about organizations such as
  hospitals

6
The Hierarchy of Data

• Database
• Collection of integrated and related files
• File
• Collection of related records
• Record
• Collection of related fields
• Field
• Name, number, or combination of characters that
  describes some aspect of an object

7
(No Transcript)
8
Data Entities, Attributes, and Keys

• Entity
• Generalized class of people, places, or things
  for which data is collected, stored, and
  maintained
• Attribute
• Characteristic of an entity
• For example, employee number or last name
• Key
• Field in a record used to identify the record
• Primary key-Uniquely identifies the record

9
Diamond
4
3
1
2
10
Put it All Together
Entity Table NameDiamond
Fields aka Attributes
Field Names
Records aka Tuples
Diamond
4
3
1
2
11
(No Transcript)
12
Records and Fields

• All Instances of an Entity have common
  characteristics that we want to record.
• We create a Field for each characteristic.
• Each Instance gets a Record, with each record
  having the same fields
• All records are stored in a File (in a database
  we call a file a Table.)

13
The Database Approach to Data Management

• Database approach
• Multiple application programs share a pool of
  related data
• Increased flexibility in the use of data
• Requires a database management system (DBMS)

14
(No Transcript)
15
(No Transcript)
16
Organizing Data in a Database

• Questions to be asked when building a database
• What data is to be collected and at what cost
• How is the data to be arranged so that it makes
  sense
• Where is the data to be physically located
• Who is responsible for maintaining an accurate
  database system

17
The Relational Database Model

• Purpose
• To describe data using a standard tabular format
• All data elements are placed in two-dimensional
  tables called relations
• Selecting
• Choosing data based on certain criteria
• Joining
• Combining two or more tables

18
Relational Models

• Relations
• Logical equivalent of files
• Tuple
• A record or collection of related facts
• Attributes
• Columns of the table
• Domain
• The allowable values for attributes or columns

19
(No Transcript)
20
(No Transcript)
21
Data Analysis

• Evaluating data to identify problems with content
  of database
• Normalization
• Process of correcting data problems or anomalies
• Breaking a table into two or more tables in order
  to correct the data problem or anomaly

22
(No Transcript)
23
(No Transcript)
24
Database Characteristics

• Database size or amount
• Depends on the number of records or files in the
  database
• Volatility of data
• Measure of the changes typically required in a
  given period of time
• Immediacy
• Measure of how rapidly changes must be made to
  data

25
Database Management Systems

• Creating and implementing the right database
  system involves
• Determining how data is stored and retrieved
• How people will see and use the database
• How the database will be created and maintained
• How reports and documents will be generated

26
Overview of Database Types

• Flat file
• Used to store and manipulate a single table or
  file
• Single User
• Only one person can use the database at any time
• Multiuser
• Networked computer systems need multiuser DBMSs

27
Overview of Database Types

• General-purpose database
• Can be used for a large number of applications
• Special-purpose database
• Designed for a limited number of applications
• Open-Source database systems
• PostgreSQL, MySQL

28
Database Design

• Numeric field Contains numbers that can be used
  in making calculations
• Alphanumeric Characters or numbers that will not
  be manipulated
• Date Can be sorted or even used in computations
• Logical piece of data Contains items, such as
  yes or no
• Computed field Determined from other fields

29
Data Types Determine

• The form of a fields data storage
• Operations the data can support

30
Specific Data Types
31
What data type for fields that have numbers and
letters?
32
Answer

• Rule of thumb if you dont do math with them or
  they sometimes contain letters or other
  characters such as dashes, etc., they should be
  character or memo data types.
• Identifiers or keys tend to be alphanumeric data
  types (even if they are all numbers)

33
Using Databases with Other Software

• Front-end application
• One that directly interacts with people or users
• Back-end application
• Interacts with other programs or applications

34
Data Accuracy and Integrity

• Data integrity
• Data stored in the database is accurate and up to
  date
• Garbage in, garbage out (GIGO)
• Inaccurate data input results in inaccurate
  output
• Database management systems
• Must be programmed to detect and eliminate data
  inaccuracies

35
Creating and Modifying a Database

• Schema
• Outline of the logical and physical structure of
  the data and relationships among the data
• Data definition language (DDL)
• Collection of instructions and commands
• Data dictionary
• Detailed description of all data used in the
  database

36
(No Transcript)
37
Creating and Modifying a Database

• Typical uses of a data dictionary
• Provide a standard definition of terms and data
  elements
• Assist programmers in designing and writing
  programs
• Simplify database modification

38
Data Entry Quality Control

• Force use of unique identifiers
• Formatting input
• Upper/Lower case control
• Pictures, e.g., SSN --
• Speed search to avoid duplicate entry

39
Manipulating Data and Generating Reports

• Data manipulation language (DML)
• Allows users to
• Access, modify, and make queries
• Generate reports
• Query by example (QBE)
• Makes manipulating databases much easier and
  faster
• Structured Query Language (SQL)
• Standardized data manipulation language

40
Using Database Systems in Organizations

• Organizations need to process routine
  transactions
• Routine processing activities
• Pay employees
• Send out bills to maintain a healthy cash flow
• Send out a monthly newsletter
• Pay suppliers for parts and raw materials
• Information and decision support
• Valuable tool to support decision making

41
In-class DB Exercise
42
Data Warehouses, Data Marts, and Data Mining

• Data warehouse
• Holds important information from a variety of
  sources
• Usually a subset of multiple databases
• Data mart
• Small data warehouse
• Often developed for a specific person or purpose

43
Data Warehouses, Data Marts, and Data Mining

• Data mining
• Extracting information from a data warehouse or a
  data mart
• Business intelligence
• Business use of use data mining can help increase
  efficiency, reduce costs, or increase profits

44
(No Transcript)
45
Database Trends Distributed Databases

• Distributed database
• Also called a virtualized database
• Actual data may be spread across several
  databases at different locations
• Allow more users direct access at different user
  sites
• Replicated database
• Database that holds a duplicate set of frequently
  used data

46
Managing Databases Database Administration

• Database administrators are responsible for
• Overall design and coordination of the database
• Development and maintenance of schemas
• Development and maintenance of the data
  dictionary
• Implementation of the DBMS

47
Database Use, Policies, and Security

• End-user computing
• Development and use of application programs by
  noncomputer-systems professionals
• End-user computing issues
• What data can users read, update, or write in a
  database?
• What procedures are needed to guarantee proper
  database use and security?

48
Quiz
49
Data Organization

• The information that can be produced by a system
  depends entirely on the structure and integrity
  of the underlying data. The design of the
  database is of critical importance and must be
  done correctly!
• Gold Star, Inc. Case (Unorganized and
  Undisciplined Data)
• Comparing with data organized properly and
  support of DBMS