Urgent-Draw ERD

wiln89

hoffer_mdm12e_pp_ch01_81710355.pptx

Home >Information Systems homework help >Urgent-Draw ERD

Chapter 1: The Database Environment and Development Process

Modern Database Management

12th Edition

Jeff Hoffer, Ramesh Venkataraman,

Heikki Topi

Chapter 1

1-‹#›

Objectives

Define terms

Name limitations of conventional file processing

Explain advantages of databases

Identify costs and risks of databases

List components of database environment

Identify categories of database applications

Describe database system development life cycle

Explain prototyping and agile development approaches

Explain roles of individuals

Explain the three-schema architecture for databases

Chapter 1

1-‹#›

Definitions

Database: organized collection of logically related data

Data: stored representations of meaningful objects and events

Structured: numbers, text, dates

Unstructured: images, video, documents

Information: data processed to increase knowledge in the person using the data

Metadata: data that describes the properties and context of user data

Chapter 1

1-‹#›

Data by itself is not very useful. It is only after the data has been processed, summarized, and organized that it becomes useful for decision makers and knowledge workers. Processed data becomes information, which is often made available to users in the form of reports or graphical displays.

Metadata really pertains to the underlying structure of the data. When you design a database, you are specifying its metadata. When you populate the database, you are putting data into it.

Figure 1-1a Data in context

Context helps users understand data

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Here we see a report that indicates several types of data entities. We have courses and sections of these courses, and we also have the students that are enrolled in a section. Thus, we have gone from just raw data to some type of useful information by organizing the data.

The concept of an entity is something we will discuss in detail later on.

Graphical displays turn data into useful information that managers can use for decision making and interpretation

Figure 1-1b Summarized data

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Here we see summaries of the data. Rather than individual data units, the data has been processed into aggregates and categories. Sums and averages are typical forms of aggregated data, and this is another way of turning raw data into useful and actionable information.

Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and data context

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Metadata is not data per se. Instead it is a description of how data is to be stored and organized into a database.

Disadvantages of File Processing

Program-Data Dependence

All programs maintain metadata for each file they use

Duplication of Data

Different systems/programs have separate copies of the same data

Limited Data Sharing

No centralized control of data

Lengthy Development Times

Programmers must design their own file formats

Excessive Program Maintenance

80% of information systems budget

Chapter 1

1-‹#›

Prior to the advent of databases, data was stored in individual files, each being used by a separate program. This was the traditional file processing approach to data storage. As business applications became more complex, it became evident that traditional file processing systems had a number of shortcomings and limitations. Database systems were developed to overcome these shortcomings. However, there is still a lot of data that is stored in traditional file systems. Legacy systems still abound with traditional files. Even Excel spreadsheets, which are relatively modern, would be considered to fall within the same category as file systems. Many companies store their important data in myriad spreadsheets, and as their businesses become more complex they run up against the limitations of these storage methods.

Problems with Data Dependency

Each application programmer must maintain his/her own data

Each application program needs to include code for the metadata of each file

Each application program must have its own processing routines for reading, inserting, updating, and deleting data

Lack of coordination and central control

Non-standard file formats

Chapter 1

1-‹#›

Duplicate Data

Chapter 1

1-‹#›

Chapter 1

1-‹#›

If the invoicing system and the order filing system each have their own copy of customer data, then this could lead to inconsistencies. What if one file changes the address or account valance of a customer, but the other one does not? In this case, we would have inconsistent data, which is not good for the organization. Data duplication is one of the biggest problems in data management, and databases are designed to eliminate or reduce duplication.

The Pine Valley Furniture (PVF) company will be an ongoing case in this textbook. You will become very familiar with PVF over time.

Problems with Data Redundancy

Waste of space to have duplicate data

Causes more maintenance headaches

The biggest problem:

Data changes in one file could cause inconsistencies

Compromises in data integrity

Chapter 1

1-‹#›

Although wasted space is an issue with data redundancy, it has become less of a problem over time. Storage media is increasingly becoming less and less expensive.

The real problem, as mentioned earlier, is with data inconsistencies. The term “data integrity” refers to ensuring the validity, security, and availability of a company’s data. Traditional file processing systems have a difficult time ensuring data integrity.

SOLUTION: The DATABASE Approach

Central repository of shared data

Data is managed by a controlling agent

Stored in a standardized, convenient form

Requires a Database Management System (DBMS)

Chapter 1

1-‹#›

Database Management System

DBMS manages data resources like an operating system manages hardware resources

