Database Environment & Development Process

Basic Terms

  • Data vs Information: Data is the raw, unprocessed facts, while Information is data that has been processed to have meaning for users.

  • Limitations of Conventional File Processing Systems:

    • Inefficient for answering queries quickly.

    • Complex administration.

    • Limited security and data sharing.

    • Lengthy development and extensive maintenance.

  • Advantages of Databases:

    • Centralized data management.

    • Access control enforced by a DBMS.

    • Improved data consistency and quality.

  • Components of Database Environment: Inclpudes administrators, developers, and end users, as well as tools for modeling and user interfaces.

Understanding Data

What is Data?

  • Definition: Data is defined as a collection of facts, which can take many forms including:

    • Numbers, words, text, measurements, images, or descriptions of things.

  • Data in Context: Examples of data in a business course may include:

    • Course: MGT 500; Semester: Spring 2021; Students (with IDs and GPAs).

    • Chart indicating enrollment by major over the years.

Definitions

  • Database: An organized collection of logically related data.

  • Data: A stored representation of meaningful objects and events.

  • Information: Data processed to increase knowledge for the user.

  • Structured Data: Includes organized data types like numbers, text, and dates.

  • Unstructured Data: Consists of formats like images, video, and documents.

  • Metadata: Data describing the properties and context of the actual data.

File-Based Data Processing Systems

Characteristics of File-Based Systems

  • Collection of application programs that manage their own data in flat files.

  • Environments typically include various departme nts with separate programs (e.g., different systems for Orders, Accounting, Payroll).

Limitations of the File-Based Approach

  • Quick answers are hard to achieve.

  • Challenges in system administration.

  • Lack of security leading to information vulnerability.

  • Lengthy development cycles and considerable maintenance are required.

The Database Approach

  • Centralized repository for shared data.

  • Managed through a Database Management System (DBMS), which standardizes and simplifies data handling.

Database Management System (DBMS)

Definition and Purpose

  • DBMS: A software that creates, maintains, and gives controlled access to user databases.

  • Facilitates application services for various database types such as student records, billing systems, etc.

Advantages of Database Approach

  • Offers improved data consistency and quality while reducing redundancy.

  • Enhances application development productivity and responsiveness.

  • Enforces data security and reduces program maintenance burdens.

  • Supports better decision-making through reliable data management.

Costs and Risks of Database Approach

  • Requires specialized personnel and can involve significant installation costs.

  • Complexity in management and potential for organizational conflict during implementation.

Components of Database Environment

  • Roles in the database environment include:

    • Data and database administrators.

    • System developers and end users.

    • Data modeling tools and user interface programs.

    • Application programs and metadata repositories.

Types of Database Systems

Single-user vs Multi-user Databases

  • Single-user Database: Supports only one user, typically on a personal computer.

  • Multi-user Database: Supports multiple users, can be divided as:

    • Workgroup databases for small teams or departments.

    • Enterprise databases encompassing multiple departments.

Types of Enterprise Applications

  • Enterprise Systems: ERP (Enterprise Resource Planning), CRM (Customer Relationship Management), etc.

  • Data Warehouses: Maintain historical data and integrate data from various sources.

DBMS Functions

Overview of DBMS Functions

  1. Data Dictionary Management: Stores definitions and relationships of data elements.

  2. Data Storage Management: Efficient management of data storage.

  3. Data Presentation Management: Formatting data appropriately for users.

  4. Security Management: Enforcing security and privacy protocols for users.

Advanced Functions of DBMS

  • Multiuser access control to maintain data integrity.

  • Backup and recovery mechanisms to ensure data safety.

  • Application programming interfaces and support for query languages (e.g., SQL).

Preparing for Your Database Professional Career

Job Opportunities in Database Field

  • Database Developer: Requires skills in programming and SQL.

  • Database Designer: Systems design and database architecture knowledge needed.

  • Database Administrator: Looking for foundational knowledge in database systems and vendor courses.

  • Database Analyst: Skills in query optimization and reporting require proficiency in SQL.

  • Database Architect: Emphasis on conceptual and logical database design and DBMS comprehension.

  • Database Security Officer: Focus on security policies and implementations in data administration.

  • Cloud Computing Data Architect: Knowledge of cloud technologies and performance tuning is essential.

  • Data Scientist: Proficiency in data analysis, statistics, and programming is necessary to extract insights from data.

Components of Database Environment

  • Data and Database Administrators

  • System Developers

  • End Users

  • Data Modeling and Design Tools

  • User Interface

  • Application Programs

  • Repository

  • Database Management System (DBMS)

  • Database

Systems Development Life Cycle (SDLC)

  • Traditional SDLC is divided into five phases:

    • Planning: Provides a general overview of the company and its objectives.

    • Analysis: Problems identified during the planning phase are examined in greater detail.

    • Detailed Systems Design: The system's processes are finalized.

    • Implementation: Installation of hardware, DBMS software, application programs, and the implementation of the database design.

    • Maintenance: Involves corrective, adaptive, and perfective maintenance.

  • The SDLC is an iterative process, rather than a purely sequential one.

Phases of SDLC Actions

  1. Planning Phase: Gathering information

  2. Analysis Phase

    • User requirements

    • Evaluation of existing systems

    • Logical system design

  3. Detailed Systems Design Phase

    • Detailed system specification

  4. Implementation Phase: testing/installing and evaluating

  5. Maintenance Phase

