Database Modeling

Outline
• Conceptual Data Model: ER Diagrams
• Logical Database Design and the Relational Model
• ER to Relational Transformations
• Logical to Physical Design
• Normalization
• ER Diagrams Exercise

Conceptual Data Model: ER Diagrams

• The E-R model is expressed in terms of entities in the business environment, the relationships
(or associations) among those entities, and the attributes (or properties) of both the entities and
their relationships
• Entity Relationship Diagrams are representation of ER Model.
• Conceptual data modeling is about understanding the organization— getting the right
requirements.
• Business rules defines relationships and cardinalities.

1. A SUPPLIER may supply many ITEMs (by “may supply,” we mean the supplier may not supply any
items). Each ITEM is supplied by any number of SUPPLIERs (by “is supplied,” we mean that the item
must be supplied by at least one supplier).
2. Each ITEM must be used in the assembly of at least one PRODUCT and may be used in many
products. Conversely, each PRODUCT must use one or more ITEMs.
3. A SUPPLIER may send many SHIPMENTs. However, each shipment must be sent by exactly one
SUPPLIER. Notice that sends and supplies are separate concepts. A SUPPLIER may be able to supply
an item, but may not yet have sent any shipments of that item.
4. A SHIPMENT must include one (or more) ITEMs. An ITEM may be included on several SHIPMENTs.
5. A CUSTOMER may submit any number of ORDERs. However, each ORDER must be submitted by
exactly one CUSTOMER. Given that a CUSTOMER may not have submitted any ORDERs, some
CUSTOMERs must be potential, inactive, or some other customer possibly without any related
ORDERs.
6. An ORDER must request one (or more) PRODUCTs. A given PRODUCT may not be requested on
any ORDER, or may be requested on one or more orders.
Logical Database Design and the Relational Model
• Logical Design transforming Conceptual design to Logical design
• Logical database design is about creating stable database structures.
• The Relational data model represents data in the form of tables.
Consists of the following:
o Data structure Data are organized in the form of tables, with rows and columns.
o Data manipulation Powerful operations (using the SQL language) are used to
manipulate data stored in the relations.
o Data integrity The model includes mechanisms to specify business rules that maintain
the integrity of data when they are manipulated.

Relational models is represented by a Table or a shorthand notation.

ER to Relational Transformations
• Logical design transforms the E-R (and EER) diagrams into relational database schemas. The
outputs are the relational schemas: Tables and shorthand notation.

• Transforming (or mapping) EER diagrams into relations is a relatively straightforward process
with a well-defined set of rules.
• Examples in the next series of slides
Normalization
• is the process of successively reducing relations with anomalies to produce smaller, well-
structured relations.
• makes no assumptions about how data will be used in displays, queries, or reports.
• based on what we will call normal forms and functional dependencies, defines rules of the
business, not data usage.
• is a logical data modeling technique used to ensure that data are well structured from an
organization-wide view

Logical to Physical Design

• Quick Seat Work: Reversed engineering POS Receipt

Development Methods
Introduction
• Increasing system complexity and need to implement new systems more quickly to achieve
benefits
• New ways for software projects from traditional waterfall model
• Continued evolution in the thinking how to construct software systems
• Choice of methods will be driven by considerations
o Organizational policy,
o Developer knowledge and preference,
o Technology being used.

Structured Techniques
• Traditional, classical SDLC approach
• framework for representing the data and process components of an application
• defining the requirements for a new system:
o Context Diagram, data flow/control flow; data dictionaries; external events; DFDs
• Next level of design
o system flowcharts, inputs/outputs, processing steps and computations, and program
and data file or database specifications
• representation of functions is developed in a modularized top-down fashion

Agile Development

• refers to a family of similar development processes that espouse a nontraditional way of

developing complex systems
• One of the 1st agile processes, Scrum (early 1990s), is a project management approach
• aims to move planning and directing tasks from the project manager to the team
o Leaving the project manager to work on removing the obstacles to the team, achieving
their objectives
• Other agile processes
o Extreme Programming (XP), Crystal, Adaptive Software Development, Feature Driven
Development and Dynamic Systems Development Method.
• and Dynamic Systems Development Method