A software system that is used to create, maintain, and provide controlled access to user databases

Order Filing

System

Invoicing

System

Payroll

System

DBMS

Central database

Contains employee,

order, inventory,

pricing, and

customer data

Chapter 1

1-‹#›

The database management system (DBMS) is a software system that is used to create, maintain, and provide controlled access to user databases.

Most current DBMSs are in the form of relational databases, which represent data as a collection of tables in which all data relationships are represented by common values in related tables. We will explore relational databases in detail throughout this course.

Note that because all the data is shared in a central database, there is no longer the need for separate systems and programs to maintain their own copy of the data. This reduces duplication and increases integrity.

Elements of the Database Approach

Data models

Graphical diagram capturing nature and relationship of data

Enterprise Data Model–high-level entities and relationships for the organization

Project Data Model–more detailed view, matching data structure in database or data warehouse

Entities

Noun form describing a person, place, object, event, or concept

Composed of attributes

Relationships

Between entities

Usually one-to-many (1:M) or many-to-many (M:N), but could also be one-to-one (1:1)

Relational Databases

Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships

Chapter 1

1-‹#›

Segment of an enterprise data model

Segment of a project-level data model

Figure 1-3 Comparison of enterprise and project level data models

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Here we see the distinction between an enterprise data model and a project data model. The enterprise-level model is more of a bird’s eye view, and less detailed than a project-level model.

Entities are represented by boxes. Relationships are represented by lines between the boxes. Attributes, which describe the important characteristics of an entity, are represented by the verbiage within the boxes.

One customer may place many orders, but each order is placed by a single customer

 One-to-many relationship

Chapter 1

1-‹#›

Chapter 1

1-‹#›

The relationship between customers and orders is one-to-many. The crows feet of the relationship line indicates the many side of the relationship.

One order has many order lines; each order line is associated with a single order

 One-to-many relationship

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Here we see another one-to-many relationship, this time between orders and order lines.

Note that some of the attributes are underlined and in bold. This typically represents an identifier attribute, which is a unique identifier of the entity. For example, only one customer can have a particular customer ID.

One product can be in many order lines, each order line refers to a single product

 One-to-many relationship

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Another one-to-many (M:N) relationship. There may be several order line for the same product. But each order line belongs is associated with only one product.

Therefore, one order involves many products and one product is involved in many orders

 Many-to-many relationship

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Notice that the Order Line entity has curved corners. This is the symbol of an “associative entity”, so called because it’s main purpose is to represent an association between two other entities. A particular order can involve purchase of many products. Also, a particular product could be involved in many orders. In a sense an associative entity could be thought of as a combination entity/relationship.

We will discuss many of these concepts in detail when we get to chapter 2.

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Note that the enterprise model doesn’t contain some details, including the attributes of the entities, as well as the associative entities. In this sense it is a more summarized view, and simpler to look at. This is in contrast to the project-level model which is much more detailed.

Advantages of THE DatabaSE APPROACH

Program-data independence

Planned data redundancy

Improved data consistency

Improved data sharing

Increased application development productivity

Enforcement of standards

Improved data quality

Improved data accessibility and responsiveness

Reduced program maintenance

Improved decision support

Chapter 1

1-‹#›

There are many advantages of databases of traditional files.

Costs and Risks of the Database Approach

New, specialized personnel

Installation and management cost and complexity

Conversion costs

Need for explicit backup and recovery

Organizational conflict

Chapter 1

1-‹#›

But there are also costs and risks. It is not an easy thing to convert from traditional file processing systems to databases. Although the end result is usually beneficial to the organization, the costs of converting are significant, especially for a large company.

Figure 1-5 Components of the database environment

Chapter 1

1-‹#›

Data modeling and design tools are automated tools used to design databases and application programs. These tools help with creation of data models and in some cases can also help automatically generate the “code” needed to create the database.

A repository is a centralized knowledge base for all data definitions, data relationships, screen and report formats, and other system components. In other words, the metadata resides in the repository.

DBMS is a software system that is used to create, maintain, and provide controlled access to databases. A database is an organized collection of logically related data, usually designed to meet the information needs of multiple users in an organization. A database is not the same as a repository. The database contains data, whereas the repository contains metadata. In other words, the repository defines the structure of the data.

Application programs interact with the database to provide functionality of use for the company. Relating this to figure 1-2, the application programs fulfill the order processing, invoicing, and payroll functions.

