Database Systems and Data Models

1
Chapter 1 and 2

• Database Systems and Data Models
• Database Systems Design, Implementation, and
  Management, Rob and Coronel

2
Objectives

• The difference between data and information
• What a database is, about different types of
  databases, and why they are valuable assets for
  decision making
• Flaws in file system data management
• How modern databases evolved from files and file
  systems
• DBMS components, classifications and functions
• Database design and models
• Using Business rules to obtain database
  requirements

3
Data vs. Information

• Data
• Raw facts building blocks of information
• Unprocessed information
• Information
• processed data to reveal meaning
• Accurate, relevant, and timely information ? good
  decision making ? business successInformation
  Age
• Data Management
• proper generation, storage, and retrieval of data

4
Sales per Employee for Each of ROBCORs Two
Divisions
5
The Historical Roots of Database Files and File
Systems

• Manual File Systems ? Computer File System?DBMS
• Manual File Systems
• collection of file folders kept in file cabinet
• adequate for small amounts of data with few
  reporting requirements
• time-consuming and cumbersome to find and use
  data

6
Conversion to Computer File System

• complex, require data processing (DP) specialists
  (now database designer)
• define file structures
• write software to manage data
• design application programs to generate reports
• File Terminology
• data - record
• field - file

7
Basic File Terminology
8
Problems with File System Data Management

• programming in a third-generation language (3GL)
• Programmer must specify task (what to do) and how
  to do
• Modern databases use fourth-generation language
  (4GL)
• Allows user to specify what must be done without
  specifying how it is to be done

9
Programming in 3GL

• Time-consuming
• must be familiar with physical file structure
• As system becomes complex, access paths become
  difficult to manage and tend to produce
  malfunctions complex coding (hard for system
  administration)
• Ad hoc queries are impossible
• changes of file structure require changes to all
  programs that use data in that file
• Security features are hard to program and
  therefore often omitted

10
A Simple File System
11
Contents of Customer File
12
Contents of the Agent File
13
File System Critiques

• Islands of information
• Each file was owned by individual or department
  who commissioned its creation
• Data redundancy ? same data occur in more than
  one place

14
File System Critiques

• Structural dependence
• each file used its own application programs
• access to file depends on its structure
• Data dependence on physical data format
• physical data format ? how computer views data
• logical data format ? how human views data

15
File System Critiques

• Data redundancy
• Data inconsistency
• Different and conflicting versions of the same
  data appear in different places
• lead to lower data integrity (changes/ update to
  redundant data are not made successfully)
• Data anomalies ? data abnormality (when all
  required changes in redundant data are not made
  successfully)
• modification anomalies changing existing
  records
• insertion anomalies when entering new records
• deletion anomalies when deleting existing
  records

16
Field Definitions and Naming Conventions

• descriptive, length restriction (not too long)
• No unique record identifier (field index)
• a more flexible record definition is the one that
  anticipates reporting requirements by breaking
  up fields into their component partseg. name ?
  f_name, l_name, initial phone? area_code,
  phone_no

17
Database and the DBMS

• Databaseshared, integrated computer structure
  that stores filing cabinet
• End user data (raw facts)
• Metadata (data about data) ? define data
  characteristics and relationships that link the
  data

18
Database and the DBMS (continued)

• DBMS (database management system) software
• manages content in database
• manages database structure and controls access to
  data
• share data among multiple applications or users
• intermediary b/w user and database
• Database Design House blueprint
• define structure to store and manage data
  (require to know the expected use)
• bad design ? redundancy, inconsistency, slow
  accessgood application ? cant overcome bad
  design

19
Components of Database System

• Hardware ? computer peripheral
• Software
• O/S
• DBMS (MS Access, MS SQL Server, Oracle)
• Application Utility
• People
• System Admin ? oversee DB systems general
  operation
• Database Administrator (DBA) ?manage DBMSs use,
  ensure proper DB functioning
• Database Designer ? design DB structure DB
  architect
• System Analyst Programmer ? design implement
  application program (screens, reports and
  procedures for user to access and manipulate
  data)
• End user ? application users

20
Components of Database System

• Procedure
• instructions rules that control design and use
  of DB system
• enforce standards for business operations
• monitor and audit data entered and information
  generated
• Data
• DB complexity depends on
• organization activities and environments that the
  activities took place
• cost effectiveness ( money to solve lt cost of
  problems)

21
The DBMS Manages the Interaction Between the End
User and the Database
22
The Database System Environment (continued)
23
DBMS Functions

• Data dictionary management
• defines data elements (data structure) and their
  relationships (meta data)
