8 Chapter 8 Implementation

Original Chapter 13 Author: Adrienne Watt

Rewrite: Fred Strickland

Learning Outcomes

Introduction to Chapter 8

Chapter 6 addressed the mechanisms for preventing bad or duplicate data from being entered into a database. We looked at integrity rules, constraints, and cardinality for ensuring the high quality of the data. From the “seat of the pants,” we figured out what tables we might need. What if you are starting with no suggestions and with no guidance? Chapter 7 provided the tools for dividing a “monster” spreadsheet into a collection of workable database tables.

The second edition’s Chapter 13 used the software development life cycle to explain how to create a database. This edition placed those concepts throughout the early chapters. This chapter will look at the selection of a database management system.

The Third Edition Style Guide

This is the style guide that this book will follow for this chapter and for the other chapters.

Figure 8.1 The database style guide. Adapted from https://vertabelo.com/blog/database-schema-naming-conventions/

Selecting the Best Database Management System

Our interest is in the situation where a client is interacting with data in a DBMS. We are not interested in a real-time DBMS¹, which is beyond the scope of this chapter.

There are various websites that provide reviews of DBMS. Capterra’s website lists 410 products. You can sort the list in various ways:

• Pricing Options
  • Free
    • There are 58 products².
  • Free Trial
  • Monthly Subscription
  • Annual Subscription
  • One-Time License
• Features
  • Backup and Recovery
  • Data Connectors
  • Data Import/Export
  • Data Migration
  • Data Replication
  • Data Synchronization
  • Data Virtualization
  • Database Conversion
  • Mobile Access
  • Multiple Programming Languages Supported
  • NOSQL Database Management
  • Performance Analysis
  • Relational database Management
  • Reporting and Statistics
  • Search/Filter
  • Secure Data Storage
• Deployment.
  • Cloud, SaaS, web-Based
  • Desktop Mac
  • Desktop Windows
  • Desktop Linux
  • On-Premise Windows
  • On-Premise Linux
  • Desktop Chromebook
  • Mobile Android
  • Mobile iPhone
  • Mobile iPad
• Number of Users
  • 1
  • 2 to 10
  • 11 to 50
  • 51 to 20
  • 201 to 500
  • 501 to 1,000
  • Over 1,000

The DB-Engines website ranks DBMSs. It has 423 systems. The ranking is based on popularity. The list has relational DBMSs³ and non-relational DBMSs. The following top ten DBMSs came from the September 2024 ranking:

Figure 8.2 Extract from the DB-Engines ranking list for September 2024. Source: https://db-engines.com/en/ranking

Some of these have the option of working in the cloud. That option is beyond the scope of this chapter. For a review of these products, see

“Cloud Database Management Systems Reviews and Ratings,” Gartner, 2024. https://www.gartner.com/reviews/market/cloud-database-management-systems

Microsoft Access: The Good and the Bad

Microsoft makes available its office suite to institutions of higher education. The arrangements frequently result in students receiving a free copy of the Microsoft Access database program. It is tempting to use Microsoft Access for the following frequently published reasons:

• Well known.
• Easy to use and quick learning curve.
  • It is similar to other Microsoft Office products.
• Rapid development and prototyping.
• Works with the other parts of the Microsoft Office suite.
• Robust data security and permission features.
  • Users can control access to their databases. Administrators can define different levels of permissions and access.
• Flexibility for designing the user interface.
• May be able to support up to ten users on a network at the same time.
  • Can share the data with 40 users.
• Could be accessed via a web page front end.
• Queries can be created graphically or with SQL statements.
• Built-in templates and wizards.
• Easy to import and to export data.

Microsoft Access has several disadvantages:

• Limited scalability and performance.
  • The data is stored in a single file and as the maximum size (2 GB) is approached, things begin to degrade.
  • Multimedia data consumes space.
• Relational database limitations.
  • It cannot handle complex relationships between tables.
  • It has a poor relational database design and thus makes it difficult to learn how to use or how to access the database’s forms.
• Weak support for concurrency.
  • It cannot handle multiple users attempting to access and to modify data at the same time.
  • Time critical transactions are difficult to capture.
• Does not have easy support for triggers nor for handling scheduled tasks.
• Lack of version control and collaboration features.
• Works only on a Windows operating system.
  • If different Windows versions are used, the data could become corrupted.
• Access does not support extensive database security protocols.
• Does not have an easy learning curve.
  • The controls and the forms are difficult to customize.
  • Some understanding of the SQL programming language is needed.
• The .accdb file format is not compatible with other systems.
  • Microsoft adopted the OpenDocument Format for Excel and for Word, but did not adopt the OpenDocument Format for Access.
    • The work-around is to export the data to a CSV format.
• The demand for individuals with Access programming language skills is dropping⁵.
  • This decline means that project managers will want to avoid the situation that currently exists for the COBOL programming language⁶.
  • Microsoft announced in November 2017 that it plans to retire Access from its on-line productivity suite in April 2018. This has not happened yet as Microsoft continues to issue updates.

The biggest reason for not using Microsoft Access is that it violates the security principle of separating the front end from the database software and from the actual database. Microsoft has added the ability for Access to be used as a front end to more commercially successful DBMSs. As Stephen Cooper wrote, “…this repurposing of the Access application feels a little like a half-life. Is this the last resort for Access, acting as a skin to more successful RDBMSs?”

If a person feels the need to have Microsoft Access, then Stephen Cooper has a list of better alternatives to Microsoft Access:

• OpenOffice derivatives
  • Apache OpenOffice
  • LibreOffice
  • OxygenOffice Professional
• Google Forms
• Calligra Office
• Zoho Creator
• Bubble

Many individuals commented on the Stephen Cooper article. He updated his article to include suggestions from others:

• Claris FileMaker. It has a 45-day free trial.
• Wisej.
• Microsoft Power Platform (Power Apps). Microsoft may be developing this as a replacement for Access. It has a 30-day free trial.
• Airtable. It has a 14-day free trial.
• Monday.com. It has a 14-day free trial.

Overview of Some Popular DBMSs

These DBMSs are covered in DB-Engines September 2024 order.