The user interface includes languages, menus, and other facilities by which users interact with various system components. Most application programs will involve user interfaces, because they will be directly used by people (users). However, not all applications involve user interfaces; some are run as batch programs in the background. For example, a program that prints paychecks may not include a user interface.

Different types of users include data and database administrators, system developers, and end users. The data administrators manage the data and database. The developers create the application programs. The end users are peope who use the systems for various business functions. These can include accountants, sales people, managers, etc. Database administrators and system developers are IT people, and their principal clientele involve end users.

Components of the Database Environment

Data modeling and design tools -- automated tools used to design databases and application programs

Repository–centralized storehouse of metadata

Database Management System (DBMS) –software for managing the database

Database–storehouse of the data

Application Programs–software using the data

User Interface–text, graphical displays, menus, etc. for user

Data/Database Administrators–personnel responsible for maintaining the database

System Developers–personnel responsible for designing databases and software

End Users–people who use the applications and databases

Chapter 1

1-‹#›

Enterprise Data Model

First step in the database development process

Specifies scope and general content

Overall picture of organizational data at high level of abstraction

Entity-relationship diagram

Descriptions of entity types

Relationships between entities

Business rules

Chapter 1

1-‹#›

FIGURE 1-6 Example business function-to-data entity matrix

Chapter 1

1-‹#›

Chapter 1

1-‹#›

How do we determine the type of data that is required and who within the organization needs what data? Often this is done using matrixes. One type of matrix matches business functions with the data entity types they need; this is called a function-to-data-entity matrix.

Two Approaches to Database and IS Development

SDLC

System Development Life Cycle

Detailed, well-planned development process

Time-consuming, but comprehensive

Long development cycle

Prototyping

Rapid application development (RAD)

Cursory attempt at conceptual data modeling

Define database during development of initial prototype

Repeat implementation and maintenance activities with new prototype versions

Chapter 1

1-‹#›

The traditional SDLC came from the days before advanced technologies made rapid application development possible. Prototyping and its variants (such as agile, scrum, and extreme programming) came about much later in the history of information technology. Many projects combine various elements from SDLC and prototyping. In the next set of slides we’ll look at the steps in each approach.

Systems Development Life Cycle (see also Figure 1-7)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Chapter 1

1-‹#›

The SDLC is sometimes called a “waterfall” approach. The outputs from one phase flow down to the next. But as you can see it is also a cycle. At any stage in the process, it is possible and sometimes necessary to return to a prior stage.

Systems Development Life Cycle (see also Figure 1-7) (cont.)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Planning

Purpose–preliminary understanding

Deliverable–request for study

Database activity–

enterprise modeling and early conceptual data modeling

Chapter 1

1-‹#›

Each of these slides show the general purposes and deliverables from a phase in the top right corner. This applies to all types of system development, not just databases. In the lower left we see the database-specific activities. As we saw in previous slides, the first step involves enterprise modeling. In terms of early conceptual data modeling, we will be a high level, so we will likely be creating enterprise-level models, not project-level.

Systems Development Life Cycle (see also Figure 1-7) (cont.)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Analysis

Purpose–thorough requirements analysis and structuring

Deliverable–functional system specifications

Database activity–thorough and integrated conceptual data modeling

Chapter 1

1-‹#›

After planning comes a more in-depth study called analysis. The data models developed here are more detailed (project-level), including more entities, attributes and relationships than the enterprise models.

Analysis includes detailed study, interviews, requirements elicitation, and document review, as well as studying the current system. The output from this involves a detailed set of specifications for what the desired system should do.

Systems Development Life Cycle (see also Figure 1-7) (cont.)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Purpose–information requirements elicitation and structure

Deliverable–detailed design specifications

Database activity–

logical database design (transactions, forms, displays, views, data integrity and security)

Chapter 1

1-‹#›

Whereas analysis involves identifying what the system should do, the logical design is concerned with specifying how the system is going to do it. From a database perspective, analysis involves drawing the various entity-relationship data models, whereas logical design involves defining the tables, screenshots, metadata, etc. of the finalized system.

Systems Development Life Cycle (see also Figure 1-7) (cont.)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Physical Design

Purpose–develop technology and organizational specifications

Deliverable–program/data structures, technology purchases, organization redesigns

Database activity–

physical database design (define database to DBMS, physical data organization, database processing programs)

Chapter 1

1-‹#›

After logical design comes physical design. This can include the development or acquisition of application programs. From a database perspective, we have decided on the physical database platform (e.g. Oracle, SQL Server, mySql) and written much of the SQL for actually creating and manipulating our data structure.

