COURSE NAME: Database Management TOPIC: Database Design
LECTURE 3
The Database System Life Cycle (DBLC) The database life cycle contains six phases;
1 Database initial study.
Analyze the company situation. (requirements of the database) Define problems and constraints.
Define objectives. (Who should use, what should the database do) Define scope and boundaries.
2 Database design.
Create the conceptual design. DBMS software selection. Create the logical design. Create physical design. 3 Implementation and loading.
Install DBMS. Create the database. Load or convert the data. 4 Testing and evaluation
Test the database. Fine-tune the database.
Evaluate the database and its application programs. 5 Operation
Produce the required information flow. 6 Maintenance and evolution
Introduce changes. Make enhancements.
Database initial study
The current systems operations are examined. The designer must determine how the current system functions i.e. spending a lot of time talking with and listening to the end users. Analyze the company situation.
The company situation describes the general conditions in which a company operates, its organizational structure and its mission. To analyze the company situation, the designer must discover what the company’s operational components are, how they function and how they interact. These issues must be resolved;
1 What is the organizations general operating environment (and what is its mission within that environment).
The design must satisfy operational demands created by the organization’s mission e.g. a mail order business is likely to have (database) operational requirements that are quite different from those of a manufacturing concern. 2 What is the organizational structure (knowing who does what and who reports to
whom).
It is quite useful when you are trying to find required information flow, specific reports, and query formats.
Define problems and constraints.
The designer has both formal and informal sources of information. The designer must find out how does the existing system function, what input does the system require, what
documents does the system generate, how is the system output used, and by whom. Studying the paper trail can be very informative.
The problem definition process might initially appear to be unstructured. Often the managerial view of the company’s’ operation is different from that of the end users who perform the actual routine work.
Define Objectives
A proposed database system must be designed to help solve at least the major problems identified during the problem discovery process. The designer’s job is to make sure that the database system objectives, as seen by the designer, correspond to those envisioned by the end users(s). The database designer must begin to address the following questions:
Will the system share the data with other systems or users?
Define Scope and Boundaries
The designer must recognize the existence of two sets of limits: scope and boundaries. The system scope defines the extent of the design, based on operational requirements. Will the database design encompass the entire organization, one or more departments within the
organization, or one or more departments within that organization, or one or more functions of a single department? Knowing the database design scope helps define the required data structures, the type and number of entities, the physical size and so on.
The proposed system is also subject to boundaries, which are external to the system. Time, people and budget to work with will be a factor to be considered. Boundaries are also imposed by existing hardware and software. Ideally, the designer will choose the hardware and software that will best accomplish the designer’s goals. The scope and boundaries thus become the factors that force the design into a specific mold, and the designer’s job to design the best system
possible within those constraints.
Database Design
The second phase focuses on the design of the database model that will support operations and objectives. In the process of database design, we must concentrate on the data characteristics required to build the database model. In short we have two views of the data within the system: the business view of data as a source of information, and the designer’s view of the data
structure, its accesses, and the activities required to transform the data into information. The systems analysts or systems programmers are in charge of designing the other system
components. Their activities create the procedures that will help transform the data within the database into useful information. The database design does not constitute a sequential process. Rather it’s an iterative process that provides continuous feedback designed to trace previous steps.
Conceptual Design
In the conceptual design stage, data modeling is used to create an abstract database structure that represents real world objects in the most realistic way possible. The conceptual model must embody a clear understanding of the business and its functional areas. At this level of abstraction, the type of hardware and/or database model to be used might not yet have been
identified. Therefore, the design must be software and hardware independent, so that the system can be set up within any hardware and software platform chosen later.
II.
III.
IV.
FIGURE 1.1 Procedure flow in Database Design
Logical Design
This is used to translate the conceptual design into the internal model for a selected DBMS, such I. Conceptual Design
Data Analysis and Requirements Entity Relationship Modeling and Normalization Data Model Verification Distributed Database Design DBMS Software Selection Logical Design Physical Design
Determine end user views, outputs, and transaction processing
requirements.
Define entities, attributes, and relationships. Draw E-R diagrams. Normalize tables.
Identify main processes, insert update and delete rules. Validate reports, queries, views, integrity, sharing and security.
Define location of tables, access requirements, and fragmentation strategy.
DBMS -independent
Translate the conceptual model into definitions for tables, views and so on.
Define storage structures and access paths for optimum performance.
DBMS dependent
Hardware dependent
all objects in the model to the specific constructs used by the selected software. For relational DNMS, the logical design includes the design of the tables, indexes, views, transactions, access authorities and so on.
Physical Design
The physical design of the database specifies the physical configuration of the database on the storage media. This includes detailed specification of data elements, data types, indexing options and other parameters residing in the DBMS data dictionary. It is the detailed design of a system that includes modules & the database's hardware & software specifications of the system. Physical design is the process of selecting the data storage and data access characteristics of the database. The storage characteristics are a function of the types of devices supported by the hardware, the type of data access methods supported by the system and the DBMS. Physical design not only affects the location of the data in the storage device(s) but also the performance of the system.
Physical design is particularly important in the older hierarchical and network models. Relational databases are more insulated from physical layer details than the older hierarchical and network models. Yet, in spite of the fact that relational model tend to hide the complexities of the
computer’s physical characteristics, the performance of the relational databases is affected by physical-level characteristics.
Data modeling is a method used to define and analyze data requirements needed to support the business processes of an organization. The data requirements are recorded as a conceptual data model with associated data definitions. Actual implementation of the conceptual model is called a logical data model. To implement one conceptual data model may require multiple logical data models. Data modeling defines not just data elements, but their structures and relationships between them. Data modeling techniques and methodologies are used to model data in a standard, consistent, predictable manner in order to manage it as a resource.
Roles of Database Personnel
Role of the Database Administrator
A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include:
Recoverability - Creating and testing Backups
Integrity - Verifying or helping to verify data integrity
Security - Defining and/or implementing access controls to the data Availability - Ensuring maximum uptime
Performance - Ensuring maximum performance given budgetary constraints
Development and testing support - Helping programmers and engineers to efficiently utilize the database.
The role of a database administrator has changed according to the technology of database
management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database design is traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.
Typically there are three types of users for a DBMS. They are:
1. The End User who uses the application. Ultimately, this is the user who actually puts the data in the system into use in business. This user need not know anything about the organization of data in the physical level. She also need not be aware of the complete data in the system. She needs to have access and knowledge of only the data she is using. 2. The Application Programmer who develops the application programs. She has more
knowledge about the data and its structure since she has manipulate the data using her programs. She also need not have access and knowledge of the complete data in the system.
3. The Database Administrator (DBA) who is like the super-user of the system. The role of the DBA is very important and is defined by the following functions:
Defining the Schema
The DBA defines the schema which contains the structure of the data in the application. The DBA determines what data needs to be present in the system ad how this data has to be represented and organized.
Liaising with Users
The DBA needs to interact continuously with the users to understand the data in the system and its use.
Defining Security & Integrity Checks
The DBA finds about the access restrictions to be defined and defines security checks accordingly. Data Integrity checks are also defined by the DBA.
Defining Backup / Recovery Procedures
The DBA also defines procedures for backup and recovery. Defining backup procedures includes specifying what data is to backed up, the periodicity of taking backups and also the medium and storage place for the backup data.
Monitoring Performance
The DBA has to continuously monitor the performance of the queries and take measures to optimize all the queries in the application.
Database Designer - design the database structure. They are in effect, the database architects. If a database design is poor, even the best application programmers and the most dedicated DBA will fail to produce a useful database environment.
Systems Analysts - Specialists who translate business problems and requirements into information requirements and systems, acting as liaisons between the information systems department and the rest of the organization.
Business analyst - Within the systems development life cycle domain (SDLC), the business analyst typically performs a liaison function between the business side of an enterprise and the providers of services to the enterprise. A Common alternative role in the IT sector is business analyst, systems analyst, and functional analyst, although some organizations may differentiate between these titles and corresponding responsibilities.
The business analyst will be required to produce the following:
Business requirements - (project initiation document), what the needed achievements will be, and the quality measures. They are usually expressed in terms of broad outcomes the business requires, rather than specific functions the system may perform. Specific design elements are usually outside the scope of this document, although design standards may be referenced.
o Example: Improve the readability of project plans.
Functional requirements - describe what the system, process, or product/service must do in order to fulfill the business requirements. Note that the business requirements often can be broken up into sub-business requirements and many functional requirements.
These are often referred to as System Requirements although some functionality could be manual and not system based, e.g., create notes or work instructions.
o An example that follows from previous business requirement example:
1. The system shall provide the ability to associate notes to a project plan. 2. The system shall allow the user to enter free text to the project plan notes,
up to 255 characters in length.
Programmers– design and implement the application programs. They design and create the data entry screens, reports, and procedures through which user’s access and manipulate the database’s data.
Reference: Database Systems by Peter Rob and Carlos Coronel
Management Information Systems – Managing the Digital Firm by K. C. Laudon & J.P Laudon http://en.wikipedia.org/wiki/Business_analyst