#1. Oracle is a relational DBMS. The initial release was in 1980. The current release is 23c (September 2023). There is a 23ai release that uses artificial intelligence. It has a commercial license. It can run on numerous operating systems including some less known operating systems. It supports the standard SQL and Oracle own extensions. It supports several access methods. It supports many programming languages. It supports stored procedures. It supports triggers. It supports foreign keys.

#2 MySQL is a relational DBMS. The initial release was in 1995. It was first developed by the defunct Sun Microsystems. Oracle took over the development in 2010. The current release is 9.0.0 (July 2024). It has an Open Source license. There is a commercial license for the MySQL version that has more features. It can run on numerous operating systems. It supports the standard SQL and its own extensions. It supports several access methods including Microsoft’s own ADO.NET. It supports many programming languages. It supports stored procedures. It supports triggers. It supports foreign keys.

#3 Microsoft SQL Server is a relational DBMS. The initial release was in 1989. The current release is SQL Server 2022 (November 2022). There are three versions; two run on the Azure cloud and one runs in the local area (on-premise). There are two free specialized editions. The SQL Server 2022 Developer is a full-featured edition that can be used as a development and test database in a non-production environment. The SQL Server 2022 Express can be used for development and for production environments such as for the desktop, for the web, and for small server applications. These can run on Windows and on Linux operating systems. If the environment supports Docker Containers⁷, then SQL Server 2022 can be installed. It supports the standard SQL and its own extensions. It supports several access methods including the Microsoft’s own ADO.NET. It supports many programming languages. It supports stored procedures. It supports triggers. It supports foreign keys.

#4 PostgreSQL is a relational DBMS. The initial release was in 1989. The current release is 16.4 (August 2024). It has an Open Source license. It can run on numerous operating systems. It supports the standard SQL and its own extensions. It supports several access methods including the Microsoft’s own ADO.NET. It supports many programming languages. It supports stored procedures. It supports triggers. It supports foreign keys.

#5 MongoDB is a document store database model. It is one of the most popular document store schemes. The initial release was in 2009. The current release is 7.0.5 (January 2024). It has an Open Source license. There is a commercial license available. It can run on numerous operating systems. It supports the standard read-only SQL. It supports several access methods. It supports many programming languages. It supports JavaScript written stored procedures. It supports triggers in the MongoDB Atlas version. It does not support foreign keys.

#6 Redis is a key-value store database model. It is popular for in-memory data platform usage scheme. The initial release was in 2009. The current release is 7.2.5 (May 2024). It has an Open Source license. There is a commercial license available for the Enterprise edition. It can run on numerous operating systems. If the RedisSQL module is used, then it supports the standard SQL. It supports only its own access method. It supports many programming languages. It supports the Lua stored procedures. It supports some trigger functions. It does not support foreign keys.

#7 Snowflake is a cloud-based data warehousing service for structured and semi-structured data. It does use the relational DBMS model. The initial release was in 2014. There is only a commercial license. It can run on the operating system provided by the host. It supports the standard SQL. It supports three access methods. It supports two programming languages. It supports stored procedures as user defined functions. It does not support triggers. It supports foreign keys.

#8 Elasticsearch is a distributed, RESTful⁸ modern search and analytics engine based on the Apache Lucene. It does not support the relational DBMS model. It is one of the most popular data search engines. The initial release was in 2010. The current release is 8.6 (January 2023). It has its own Open-Source license. It can run on the operating system provided by a Java VM⁹. It supports a SQL-like query language. It supports two access methods. It supports a small number of programming languages. It supports stored procedures. It does support triggers when the percolation feature is used. It does not support foreign keys.

#9 IBM Db2 is a relational DBMS. The initial release was in 1983 and this was the host version. It has had other names such as “DB2” and “IBM Database 2.” The current release is 11.5.9 (late 2023)¹⁰. There is a commercial license. IBM does have a non-licensed free version (DB2 Community Edition). It can run on numerous operating systems. It supports the standard SQL. It supports several access methods including the Microsoft’s own ADO.NET. It supports many programming languages. It supports stored procedures. It supports triggers. It supports foreign keys.

#10 SQLite is a widely used embeddable relational DBMS. The initial release was in 2000. The current release is 3.46.2 (August 13, 2024). Dwayne Richard Hipp has put this into the public domain. It runs with any operating system. It supports SQL, but it does not fully support the SQL-92 standard. It supports several access methods including the Microsoft’s own ADO.NET. It supports many programming languages. It does not support stored procedures. It supports triggers. It supports foreign keys.

Overview of Some Non-Relational DBMS Schemes

DBMSs that are designed to handle and to store large volumes of unstructured and semi structured data are known as “NoSQL” or as “non-relational” databases. The term has evolved to mean “not only SQL” since some DBMSs include other options for working with the data. Those DBMS that can handle both NoSQL and traditional relational DBMS are now known as “NewSQL.”

NoSQL has six advantages:

• High scalability
• Flexibility
• High availability
• Performance
• Cost-effectiveness
• Agility

NoSQL has ten disadvantages:

• Lack of standardization
• Lack of ACID compliance
• Narrow focus
• Open source
• Lack of support for complex queries
• Lack of maturity
• Management challenge
• GUI is not available
• Weakness in performing backups
• Large document size.

NewSQL on the surface appears to be a perfect combination of NoSQL and of traditional relational DBMS. But there are three issues:

1. NewSQL offers only partial access to the rich resources of a traditional relational DBMS.
2. NewSQL may cause problems for in-memory architectures when the volume exceeds the available memory.
3. Users of the NewSQL DBMS need to understand the traditional relational DBMS.

Geeks for Geeks listed four types. DB-Engines listed 15 types. We will look at the types as defined by DB-Engines.

Columnar. DB-Engines listed this DBMS, but does not explain it. It has only one example.

• GridGain is an example of this approach. It has a popularity ranking of 150 and thus was not covered elsewhere in this chapter.

Content Stores. This is also known as “content repositories.” It is specialized for the handling of digital content such as text, pictures, and videos. It stores metadata on each item.

• Apache Jackrabbit is an example of this approach. It has a popularity ranking of 64 and thus was not covered elsewhere in this chapter.

