Databases
The goal of database development is to build scalable, robust solutions to address business objectives. A database system has four components, beginning with the database, responsible for storing data, The DBMS creates, processes, and administers databases. Application programs interface between the DBMS and users. Finally, users deploy application programs to connect to their stored data, as seen in Figure 24 (Kroenke et al., 2020, pp. 18–22). Many concepts from the systems course overlap correspond with database design. If possible, these courses should be taken at the same time. Repetition of the analysis and diagramming processes helps transform short-term tasks into long-term skills. Common principles of design outlined in Figure 24 should be applied to methodologies through the database development cycle. Use case narratives describe business requirements and document scope boundaries (Appendix E).
Figure 24
Database System
Database Logical Design
To determine how a logical design should be outlined, there are a few concepts important concepts to review regarding databases. Databases store collections of relations, as seen in Figure 25. Relations are also called files or tables. Some people find it helpful to think of relations as spreadsheets. Attributes, also called columns, features, variables, or fields, contain information about a single characteristic common among the records. Records describe a single instance of the attribute set and might also be referred to as a row or tuple. Finally, individual values are stored in cells and are the smallest element contained within a relation (Kroenke et al., 2020, pp. 75–85).
Figure 25
Relational Database Elements
Database Elements
Figure 26 describes each database element, along with minimum required characteristics for relations to maintain database integrity. Relationships connect each table together by using unique identifiers called keys. A primary key is an attribute within a relation that can uniquely identify a record. Sometimes distinguishing attributes exist organically within data, like social security numbers or customer identifiers. If no suitable key exists in a single attribute, two or more fields may form distinctive identifier called a composite key. Single attributes or combinations of fields with the capacity to narrow data down to a single record are referred to as a candidate key.
Figure 26
Relational Database Elements
When determining the optimum logical design for the business case, it is important to consider any factor with the potential to impact database integrity. The decision tree in
Figure 27 highlights a few important questions to ask when determining whether a naturally occurring or surrogate primary key is best for the logical diagram.
Figure 27
Natural/Surrogate Key Decision Tree
Database Physical Design
Physical design phase for databases follows a similar system development life cycle done learned in systems analysis, where logical components are fleshed out to give greater detail to the model. As the most popular tool for visualizing data projects for stakeholders, ERD diagramming is an important skill to develop. Lucidchart is the tool I usually recommend because of the low learning curve, ease of collaboration, and beautiful design capability. Lucidchart automatically designates entities and relationships as data objects when outlining database structures using ERD shapes. After the datatypes are added in the physical design phase, the entire framework SQL can be generated using syntax for MySQL, PostgreSQL, SQL Server, Oracle SQL, or QuickBase, as seen in Figure 28 (Lucidchart, 2017).
Using the generated SQL, students and designers get an enormous jumpstart on building a prototype database for data model validation. Validating the model through prototype development is an opportunity review integration of all use cases, ensuring business all requirements have been met (Kroenke et al., 2020, pp. 303–304). It is also a tangible and practical error checking method. Most importantly, model validation through user prototype testing and feedback could substantially increase user acceptance.
Figure 28
Exporting SQL from ERD with Lucidchart
Database Management
Developing proper policies and procedures for management of a database is essential to successful deployment. Administrators must find a balance between maximizing efficiency while also ensuring the system is secure and properly managed. With the growing wealth of organizational data comes an ever-increasing pressure for rapid operationalization, which is why proper process and policy management remains vital to development projects. Database administration involves three primary functions: backup and recovery, security, concurrency control. Backup and recovery addresses archiving and backup retrieval when necessary. Security functions involve the use of tools and management to protect data confidentiality while maintaining availability. Concurrency control handles simultaneous connections to the database, ensuring data integrity (Kroenke et al., 2020, pp. 446–447). Policies and procedures must be documented and maintained. Continuous improvement strategies involve systematic review and revision to support an optimum database environment.