Systems Development Life Cycle (see also Figure 1-7) (cont.)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Implementation

Purpose–programming, testing, training, installation, documenting

Deliverable–operational programs, documentation, training materials

Database activity–

database implementation, including coded programs, documentation, installation and conversion

Chapter 1

1-‹#›

Often “physical design” and “implementation” overlap. There’s still programming and more SQL that takes place during implementation. But other activities include installing the finished product on the production environment (previously it was only in test), and also preparing users through documentation and training. At the end of implementation is when the system is actually “up and running”.

Systems Development Life Cycle (see also Figure 1-7) (cont.)

Planning

Analysis

Physical Design

Implementation

Maintenance

Logical Design

Maintenance

Purpose–monitor, repair, enhance

Deliverable–periodic audits

Database activity–

database maintenance, performance analysis and tuning, error corrections

Chapter 1

1-‹#›

After implementation comes maintenance. This is typically by far the longest phase, because maintenance lasts throughout the life time of the operational system. During this time there will be needs for enhancements, bug fixes, and problem-solving of various sorts. Sometimes you can think of enhancements as little mini-SDLCs that produce the needed improvements to the system.

Prototyping Database Methodology (Figure 1-8)

Prototyping is a classical Rapid Application Development (RAD) approach

Chapter 1

1-‹#›

Prototyping takes a different approach to system development. It is less formal and more ad hoc, involving iterations of coding and using and evolving. The concept of prototyping has evolved into several rapid application development variations, as we’ll discuss later.

Unlike the traditional SDLC, there is less of an emphasis on up-front planning and analysis and more of an experimentation flavor to the development process.

Prototyping Database Methodology (Figure 1-8)

Chapter 1

1-‹#›

First, you identify the problem. This is sort of a condensed blend of the planning an analysis phases of the SDLC. Out of this comes initial requirements.

The database activity for this phase is to come up with ER models. But these are much less formalized an complete than in a typical SDLC. They serve more as “sketches” of the system, with the understanding that these sketches wiil evolve over time.

Prototyping Database Methodology (Figure 1-8)

Chapter 1

1-‹#›

Based on the ER models and system requirements develop the initial prototype. Her you dive right into coding and development. This is like logical design, physical design, and implementation rolled into one.

Prototyping Database Methodology (Figure 1-8)

Chapter 1

1-‹#›

This is followed by a series of iterations where you use the prototype, then modify it, then use it, then modify it. Hopefully, by the end of the process, you can use the system.

But even if the system can’t be used do to scalability of efficiency reasons, it provides a starting point for developing the operational system.

Prototyping Database Methodology (Figure 1-8)

Chapter 1

1-‹#›

Database maintenance will occur throughout the operational lifetime of the system, just like in the SDLC methodology.

Other Rapid Application (RAD) Approaches

Agile – emphasizes “individuals and interactions over processes and tools, working software over comprehensive documentation, customer collaboration over contract negotiation, and response to change over following a plan.” (The Agile Manifesto)

Examples of agile programming methodologies

eXtreme programming

Scrum

DSDM Consortium

Feature-driven development

Chapter 1

1-‹#›

Prototyping led to many RAD approaches. The “Agile Manifesto” was a statement that espoused a radical free-flowing and flexible approach to systems development. Here is a list of some of the methodologies.

Database Schema

External Schema

User Views

Subsets of Conceptual Schema

Can be determined from business-function/data entity matrices

DBA determines schema for different users

Conceptual Schema

E-R models–covered in Chapters 2 and 3

Internal Schema

Logical structures–covered in Chapter 4

Physical structures–covered in Chapter 5

Chapter 1

1-‹#›

The term “schema” here refers to the view you have of the system being developed. Users of a system have one view, based on the reports, forms, and interactions they have with the system. Designers have s view of the entities, attributes, and relationships involved in the world being modeled (the conceptual schema), as well as a view of tables, fields, primary and foreign keys, together with an understanding of what makes a database well structured and efficient (the internal schema).

Different people have different views of the database…these are the external schema

The internal schema is the underlying design and implementation

Figure 1-9 Three-schema architecture

Chapter 1

1-‹#›

Chapter 1

1-‹#›

If you are a database designer, you will be extensively involved in the conceptual schema logical and physical pieces of the internal schema. You will be particularly involved in the physical schema if you are a database administrator (DBA). If you are a user, these schemas don’t matter much to you; rather you will be involved in the external schema.

Managing People and Projects