Document. This is also known as “document-oriented database systems.” These are characterized by the lack of a schema for organizing the data. This approach means that:

• Records do not need to have a uniform structure, i.e. different records may have different columns.
• The types of the values of individual columns can be different for each record.
• Columns can have more than one value (arrays).
• Records can have a nested structure.

• MongoDB is an example of this approach.

Event Stores. This implements the concept of event sourcing. This uses a timestamp in order to create a series of events for an object. This scheme is unique in that it stores the history. This scheme does not support making changes to an object. Nor does it support deleting any of the events. This could be used to support snapshots of something.

• EventStoreDB is an example of this approach. It has a popularity ranking of 181 and thus was not covered elsewhere in this chapter.

Graph DBMS. This is also known as “graph-oriented DBMS” or “graph database.” Data is presented as nodes and edges with a relationship between the nodes. Direct access to a node based on an attribute value is not possible.

• Neo4j is an example of this approach. It has a popularity ranking of 21 and thus was not covered elsewhere in this chapter.

Key-value. This is a very simple DBMS. Each piece of data has a key that is used to locate it. It is not adequate for any complex application. Other schemes such as document stores and wide column stores may incorporate the key-value scheme.

• Redis is an example of this approach.

Native XML DBMS. This may be abbreviated as “NXD.” This can represent hierarchical data. It works with special XML specific query languages.

• MarkLogic is an example of this approach. It has a popularity ranking of 70 and thus was not covered elsewhere in this chapter.

Navigational DBMS. This allows access to data sets via linked records only. It is an old scheme from the 1960s.

• IMS is an example of this approach. It has a popularity ranking of 146 and thus was not covered elsewhere in this chapter.

Object Oriented DBMS. This is also known as “object databases.” This follows the object-oriented data model as used in object-oriented programming languages. Instead of being a row in a table, the data pieces are stored in a single object. No complex joins would be needed.

• InterSystems IRIS is an example of this approach. It has a popularity ranking of 91 and thus was not covered elsewhere in this chapter

RDF Stores. RDF stands for “Resource Description Framework.” This was created for describing metadata of IT resources. Now it is used to store triples of data as in subject-predicate-object. This is a subclass of the graph DBMS.

• MarkLogic is an example of this approach. It has a popularity ranking of 70 and thus was not covered elsewhere in this chapter.

Search Engine. This is a true NoSQL DBMS. The typical offering has the following features:

• Support for complex search expressions
• Full text search
• Stemming (reducing inflected words to their stem)
• Ranking and grouping of search results
• Distributed search for high scalability

• Ellasticsearch is an example of this approach.

Spatial DBMS. This is able to efficiently store, to manipulate, and to query spatial data. Spatial data represents objects in a geometric space such as points or as polygons. This is used to describe locations on the surface of the earth and is used in geographic information systems (GIS).

• PostGIS is an example of this approach. It has a popularity ranking of 290 and thus was not covered elsewhere in this chapter.

Time Series DBMS. It is optimized for handling time series data. Each entry has a timestamp. This would be ideal for handling data from sensors, smart meters, or from other Internet of Things products.

• InfluxDB is an example of this approach. It has a popularity ranking of 28 and thus was not covered elsewhere in this chapter.

Vector DBMS. This is also known as “vector databases.” This is optimized for efficient storage, indexing, and querying of high-dimensional vector data. Specialized algorithms and data structures are used for supporting search for machine learning, for data mining. These DBMSs can handle multiple data types, such as numerical, text, and binary.

• Kdb is an example of this approach. It has a popularity ranking of 45 and thus was not covered elsewhere in this chapter.

Wide Column Stores. It is also known as “extensible record stores.” It can store data in records with an ability to hold very large numbers of dynamic columns. These are not fixed. So a record could have billions of columns. This is like a two-dimensional key-value store scheme. These are schema free.

• Apache Cassandra is an example of this approach. It has a popularity ranking of 11 and thus was not covered elsewhere in this chapter.

Reviewing DBMSs for the Rest of the Textbook

