Overview
In this chapter, we will be looking at how we begin to develop a database for an enterprise. We first need to realize that creating a database begins with the enterprise itself. We need to understand the data and information requirements of the company.
Information Engineering
One way of doing this is to use Information Engineering - using a top-down approach to understanding broad information needs. Information Engineering includes four steps: planning, analysis, design, and implementation.
Planning:
Identify strategic planning factors
1. goals of the company
2. critical success factors of the company
3. problem areas
Identify corporate planning objects
1. organizational units
2. locations
3. business functions (what the company does, i.e., sales, marketing)
4. Entity types (people, places, and things that you need to support each business function
Develop an enterprise model
1. functional decomposition
2. entity-relationship diagram
3. planning matrices
Begin by creating lists of the goals, critical success factors, and problem areas of the enterprise. Then list your organizational units and locations. Then list all your business functions and entity types for each business function.
An example of business functions are: sales, marketing, customer service, ordering
For each of the business functions, the entities (people, places, and things you need to make the business function work) are:
SALES: Customer (need a customer to make a sale)
Employee (need an employee to make a sale) Product (need a product(s) to sell)
Sales Receipt (customer and employee need receipts of the transaction)
MARKETING: Product (what you are marketing)
Marketing Materials (type of material - add, etc.) Employee (places material)
Vendor (firm to create material)
ORDERING: Product (what product to order and sent on order)
Employee (places order with the vendor)
Vendor (receives order from employee/company
Order (order placed from company to vendor) Invoice (invoice sent from vendor to company for payment)
CUSTOMER SERVICE: Customer (who they are)
Employee (handles customers)
You can then develop a business function to entity matrix. Notice that you see Customer, Product, and Employee listed in several business functions - this is CORRECT.
SDLC
Another way of looking at developing the database is to use the Systems Development Life Cycle (SDLC). This is a set of steps (like Information Engineering) that will create the database or other systems. The five steps of SDLC are Planning, analysis, design/development, implementation, and maintenance.
Planning involves: enterprise modeling (looking at the enterprise and what data is needed for each information system); and conceptual data modeling (identifying the scope of database requirements and identifying the business functions as listed above)
The analysis involves: conceptual data modeling continued (creating the entities for each business function), adding relationships to entities and business functions, and adding business rules.
Design/development involves Logical database design (look at what will be needed for transactions, forms, displays, and inquiries from the business functions); identify data integrity and security requirements. Physical database design (decide on physical organization of data - a relational database, etc.; populate database)
Implementation involves: code and testing the database; completing database documentation, installing the rest of the database.
Maintenance involves: analyzing the database continually, tuning the database for improved performance; fixing errors in the database; recover when there are problems.
Additional Database Development Approaches
Another approach rather than Information Engineering or SDLC is using RAD - Rapid Application Development. This uses prototyping which is an iterative process where requirements are converted to a working system that is continually revised.
CASE Tools
Case tools are computer-aided software engineering tools that provide automated support for some portion of the systems development process. They can help us draw entity-relationship (E-R diagrams) and help us with logical design and physical design as well.
Schema Architecture
The word schema means a model - or, in the case of a database - a collection of related things - such as database tables and views, domains, constraints, etc. The higher level definition means "model," and we can look at the 3-tiered architecture. The 3-schema architecture includes:
1. External schema - view of the enterprise
2. Conceptual schema - E/R (entities, relationships, and business functions, plus business rules)
3. Internal schema (logical and physical schemas)