DATA RESOURCE MANAGEMENT

1
CHAPTER 3

• DATA RESOURCE MANAGEMENT

2
Learning Objectives

• Examine the managerial and organizational
  requirements for managing data as a resource.

3
Foundation concepts

• A computer system organizes data in a hierarchy
  that starts with
• bytes and progresses to
• fields,
• records,
• files, and
• databases.

BIT BYTE FIELD RECORD FILE DAT
ABASE
4
FILE ORGANIZATION

• BIT the smallest unit of data a computer can
  handle.
• BYTE Combination of BITS called a byte,
  represents a CHARACTER which can be a letter, a
  number or another symbol.
• FIELD A grouping of characters into a word, a
  group of words, or a complete number (such as
  persons name or age).
• RECORD A group of related fields, such as the
  students name, the course taken, the date, and
  the grade comprise a RECORD.
• FILE A collection of similar RECORDS.
• DATABASE A group of related files makes up a
  DATABASE.

5
DATABASE
ORGANIZATIONS ELECTRONIC LIBRARY STORES
MANAGES DATA IN A CONVENIENT FORM
6
Example Student Database
HIERARCHY
Financial file
Course file
Database
Personal history file
NAME
COURSE
DATE
GRADE
John Stewart IS 101 F02 B Karen
Taylor IS 101 F02 A Emily Vincent
IS 101 F02 C
File
NAME
COURSE
DATE
GRADE
Record
John Stewart IS 101 F02 B
Field
John Stewart (NAME field)
Byte
01001010 (Letter J in ASCII)
Bit
0, 1
7
KEY FIELD

• Every record on a file should contain at least
  one field that uniquely identifies instances of
  that record so that record can be retrieved,
  updated and sorted.
• This identifier field is called KEY FIELD.
• Social security number for a personnel record
  would be a key field.

8
File organization

• In the database, the individual database is a
  collection of related attributes about entities.
• A record describes an ENTITY. An ENTITY is
  something you collect data about, such as people
  or classes.
• A record is a collection of related attributes
  about a single entity.
• Each record typically consists of many attributes
  which are individual pieces of information.
• For example, order number, order date, order
  account, item number, and item quantity would
  each be an attribute of the entity order.

9
EntityORDER Attributes
Order number
Order date
Item number
Quantity
Amount
4340
02/08/03
1583
2
17.40
fields
Key field
10
The simple data table
Entity Student
Attribute types
Attribute
Record (one row)
11
Database Management Systems (DBMS)

• A DBMS is a software application with which you
  create, store, organize, and retrieve data from a
  single database or several databases.
• In the traditional file environment each
  functional area tended to develop systems in
  isolation from other functional areas.
• Accounting, finance, manufacturing, human
  resources all developed their own systems and
  data files.
• The organization is saddled with hundreds of
  programs with no one who knows what they do, what
  data they use, and who is using the data.

DBMS
12
DBMS

• DBMS is a software that permits to centralize
  data, manage them effectively, and provide access
  to the stored data by application programs.
• Software to create maintain data
• Enables business applications to extract data.
• Rather than storing data in separate files for
  each application, data are stored physically to
  appear to users as being stored in only one
  location.
• Microsoft Access is an example of a popular DBMS
  for personal computers.

13
DBMS

• DBMS software enables end users to create and
  manage their own database applications.
• Most DBMSs have a specialized language called a
  data manipulation language that is used to
  manipulate data in the database.

14
STRUCTURED QUERY LANGUAGE (SQL)

• SQL is a data manipulation language to access,
  read and update data from databases.
• Most common language used to interface with the
  databases.

15
SQL

• The most important SQL commands
• SELECT lists the columns from tables that the
  user would like to see in a result table.
• FROM identifies the tables or views from which
  the columns will be selected.
• WHERE includes conditions for selecting specific
  rows (records) within a single table and
  conditions for joining multiple tables.

16
SQL
Database
Part number
Part description
Unit price
Supplier number
PART
137 145 150 152
Door latch Door handle Door seal Compressor
22.50 26.25 6.00 70.00
4058 2038 1058 1125
Supplier number
Supplier name
Supplier address
SUPPLIER
4058 2038 1125
CBM Inc. Ace Inc. Bryant Corp.
44 Winslow, Gary, IN 44950 Rte, 101, Essex, NJ
07763 51 Elm, Rochester, NY 11349
17
SQL

