SQL101 / Relational Database Management Systems

A lot of SQL tutorials go through the ins and outs of writing SQL Statements, however, I wanted to do something slightly different and more comprehensive in this tutorial set. I want to walk you through the end-to-end steps of installing a Relational Database Management System (RDBMS) which will be Microsoft SQL Server Express, and with subsequent tutorials teach you:

• The basics of writing SQL (DML)
• The basics of working with database structures (DDL)
• The basics of performance tune
• The basics of functions and procedures
• The basics of database design from Conceptual, Logical to Physical Design

Relationship Database Management Systems

A relational database management system (RDBMS) is a database management system (DBMS) based on the relational model invented by Edgar F. Codd at IBM’s San Jose Research Laboratory. Edgar F. Codd produced 12 rules as part of a personal campaign to prevent the vision of the original relational database from being diluted, as database vendors scrambled in the early 1980s to repackage existing products with a relational veneer, rule 12 was particularly designed to counter such a positioning.

• Rule 0: The foundation rule
  For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities.
• Rule 1: The information rule
  All information in a relational database is represented explicitly at the logical level and in exactly one way – by values in tables.
• Rule 2: The guaranteed access rule
  Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.
• Rule 3: Systematic treatment of null values
  Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.
• Rule 4: Dynamic online catalogue based on the relational model
  The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.
• Rule 5: The comprehensive data sublanguage rule
  A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all of the following items:

   

  • Data definition
  • View definition
  • Data manipulation (interactive and by a program)
  • Integrity constraints
  • Authorization
  • Transaction boundaries (begin, commit and rollback)
• Rule 6: The view updating rule
  All views that are theoretically updatable are also updatable by the system.
• Rule 7: Possible for high-level insert, update and delete
  The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.
• Rule 8: Physical data independence
  Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.
• Rule 9: Logical data independence
  Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
• Rule 10: Integrity independence
  Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalogue, not in the application programs.
• Rule 11: Distribution independence
  The end-user must not be able to see that the data is distributed over various locations. Users should always get the impression that the data is located at one site only.
• Rule 12: The non-subversion rule
  If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher level relational language (multiple-records-at-a-time).

However, many of the early implementations of the relational model did not conform to all of Codd’s rules, so the term gradually came to describe a broader class of database systems, which at a minimum:

• Present the data to the user as relations (a presentation in tabular form, i.e. as a collection of tables with each table consisting of a set of rows and columns)
• Provide relational operators to manipulate the data in tabular form.

The first systems that were relatively faithful implementations of the relational model were from:

• University of Michigan — Micro DBMS (1969)
• Massachusetts Institute of Technology (1971)
• IBM UK Scientific Centre at Peterlee — IS1 (1970–72) and its successor, PRTV (1973–79)

The first system sold as an RDBMS was Multics Relational Data Store (1978). Ingres and IBM BS12 followed.

According to DB-Engines, in June 2018, the most widely used systems were Oracle, MySQL (Free software), Microsoft SQL Server, PostgreSQL (Free software), IBM DB2, Microsoft Access, and SQLite (Free software). Thankfully, Microsoft SQL Server comes with an Express Edition.

Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).

Microsoft SQL Server Express is a version of Microsoft’s SQL Server relational database management system that is free to download, distribute and use. It comprises a database specifically targeted for embedded and smaller-scale applications. The product traces its roots to the Microsoft Database Engine (MSDE) product, which was shipped with SQL Server 2000. The “Express” branding has been used since the release of SQL Server 2005.

SQL Server Express provides many of the features of the paid, full versions of Microsoft SQL Server database management system. However, it has technical restrictions that make it unsuitable for some large-scale deployments. Differences in the Express product include:

• Maximum database size of 10 GB per database in SQL Server 2016, SQL Server 2014, SQL Server 2012, and 2008 R2 Express (4 GB for SQL Server 2008 Express and earlier; compared to 2 GB in the former MSDE). The limit applies per database (log files excluded); but in some scenarios, users can access more data through the use of multiple interconnected databases.
• No SQL Server Agent service
• Artificial hardware usage limits:
  • Single physical CPU, but multiple cores allowable
  • 1 GB of RAM (runs on a system with higher RAM amount, but uses only at most 1 GB per instance of SQL Server Database Engine. “Recommended: Express Editions: 1 GB All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.”). Express with Advanced Services has a limit of 4 GB per instance of Reporting Services (not available on other Express variants). Analysis Services is not available for any Express variant.