• “agile” - designed to flexibly handle changes to the system being developed or the project that
is performing the development
• Agile development process have common characteristics
o iteration forms the basis for planning the next iteration
o Sprint: replanning the project at the end of each iteration
o A heavy influence on mechanisms to effectively disseminate tacit knowledge and
promote teamwork.
o stipulate pair-wise programming: sharing knowledge and as quality check
o change in the role of the project manager; responsibility for planning and control is
delegated to the team members.
• Agile development only plans for the next iteration of development in detail
• Agile development’s adaptive approach to requirements does not emphasize managing a
requirements baseline
• Agile development’s focus is to quickly prove an architecture by building actual functionality
versus formally defining
• Agile development assumes limits to defect testing but attempts to validate functions through a
frequent-build test cycle
• Agile development does not emphasize defined and repeatable processes but instead
performs and adapts
Prototyping-Evolutionary Development

• heuristic or evolutionary development, is the process of creating a system through controlled

trial and error procedures
• using prototyping as a risk reduction mechanism
• Combination of classic SDLC (systematic stepwise) and iterative
• initial emphasis during the development of the prototype – reports and screens
o Allows the end user to see a working model of the proposed system

2 basic approach:
• Build the model to create the design – define requirements; then develop the system design
o Pressure to implement an early prototype
o Other information not often understood: transaction volumes, network connectivity,
backup, security and control
• Built the actual system using 4GL-IDE
o Works with small applications

Disadvantages
• Leads to functions or extra being added to the system
• Finished systems will have poor controls
• Change control often becomes much more complicated
IS Auditor
• Know the risk associated
• Look into significant time and cost savings

Rapid Application Development

• methodology that enables organizations to develop strategically important systems quickly while
reducing development costs and maintaining quality
• Techniques includes:
o Small, well-trained development teams
o Evolutionary prototypes
o Integrated power tools that support modeling, prototyping and component reusability
o A central repository
o Interactive requirements and design workshops
o Rigid limits on development time frames
RAD Methodology four major stages:
• concept definition stage
o defines the business functions and data; determines the system scope.
• functional design stage
o uses workshops to model data & processes and build a working prototype of critical system
components
• development stage
o completes the construction of the physical database and application system, builds the
conversion system
• deployment stage
o includes final-user testing and training, data conversion and the implementation of the
application system

Object-Oriented System Development

• process of solution specification and modeling where data and procedures can be grouped into
an entity known as an object
o object’s data are referred to as its attributes
o and its functionality is referred to as its methods
• Contrast with traditional SDLC considered data separately
from the procedures that act on them.
• OOSD is a programming technique, not a software
development methodology.
• A particular programming language, or use of a particular
programming technique, does not imply or require use of a
particular software development methodology.
• Objects usually are created from a general template called a
class.
• OO programming should employ object-oriented analysis
and design approaches
• OOD to join forces and merge approaches into Unified
Modeling Languages (UML)

Component-Based Development
• means assembling applications from cooperating packages of executable software that make
their services available through defined interfaces – objects
• Basic types of components
o In-process client
o Stand-alone client
o Stand-alone server
o In-process server components
• Microsoft and IBM are supporting component-based development
• primary benefit of component-based development is the ability to buy proven, tested software
from commercial developers.
•
 • Reduces development time prewritten components and only code for unique parts of the
system needs to be developed

• Improves quality prewritten components means code been tested already

• Allows developers to focus more strongly on business functionality

• Promotes modularity By encouraging or forcing impassable interfaces between discrete units

of functionality, it encourages modularity.

• Simplifies reuse reusable code to be distributed in an executable format

• Reduces development cost

• Supports multiple development environments Components written in one language can

interact with components written in other languages or running on other machines.

• Allows a satisfactory compromise between build and buy options

Web-Based Application Development

