Database Systems: Chapter 9 - Database Design

Front 1

What is an information system?

Back 1

The database is part of a larger whole known as the information system, which provides for data collection, storage and retrieval.
The information system also facilitates the transformation of data into information  and it allows for the management of both data and information. A main purpose of this is to support decision making.
A complete information system is composed of:
people, hardware, software, the database(s)/DBMS, application programs & procedures

Front 2

What is systems analysis?

Back 2

Systems analysis is the process that establishes the need for and the extent of an information system

Front 3

What is systems development?

Back 3

Systems development is the process of creating an information system.

Front 4

Upon what factors does the performance of an information system depend?

Back 4

- database design and implementation.
- application design and implementation.
- administrative procedures

Front 5

Why are systems analysis and development important?

Back 5

Creating a sound information system is difficult and both systems analysis and development require much planning to ensure all activities will:
- interface with each other.
- compliment each other.
- be completed on time

Front 6

How is the information system developed and maintained?

Back 6

The information system is developed and maintained within a framework called the System Development Life Cycle (SDLC) which manages the changing needs of the information system.
The Database Life Cycle (DBLC) is the management of change in the database.

Front 7

Describe the SDLC in general terms.

Back 7

The Systems Development Life Cycle traces the history/life cycle of an information system and provides the big picture within which database design and application development can be mapped out and evaluated.
It is the larger framework within which the Database Life Cycle exists.

Front 8

What are the 5 phases of the SDLC?

Back 8

The SDLC is divided into 5 phases: planning, analysis, detailed systems design, implementation, and maintenance. (PADIM)

Front 9

Describe the SDLC Planning phase.

Back 9

- General overview of organisation and objectives
- starting point for evaluating alternative solutions
- Initial assessment and feasibility study
- gets and overview of business rules
- determining costs in terms of technical requirements, systems cost, operational cost

Front 10

Describe the SDLC Analysis phase.

Back 10

- Examine problems defined in planning stage in greater detail
- an audit of user requirements
- study existing hardware and software systems
- includes creation of a logical systems design to specify appropriate conceptual data model, inputs, processes and expected outputs
- may involve use of data flow diagram (DFDs) ERDs, UML to discover and describe all entities, attributes and relationships.

Front 11

Describe the Detailed Systems Design Phase.

Back 11

- completion of design of system's processes.
- technical specifications (screens, reports, menus etc)
- pathway from old system to new laid out
- training principles and methodologies

Front 12

Describe the Implementation Phase.

Back 12

- Deployment of hardware, DBMS, application programs
- Database design implemented, database created and customised (views, tables, user authorisation etc)
- cycle of coding, testing and debugging
- after testing concluded, final documentation released and users trained and system becomes fully operational.

Front 13

Describe the Maintenance Phase.

Back 13

Change requests from users generate system maintenance activities:
- corrective maintenance: in response to systems errors
- adaptive maintenance: due to changes in business environment
- perfective maintenance: to enhance system
- each request for change requires retracing SDLC steps

Front 14

How are Computer Aided System Engineering (CASE) tools used in designing information systems?

Back 14

Computer-aided systems engineering tools (CASE. e.g. Visio,) make it possible to produce better systems in reasonable time for a reasonable cost. CASE produced applications are more structured, documented and standardised.

Front 15

What is the Database Life Cycle (DBLC)?

Back 15

Within the larger information system, the database too, is subject to a lifecycle. The Database Life Cycle (DBLC) contains six phases: database initial study, database design, implementation and loading, testing and evaluation, operation, and maintenance and evolution.(DDITOM)

Front 16

Describe the DBLC Database Initial Study phase.

Back 16

- Analyse the company situation:  organisation's structure, objectives, operations; understanding of various functional units.
-define problems and constraints: differing views of managers/end users, need to understand database needs in context of whole organisation, very important to understand the situation and objective of the DBMS
- define objects: what is proposed systems initial objective, will it interface with other existing or future systems; will it share data with other systems or users
-define scope and boundaries: scope defines the extent of the design according to operational requirements; boundaries are the external limits to the system  e.g. budget, personnel, hardware/software