The Database Life Cycle

  1. Enterprise Modeling

  2. Conceptual Data Modeling

  3. Logical Database Design

  4. Physical Database Design

  5. Database Implementation

  6. Database Maintenance

Three-Schema Architecture

  • Different people have different views of the database:

    • External Schema: User views

    • Conceptual Schema: Global view of the database

    • Internal Schema: View from DBMS

Planning - Enterprise Modeling

  • Database planning must include the development of standards that govern:

    • How data will be collected

    • What data are already available

    • What documentation is necessary

    • How design and implementation should proceed

Business Function-to-Data Entity Matrix Example

  • Defines the relationship between data entities and business functions where data entities are marked as used within a business function.

  • Types of Data Entities:

    • Customer

    • Product

    • Raw Material

    • Order

    • Work Center

    • Work Order

  • Business Functions:

    • Business Planning

    • Product Development

    • Materials Management

    • Order Fulfillment

    • Order Shipment

    • Sales Summarization

    • Production Operations

    • Finance and Accounting

Enterprise Model

  • Illustrates the relationships between various data entities like CUSTOMER, ORDER, and EMPLOYEE

  • Examples:

    • CUSTOMER places an ORDER.

    • ORDER is for a PRODUCT.

    • PRODUCT has an associated INVENTORY.

Planning - Conceptual Data Modeling

  • Describes the scope and boundaries of the database system and major user views.

  • User view: defines requirements from the perspective of a specific job role (e.g., Manager, Supervisor) or application area (e.g., marketing, personnel, stock control).

Representation of a Database System

  • Known for including multiple user views and interactions.

User Interface (External View)

  • Example interface elements:

    • Homepage with options for resources, schedules, and forms.

    • Navigation through specific application links (e.g., PeopleSoft, Registrar, Financial Services).

User View Examples

  • Detailed layout for user engagement, including dashboards for grades, class schedules, and personal information.

Segment of ERD

Entities Represented:

  • CUSTOMER

    • Attributes: Customer ID, Customer Name

  • PRODUCT

    • Attributes: Product ID, Standard Price

  • ORDER

    • Attributes: Order ID

Analysis - Conceptual Data Modeling

  • Collecting and analyzing information about the business to support the database system and identify user requirements.

  • Conceptual data model is independent of software and physical details, defining main data entities, attributes, relationships, and constraints.

  • Conceptual Schema: A holistic view of data that accommodates community perspectives.

Example of ERD

  • Detailed entities, attributes for entities like Student, Course, and Teacher. Data integrity and relationships defined, including primary and foreign keys.

Design - Logical Database Design

  • Transforming the conceptual model (schema) into a logical model (schema) using data management technology.

  • Enterprise-wide database design based on a specific data model (e.g., relational model) yet independent of physical details.

  • Logical Schema: Representation of data specific to a data management technology.

Logical Design Example

  • Class structure detailed with attributes, including data types and sizes.

  • SQL syntax for table creation demonstrated:
    sql CREATE TABLE CLASS ( CLASS_CODE CHAR(9), CRS_CODE CHAR(5), CLASS_SECTION VARCHAR(10), CLASS_TIME TIME, CLASS_ROOM VARCHAR(10), PROF_NUM INTEGER(5) );

Design - Physical Design

  • Process involves storage organization and data access characteristics to ensure integrity, security, and performance.

  • Defines data storage organization and measures for integrity and security, tailored to a specific DBMS.

  • Physical Schema: Specifications regarding how data from a logical schema is stored in secondary memory by a database management system.

DBMS Software Selection Factors

Considerations in the purchasing process:

  • Cost

  • DBMS features and tools

  • Underlying model compatibility

  • Portability of the DBMS

  • DBMS hardware requirements

Three-Schema Architecture

  • Different views of the database according to user definitions:

    • External Schema: User-specific views and subsets of conceptual schemas. Determined through business-function/data entity matrices facilitated by DBA.

    • Conceptual Schema: Depicts a comprehensive view through Entity-Relationship models.

    • Internal Schema: Represents logical and physical structures.

How Three Schema Structure Works

  • Logical (Data) Independence: Changes in the conceptual schema (e.g., addition/removal of entities) should not affect external or logical schemas.

  • Physical (Data) Independence: Changes in internal schema (such as different file organizations or storage structures) should not impact conceptual or external schemas.

Application Design

  • Design involves the user interface and application programs using and processing the database.

  • Database and application designs are parallel activities that include transaction design and user interface design.

Prototyping

  • Building a working model of a database system for assessing features, suggesting improvements, clarifying user requirements, and evaluating the feasibility of a design.

Implementation

  • The real-world application of database and application designs, involving:

    • Using Data Definition Language (DDL) to create schemas and files for databases.

    • Creating specified user views through DDL.

    • Employing Third Generation Language (3GL) or Fourth Generation Language (4GL) for application program creation, including database transactions executed through Data Manipulation Language (DML).

Data Conversion and Loading

  • Process of transferring existing data into a new database and adapting applications to the new system, necessary when an old system is being replaced.

  • The DBMS typically provides a utility to load existing files into the new database.

Testing

  • Systematic running of the database system to identify errors.

  • Utilizes carefully planned test strategies and realistic data. Usability of the system is also tested.

Operational Maintenance

  • Ongoing monitoring and maintenance of the database system post-installation.

  • Monitoring performance to identify when tuning or reorganization is necessary

  • Maintaining and updating the database application as needed

  • Adjusting to incorporate new requirements into the database application.