3.2 Practical Applications of Database Design and Management

Database Design

There are two fundamental stages of database design—logical and physical. Logical design involves creating a blueprint of the database that outlines the structure without considering how it will be physically implemented. This stage includes defining entities, their attributes, and relationships, often using tools like ERDs. The goal is to ensure that the database model aligns with business requirements and eliminates redundancies, such as duplicate records or repetitive fields. Physical design focuses on how the database will be built on a specific DBMS. It includes selecting data types, indexing strategies, and storage methods to optimize performance and storage. The transition from logical to physical design is essential as it translates a theoretical model into a practical, efficient database.

Database design needs to take into consideration the data and the data life cycle. As Figure 3.2 illustrates, the data life cycle includes the stages that data undergo from collection to deletion, ensuring data remain accurate, accessible, and valuable throughout their life cycle. It begins with data collection where raw data are gathered from various sources, with the goal of capturing accurate and relevant information for future use. Next, data storage involves saving this information in a database for easy access and management, ensuring it is organized and can be retrieved efficiently. Processing follows, and data are cleaned, transformed, and organized to prepare the data for analysis, ensuring data quality and usability. During the data analysis phase, data are examined by stakeholders to extract useful insights that inform decision-making, revealing patterns, trends, and correlations valuable for strategic planning. Finally, data are either archived for future reference or deleted if no longer needed, maintaining a clean and efficient database environment.

Figure 3.2 The data life cycle processes are interconnected and continuous. (attribution: Copyright Rice University, OpenStax, under CC BY 4.0 license)

Database system design should proceed through several steps:

1. Defining requirements
2. Designing the structural components
3. Ensuring performance capabilities
4. Creating a positive user experience
5. Planning for smooth integration with existing systems

The first step in database design is to gather and define the system requirements. This involves understanding the needs of the users and the objectives of the database. Requirements are categorized as functional, detailing what the database should do, and nonfunctional, specifying performance criteria such as speed, reliability, and security. Clear and comprehensive requirements ensure that the database system meets user expectations and business goals, providing a solid foundation for the design process.

Once the requirements are defined, the next step is to map out the system’s structure and architecture. Diagrams can illustrate how different components of the system interact with each other. These may include software modules, hardware components, network infrastructure, and data storage solutions. A clear architecture helps plan the implementation and ensures that all parts of the system work together harmoniously, facilitating efficient and effective design.

A critical aspect of database system design is ensuring that the database performs efficiently and can scale to handle increasing loads. This involves selecting appropriate technologies, optimizing algorithms, and designing for concurrency and parallel processing. Scalability ensures that the database can grow and adapt to higher demands without significant performance degradation. This is particularly important for databases expected to handle large volumes of data or high user traffic, ensuring long-term viability and performance.

The design process also focuses on creating a positive user experience. This includes designing intuitive user interfaces, ensuring fast response times, and providing clear results to users.

Finally, database design must ensure smooth integration with existing systems. This might involve interfacing with legacy systems, using standardized protocols for communication, and ensuring data compatibility. Successful integration minimizes disruptions and allows for seamless operation across different platforms and technologies, enhancing the overall functionality and efficiency of the database system.

Steps for Determining the Design and Structure for a Database Management System

To determine the design and structure for a DBMS, consider the process of designing a library management system. Imagine you’re tasked with designing a library management system for a local library. The system must handle book inventories, track borrowings and returns, maintain member records, and generate reports. This scenario will guide you through the design process using a structured task list.

1. Requirement analysis. The first step is to gather and analyze user requirements. After consulting with library staff and stakeholders, you identify the following criteria:
   • Store information about books, including
     • title,
     • author,
     • International Standard Book Number (ISBN) (a unique thirteen-digit code used to identify a book),
     • edition, and
     • availability status.
   • Maintain member records with details like
     • name,
     • membership ID,
     • contact information, and
     • borrowing history.
   • Track lending and returns, including
     • due dates and
     • fines for overdue books.
   • Generate reports such as
     • book inventories,
     • popular books, and
     • member activity.
   • Support user authentication with roles for librarians and regular members.
2. Feasibility study. Next, you conduct a feasibility study to ensure the project is viable:
   • Technically, the project is feasible with the available tools, including an RDBMS like MySQL.
   • Economically, the library has allocated a sufficient budget. The budget is the amount of money reserved for the project and was decided by the finance committee. If the budgeted amount is not enough, they could apply for grants, shift funds from other areas, or adjust the project to focus on the most important parts.
   • Legally, there are no significant concerns, but data privacy for member records must be ensured.

   From the first two steps, you determine that the project is feasible and worth pursuing.

3. System specification is based on the requirements, so you define the DBMS specifications:
   • The system will be a web application with a MySQL database back end. The back end is the part of a software application that handles data, logic, and operations, supporting what users interact with on the front end. The front end is the part of a software application that users interact with, including the design and user interface.
   • The system will contain modules for
     • book management,
     • member management,
     • lending and return tracking, and
     • reporting.
   • User roles will be implemented to distinguish between librarians and regular users.
   • The application will be accessible via the library's internal network.