Database courses have been taught with Oracle, with MySQL, with PostgreSQL, with Microsoft Access (#13 in September 2024), Microsoft Azure SQL Database (#16 in September 2024), and with Java Derby (not ranked).

Ideally, the selected DBMS should be similar to what is used in the real world. Companies want students that can apply their knowledge immediately on the job. That is why programming languages such as Java, C++, C#, and other languages are taught instead of Pascal¹¹.

The following are DBMSs that could be used in an academic environment.

Oracle

Oracle is an expensive relational DBMS. The following is based on a website with a date of May 22, 2024:

• Oracle Database Enterprise Edition – License price for processor is $47,500.
• Oracle Database Standard Edition 2 – License price for processor is $17,500.
• Personal Edition – License price per user is $400.
• Mobile Server – The license price for processor is $23,000.
• NoSQL Database Enterprise Edition – License price for processor is $10,000.

The license price is based on used processor cores. So if a processor has four cores, then the above prices need to be increased by a factor of four.

MySQL

MySQL is a relational DBMS. Several websites have long lists of issues with MySQL. The following are the important issues:

• MySQL is inefficient in handling very large databases.
  • MySQL is hard to scale. It doesn’t support auto sharding¹². One needs to maintain the nodes manually.
• MySQL has limited support for advanced SQL features.
  • Limited support for stored procedures and triggers.
• MySQL is inefficient in handling transactions as it is prone to data corruption.
• MySQL has limited security features.
• MySQL is resource intensive for complex queries.
• The development is not community driven.
  • Support and documentation are weak.
• MySQL does not have good developing and debugging tools as compared to other databases.
• It does not support SQL check constraints.
• It does not support ROLE, COMMIT, and stored procedures in versions less than 5.0.
• MySQL has weak stored procedures.
• It is very difficult to install a consistent database cluster with MySQL.
• Group by function does not work correctly.
• Some errors are misleading.
• Correlated Subquery does not work as intended.

Daniel Raymond wrote that the solution to fixing 1 is to use clustering or to split the database into smaller pieces or add Hadoop¹³ support or switch to a different DBMS such as MongoDB. He wrote that the solution to fixing 2 is to upgrade to a paid version or to a different DBMS such as PostgreSQL. He wrote that the solution to fixing 3 is to use a storage engine that supports row level locking or optimizing the database queries. He wrote that the solution to fixing 4 is to upgrade to the enterprise edition. He wrote that the solution to fixing 5 is to optimize the queries or to use a more efficient database engine. He wrote that the solution to fixing 6 is to seek support from third-party vendors and from MySQL user communities or hire an experienced database professional.

PostgreSQL

PostgreSQL is an open-source relational DBMS that is very popular. This is due to a community of devoted and talented software engineers and to the liberal open-source license. It has several components. It is difficult to configure and to maintain high availability and obtain optimal performance. PostgreSQL is not complete out of the box. One must determine what is needed and add those pieces. Curt Kolovson listed the components that must be added to a PostgreSQL installation:

• Monitoring tools for uptime and performance
• Scale-out distributed processing based on partitioning or sharding
• Connection pooling
• HA clustering
• Replication management
• Disaster recovery (DR) solutions
• Backup solutions
• Log archiving

The actual PostgreSQL software has some issues:

• Software architecture based on process-per-connection can lead to inefficient resource utilization.
• Too many tunable parameters, many which are poorly documented, and some with poorly chosen default values.
• Garbage-collection implementation related to its multi-version concurrency control (MVCC) can lead to database bloat and performance problems.
• Query optimizer (“planner”) can make very bad decisions, leading to performance problems that may be hard to diagnose and fix.
• Troubleshooting is sometimes very difficult.

To compound the problems, there are many variants of PostgreSQL and many database-as-a-service (DBaaS) offerings that claim to be PostgreSQL compatible, but are not. To have a successful operation requires having many highly knowledgeable developers or consultants.

MongoDB

MongoDB is well-suited for some applications such as for web applications, for e-commerce, for big data, for demographic and biometric data, and for synchronization. The disadvantages come from the way that MongoDB handles data.

• It cannot handle situations when multiple things must be done across lots of data.
• It is not fully ACID compliance. That is, it supports Atomicity, Consistency, Isolation, and Durability (ACID) at the document level. It cannot support ACID across multiple documents.
• It does not support SQL join operations.
• It uses value pairs and the result is that there are some redundancy introduced.
• It has a maximum document size of 16 MB.

The main emphasis of this textbook is the relational DBMS and MongoDB is not a relational DBMS.

Redis

Remote Dictionary Server (Redis) is a data storage system that is designed to be very quick. This is ideal for organizations that have data that must be written quickly and queried quickly. Example applications are caching, chat and messaging, game leaderboards, session storage, and live streaming.

Redis is well-suited for some applications such as web applications, e-commerce, big data, demographic and biometric data, and for synchronization. The disadvantages come from the way that it handles data.

• It requires a huge amount of memory.
• It uses commands instead of a query language to find data.
• No support for relational algebra.
• No support for ad-hoc queries.
• Security is done at the instance level.
• It is not scalable. It uses a CPU core in a single-threaded mode. Scalability is obtained by deploying more Redis instances.
• Some functions are pushed to the client. (Traditional relational DBMSs handle many tasks on the server side.)

The main emphasis of this textbook is the relational DBMS and Redis is not a relational DBMS.

Snowflake

Snowflake is a data warehouse. This is ideal for organizations that need a platform that offers unique solutions that a traditional data platform cannot provide. It requires a huge amount of memory

• It does not encourage the data to be structured.
• It does not provide an easy means of migrating data. The work around is Snowpipe, but this is not ideal for most situations.
• It has no data constraints. That is, if a company is using a service that bills for data usage, Snowflake can easily exceed a ceiling.
  • The pay-as-you-go pricing can result in a bill that is greater than Amazon Redshift. Snowflake pricing is based on usage, not actual storage.
• Users cannot deploy Snowflake within the company’s server room.
• It has a small community of experienced users.

The main emphasis of this textbook is the relational DBMS and Snowflake is not a relational DBMS.

Elasticsearch

Elasticsearch is a distributed search and analytics engine. This is ideal for organizations that need a platform that permits many users to access a database at the same time while isolating their transactions. There are some issues:

• It has a complex setup. And maintenance is also complex.
• It is resource intensive and requires a huge amount of CPU, of memory, and of storage.
• It prioritizes search performance and scalability over data consistency and durability.
• It has a steep learning curve. One needs to under concepts such as indexing, querying, mapping, and cluster management.

The main emphasis of this textbook is the relational DBMS and Elasticsearch is not a relational DBMS.

IBM Db2

IBM Db2 is a relational DBMS. This is ideal for organizations that need scalability, high security, high compatibility, high flexibility, and high availability. However, IBM Db2 has four disadvantages.

• It is expensive. Oracle, Microsoft SQL Server, and MySQL would be less expensive.
• It is very complex. It requires individuals with specialized expertise for implementing and for managing.
• It has a performance drop for certain types of queries.
• It needs regular maintenance and updates in order to have optimal performance and security.

The main emphasis of this textbook is the relational DBMS and IBM Db2 is a relational DBMS. The IBM Db2 Community Edition is an option. It has the same core capabilities, but it is restricted to a maximum of four virtual processor cores and 16 GB of instance memory. There is no enterprise-level support.

SQLite

SQLite is a relational DBMS. This is ideal for a desktop application such as version control systems, financial analysis tools, media cataloging and editing suites, CAD¹⁴ packages, recording keeping programs, and so on. However, SQLite has at least six major disadvantages.

• It is limited to handling low to medium traffic HTTP requests.
• Its database size is restricted to two GB.
  • It may struggle when dealing with large datasets.
• It has limited ability to scale up.
• It has limited functionality.
  • It does not support stored procedures nor user-defined functions.
• It lacks user management features.
  • It does not handle user authentication and authorization.
• It cannot be used in a centralized control environment.
  • It can only be used by one writer at a time.

The main emphasis of this textbook is the relational DBMS and SQLite is a relational DBMS, but SQLite is too limited.

The Reasons for Selecting Microsoft SQL Server Developer Edition

DataNumen considers the Microsoft SQL Server to be the best choice for “large enterprises for its capability of handling large amounts of data, and its wide array of built in features for data analysis and reporting.”

Microsoft SQL Server Developer edition is exactly like the Microsoft SQL Server Enterprise edition. The difference is that the Developer edition is free whereas the Enterprise edition is not.

The Microsoft SQL Server Express edition is a free, deployable relational DBMS. However, it has some limitations and some missing components. For more information see

Alexandra. “What is SQL Server Express? Definition, Benefits, and Limitations of SQL Server Express,” Stackify, June 19, 2024. https://stackify.com/what-is-sql-server-express/

Express DB has a web page that has a very detailed listing of the features of each edition. Look in the reference for “SQL Server Edition Feature Comparison” information.

Advantages of Using Microsoft SQL Server

• Extensive online product documentation.
• Can be used on a Linux platform.
• Many career opportunities
• Thriving on-line community.
• Works with many useful tools:
  • SQL Server Management Studio
  • Azure Data Studio
  • SQL Server Data Tools
  • SQL Server Profiler
• Increased data security.
• Installing and configuring Microsoft SQL Server is fairly easy to do.
• Support for data recovery.

Disadvantages of Using Microsoft SQL Server

• In order to interact with the SQL Server databases, one must use the Transact-SQL (T-SQL) language, which is a Microsoft extension to the SQL standard.
• When switching to the Enterprise edition, one will have to deal with the difficult and always changing licensing process.
  • The Enterprise edition can cost as much as $14,256 for a per-core license.
• Larger databases may require following complex performance tuning steps.
• There is no native support for source control.
• Vendor lock-in. Microsoft SQL Server is not compatibility with non-Microsoft products.
• New versions of Microsoft SQL Server may not run on older hardware.
• If you uninstall the software and it is not a complete removal, attempting to install it again may result in failure¹⁵.

Downloading and Installing Microsoft SQL Server and Microsoft SQL Server Management Studio with Azure Data Studio

The following information assumes you are using a Windows operating system. Microsoft SQL Server can be installed on other platforms. The images will be similar to the Windows installation.

In September 2024, the current version is SQL Server 2022. It appears that the Microsoft SQL Server team has an irregular release schedule. Here is a table that shows the release dates for SQL Server 2005 through SQL Server 2022:

Figure 8.3 SQL Server release dates and lifecycle as of September 2024. Source: https://www.mssqltips.com/sqlservertutorial/9217/sql-server-release-dates-and-lifecycle/

Finding the Microsoft Landing Web Page for SQL Server

Use a search engine such as Microsoft Bing¹⁶ and type in “Microsoft SQL Server 2022.” Ignore entries for Microsoft Azure and for third party websites. In September 2024, Microsoft Bing had the following display:

Figure 8.3 Extract from the Microsoft Bing search engine for the search phrase “Microsoft SQL Server 2022.”

The top of the landing page may look like the following:

Figure 8.4 Microsoft SQL Server 2022 landing web page. Source: https://www.microsoft.com/en-us/sql-server/sql-server-2022

Finding the Microsoft SQL Server Developer Edition

Click on the small black box in the upper right corner. You will be brought to the download web page:

Figure 8.5 Microsoft SQL Server 2022 download web page. Source: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Click on the download now button for Developer. In Microsoft Edge, a floating download menu may appear.

Figure 8.6 Microsoft Edge download menu.

Click on the open button. When prompted, install to your Windows computer instead of to the virtual machine in Azure.

Figure 8.7 Windows or Virtual Machine in Azure option screen.

Note: You could download the software and execute it from your local computer. The benefit of doing this is that you could mount the ISO image on your computer. Then you can execute the software with administrator privileges. You would use setup.exe. It would take you to the SQL Server Installation Center (Figure 8.12).

You will see three options. Go with the Custom option.

Figure 8.8 SQL Server 2022 Developer Edition installation option screen.

Accept the default download location.

Figure 8.9 SQL Server 2022 Developer Edition media download target location option screen.

The download file may be corrupted (Figure 8.11). If so, then an error message will appear. The solution is to restart the download with a fresh file.

Figure 8.10 SQL Server 2022 Developer Edition downloading package screen.

Figure 8.11 SQL Server 2022 Developer Edition possible error message screen.

The next message screen shows the SQL Server Installation Center.

Figure 8.12 SQL Server Installation Center screen.

We will explore these:

• “Hardware and Software Requirements” takes one to a hyper link with the title of “SQL Server 2022: Hardware and software requirements.”
• “Security Documentation” takes one to a hyper link with the title of “Security for SQL Server Database Engine and Azure SQL Database”
• “Online Release Notes” takes one to a hyper link with the title of “SQL Server 2022 release notes.”
• “Azure extension for SQL Server (New)” takes one to a hyper link with the title of “Azure Arc-enabled SQL Server.”
• “System Configuration checker” takes one to a floating dialog box:

Figure 8.13 SQL System Configuration Checker floating dialog box.

• “Download Data Migration Assistant (DMA)” does not seem to be needed for our purposes.
• “Online Installation Help” would launch the documentation.
• “How to Get Started with SQL Server Failover Clustering” assumes that there is more than one server involved. We are not set-up that way.
• “Upgrade Documentation” is of no interest to us since we did a clean install of SQL Server 2022.
• “Download SQL Server Migration Assistant (SSSMA)” is of no interest to us since we did not have another database system to bring over.
• “How to apply SQL Server updates” takes one to the latest updates and version history for SQL Server.
• See https://learn.microsoft.com/en-us/troubleshoot/sql/releases/find-my-sql-version in order to find out which version you are running. You may need to install an update.

Launching the Actual Installation of the Microsoft SQL Server Developer Edition

From the SQL Server Installation Center screen, select “Installation.”

Figure 8.14 The installation tab and menu from the SQL Server Installation Center.

Select “New SQL Server standalone installation or add features to an existing installation.” This will launch a wizard. This has a left-hand side menu. You may review the “Edition,” the “Licenses Terms,” the “Global Rules,” the “Product Updates,” and the “Install Setup Files.” Our interest is in the “Install Rules.”

The Edition Screen

Figure 8.15 The edition screen with the Developer edition selected.

The License Terms Screen

Figure 8.16 The license terms screen.

In the early days of SQL Server 2022, the “Microsoft Update” screen does not appear. For example of this screen, see the Daniel Calbimonte article.

The Install Rules Screen

The “Install Rules” will execute.

Figure 8.17 The “Install Rules” from the SQL Server 2022 Setup Wizard.

The warning on the firewall is not a concern for a stand-alone installation.

The Azure Extension for SQL Server Screen

The “Azure Extension for SQL Server” menu provides the means to connect to Microsoft’s own cloud platform. We will not be doing anything with this. So uncheck the “Azure Extension for SQL Server.”

Figure 8.18 The “Azure Extension for SQL Server” from the SQL Server 2022 Setup Wizard.

Feature Selection Screen

Figure 8.19 The “Feature Selection” from the SQL Server 2022 Setup Wizard.

We will install the database engine and the full-text search feature¹⁷. Select these two checkboxes. Hit “Next.”

Instance Configuration Screen

Explore the “Instance Configuration” from the SQL Server 2022 Setup Wizard. Notice the default name of “MSSQLSERVER.” This will be needed for the log-in screen.

Figure 8.20 The “Instance Configuration” from the SQL Server 2022 Setup Wizard.

We are going with the default instance. Hit the “Next” button.

Server Configuration Screen

The “Server Configuration” provides information on the server configuration. The check box improves the performance, but there is a security risk. Until the old data and log files are overwritten, a hacker could read the old data. Microsoft recommends enabling instant file initialization as the benefits outweigh the security risk. The use of discretionary access control list (DACL) on the file should help to address this risk, because DACL allows file access only to the SQL Server service account and to the local administrator.

We will go with the default setting and not do anything with the checkbox.

Figure 8.21 The “Server Configuration” from the SQL Server 2022 Setup Wizard.

Database Engine Configuration Screen

In the “Database Engine Configuration” menu, select “Windows authentication mode.”

Figure 8.22 The “Database Engine Configuration” from the SQL Server 2022 Setup Wizard.

There are six tabs.

The “Data Directories” tab provides information on where these resources are stored.

Figure 8.23 The “Data Directories” tab in the “Database Engine Configuration” from the SQL Server 2022 Setup Wizard.

The “TempDB” tab shows the configuration. No action is needed.

Figure 8.24 The “TempDB” tab in the “Database Engine Configuration” from the SQL Server 2022 Setup Wizard.

The “MaxDOP” tab shows information about the number of detected cores. Figure 8.25 shows a laptop with four cores.

Figure 8.25 The “MaxDOP” tab in the “Database Engine Configuration” from the SQL Server 2022 Setup Wizard.

The “Memory” tab provides information about the memory. No action is needed.

Figure 8.26 The “Memory” tab in the “Database Engine Configuration” from the SQL Server 2022 Setup Wizard.

The “FILESTREAM” tab has some check boxes. This is used to store files outside of a database such as videos, PDFs, Word files, Excel files, and other items. For our purposes, we will not be working with these types of files.

Figure 8.27 The “FILESTREAM” tab in the “Database Engine Configuration” from the SQL Server 2022 Setup Wizard.

Figure 8.28 Hitting the “Next” button without an entry in the “Specify SQL Server administrators” box.

If you hit the “Next” button without adding a user, an error message will appear (Figure 8.28). You need to add yourself at least. See Figure 8.29.

Figure 8.29 An example of an entry in the “Specify SQL Server administrators” box. This is the user’s name or the computer’s name.

Figure 8.30 Close up of the “Add Current User” screen.

Ready to Install Screen

Click on the “Ready to Install” menu. You can review the long list of options.

Figure 8.31 The “Ready to Install” screen from the SQL Server 2022 Setup Wizard.

Click on the “Install” button in the lower right-hand corner. The display will change to the “Installation Progress” screen (Figure 8.32).

Figure 8.32 The “Installation Progress” screen from the SQL Server 2022 Setup Wizard.

Success Screen

Figure 8.33 The success screen from the SQL Server 2022 Setup Wizard.

SQL Server can fix some minor issues. If you are viewing an error message about the target principal name being incorrect, then repair take care of this. If you see a message about SQL-Engine_Core_Inst not being configured, repair cannot fix this.

Installing Additional Components

Microsoft provides the option of installing other components. You will need to explore to determine which ones are needed.

Figure 8.34 Available components

Some companies use “Reporting Services” whereas other companies use “Business Intelligence.

We will install “SQL Server Management Tools” and “SQL Server Data Tools.”

We will want to have SQL Server Management Studio. Microsoft explained this:

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VM, and Azure Synapse Analytics. SSMS provides a single comprehensive utility that combines a broad group of graphical tools with many rich script editors to provide access to SQL Server for developers and database administrators of all skill levels.

We will want to have the SQL Server Data Tools. Microsoft explained this:

SQL Server Data Tools (SSDT) is a set of development tools for building SQL Server databases, Azure SQL databases, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. SSDT allows you to design and deploy SQL objects with the same project concept as other application development tools. The SQL projects capability extends to CI/CD pipelines, enabling you to automate the build and deployment of your database projects with the SqlPackage CLI.

Installing SQL Server Management Tools

Click on the link. After a delay, a dialog box will appear. Figure 8.29 shows Release 19.1, which contains the Azure Data Studio.

Figure 8.35 The welcome screen for installing Microsoft SQL Server Management Studio

If the installation fails, reboot your computer and try it again. You will see a success message.

Figure 8.36 The success screen for installing Microsoft SQL Server Management Studio

Installing SQL Server Data Tools

We will be installing the following tools:

• Support for Microsoft Azure SQL Database
• SQL Server Database Engine
• Reporting Services
• Analysis Services
• Integration Services

Click on the link.

If Visual Studio is not present on your computer, then this is needed. We will use this for writing programs to interact with a hosted database.

Figure 8.31 Screenshot of Visual Studio 2022

We will install the community version, because it is free and it is sufficient for our needs.

Figure 8.37 Screenshot of Visual Studio 2022 three editions

The Visual Studio Installer will show Figure 8.38. Notice what it has selected to install.

Figure 8.38 Screenshot of Visual Studio 2022 selected options.

The Visual Studio installer has selected the following:

• ASP.NET and web development
• Azure development
• .NET desktop development

And of course, the Visual Studio installer will include the core editor.

Look for the “Data storage and processing” toolset. Select this.

As your needs grow, you can return to the Visual Studio Installer screen and add more components.

Figure 8.39 shows the success screen.

Figure 8.39 Screenshot of Visual Studio 2022 success screen

Starting the SQL Server Management Studio

On a recent Windows operating system, the start menu will have the appearance as shown in Figure 8.40.

Figure 8.40 Screenshot of the start menu on a Windows computer

Look for “Microsoft SQL Server Tools nn.” Expand the menu. Select “SQL Server Management Studio Management Studio nn.”

The startup screen will appear.

Figure 8.41 Screenshot of launch of SQL Server Management Studio

When the program opens up, a login dialog box will appear.

Figure 8.42 Screenshot of SQL Server Management Studio login dialog box

Finding the Lost SQL Server Installation Center

Look on the C-Drive for the setup.exe program and launch it.

Figure 8.43 Screenshot showing the location of the SQL Server Installation Center program

What is Next?

The next step is to execute commands to create a database. In Chapter 9, we will install the AdventureWorld database and begin to learn some of the common SQL commands (joins and group by are two examples).

Some terms were used without being defined. For example, SQL will be defined in the next chapter. Commands used within the SQL environment will be defined in the next chapter. Terms like “stored procedures” and “triggers” will be explained in another chapter. Relational algebra will be explained in another chapter.

Key Terms

ACID: This stands for Atomicity, Consistency, Isolation, and Durability. These behaviors are highly desirable.

discretionary access control list (DACL): This allows file access only to the SQL Server service account and to the local administrator.

NewSQL: This is different from the traditional relational DBMS. A NewSQL DBMS uses the NoSQL approach with the traditional relational DBMS. Queries are executed against the DBMS using the best approach.

NoSQL: This is different from the traditional relational DBMS. Queries are executed against the DBMS using a different approach.

Real-time DBMS: This is a database that stores data in JSON files. All clients are synchronized in real-time with a connection to the database.

Sharding: This is a technique that involves splitting a large database into smaller, more manageable pieces. These pieces are known as shards. Each shard contains a subset of the data and operates independently. This allows for horizonal scaling and improved performance.

Spatial data: This represents objects in a geometric space such as points or as polygons.

Exercises

1. Explain or define the following terms within the context of this chapter. (CCDSC2021 DM Information Retrieval – T2) (IT2017 ITE-IMA-05 b; IT2017 ITE-IMA-06 a)
   1. Relational DBMS
   2. Content Stores
   3. Document
   4. Event Stores
   5. Graph DBMS
   6. Key-value
   7. Native XML DBMS
   8. Navigational DBMS
   9. Object Oriented DBMS
   10. RDF Stores
   11. Search Engine
   12. Spatial DBMS
   13. Time Series DBMS
   14. Vector DBMS
   15. Wide Column Stores
2. For question 1, provide an example of a DBMS product that uses each DBMS scheme.
3. For question 2, which DBMS product may be considered to be a NewSQL?
4. What is the drawback with using a NewSQL (NoSQL) over using a traditional relational DBMS?
5. Which DBMS support concurrency better than other DBMS types? (IS2020 A3.2.1.5)
6. What is sharding?
7. Imagine your future employment. Of the various DBMS mentioned in this chapter, which one would you use? What is your reason for your choice?

A Running Project

A running project was introduced in Chapter 2. You were encouraged to collect insights about the needs. In Chapter 3, you were told to use a word processor to create tables with columns for the entity pieces. In Chapter 4, you were told to identify the data types. In Chapter 5, you were told to create ERD. In Chapter 6, you were told to revise your ERD to add PK and FK to your tables. You were told to add cardinality between the tables. In Chapter 7, you were to review your tables. Use the normalization steps to bring your database to 3NF.

Select a DBMS. Explain your choice. Install the DBMS on your personal computer.

Attribution

This chapter of Database Design is a derivative copy of Database System Concepts by Nguyen Kim Anh licensed under Creative Commons Attribution License 3.0 license

The second edition chapter 13’s approach was a bit misleading. The other second edition chapters covered the steps for creating a database, but the second edition chapter 13 was repeat of the material from a software engineering viewpoint.

This chapter drew from many sources. The information was completely revised by Fred Strickland for the third edition.

Image Attributions

No second edition images were used.

References

“13 Advantages And Disadvantages of Redis?” Prosconslab, 2023. https://prosconslab.com/articles/32/13-advantages-and-disadvantages-of-redis

“Advantages and Disadvantages of Elasticsearch,” Geeks for Geeks, May 7, 2024. https://www.geeksforgeeks.org/advantages-and-disadvantages-of-elasticsearch/

“Advantages and Disadvantages of Microsoft Access,” theknowledgeacademy, n.d. https://www.theknowledgeacademy.com/blog/advantages-and-disadvantages-of-microsoft-access/

Er R S Banger. “30+ Advantages and Disadvantage of Microsoft Access | Features & Benefits,” DigitalThinkerHelp, July 24, 2023. https://digitalthinkerhelp.com/advantages-and-disadvantages-of-microsoft-access/

Daniel Calbimonte. “How to install SQL Server 2022 step by step,” MS SQL Tips, November 17, 2023. https://www.mssqltips.com/sqlservertip/7313/install-sql-server-2022/

Vera Chen. “11 Best Database Management Systems (2024) [FREE},” April 18, 2024. https://www.datanumen.com/blogs/11-best-database-management-systems-free/

The word “FREE” in the article title is misleading. The information is free, but most of the reviewed DBMSs are not free.

“Content Stores,” DB-Engines, n.d. https://db-engines.com/en/article/Content+Stores

I tried to access this website on October 19, 2024 and it was down.

Stephen Cooper. “Microsoft Access: Is it Still Relevant in 2024?” Comparitech, January 3, 2024 https://www.comparitech.com/net-admin/microsoft-access/

“Db2 database product editions and Db2 offerings,” IBM, January 12, 2024. https://www.ibm.com/docs/en/db2/11.5?topic=editions-db2-database-product-offerings

“Document Stores,” DB-Engines, n.d. https://db-engines.com/en/article/Document+Stores

“Database Software,” Capterra, n.d. https://www.capterra.com/database-management-software/

Capterra is a company that provides solutions to businesses. The company does not sell a product, but finds the best products that satisfy the needs of a business. See https://www.capterra.com/our-story/

“DB-engines Ranking,” DB-Engines, September 2024. https://db-engines.com/en/ranking

“Elasticsearch System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/Elasticsearch

“Event Stores,” DB-Engines, n.d. https://db-engines.com/en/article/Event+Stores

Brandon Gaille. “13 Pros and Cons of Microsoft Access,” BrandonGaille, September 13, 2016. https://brandongaille.com/13-pros-and-cons-of-microsoft-access/

“The Good and the Bad of Snowflake Data Warehouse,” Altexsoft, January 30, 2023. https://www.altexsoft.com/blog/snowflake-data-warehouse-pros-cons/

“Graph DBMS,” DB-Engines, n.d. https://db-engines.com/en/article/Graph+DBMS

“How to Navigate the Oracle Technology Price List,” Oracle Licensing Experts, May 22, 2024. https://oraclelicensingexperts.com/how-to-navigate-the-oracle-technology-price-list/

“IBM Db2 System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/IBM+Db2

“Introduction to NoSQL,” Geeks for Geeks, July 25, 2024. https://www.geeksforgeeks.org/introduction-to-nosql/

Geeks for Geeks has a second article that uses a table to compare SQL and NoSQL. See

“SQL vs NoSQL: Which One is Better to Use,” Geeks for Geeks, August 8, 2024. https://www.geeksforgeeks.org/sql-vs-nosql-which-one-is-better-to-use/

“Key-value Stored,” DB-Engines, n.d. https://db-engines.com/en/article/Key-value+Stores

Curt Kolovson. “Weighing the Pros and Cons of PostgreSQL,” Medium, December 6, 2021. https://medium.com/@ckolovson/weighing-the-pros-and-cons-of-postgresql-5a3603dd34ce

“Microsoft SQL Server System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/Microsoft+SQL+Server

“MongoDB Advantages & Disadvantages,” Geeks for Geeks, December 1, 2023. https://www.geeksforgeeks.org/mongodb-advantages-disadvantages/

“MongoDB System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/MongoDB

“MySQL System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/MySQL

“Native XML DBMS,” DB-Engines, n.d. https://db-engines.com/en/article/Native+XML+DBMS

“Navigational DBMS,” DB-Engines, n.d. https://db-engines.com/en/article/Navigational+DBMS

“Object oriented DBMS,” DB-Engines, n.d. https://db-engines.com/en/article/Object+oriented+DBMS

“Oracle System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/Oracle

Roman Pijacek. “Microsoft SQL Server Pros and Cons,” Learn SQL, January 17, 2019. https://learnsql.com/blog/microsoft-sql-server-pros-and-cons/

“PostgreSQL System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/PostgreSQL

“Pros and Cons of Using Snowflake Cloud Data Warehouse,” Zuar, February 12, 2022. https://www.zuar.com/blog/pros-and-cons-of-using-snowflake-cloud-data-warehouse/

Daniel Raymond. “MySQL: 10 Cons & Disadvantages with 5 Alternatives” ProjectManagers.net, January 25, 2024. https://projectmanagers.net/mysql-10-cons-disadvantages-with-5-alternatives/

“RDF Stores,” DB-Engines, n.d. https://db-engines.com/en/article/RDF+Stores

“Redis System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/Redis

Sophie Reed. “The Advantages and Disadvantages of Microsoft SQL Server,” Rothobot, March 15, 2020. https://rothmobot.com/the-advantages-and-disadvantages-of-microsoft-sql-server/

“Search Engines,” DB-Engines, n.d. https://db-engines.com/en/article/Search+Engines

“Snowflake System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/Snowflake

“Spatial DBMS,” DB-Engines, n.d. https://db-engines.com/en/article/Spatial+DBMS

Mike Springgay. “Six new Db2 capabilities DBAs must try today with Db2 115.9,” IBM, April 9, 2024. https://www.ibm.com/blog/announcement/6-new-db2-capabilities-dbas-must-try-today-with-db2-11-5-9/

“SQL Server Data Tools,” Microsoft Learn, August 20, 2024. https://learn.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-ver16

“SQL Server Editon Feature Comparison,” Express DB, n.d. https://expressdb.io/sql-server-express-feature-comparison.html

“SQLite Advantages” Java T Point, https://www.javatpoint.com/sqlite-advantages-and-disadvantages

“SQLite System Properties,” DB-Engines, 2024. https://db-engines.com/en/system/SQLite

“Time Series DBMS,” DB-Engines, n.d. https://db-engines.com/en/article/Time+Series+DBMS

“Vector DBMS,” DB-Engines, n. d. https://db-engines.com/en/article/Vector+DBMS

“What is SQL Server Management Studio (SSMS)” Microsoft Learn, August 19, 2024. https://learn.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms?view=sql-server-ver16

“What is the disadvantage of just using Redis instead of an RDBMS?” Stackoverflow, n.d. https://stackoverflow.com/questions/10906246/what-is-the-disadvantage-of-just-using-redis-instead-of-an-rdbms

Didier Spezia answered this question on June 6, 2012. Stackoverflow is a forum where people ask questions and others answer. The quality of the answers is high, but there is no one checking on the accuracy of the information.

“Wide Column Stores,” DB-Engines, n.d. https://db-engines.com/en/article/Wide+Column+Stores

Christopher Wong. “Advantages and Disadvantages of Using SQLite,” Medium, March 31, 2023. https://medium.com/@cw30355/advantages-and-disadvantages-of-using-sqlite-2f490fa467bd