Project–a planned undertaking of related activities to reach an objective that has a beginning and an end

Initiated and planned in planning stage of SDLC

Executed during analysis, design, and implementation

Closed at the end of implementation

Chapter 1

1-‹#›

Project management is an important skill in any information systems project, and managing the database design and implementation tasks is part of project management.

Projects often are organized into tasks and subtasks, each of which is scheduled for an expected time period, and assigned to various people involved in the project. It is up to the project manager to monitor the progress and cost of the project.

Managing Projects: People Involved

Business analysts

Systems analysts

Database analysts and data modelers

Users

Programmers

Database architects

Data administrators

Project managers

Other technical experts

Chapter 1

1-‹#›

There are many different people, each with different perspectives, skills, and needs, involved in a systems development project.

Figure 1-10a Evolution of database technologies

Chapter 1

1-‹#›

Over the years since the advent of computer data processing systems in business, there have been many approaches to database technology. We already discussed flat files and their problems. Early attempts to solve these problems led to hierarchical and network databases. Legacy systems, those initially created in the 1950s and 1960s sometimes still use these earlier technologies.

The relational database is the most common form, especially for business applications. However, others also exist, including object-oriented and object-relational. Data warehousing is commonly used for managerial decision making.

Evolution of Database Systems

Driven by four main objectives:

Need for program-data independence  reduced maintenance

Desire to manage more complex data types and structures

Ease of data access for less technical personnel

Need for more powerful decision support platforms

Chapter 1

1-‹#›

Here are some of the motivations that led to the evolution of database systems.

Figure 1-10b Database architectures

Chapter 1

1-‹#›

The hierarchical and network database models was the first attempt to structure data according to relationships between entities. But they fell short because they were very inflexible. For example, many-to-many relationships are impossible in hierarchical databases, and although they are possible in network models they are difficult to modify in these structures.

Figure 1-10b Database architectures (cont.)

Chapter 1

1-‹#›

The relational model is the most ubiquitous, and represents relationships as primary-to-foreign key associations in different “relations”. Here we see some confusing terminology. There is the concept of “relationship”, which was shown as lines between boxes in figure 1-3. And there is another concept of “relation”, which is really like a database table. We’ll see this distinction later in the semester.

Object-oriented databases are interesting in that they allow for a sort of inheritance between classes and subclasses. Also, unlike relations (tables) in relational databases, objects in object-oriented databases are capable of behaviors (program code) in the form of “methods”. If you take a course in Java or another object-oriented language, you will become familiar with these ideas.

Figure 1-10b Database architectures (cont.)

Chapter 1

1-‹#›

Multidimensional models are typically based on data warehouses, and are used for decision support purposes. The term Online Analytical Processing (OLAP) refers to the types of systems that use multidimensional data.

The Range of Database Applications

Personal databases

Two-tier and N-tier Client/Server databases

Enterprise applications

Enterprise resource planning (ERP) systems

Data warehousing implementations

Chapter 1

1-‹#›

Here are some size ranges of typical database applications. Personal databases are often done in Microsoft Access. Multitier client/server and ERP systems are typical for the normal operational activities of most companies. Data warehouses tend to be large because they collect and maintain historical data over time.

Figure 1-11 Multi-tiered client/server database architecture

Chapter 1

1-‹#›

Chapter 1

1-‹#›

When we get to chapter 8, we will see how databases are used in applications. Most web applications follow the 3-tier approach. Databases are typically at an enterprise tier, application program code is at an application/Web tier, and user interfaces for different users are at the client tier.

Enterprise Database Applications

Enterprise Resource Planning (ERP)

Integrate all enterprise functions (manufacturing, finance, sales, marketing, inventory, accounting, human resources)

Data Warehouse

Integrated decision support system derived from various operational databases

Chapter 1

1-‹#›

FIGURE 1-13 Computer

System for Pine Valley

Furniture Company

Chapter 1

1-‹#›

Chapter 1

1-‹#›

Throughout the textbook, you will see many descriptions of Pine Valley Furniture. This figure shows a typical system configuration. Note the 3-tier architecture, with a database server, an application server, and client workstations for customers and employees in different departments.

FIGURE 1-15 Project data model for Home Office product line marketing support system

Chapter 1

1-‹#›

Chapter 1

1-‹#›

This data model shows some of the main entities for PVF, as well as their attributes and relationships. Over time you will see many other diagrams and descriptions of PVF.

Chapter 1

1-‹#›

Chapter 1

1-‹#›