• Suppose you wanted to see the Part_Number,
  Part_Description, and Unit_Price for each part in
  the PART table. You would specify
• SELECT Part_Number, Part_Description, Unit_Price
• FROM PART
• The results of using the SELECT statement to
  select only the columns Part_Number,
  Part_Description and Unit_Price from all rows in
  the PART table.

Part number
Part description
Unit price
137 145 150 152
Door latch Door handle Door seal Compressor
22.50 26.25 6.00 70.00
18
SQL

• Suppose, you wanted to see the same data only for
  parts in the PART table with unit prices less
  than 25.00. You would specify
• SELECT Part_Number, Part_Description, Unit_Price
• FROM PART
• WHERE Unit_Pricelt25.00

The results of using a conditional selection to
select only parts that meet the condition of
having unit prices less than 25.
Part number
Part description
Unit price
137 150
Door latch Door seal
22.50 6.00
19
Data manipulation language

• Many DBMS packages have a simpler way of
  interfacing with the databases-using a concept
  called query by example (QBE).
• It enables us to fill out a grid, or template, in
  order to construct a sample of the data we want
  to see.

20
Database Trends1.Data Warehouses

• Database that stores current and historical data
  of potential interest to managers throughout the
  company.
• This data is extracted for various operational
  systems and external sources including web site
  transactions.
• The data from these diverse applications are
  copied into the data warehouse database as often
  as needed (hourly, weekly, daily, monthly).

21
Data Warehouses

• The data are standardized into a common data
  model and consolidated so that they can be used
  across the enterprise for management analysis and
  decision making.
• The data are available for anyone to access as
  needed but cannot be altered.
• Much like a physical warehouse for products and
  components, the data warehouse stores and
  distributes data on computer-based information
  systems.

22
Components of a data warehouse
Operational data
Historical data
INTERNAL DATA SOURCES
Data Ware- house
Operational data
Data Access and analysis
Extract and transform
Historical data

• Data mining
• Reports

Information directory
External data
EXTERNAL DATA SOURCES
External data
23
2. Data marts

• Companies can build enterprise-wide data
  warehouses where a central data warehouse serves
  the entire organization, or they can create
  smaller, decentralized warehouses called data
  marts.
• Small data warehouse for special function, e.g.,
  Focused marketing based on customer info.

24
Data marts

• Subset of a data warehouse in which a summarized
  or highly focused portion of the organization's
  data is placed in a separate database for a
  specific population of users.
• Each data mart contains a subset of the data for
  a single aspect of a companys business, for ex.
  finance, inventory, or personnel.
• Data marts have been popular among small and
  medium sized businesses and among departments
  within larger companies.
• Data marts typically contain tens of gigabytes of
  data, as opposed to the hundreds of gigabytes in
  data warehouse.
• The cost of data mart is typically less than 1
  million, while the cost for a data warehouse can
  exceed 10 million.

25
DATABASE ADMINISTRATION

• DEFINES ORGANIZES DATABASE STRUCTURE AND
  CONTENT
• DEVELOPS SECURITY PROCEDURES
• DEVELOPS DATABASE DOCUMENTATION
• MAINTAINS DBMS

26
Management requirements for database systems

• The database is an organizational discipline, a
  method, rather than a tool or technology.
• It requires organizational change.
• Without management support and understanding,
  database efforts fail.
• The critical elements in a database environment
  are data administration, data planning, and
  database management.

27
ELEMENTS OF DATABASE ENVIRONMENT
28
Data administration

• The organizations must develop a data
  administration function for managing (collecting,
  storing, and dissemination of all types of data)
  the organization's data resources.
• Data administration is responsible for the
  specific policies and procedures through which
  data can be managed as an organizational
  resource.

29
Data planning

• The organization requires enterprise-wide
  planning for data.
• Enterprise analysis, which addresses the
  information requirements of the entire
  organization is needed to develop databases.
• The purpose of it is to identify the key
  entities, attributes, and relationships that
  constitute the organization's data.

30
Database management