• an important emerging software development approach designed to achieve easier and more
effective integration of code modules within and between enterprises
• Web-based AD and associated XML technologies are designed to further facilitate and
standardize code module and program integration
Software Reengineering

• process of updating an existing system by extracting

and reusing design and program components
• Business process reengineering (BPR)
o is the thorough analysis and significant
redesign of business processes and
management systems to establish a better
performing structure
• Service-oriented software reengineering methodology
o is based upon the service-oriented computer
architecture, and the reengineering processes
apply many concepts of RAD development
leveraging RACI (responsible, accountable, consulted and informed) charts and UML
modeling)

Reverse Engineering

• is the process of studying and analyzing an application, a software application or a product to

see how it functions and to use that information to develop a similar system
• The major advantages of reverse engineering are:
o Faster development and reduced SDLC duration
o possibility of introducing improvements by overcoming the reverse-engineered
application drawbacks
• The IS auditor should be aware of the following risk:
o Software license agreements often contain clauses prohibiting the licensee from reverse
engineering
o Decompilers are relatively new tools with functions that depend on specific computers.
Any change in one of these components may require developing or purchasing a new
decompiler.

Business Application Development

Introduction
• Organization develop, acquire, integrate and maintain application systems that are critical to the
effective functioning of key business processes
• Begins with a feasibility study
• key business drivers: attributes of a business function that drive the behavior and implementation
of that business function to achieve the strategic business goals of the company
• critical business objectives (as a breakdown of the corporate strategy) have to be translated into
key business drivers for all parties involved in business operations during an SDLC project
• IS auditor’s influence is significantly increased when there are formal procedures and guidelines
identifying each phase in the business application life cycle and the extent of auditor
involvement.
• IS auditor can review all relevant areas and phases of the systems development project and
report independently to management on the adherence to planned o
• IS auditor can identify selected parts of the system and become involved in the technical aspects
on the basis of his/her skills and abilities objectives and company procedures.
 • IS auditor can provide an evaluation of the methods and techniques applied through the
development phases of the business application life cycle

Traditional SDLC Approach

• waterfall technique
• oldest and most widely used
• systematic, sequential approach to software development
• Feasibility Study
• Requirements Definition
• Software Selection and Acquisition (purchased systems); or Design (in-house development)
• Configuration (purchased systems); or Development (in-house development)
• Final Testing and Implementation
• Postimplementation
• works best when a project’s requirements are likely to be stable and well defined
• iterative approach: system development in iterations until the entire application is designed,
built and tested.
• purchased packages: design and development phases of the traditional life cycle are being
replaced with the selection and configuration phases

Disadvantage of Traditional Approach

• Unanticipated events. Iteration always occurs and creates problems in implementing the
approach from sequential traditional processes.
• Incorrect and missing explicit set of requirements from the customer/user as the approach
requires
• Conflict in managing user requirements
• working version of the system’s programs will not be available until late in the project’s life cycle
• A changing business environment that alters or changes the customer/user requirements before
they are delivered

Phase 1: Feasibility
• an analysis begins to clearly define the need and to identify alternatives for addressing the need
• development of a business case: which states the strategic benefits of implementing the system
either in productivity gains or in future cost avoidance;
• identifies and quantifies the cost savings of the new system;
• estimates a payback schedule for the cost incurred in implementing the system or shows the
projected ROI.
• Identify Intangible benefits and Tangible benefits should be quantified

Phase 2: Requirement Definition

• concerned with identifying and specifying the business requirements of the system chosen for
development during the feasibility study.
• Requirements include:
o descriptions of what a system should do,
o how users will interact with a system,
o conditions under which the system will operate and the information criteria the system
should meet
• deals with overarching issues that are sometimes called nonfunctional requirements
 • preliminary design of the system may be developed and presented to user management for their
review, modification, approval and endorsement
• User involvement
o clearly defined requirements
o prevent problems such as expending resources on a system that will not satisfy the
business requirements
o necessary to obtain commitment and full benefit from the system
• IS auditors involvement
o determine whether adequate security requirements
o adequate audit trails are defined as part of the system