• provide data abstraction,remove structural data
  dependency
• Data storage management
• stores data and related data entry forms, data
  validation, report definitions, etc.
• Data transformation and presentation
• translates logical requests into commands to
  physically locate and retrieve the requested data
  (data independence)

24
DBMS Functions (continued)

• Security management
• enforces user security and data privacy (who can
  access to which data what operations they can
  perform)
• Multi-user access control
• creates structures that allow multiple users to
  access the data
• Backup and recovery management
• provides backup and data recovery procedures

25
DBMS Functions (continued)

• Data integrity management
• promotes and enforces integrity rules to
  eliminate data integrity problems(minimize
  redundancy, maximize consistency)
• Database communication interfaces
• allows database to accept end-user requests
  within a computer network environment
• accept user request through network, web browser

26
DBMS Functions (continued)

• Database access languages and application
  programming interfaces
• query language ? nonprocedural language
• Data Definition Language (DDL)define structure
  in which data are stored
• Data Manipulation Language (DML)allow user to
  extract data from database

27
Illustrating Metadata with Microsoft Access
28
Illustrating Data Storage Management with Oracle
29
Types of Databases classified by of users

• Single-user ? supports only one user at a time
• Desktop user ? single-user database running on a
  personal computer
• Multi-user ? supports multiple users at the same
  time
• Workgroup ? supports a small group of users or a
  single department
• Enterprise ? supports a large group of users or
  an entire organization

30
Types of Databases classified by location of DB

• Centralized
• Supports data located at a single site
• Distributed
• Supports data distributed across several sites

31
Types of Databases classified by uses of DB

• Transactional / Production
• supports a companys day-to-day operations
• time-critical, accurate, immediate recording of
  data
• emphasize on data integrity, consistency,
  operational speed
• Data warehouse / DSS
• stores data used to generate information required
  to make tactical or strategic decisions (middle
  to high
• extract historical data (time factor not
  critical), and aggregated data from many sources
• eg)) Use to make pricing decision, sale forecast,
  market positioning

32
Database vs. File System

• File system
• Many separate and unrelated files

• Database
• Logically related data stored in a single logical
  data repository
• eliminate data inconsistency, data anomalies

33
Contrasting Database and File Systems
34
Database Design and Modeling

• Model ? abstraction of real world events or
  objects
• Data model ? collection of logical constructs
  used to represent the data structure,
  characteristics, relations, constraints and
  transformations found in the database
• help in easier database design
• good model ? good DB design ? good application

35
Data Model Basic Building Blocks

• Entity
• Anything (person, thing, place, event) about
  which data are to be collected and stored
• Customer, agent, order
• Attribute
• Characteristic of an entity
• Customer first name, phone, address
• Relationship
• Association among two or more entities
• An agent can serve many customers, a customer may
  be served by one agent.
• One-to-Many (1M)
• Many-to-Many (MN)
• One-to-One (11)

36
Business Rules

• Brief, precise, and unambiguous description of a
  policy, procedure, or principle within a specific
  organizations environment
• Apply to any organization that stores and uses
  data to generate information
• Description of operations that help to create and
  enforce actions within that organizations
  environment
• Define entities, attributes, relationships and
  constraints

37
Business Rules (continued)

• Must be rendered in writing
• Must be kept up to date
• Must be easy to understand and widely
  disseminated
• Describe characteristics of the data as viewed by
  the company

38
Importance of Business Rules

• Standardize companys view of data
• Constitute a communications tool between users
  and designers
• Allow designer to understand the nature, role,
  and scope of data
• Allow designer to understand business processes
• Allow designer to develop appropriate
  relationship participation rules and constraints

39
Sources of Business Rules

• Company managers
• Policy makers
• Department managers
• Written documentation
• Procedures
• Standards
• Operations manuals
• Direct interviews with end users

40
Business Rules for Video Rental shop Sample

• A customer may or may not register as a member
• Only member can get discount
• A movie have many copies
• A rental can contain maximum of 3 movies copies
• A movie copies can be rented in many rental
• A rental must be returned within 3 days

41
Evolution of Data Models

• Hierarchical Model
• Network Model
• Relational Model
• Entity-Relationship Model
• Object-Oriented Model

42
The Hierarchical ModelEvolution

• GUAM (Generalized Update Access Method)
• Based on the recognition that the many smaller
  parts would come together as components of still
  larger components
• Best understood by examining manufacturing
  process
• Best for 1M relationship that remain fixed over
  time (same part, same way)

43
A Hierarchical Models Logical Structure
44
Hierarchical Database StructureCharacteristics

