Database Design and Users

Database Systems

• Sophisticated users, such as business analysts, scientists, and engineers, interact with the database using a query language (SQL) without writing programs.
• Stand-alone users, such as those using tax programs, maintain personal databases using ready-to-use packaged applications.

Importance of Database Design

• Database design focuses on designing the database structure for end-user data.
• The designer must identify the database's expected use.
• A well-designed database: • Facilitates data management. • Generates accurate and valuable information.
• A poorly designed database: • Causes difficult-to-trace errors.

Historical Roots of Database Systems

• File systems were initially used to store data.
• File systems resembled manual systems, with each file owning its own application program to store, retrieve, and modify data.
• As the number of files increased, file systems evolved to include each file owning its own application program.
• Drawbacks of using file systems include: • Data redundancy and inconsistency. • Difficulty in accessing data. • Ad hoc queries were impossible. • Changing existing structures was difficult. • Data isolation and integrity problems. • Atomicity of updates and concurrent access by multiple users. • Security problems.

Database Systems

• A database system consists of a logically related data stored in a single logical data repository.
• A database system: • Defines and regulates the collection, storage, management, and use of data. • Comprises five major parts: hardware, software, people, procedures, and data.
• DBMS (Database Management System) eliminates most of the file system's problems.

DBMS Functions

• Data dictionary (metadata) management: • Stores definitions of data elements and relationships. • Looks up required data component structures and relationships. • Changes are automatically recorded in the dictionary.
• Data storage management: • Creates and manages complex structures required for data storage. • Stores related data entry forms, screen definitions, report definitions, etc.
• Three-Tier Architecture: • Common for web applications. • Includes an intermediate layer called an application server or web server.

Classification of DBMSs

• Based on the data model used: • Traditional: Relational, Network, Hierarchical. • Emerging: Object-oriented, Object-relational.
• Other classifications: • Single-user vs. Multi-user. • Centralized vs. Distributed.
• Centralized DBMS: • Combines everything into a single system.
• Distributed Database Systems: • Are now known as client-server-based database systems.

DBMS Languages

• High-Level/Non-procedural Languages: • SQL (Structured Query Language). • Set-oriented and specify what data to retrieve.
• Low-Level/Procedural Languages: • PL/SQL and other programming languages. • Specify how to retrieve data.

Types of SQL Statements

• Data Definition Language (DDL): • Used by the DBA, developers, and database designers. • Specifies the conceptual schema of a database and creates database objects.
• Data Manipulation Language (DML): • Used to specify database retrievals and updates. • Can be embedded in a general-purpose programming language or applied directly.

Advantages of Using the Database Approach

• Controlling redundancy in data storage and development and maintenance efforts.
• Sharing of data among multiple users.
• Restricting unauthorized access to data.
• Providing persistent storage for program objects.
• Providing storage structures for efficient query processing.
• Providing backup and recovery services.
• Providing multiple interfaces to different classes of users.
• Representing complex relationships among data.
• Enforcing integrity constraints on the database.
• Drawing inferences and actions using rules.

Additional Implications of Using the Database Approach

• Potential for enforcing standards.
• Standards refer to data item names, display formats, screens, report structures, meta-data, etc.