4. Logical design is the next phase where you will create data models and define the DBMS architecture. You design ERDs to represent the following entities and their relationships:
   • Book attributes include
     • book ID,
     • title,
     • author,
     • ISBN,
     • genre, and
     • availability.
   • Member attributes include
     • member ID,
     • name,
     • contact information, and
     • membership date.
   • Lending attributes include
     • loan ID,
     • book ID,
     • member ID,
     • loan date,
     • due date, and
     • return date.

   The relationships between these entities are established, such as a member can borrow multiple books, and a book can be borrowed by multiple members over time.

5. Physical design. In this phase, you plan the database schema based on the logical design. You define the tables, columns, and data types. Table 3.10, Table 3.11, and Table 3.12 represent the attributes and data types for books, member, and lending tables.

   Attribute	Data Type
   BookID	Primary Key - Integer
   Title	CHAR
   Author	CHAR
   ISBN	CHAR
   Generation	CHAR
   Availability	BOOLEAN

   Table 3.10 Books Table First, the attributes and data types for the book table are defined.

   Attribute	Data Type
   MemberID	Primary Key - Integer
   Name	VARCHAR
   ContactInfo	VARCHAR
   MembershipDate	DATE

   Table 3.11 Member Table Next, the attributes and data types for the member table are established.

   Attribute	Data Type
   LendingID	Primary Key - Integer
   BookID	Foreign Key - Integer
   MemberID	Foreign Key - Integer
   LoanDate	DATE
   DueDate	DATE
   ReturnDate	DATE

   Table 3.12 Lending Table Finally, the attributes and data types for the lending table are defined.

   You also consider indexing strategies to optimize query performance and storage methods to ensure efficient data retrieval.

6. Prototyping is the next step for developing a library management system, and you’ll focus on key functionalities like
   • book search,
   • member registration, and
   • borrowing transactions.

   You then present this prototype to the library staff for feedback. The prototype is well received, and they suggest adding a feature to notify members about due dates via email.

7. System integration is the next step as you integrate various components of the DBMS, ensuring that the web application interfaces correctly with the MySQL database. You also integrate the email notification feature suggested by the library staff, using an SMTP server to send due date reminders to members.
8. Testing is conducted to ensure the DBMS works as expected. Individual functions are verified by a unit test, an integration test checks the interaction between modules, and a system test evaluated the overall functionality. To ensure the system meets the needs of the end users, user acceptance testing is performed.
9. Documentation is an important step as you create comprehensive documentation, including user manuals for librarians and technical documentation for developers. The user manual covers how to
   • manage books,
   • register members,
   • process loans and returns, and
   • generate reports.

   The technical documentation includes database schemas, API references, and deployment instructions.

10. Deployment on the library’s internal network is a major step for your system. Software is installed on the server, the database is set up, and the application is configured. The library staff is trained to use the new DBMS.
11. Maintenance is provided to keep the system running smoothly. This includes
    • regular backups of the database,
    • updates to the software, and
    • support for any issues that arise.

Feedback from the library staff is continuously gathered to make further improvements to the system. By following these steps, you have successfully designed and implemented a database management system that meets the library’s needs and enhances their operational efficiency.

Applications of Database Management Systems in Different Industries

Databases and DBMSs are used extensively across various industries. In health care, databases are used to store patient records, medical histories, treatment plans, prescriptions, and billing information. Common DBMSs used in health care include MySQL, PostgreSQL, Oracle Database, and MongoDB. These databases allow health-care providers to access patient data quickly and enhance the quality and coordination of care.

Electronic health record systems integrate various data points to give a comprehensive view of a patient’s health status, aiding in better diagnosis and treatment. Health-care databases often use relational structures to ensure data integrity and support complex queries. Increasingly, cloud-based databases are being adopted for their scalability, flexibility, and compliance with data security regulations like HIPAA standards.

Financial institutions use databases for transaction processing, customer information management, account balances, loan processing, and investment portfolios. Common DBMSs used in the financial sector include Oracle Database, Microsoft SQL Server, and IBM Db2. Financial database management systems need to be highly reliable and secure to handle sensitive financial data and support real-time transaction processing. They enable efficient management of large volumes of transactions and customer data. Relational databases are favored in finance for their robustness and ACID properties, ensuring reliable transaction processing. Some institutions also use NoSQL databases for big data analytics and handling unstructured data.

In education, databases manage student records, course schedules, grades, and administrative data. Learning management systems like Moodle, Canvas, and Blackboard use databases to store course materials, assignments, and grades, facilitating online learning and tracking student progress. Educational institutions typically use relational databases for structured data such as student records and course information. Cloud-based databases are popular due to their scalability, ease of access, and ability to support large numbers of users, especially in remote learning scenarios.

Manufacturers use databases to track inventory levels, manage supply chains, monitor production processes, and maintain equipment logs. Examples of DBMSs in manufacturing include SAP HANA, Oracle Database, SQL Server, and MongoDB. Databases support efficient operations by providing real-time data on stock levels, production schedules, and maintenance needs, helping optimize manufacturing processes and inventory management. Both relational and NoSQL databases are used. Relational databases handle structured data like inventory lists and production schedules, while NoSQL databases manage large volumes of sensor data from IoT devices in smart manufacturing.

Additional key industries using databases for management and control include retailers that use databases for managing product catalogs, customer orders, and transaction records; logistics companies that use databases to optimize routes, manage deliveries, and track shipments; and telecommunication providers that use databases to manage customer accounts, billing information, and network performance data.