• hierarchy of segments ( records)
• Each parent can have many children 1M
• Each child has only one parent
• Hierarchical path
• Preorder traversal or hierarchic sequence
  (Left-list path)
• start from root
• For efficient data access, most frequently
  accessed segment closest to left

45
Hierarchical Database StructureCharacteristics

• To access PartD (Navigational path)
• Final assembly ?ComponentA?AssemblyA?PartA?PartB?C
  omponentB?ComponentC?AssemblyB?PartC?PartD
• If PartD need sequent access the structure should
  change
• move ComponentC
• move PartD
• Example of system appropriate for this structure
• Bank customer account system
• Transaction involve either debit or credit
• why appropriate ? 1M and transaction is fixed
• 1 Customer , Many accounts (1 Account belong to
  only 1 Customer)
• Restriction to real world ?a doctor many patient,
  a patient can have one or many doctor

46
Network Database ModelBasic Structure

• Resembles hierarchical model (collection of
  records in 1M relationships)
• unlike as it also support MN relationship
• Set/Relationship (explicit)
• Composed of at least two record types
• Owner hierarchical models parent
• Member hierarchical models child (can have gt
  1 owners)

47
A Network Data Model
48
The Relational Database ModelBasic Structure

• implemented through Relational Database
  Management System (RDBMS)
• Performs same basic functions provided by
  hierarchical and network DBMS systems
• advantage able to let the user/designer operate
  in a human logical environment
• RDBMS will manage the physical detail

49
The Relational Database ModelBasic Structure
(continued)

• Table (relations)
• Matrix consisting of a series of row/column
  intersections
• Related to each other by sharing a common entity
  characteristic (eg. CUSTOMER table relates to
  AGENT table by AGENT_CODE)
• Relational schema
• Visual representation of relational databases
  entities, attributes of entities, and
  relationships between entities

50
A Relational Schema
51
Linking Relational Tables
52
The Relational Database Model

• Advantages
• Structural independence ? change db structure
  without the need to change application
• Improved conceptual simplicity ?focus on how
  human see data (ignore physical data storage
  characteristics)
• Easier database design, implementation,
  management, and use
• Ad hoc query capability ? SQL (4GL)
• Powerful database management system ? hide system
  complexity (physical structure) from designer and
  end user

53
The Relational Model (continued)

• Disadvantages
• Substantial hardware and system software overhead
• Easy-to-use
• poor design and implementation (slow system
  data anomalies)
• promote islands of information problems(each
  department create their own database)

54
The Entity Relationship Data Model

• Graphical representation of entities and their
  relationships in a database structure
• Introduced by Chen in 1976
• Crows Foot Model

55
The Entity Relationship ModelBasic Structure

• Entity relationship diagram (ERD)
• Uses graphic representations to model database
  components
• Entity is mapped to a relational table
• Entity set/ Entity collection of like entities
• Entity instance / occurrence row in table
• Attribute characteristics of entity
• Relationship association among data (diamond
  shape)
• Connectivity ? labels types of relationships
• One-to-Many (1M)
• Many-to-Many (MN)
• One-to-One (11)

56
Relationships The Basic Chen ERD
57
Relationships The Basic Crows Foot ERD
58
The Entity Relationship ModelBasic Structure

• Advantage
• conceptual view
• visual representation
• effective communication tools among (database
  designer, programmers, managers, end users)
• Disadvantage
• limited constraints representation (eg. GPA
  between 0 and 4.0)
• limited relationship representation ? only
  between entities, not within entity (eg.
  classification and credit completed)
• loss of information content ? crowed model so
  ignore attribute mapping

59
The Object Oriented Model

• collection of objects (object data
  relationships)
• Now ,object ? attributes relationship
  behaviors unlike ERD Model (entity set)
• attribute ? characteristic (name, ssc, dob)
• Behaviors/Method ? real-world action (find
  PERSONs name, print address)
• Class ? group objects that share similar
  characteristics and behavior EG. PERSON
• Classes are organized in Class hierarchy
• 1 class 1 parent (PERSONCUSTOMER),(PERSON,EMPLO
  YEE)
• Inheritance ? ability of an object within the
  class hierarchy to inherit the attributes and
  methods of classes above it

60
A Comparison of the OO Model and the ER Model
61
The Development of Data Models
62
Summary

• Information is derived from data, which is stored
  in a database
• Databases were preceded by file systems
• file management becomes difficult as a file
  system grows
• DBMS were developed to address file systems
  inherent weaknesses
• To implement and manage a database, use a DBMS
• Database design defines its structure (good
  design?good application)
• Database model helps to represent data structure
  and relationship