Front 17

Describe the DBLC Database Design phase.

Back 17

Create the conceptual design: Data analysis and requirements, entity relationship modelling and normalisation, data model verification and possibly distributed database design*
- DBMS software selection: factors include cost, features/tools, underlying model, portability and hardware requirements
- create the logical design: translate conceptual model into the internal model - definitions for tables, views, transactions and so on
-create the physical design: define storage structure and access paths for optimum performance

Front 18

Describe the DBLC Implementation and Loading phase.

Back 18

- install the DBMS
- create the database(s), tables, definitions of indexes, triggers, stored procedures. Define access control and create reports
- load or convert the data
- Manage performance issues: running of DBMS, de-normalisation, "self-tuning" facilities
- Security concerns: authorizations, views, read/write privileges
- backup and recovery mechanisms to be defined

Front 19

Describe the DBLC Testing & Evaluation phase.

Back 19

- test the database for performance, integrity, concurrent access and security constraints
- fine tune the database
- evaluate the database and its application programs
- physical and logical design can be changed
- may require more hardware or software patches

Front 20

Describe the DBLC Operation phase.

Back 20

- DB is deployed or goes "live"
- start of system evolution
- DBA ensures day to day operations of operational DB

Front 21

Describe the DBLC Maintenance and Evolution phase.

Back 21

- three types of maintenance: preventative (backup) corrective (recovery) and adaptive (enhance performance, add entities/attributes etc)
- Adjust DBMS based on operational statistics
- Security audits

Front 22

What are two classical approaches to database design?

Back 22

- Top-down design
- Bottom-up design

Front 23

Describe Top-down design.

Back 23

Starts by identifying the data sets, and then defines the data elements for each of those sets. The process involves the identification of different entity types and the definition of each entity's attributes. The ER modelling process can be considered in some ways top be top-down.

Front 24

Describe Bottom-up design.

Back 24

First identifies the data elements (items) and then groups them together in data sets. In other words it first defines attributes and then groups them to form entities. The normalisation process can be considered in some ways to be bottom-up.

Front 25

How could you compare Top-down & Bottom-up design.

Back 25

The selection of one or the other approach often depends on the scope of the problem or personal preferences. Can be considered to be complimentary rather than mutually exclusive, a primary emphasis on the bottom-up approach may be more productive for small databases with fewer entities, attributes, relations and transactions. For situations where the number, variety and complexity to entities, relations and transactions is overwhelming, a primarily top-down approach may be more easily managed.

Front 26

What are other basic database design philosophies?

Back 26

Centralised and decentralised design.

Front 27

Describe Centralised design.

Back 27

- productive when data component is composed of small number of objects and procedures.
- typical of relatively simple and/or small databases
- can be managed by a single person or small team
- organisations operations and requirements sufficiently simple to allow a single designer to define problems, create and verify conceptual design, and ensure compliance with requirements

Front 28

Describe Decentralised design.

Back 28

might be used when data component of system has a considerable number of entities and complex relations on which complex operations are performed
- also likely to be used when problem spread across several operational sites and each element is a subset of the entire data set.
- in large complex projects, db design usually not done by a single person. Requires team of db designers, division of design task into several modules
- due to design groups focus on modelling a subset of the whole system, boundary definition and interrelation between data sets must be very precise.
- each design group creates a conceptual data model corresponding to data subset , verified against user views processes and constraints for each of the modules
- after verification, all modules integrated into one conceptual model.
- the data dictionary plays a vital role in integration process as it describes the characteristics of all objects within the conceptual data model
- aggregation process requires addressing problems such as:
synonyms and homonyms - different departments may refer to same object by different names or use the same name for different objects
- entity and entity subtypes - e.g. SECRETARY & PILOT are subsets of EMPLOYEE, not separate entities. Must integrate such subtypes into a higher-level entity
- conflicting object definitions - different attributes or definitions for the same entity