Phase 3A—Software Selection and Acquisition

• evaluate the risk and benefits of developing a new system versus acquiring from a vendor a
suitable system that is complete, tested and proven
• Consideration
• Project costs and risk
• Benefits of having total ownership
• Control over the new system rather than becoming dependent on a vendor
• Software acquisition is not a phase in the standard SDLC. –acquire rather than develop software
• feasibility study documents and supports the decision to acquire the software

Request for Proposal (RFP) or Invitation to Tender (ITT).

• distributed to appropriate vendors a
• posted via a public procurement medium (Internet or newspaper)
• offers the best solution at the most cost-effective price
• Look into vendor’s:
o Reliability—Are the vendor’s deliverables (enhancements or fixes) dependable?
o Commitment to service—Is the vendor responsive to problems with its product? Does
the vendor deliver on time?
o Commitment to providing training, technical support and documentation for its
product —What is the level of customer satisfaction?
• last step in the acquisition process is to negotiate and sign a contract
• Managing the contract
o major level of effort to ensure that deployment efforts are controlled, measured and
improved on, where appropriate.
o Regular status reporting requirements
o milestones and metrics to be reported against should be agreed on with the vendor
• IS auditors are involved in the software acquisition process
o to determine whether an adequate level of security controls
o ensure data integrity for the information
o Risk involved with the software package includes inadequate audit trails, password
controls and overall security of the application
o ensure that these controls are built into the software application

Implementation Alternatives
Phase 3B—Design
• programming and analyst team is assigned the tasks of
o defining the software architecture depicting a general blueprint of the system
o detailing or decomposing the system into its constituent parts such as modules and
components
• Depending on the complexity of the system several iterations in defining system-level
specifications and coding
• Different Key design phase activities
• Data Design: ERD
• Software Baseline: cutoff point in the design and is also referred to as design freeze
o Scope creep—the process through which requirements change during development
• Software baselining also relates to the point when formal establishment of the software
configuration management process occurs.
• involvement of users during the design phase is limited
o end-user participation in the review of detailed design work products is normally not
appropriate
• detailed design has been completed, including user approvals and software baselining, the
design is distributed to the system developers for coding
• The IS auditor involvement
o system of controls is incorporated into system specifications and test plans,
o evaluating the effectiveness of the design process
o establish a formal software change process that effectively freezes the inclusion of any
changes to system requirements

Phase 4A—Configuration
• System configuration, as it relates to the SDLC, consists of defining, tracking and controlling
changes in a purchased system to meet the needs of the business.
• For ERP systems, the task often
o involves the modification of configuration tables as well as some development,
o integrate into the existing IT architecture

Phase 4B—Development
• Design to begin coding, moving the system one step closer to a final software product
• To enhance the quality of programming activities and future maintenance capabilities, program
coding standards should be applied
• facilitate effective use of structured programming methods and techniques, an online
programming facility should be available as part of an integrated development environment (IDE)
• Testing is an essential part of the development process that verifies and validates that a program

Testing Classification
• Unit testing—The testing of an individual program or module.

• Interface or integration testing—A hardware or software test that evaluates the connection of
two or more components that pass information from one area to another.
 • System testing—A series of tests designed to ensure that modified programs, objects,
database schema, etc., which collectively constitute a new or modified system, function properly.
o Recovery testing, Security testing, Load testing, Volume testing, Stress testing,
Performance testing
• Final acceptance testing—After system tests, the new or modified system is ready for the
acceptance testing
o quality assurance testing (QAT) and user acceptance testing (UAT)
• Other types of testing

Phase 5—Final Testing and Implementation

• the actual operation of the new information system is established and tested
• Final UAT is conducted in this environment.
• certification and accreditation process to assess the effectiveness of the business application at
mitigating risk to an appropriate level
• Implementation Planning: efficient support structure
• End-User Training
• Data Migration
• System Change Over

Direct deployment and cutover Parallel deployment and operation

Phased deployment with direct cutover and parallel operation