Database Fundamentals- Content Analysis
wostinabin2
Week9-Lectureslides-20200617.zipLecture9-Chap10- Database Development Process.pptx
Database Principles: Fundamentals of Design, Implementations and Management
Lecture9-CHAPTER 10 : Database Development Process
In this chapter, you will learn:
That successful database design must reflect the information system of which the database is a part
That successful information systems are developed within a framework known as the Systems Development Life Cycle (SDLC)
That within the information system, the most successful databases are subject to frequent evaluation and revision within a framework known as the Database Life Cycle (DBLC)
How to conduct evaluation and revision within the SDLC and DBLC frameworks
2
In this chapter, you will learn (cont..):
About database design strategies: top-down vs. bottom-up design and centralized vs. decentralized design
Common threats to the security of the data and what security measures could be put in place
The importance of the database administration in an organization
The technical and managerial roles of the database administrator (DBA)
3
The Information System
Provides for data collection, storage, and retrieval
Composed of people, hardware, software, database(s), application programs, and procedures
Systems analysis
Is the process that establishes the need for and extent of an information system
Systems development
Is the process of creating information system
4
The Information System (cont..)
Applications
Transform data into information that forms the basis for decision making
Usually produce the following:
Formal report
Tabulations
Graphic displays
Composed of following two parts:
Data
Code by which data are transformed into information
5
The Information System (cont..)
6
The Information System (cont..)
Information system performance depends on triad of factors:
Database design and implementation
Application design and implementation
Administrative procedures
Database development
Is the process of database design and implementation
The primary objective is to create complete, normalized, non-redundant (to the extent possible), and fully integrated conceptual, logical, and physical database models
7
The Systems Development Life Cycle (SDLC)
Traces history (life cycle) of information system
Provides “big picture” within which database design and application development can be mapped out and evaluated
Divided into following five phases:
Planning
Analysis
Detailed systems design
Implementation
Maintenance
Iterative rather than sequential process
8
The Systems Development Life Cycle (SDLC) (cont..)
9
Planning
Yields a general overview of the company and its objectives
Is an initial assessment made of information-flow-and-extent requirements
Must begin to study and evaluate alternate solutions
Technical aspects of hardware and software requirements
System cost
10
Analysis
The problems defined during planning phase are examined in greater detail during analysis
Thorough audit of user requirements
The existing hardware and software systems are studied
Goal is a better understanding of :
system’s functional areas,
The actual and potential problems,
and opportunities
11
Analysis (cont..)
Includes the creation of logical system design
Must specify appropriate conceptual data model, inputs, processes, and expected output requirements
Might use tools such as data flow diagrams (DFDs), hierarchical input process output (HIPO) diagrams, and entity relationship (ER) diagrams
Yields functional descriptions of system’s components (modules) for each process within database environment
12
Detailed Systems Design
The designer completes the design of the system’s processes
Includes all necessary technical specifications
The steps are laid out for conversion from old to new system
The training principles and methodologies are also planned
Submitted for management approval
13
Implementation
Hardware, DBMS software, and application programs are installed,
and the database design is implemented
The system enters into:
A cycle of coding,
Testing, and debugging continues until it is ready to be delivered
The actual database is created and the system is customized by
creation of tables and views,
and user authorizations
14
Maintenance
Maintenance activities can be grouped into three types:
Corrective maintenance in response to systems errors
Adaptive maintenance due to changes in business environment
Perfective maintenance to enhance system
Computer-assisted systems engineering (CASE)
Make it possible to produce better systems within reasonable amount of time and at reasonable cost
CASE-produced applications are structured, documented, standardized
15
16
The Database Life Cycle (DBLC)
Six phases:
Database initial study
Database design
Implementation and loading
Testing and evaluation
Operation
Maintenance and evolution
The Database Life Cycle (DBLC)
17
The Database Initial Study
Overall purpose:
Analyze company situation
Define problems and constraints
Define objectives
Define scope and boundaries
Fig 10.4 in the next slide depicts the interactive and iterative processes required to complete first phase of DBLC successfully
18
The Database Initial Study (cont..)
Fig 10.4 in your book
19
Analyze the Company Situation
Analysis–To break up any whole into its parts so as to find out their nature, function, and so on
Company situation
General conditions in which company operates, its organizational structure, and its mission
Analyze company situation
Discover what company’s operational components are, how they function, and how they interact
20
Define Problems and Constraints
Managerial view of company’s operation is often different from that of end users
The Database Designer must continue to carefully probe to generate additional information that will help define problems within larger framework of company operations
Finding precise answers is important
Defining problems does not always lead to perfect solution
21
Define Objectives
Designer must ensure that database system objectives correspond to those envisioned by end user(s)
Designer must begin to address following questions:
What is proposed system’s initial objective?
Will system interface with other existing or future systems in the company?
Will system share data with other systems or users?
22
Define Scope and Boundaries
Scope
Defines extent of design according to operational requirements
Helps define required data structures, type and number of entities, and physical size of database
Boundaries
Limits external to system
Often imposed by existing hardware and software
23
Database Design
Necessary to concentrate on data
Characteristics required to build database model
Two views of data within system:
Business view of
data as information source
Designer’s view of
data structure, its access, and activities required to transform data into information
24
Database Design (cont..)
Fig 10.5 in your book
25
Database Design (cont..)
Loosely related to analysis and design of larger system
The Systems analysts or systems programmers are in charge of designing other system components
Their activities create procedures that will help transform data within database into useful information
Does not constitute sequential process
Iterative process that provides continuous feedback designed to trace previous steps
26
Database Design (cont..)
27
I. Conceptual Design Overview
Data modeling used to create an abstract database structure
that represents real-world objects in most realistic way possible
Must embody clear understanding of business and its functional areas
Ensure that all data needed are in model, and that all data in the model are needed
28
I. Conceptual Design Overview (cont..)
Requires four steps
Data analysis and requirements
Discover data element characteristics
Obtains characteristics from different sources
Take into account business rules
Derived from description of operations
Entity relationship modeling and normalization
Designer enforces standards in design documentation
Use of diagrams and symbols, documentation writing style, layout, other conventions
29
30
I. Conceptual Design Overview (cont..)
3. Data model verification
Verified against proposed system processes
Revision of original design
Careful reevaluation of entities
Detailed examination of attributes describing entities
Define design’s major components as modules:
Module: information system component that handles specific function
31
I. Conceptual Design Overview (cont..)
Data model verification (cont…)
Verification process
Select central (most important) entity
Defined in terms of its participation in most of model’s relationships
Identify module or subsystem to which central entity belongs and define boundaries and scope
Place central entity within module’s framework
32
I. Conceptual Design Overview(cont..)
Distributed database design
Portions of the database may reside in different physical locations
Processes accessing the database vary from one location to another
The Designer must also develop data distribution and allocation strategies
II. DBMS Software Selection
Critical to information system’s smooth operation
Common factors affecting purchasing decisions:
Cost
DBMS features and tools
Underlying model
Portability
DBMS hardware requirements
Advantages and disadvantages should be carefully studied
33
III. Logical Design Overview
Used to translate conceptual design into internal model for selected database management system
Logical design is software-dependent
Requires that all objects in model be mapped to specific constructs used by selected database software
Definition of attribute domains, design of required tables, access restriction formats
Tables must correspond to entities in conceptual design
Translates software-independent conceptual model into software-dependent logical model
34
III. Logical Design Overview (cont..)
The logical design stage consists of the following phases:
Creating the logical data model.
Validating the logical data model using normalization.
Assigning and validating integrity constraints.
Merging logical models constructed for different parts for the database together.
Reviewing the logical data model with the use
35
IV. Physical Design Overview
Is the Process of selecting data storage and data access characteristics of database
Storage characteristics are function of device types supported by hardware, type of data access methods supported by system, and DBMS
Particularly important in older hierarchical and network models
Becomes more complex when data are distributed at different locations
36
IV. Physical Design Overview (cont..)
Physical database design can be broken down into a number of stages:
Analyze data volume and database usage.
Translate each relation identified in the logical data model into tables.
Determine a suitable file organization.
Define indexes.
Define user views.
Estimate data storage requirements.
Determine database security for users.
37
Implementation and Loading
New database implementation requires creation of special storage-related constructs to house end-user tables
38
Performance
Is one of the most important factors in certain database implementations
Not all DBMSs have performance-monitoring and fine-tuning tools embedded in their software
Performance evaluation is rendered more difficult as there is no standard measurement for database performance
39
Backup and Recovery
Database can be subject to data loss through unintended data deletion and power outages
Data backup and recovery procedures
Create safety valve
Allow database administrator to ensure availability of consistent data
Integrity
Enforced through proper use of primary and foreign key rules
40
Company Standards
May partially define database standards
Database administrator must implement and enforce such standards
Database Security
Data must be protected from access by unauthorized users
Establish security goals
- What are we trying to protect the database from?
- What security related problems are we trying to prevent?
The most common security goals relate to the integrity, confidentiality and the availability of data.
41
Data Security Measures
Physical security allows only authorized personnel physical access to specific areas.
User authentication is a way of identifying the user and verifying that the user is allowed to access some restricted data or application.
achieved through the use of passwords and access rights.
Audit trails are usually provided by the DBMS to check for access violations.
42
Data Security Measures (cont..)
Data encryption
Can be used to render data useless to unauthorised users. ORACLE DBMS has a Transparent Data Encryption
User-defined policies and procedures
Backup and recovery strategies should be in place in the event of a disaster occurring
Antivirus software
Firewalls are systems comprising of hardware devices or software applications which act as gatekeepers to an organisation’s network.
For more details on security measures read the slides after the chapter summary
43
Testing and Evaluation
This phase occurs in parallel with applications programming
Programmers use database tools to prototype applications during coding of the programs
If the DB implementation fails to meet some of system’s evaluation criteria, several options may be considered to enhance the system:
Fine-tune specific system and DBMS configuration parameters
Modify physical design
Modify logical design
Upgrade or change DBMS software and/or hardware platform
44
Operation
Once the database has passed the evaluation stage, it is considered operational
The beginning of the operational phase starts the process of system maintenance and evolution
45
Maintenance and Evolution
Required periodic maintenance:
Preventive maintenance (backup)
Corrective maintenance (recovery)
Adaptive maintenance
Assignment of access permissions and their maintenance for new and old users
Generation of database access statistics
Periodic security audits
Periodic system-usage summaries
46
Parallel Activities in the DBLC and the SDLC
47
Summary
Information system is designed to facilitate transformation of data into information and to manage both data and information
SDLC traces history (life cycle) of an application within the information system
DBLC describes history of database within the information system
Database design and implementation process moves through series of well-defined stages
Conceptual portion of design may be subject to several variations, based on two design philosophies
48
Summary (cont..)
Threats to database security include the loss of integrity, confidentiality and availability of data.
The database administrator (DBA) is responsible for managing the corporate database.
The development of the data administration strategy is closely related to the company’s mission and objectives.
49
Threats to Security
Threats are any set of circumstances that have the potential to cause loss, misuse or harm to the system and/or its data.
Threats can cause:
The loss of the integrity of data through unauthorized modification.
For example a person gaining unauthorized access to a bank account and removing some money from the account.
50
Threats to Security
The loss of availability of the data.
For example some adversary causes the database system from being operational which stops authorized users of the data from accessing it.
The loss of confidentiality of the data (also referred to as the privacy of data).
This could be caused by a person gaining access to private information such as a password or a bank account balance.
51
Examples of Threats
Theft and fraud of data.
Human error which causes accidental loss of data.
Electronic infections
Viruses
Email Viruses
Worms
Trojan Horses
52
Examples of Threats (cont..)
The occurrence of natural disasters such as hurricanes, fires or floods.
Unauthorized access and modification of data.
Employee sabotage is concerned with the deliberate acts of malice against the organization.
Poor database administration.
53
Examples of Threats (cont..)
54
Database Design Strategies
Two classical approaches to database design:
Top-down design
Identifies data sets
Defines data elements for each of those sets
Bottom-up design
Identifies data elements (items)
Groups them together in data sets
55
Database Design Strategies Top-down vs. bottom-up design sequencing
56
Centralized vs. Decentralized Design
Database design may be based on two very different design philosophies:
Centralized design
Productive when data component is composed of relatively small number of objects and procedures
Decentralized design
Used when data component of system has considerable number of entities and complex relations on which very complex operations are performed
57
Centralized vs. Decentralized Design Centralized Design
58
Decentralized Design
59
Centralized vs. Decentralized Design (cont..)
Aggregation process
Requires designer to create single model in which various aggregation problems must be addressed:
Synonyms and homonyms
Entity and entity subtypes
Conflicting object definitions
60
Centralized vs. Decentralized Design Summary of aggregation problems
61
Database Administration
Data management is a complex job
Led to the development of the database administration function.
The person responsible for the control of the centralized and shared database is the database administrator (DBA).
62
DBA Activities
Database planning, including the definition of standards, procedures and enforcement.
Database requirements gathering and conceptual design.
Database logical design and transaction design.
63
DBA Activities (cont..)
Database physical design and implementation.
Database testing and debugging.
Database operations and maintenance, including installation, conversion and migration.
Database training and support.
64
The DBA in the Organisation
65
DBA Skills
66
The Managerial Role of the DBA
67
The Managerial Role of the DBA (cont..)
End-User Support
Gathering user requirements
Building end-user confidence.
Resolving conflicts and problems.
Finding solutions to information needs.
Ensuring quality and integrity of applications and data.
Managing the training and support of DBMS users.
68
The Managerial Role of the DBA (cont..)
Policies, Procedures and Standards
Policies are general statements of direction or action that communicate and support DBA goals.
Standards are more detailed and specific than policies and describe the minimum requirements of a given DBA activity.
Procedures are written instructions that describe a series of steps to be followed during the performance of a given activity.
69
The Managerial Role of the DBA (cont..)
Data Security, Privacy and Integrity
Protecting the security and privacy of the data in the database is a function of authorization management.
Authorization management defines procedures to protect and guarantee database security and integrity.
Includes: user access management, view definition, DBMS access control and DBMS usage monitoring.
70
The Managerial Role of the DBA (cont..)
Data Backup and Recovery
Many DBA departments have created a position staffed by the database security officer (DSO).
The DSO’s activities are often classified as disaster management.
Disaster management includes all of the DBA activities designed to secure data availability following a physical disaster or a database integrity failure.
Disaster management includes all planning, organizing and testing of database contingency plans and recovery procedures.
71
The Managerial Role of the DBA (cont..)
Data Distribution and Use
The DBA is responsible for ensuring that the data are distributed to the right people, at the right time and in the right format.
72
The Technical Role of the DBA
Evaluating, selecting and installing the DBMS and related utilities.
Designing and implementing databases and applications.
Testing and evaluating databases and applications.
Operating the DBMS, utilities and applications.
Training and supporting users.
Maintaining the DBMS, utilities and applications.
73
Evaluating, Selecting and Installing the DBMS and Utilities (DBA)
Covers the selection of the database management system, utility software and supporting hardware for use in the organization.
Must be based primarily on the organization’s needs
The DBA would be wise to develop a checklist of desired DBMS features.
74
Designing and Implementing Databases and Applications (DBA)
Covers data modelling and design services to the end-user community
Determine and enforce standards and procedures to be used.
DBA then provides the necessary assistance and support during the design of the database at the conceptual, logical and physical levels
75
Testing and Evaluating Databases and Applications (DBA)
The DBA must also provide testing and evaluation services for all of the database and end-user applications.
Those services are the logical extension of the design, development and implementation services.
Testing procedures and standards must already be in place before any application program can be approved for use in the company.
76
Operating the DBMS, Utilities and Applications (DBA)
DBMS operations can be divided into four main areas:
System support.
Performance monitoring and tuning.
Backup and recovery.
Security auditing and monitoring.
77
Training and Supporting Users (DBA)
Training people to use the DBMS and its tools is included in the DBA’s technical activities.
The DBA also provides or secures technical training in the use of the DBMS and its utilities for the applications programmers.
78
Maintaining the DBMS, Utilities and Applications (DBA)
The maintenance activities of the DBA are an extension of the operational activities.
Maintenance activities are dedicated to the preservation of the DBMS environment.
79