• Databases require new software and a new staff
  specially trained in DBMS techniques as well as
  new data management structures.
• Most corporations develop a database design and
  management group within the corporation that is
  responsible for defining and organizing the
  structure and content of the database and
  maintaining the database.

31
Advantages of DBMS

• DBMS can manage hundreds of tables
  simultaneously.
• Helps us to manage tremendous volume and
  complexity of interrelated data.
• Prevents unnecessary redundancies of data.
• Improved data sharing.
• Improved data quality.
• Improved data accessibility.

32
Database Structures

• In all information systems, data resources must
  be organized and structured in some logical
  manner.
• Contemporary DBMS uses different database models
  to keep track of entities, attributes and
  relationships.

33
Database Structures

• Hierarchical
• Network
• Relational
• Object-oriented

34
TYPES OF RELATIONS
35
HIERARCHICAL DATA MODEL

• Hierarchical DBMS depict data logically as
  one-to-many relationships.
• The hierarchical DBMS presents data to users in a
  treelike structure.
• To the user, each record looks like an
  organization chart with one top-level segment
  called the root.
• An upper segment is connected logically to a
  lower segment in a parent-child relationship.
• Used for structured, routine types of transaction
  processing.

36
HIERARCHICAL DATA MODEL
37
NETWORK STRUCTURE

• Network DBMS depict data logically as
  many-to-many relationships.
• More flexible than the hierarchical structure in
  support of databases for many types of business
  operations.
• A typical many-to-many relationship for a network
  DBMS is the student-course relationship. There
  are many courses in the university and many
  students. A student takes many courses and a
  course has many students.

38
NETWORK DATA MODEL
39
RELATIONAL DBMS

• The most popular type of DBMS today for PCs as
  well as for larger computers and mainframes is
  the relational DBMS.
• It can relate data stored in one table to data in
  another as long as the two tables share a common
  data element.
• Easier for programmers to work with and easier to
  maintain than the hierarchical and network
  models.
• Leading mainframe relational database management
  systems include IBMs DB2 and Oracle from the
  Oracle Corporation.
• MS Access is a PC relational database management
  system.

40
RELATIONAL DBMS
PART
SUPPLIER
Part number
Part description
Unit price
Supplier number
Supplier number
Supplier name
Supplier address
137 145 150 152
Door latch Door handle Door seal Compressor
22.50 26.25 6.00 70.00
4058 2038 1058 1125
4058 2038 1125
CBM Inc. Ace Inc. Bryant
44 Winslow, Gary, IN 44950 Rte, 101, Essex, NJ
07763 51 Elm, Rochester, NY 11349
Select Part_number137 or 152
Join by Supplier_number
Part number
Supplier number
Supplier name
Supplier address
137 152
4058 1125
CBM Inc. Bryant
44 Winslow, Gary, IN 44950 51 Elm, Rochester, NY
11349
Combined data from two different tables and only
selected attributes are displayed.
41
Object-oriented DBMS

• Many applications today and in the future will
  require databases that can store and retrieve not
  only structured numbers and characters but also
  drawings, images, photographs, voice, and
  full-motion video.
• Stores data procedures as objects.
• Conventional DBMSs are not well suited to
  handling graphics-based or multimedia
  applications.
• They are becoming more popular because they can
  be used to manage the various multimedia
  components or Java applets used in Web
  applications which typically integrate pieces of
  info from a variety of sources.

42
CREATING A DATABASE

• To create a database, one must go through two
  design exercises
• CONCEPTUAL DESIGN
• PHYSICAL DESIGN

43
CREATING A DATABASECONCEPTUAL DESIGN

• Abstract model from a business perspective.
• It describes how data will be grouped.
• It establishes end-user needs.

44
CREATING A DATABASEPHYSICAL DESIGN

• Detailed model by database specialists
• How data are to be physically stored and accessed
  on the storage devices

45
CREATING A DATABASE Entity-relationship diagram

• Database designers document the logical data
  model with an entity-relationship diagram (ERD)
  which is a methodology for documenting databases
  illustrating the relationship between various
  entities in the database.

46
ENTITY- RELATIONSHIP DIAGRAM
Entity Attributes
Entities
Relationships