Unlike the predecessor product, MSDE, the Express product does not include a concurrent workload-governor to “limit performance if the database engine receives more work than is typical of a small number of users”. SQL Server Express includes several GUI tools for database management. These include:

• SQL Server Management Studio – since 2012 SP1; before that, only a stripped-down version called SQL Server Management Studio Express is provided.
• SQL Server Configuration Manager.
• SQL Server Surface Area Configuration tool.
• SQL Server Business Intelligence Development Studio.

Installing Microsoft SQL Server Express

Let us get started by downloading Microsoft SQL Server Express from the Microsoft official website: https://www.microsoft.com/en-ie/sql-server/sql-server-editions-express

To install:

• Double-click the installation file (you may require administrator rights).
• Choose Basic.
• Read the Microsoft SQL Server License agreement and click Accept.
• Choose where you want the installation to go (make sure you have free space).
• Click Install.
• Click Close one the install is completed.

Congratulation, you have installed Microsoft SQL Server Express Edition, now we will install SQL Server Management Studio that will give you a fantastic user interface to interact with your newly minted SQL Server Database.

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a software application first launched with Microsoft SQL Server 2005 that is used for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server.

A central feature of SSMS is the Object Explorer, which allows the user to browse, select, and act upon any of the objects within the server. It also shipped a separate Express edition that could be freely downloaded, however recent versions of SSMS are fully capable of connecting to and manage any SQL Server Express instance. Microsoft also incorporated backwards compatibility for older versions of SQL Server thus allowing a newer version of SSMS to connect to older versions of SQL Server instances.

To install:

• Go to: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
• Click Download SQL Server Management Studio.
• Double-click on the downloaded file to start the installation.
• Click Install.
• Click Restart to complete the installation.

Once your machine has restarted:

• Open Microsoft SQL Server Management Studio.
• In the Connect to server window, click on connect.

Congratulations, now have installed Microsoft SQL Server Express and connected to it via Microsoft SQL Server Management Studio.

AdventureWorks

AdventureWorks is a sample database provided by Microsoft; for our SQL tutorials, we will make use of this so we need to set this up on your locally installed Microsoft SQL Server database.

To start we will download the provided database backup files and restore them to your locally installed SQL Server database:

• Go to the following: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure
• Download the following file:
  • AdventureWorks2017.bak
  • AdventureWorksDW2017.bak
• Copy these two files to the backup directory of your SQL Server, for me this is here: C:\SQLServer2017Media\MSSQL14.SQLEXPRESS\MSSQL\Backup

Now for the restore:

• Open SQL Server Management Studio.
• Connect to your SQL Server Database.
• Right-click on the Databases node, and select Restore Database.
• Select Device and click the (…)
  • In the Select backup devices window make sure File is selected and then click Add.
  • Navigate to the database backup in the filesystem of the server, and select the AdventureWorks2017.bak and click OK.
  • Click OK. This will initiate the database restore. After it completes, you will have the AdventureWorks2017 database installed on your SQL Server instance.
• Repeat this process for the AdventureWorksDW2017.bak.

Note: Restore the two files separately so that we have two databases afterwards.

Summary

Congratulations, we have now:

• Talked about Relational Database Management Systems.
• Installed Microsoft SQL Server Express.
• Installed Microsoft SQL Server Management Studio.
• Installed the AdventureWorks Relational and Dimensional Databases.

We will cover relational and dimensional modelling in other articles, but in the next part of this SQL introductory series, we will cover the basics of the SQL Data Manipulation Language (DML).

I hope you enjoyed this article and, as always, let me know what you thought about this and what other things you would like me to write about.

Extra Credit

• Open up Tableau Desktop
• Connect Tableau to the AdventureWorks
• Explore the data
• Build some fun dashboards.

Sources

• Wikipedia
• Microsoft Official Website(s)