The Database Environment

Objectives

Definition of terms Explain growth and importance of databases Name limitations of conventional file processing Identify categories of databases Explain advantages of databases Identify costs and risks of databases List components of database environment Describe evolution of database systems
2

Definitions

Data: stored representations of meaningful objects and events. Data are Facts that could be recorded on computer media.

Structured: numbers, text, dates Unstructured: images, video, documents

Database: organized collection of logically related data. Organised means data are structured so as to be easily stored, manipulated and retrieved by users. Related means data describe a domain of interest to a group of users who want to query that data.
3

Query: A statement requesting retrieval of information. Information: data processed to increase knowledge in the person using the data Metadata: data that describes the properties and context of user data DBMS: Data along with set of programs which manage this data. These programs enable user to create and maintain a database. The management of data include add, delete, modify, retrieve data.
4

Figure 1-1a Converting data into information : Data in Context

C O N T E X T

D A T

A Context helps users understand data

5

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

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

Disadvantages of File Processing

Program-Data Dependence

All programs maintain metadata for each file they use

Data Redundancy (Duplication of Data)

Different systems/programs have separate copies of the same data Gives rise to inconsistency (data updated at one place & not at other)
No centralized control of data, difficulty in accessing data Programmers must design their own file formats Programs are changed/added to get new information required 80% of of information systems budget

Limited Data Sharing (Data isolation)

Lengthy Development Times

Excessive Program Maintenance

Problems in concurrent access, atomicity & security of data

8

Problems with Data Dependency

Each application programmer must maintain their 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

Figure 1-2 Three file processing systems at Pine Valley Furniture

Duplicate Data

10

Problems with Data Redundancy

Waste of space to have duplicate data Causes more maintenance headaches The biggest problem:

When data changes in one file, could cause inconsistencies Compromises data integrity (implementation of Constraints on data)

11

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)

12

Database Management System

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

DBMS manages data resources like an operating system manages hardware resources
13

14

15

Costs and Risks

Requires specialised persons to design and implement database, database administration services, managing new staff High initial cost as database system is large and complex multiuser system, may require hardware and software upgradation, more cost for maintenance and support, data security and sharing, training for upgrades Cost in converting legacy system (file processing or older technology) to modern database technology Cost for providing backup copies of data and for restoring database when any failure occures Resolve conflicts on data definitions, formats and coding, responsibilities and rights for data maintenance for sound database development 16

Elements of the Database Approach

Enterprise Data Model

Graphical model showing high-level entities and relationships for the organization. Provides information about organisational data, processes and constraints Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships Networks and telecommunications, distributed databases, clientserver and 3-tier architectures Application programs used to perform database activities (create, read, update, and delete) for database users

Relational Databases

Use of Internet Technology

Database Applications

17

18

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

19

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

One-to-many relationship

20

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

21

Therefore, one order involves many products and one product is involved in many orders Many-to-many relationship

22

Relationships established in special columns that provide links between tables

23

Client/server system architecture

24

Application program functions: inserting new data, updating existing data, deleting existing data, reading data for display
25

26

Range of Database Applications

1. Personal to support one user, on PCs, laptops or handheld devices. e.g. salesman Issues application purchased from outside or developed within organisation, what and how data should be designed, which DBMS should be used, how to synchronise data with other databases- difficult to share data 2. Workgroup Team of people working on same or similar project/applications Issues optimization of database for variety of group members information requirements, concurrent users, operations on server & client 27

3. Department Databases- Functional unit within organisation e.g. personnel, marketing accounting etc., diverse range of functions & activities of department Issues performance with many users and transactions, security against unauthorised disclosure, database and tools for this complex environment, sharing data among departments, whether to use distributed database 4. Enterprise database : scope is entire organisation for operations and decision making ERP and Data warehousing implementations Issues- distribution of data, maintenance of standards, integration of various systems 5. Internet, Intranet and Extranet databases
28

Range of Database Applications

29

Figure 1-9 Workgroup database with local area network

30

Figure 1-10 An enterprise data warehouse

31

Components of the Database Environment

CASE Tools computer-aided software engineering Repository centralized storehouse of metadata Database Management System (DBMS) software for managing the database (define, create, maintain and provide controlled access to database and repository) Database storehouse of the data i.e. organised collection of logically related data to meet information needs of organisation Application Programs software using the data User Interface text and graphical displays to users Data Administrators personnel responsible for maintaining the database (e.g. systems analysts, programmers) System Developers personnel responsible for designing databases and software End Users people who use the applications and databases32

Figure 1-11 Components of the database environment

33

Evolution of DB Systems

Objectives of development and evolution

Increase independence between programs and data Manage complex data types and structures Provide easier and faster access to data and to users not having programming background

34

Evolution of DB Systems

Flat files - 1960s - 1980s Hierarchical 1970s - 1990s Network 1970s - 1990s Relational 1980s - present Object-oriented 1990s - present Object-relational 1990s - present Data warehousing 1980s - present Web-enabled 1990s - present
35

Evolution of DB Systems

1970s- Drawbacks of hierarchical and network models Difficult to access data because of one record at a time access & hence complex programs Limited data independence Lack of widely accepted theoretical foundation

36

Evolution of DB Systems

1980s- Relational model introduced Data represented in the form of tables Supporting SQL for data retrieval & ease of access for non-programmers Based on strong foundation of relational algebra

37

Evolution of DB Systems

1990s- Client server computing and internet became popular

Multimedia data became common Object oriented databases Data warehousing

Multidimensional data Universal servers based on object relational DBMS Advancements in distributed databases, AI, Data mining
38

Beyond 2000