Querying and Managing Databases
The Database Development Process
Objectives
The Database Development Process
Database Modelling Lifecycle
2
Database Design
• Database Design
– Foundation of a successful information system
– Should promote
• data integrity
• prevent data redundancies & anomalies
– Must yield a database that
• is efficient in its provision of data access.
• serves the needs of the information system.
Database Development
• System Construction
– System Analysis
• establish the need and extent of an information system
– System Development
• design & implement the information system
• Database Construction
– Planning & Analysis
– Database Development
• Design
– create complete, normalized, and integrated database models
– establish data management processes
• Implementation
– create storage structure
– load data into database
– provide for data management activities
» data entry/update, report generation, search
• Maintenance
– (corrective & adaptive) modification, upgrade, backup & recovery
S511 Session 3, IU-SLIS 4
Database Lifecycle: Phase 1
• Planning & Analysis
► Discover
• Company Objectives
• Operations & Structure
– what they are
– how they function
– how they interact
• Information Flow
► Identify
• Problems & Constraints
► Define
• Database Specifications
• objectives Database Systems: Design, Implementation, & Management: Rob &
Coronel
• scope
• boundaries
S511 Session 3, IU-SLIS 5
Database Lifecycle: Planning & Analysis
1. Analyze Company Situation
– What is organization’s general operating environment & its mission?
• what are operational components, how do they function & interact?
• design must satisfy the operational demands created by the organization’s mission
– What is organization’s structure?
• who controls what and who reports to whom?
• design involves defining information flows, queries, reports, etc.
2. Define Problems & Constraints
– What are the problems to be solved?
– What are the constraints that limit the database design?
• e.g. time, budget, personnel, etc.
3. Define Objectives
– Database should be designed to help solve the major problems.
• examine the problem space for possible database solutions.
4. Define Scope and Boundaries
– Consider factors that force the design into a specific mold
– Scope defines the extent of design.
• data structures, type & number of entities, size of database
– Boundaries
• imposed by (resource) constraint, existing hardware/software
S511 Session 3, IU-SLIS 6
Database Lifecycle: Phase 2
• Database Design
– Create Database Model Two Views of Data
• To support company operations and
objectives
• To meet system requirements
• To meet user requirements
– Focus on Data Requirements
• Data structure
• Data access
• Data to info. transformation
– Subphases
• Conceptual Design
• DBMS software selection
• Logical Design
• Physical Design
Database Systems: Design, Implementation, & Management: Rob
& Coronel
S511 Session 3, IU-SLIS 7
DB Design: Conceptual Design
• Create a conceptual model
– i.e., Abstract data structure that represent real-world items.
– Need to understand how business works and what role data plays
– Software and hardware independent
• Minimal Data Rule:
– “All that is needed is there, and all that is there is needed.”
– Make sure that all data needed are in the model, and that all data in the model
are needed.
• Conceptual Design Steps
– Data Analysis & Requirements
– E-R Modeling & Normalization
– Data Model Verification
S511 Session 3, IU-SLIS 8
Conceptual Design: Data Analysis & Requirements
Discover the data that can be transformed into desired information
► Information Need
• What kind of information is needed?
what output (queries & reports) must be generated by the system?
► User Characteristics
• Who will use the information?
• How will information be used?
► Information Source
• Where is the information to be found?
• How is the information to be extracted?
► Information Constitution
• What data elements are needed to produce the information?
• What are the data attributes and relationships?
• What data transformations are to be used to produce the information?
Develop a thorough understanding of the company’s data
► Flow, uses, characteristics
• Data Flow Diagram
Data sources
► Interviews, direct observation
► Business Rules
• Narrative description of policy & procedures
S511 Session 3, IU-SLIS 9
Conceptual Design: E-R Modeling
• E-R Modeling steps
1. Identify, analyze, and refine the business rule
2. Identify the main entities
3. Define the relationships among entities
4. Define attributes, primary keys, and foreign keys for each entity
5. Create an initial E-R diagram
6. Normalize the entities
• process for evaluating & designing good table structures
– reduce data redundancies & help eliminate data anomalies
7. Verify the model & modify the E-R diagram iteratively
► Data Dictionary
• Defines all objects (entities, attributes, relations, etc.)
• Used in tandem with the normalization process
– to help eliminate data anomalies & redundancy problems
S511 Session 3, IU-SLIS 10
Conceptual Design: E-R Model Verification
• E-R model is verified against proposed system processes.
– Corroboration that intended processes can be supported by the database model
– Careful reevaluation of the entities and detailed examination of attributes
– Verification of business transactions as well as system and user requirements
• May reveal additional entity and attribute details.
• Verification process is iterative.
1. identify ER model’s central entity
2. identify modules/subsystems & components
3. identify transaction requirements
• update/insert/delete/query/report
• user interface
4. verify all processes against ER model
5. make necessary changes
6. repeat steps 2 through 5
Database Systems: Design, Implementation, & Management: Rob &
Coronel
S511 Session 3, IU-SLIS 11
DB Design: DBMS Selection
• What are the advantages & disadvantages?
– Cost
• purchase, maintenance, operational, training, etc.
– Features & Tools
• ease-of-use, performance, DB administration, etc.
• application development tools
– DB model
• hierarchical, RDB, Object-oriented, etc.
– Portability
• platform, O/S, SQL
– Hardware requirement
• processor, RAM
S511 Session 3, IU-SLIS 12
DB Design: Logical & Physical Design
• Logical Design
– Translate conceptual design into internal model that maps objects in model to
specific DBMS constructs
• From software independent to software dependent
– Detailed & functional descriptions of system components
• Specify system processes and I/O.
• e.g., table design, attribute definitions, access restrictions
• Physical Design
– Select the data storage and data access characteristics of the database
• More important in older hierarchical and network models
• Becomes more complex when data are distributed at different locations
– Can affect the database performance
• e.g. storage media, buffer size, etc.
– Designers favor software that hides physical details
S511 Session 3, IU-SLIS 13
Database Lifecycle: Phase 3 & 4
• Implementation
– Create the database
• Tables, forms, queries, reports
• Programming: SQL, VBA
• Security provisions: password, access rights, data encryption
– Fine-tune
• Repeated testing, debugging and evaluation
• Maintenance
– Preventative maintenance
• To prevent and prepare for problem situations (e.g. backup)
– Corrective maintenance
• To address database system error (e.g. recovery)
– Adaptive maintenance
• To adapt to the external changes (e.g. database update, enhancement)
– General maintenance
• Security audits, system-usage analysis
S511 Session 3, IU-SLIS 14
Database Design Strategies
• Top-down vs. Bottom-up
► Top-down design (e.g., E-R modeling)
1. Identify entities/data sets.
2. Define attributes/data elements for each entity.
► Bottom-up design (e.g., normalization)
1. Identify attributes.
2. Group them together to define entities.
• Centralized vs. Decentralized
► Centralized design
• small number of objects and procedures
• single design process
► Decentralized design
• large number of entities with complex relations and operations
• multiple parallel design of subsystems & aggregation
S511 Session 3, IU-SLIS 15
DB Lifecycle: Designer’s Perspective
Planning
Maintenance Analysis
Design is a never-ending
process...
conceptual
Implementation design
S511 Session 3, IU-SLIS 16
DB Design: Step 1. Planning
Database
Business Plan Information Database Plan Development
or Org. Goals Needs Projects
• Strategic Planning
– Objectives:
• Understand the business model
– Organizational goal
– Product and information flow
• Assess the information need
• Put together an initial database plan
– Activities:
• Take stock of what is in place
• Interacts with users at all levels
– end-user, managers, support staff
S511 Session 3, IU-SLIS 17
DB Design: Step 2. Analysis
• Requirement Analysis
– Examination of existing database environment
• What hardware and software are available?
• What files/records are in use?
• What is under development?
– From Information Need to Database Requirement
• What type of information is desired?
• What can database system do to satisfy the information need?
• Why design it? What would it improve?
– Feasibility Analysis
• Technological
• Operational
• Economical
S511 Session 3, IU-SLIS 18
DB Design: Feasibility Analysis
• Technological Feasibility
– What hardware, software, and additional resources would be needed?
– What is available in-house? What has to be purchased?
– How will the new system be integrated?
• Operational Feasibility
– Who will design the system?
– Who will maintain the system?
– Who will do training or help-desk support?
– Can the available personnel provide the time? New personnel necessary?
• Economic Feasibility
– Expected cost of the overall project ($)?
• Software, hardware, application development, staff-time
• Hidden cost (unforeseen)
– Other costs
• What is the competition/comparable unit doing?
• Would data sharing among departments lead to additional expenses?
– Benefits
• How soon expected?
S511 Session 3, IU-SLIS 19
DB Design: Step 3. Design
• Conceptual Design
– Map organizational & user needs to a conceptual model
– Data Modeling
• Relational tables, attributes, & constraints
– Event Table
• list of events that will occur within the database system
– Use Cases
• description of how users will interact with the system
– User interfaces
Conceptual Relational Relational
Data Model Data Model DBMS
E-R modeling Schema & Normalization
S511 Session 3, IU-SLIS 20
Data Modeling
• What is Data Modeling?
– A model is a representation of reality that retains only carefully selected essential
details.
– Logical organization of data for optimum information extraction and data
manipulation
• Why Model?
– To understand and identify essential data elements
– To produce a representation that can be transformed into a schema
• How?
– Data modeling involves
• identifying entities, attributes, and relationships
S511 Session 3, IU-SLIS 21
Data Modeling: Bank Example
N
ID# Checking Balance
Account
M Has Chkg-Acct
Customer
M Has Sav-Acct
Savings Balance
Account
N
Person Institution
Birthday Gender Org. Type # Employee
S511 Session 3, IU-SLIS 22
Thank you !
