Module 8 - Virtual Desktop and Implementing SQL Queries | Introduction to Database Systems

Module_8 – Virtual Desktop and Implementing SQL Queries

8.1 Introduction and Background

Desktop virtualization is a computing method in which computers categorized as “thin clients” are implemented in an environment where the system components are protected from the underlying platforms. (Agrawal) The benefit of desktop virtualization seems to be that the efficiencies of central processing can be used in a more practical manner.

Historically speaking, in the 1960s, IBM began working on a mainframe system to address the faults of batch processing. Around this time, MIT began seeking proposals from hardware companies to assist with its Project MAC: a research endeavor, funded with a DARPA grant, that needed the capability of more than one simultaneous user. Thus, IBM temporarily began to shift its focus to Project MAC, but eventually lost out to GE, due to their unwillingness to commit to a technology that they didn’t see as relevant. (Conroy, “History of Virtualization”) Nevertheless, this prompted IBM to reassess the demand for this technology, and, consequently, they designed the cp-40 mainframe. This product was not sold for consumer use but later evolved into the cp-67, the first commercial mainframe to support virtualization.

Moreover, the cp-67 was run on an operating system called the CP/CMS (control program, console monitoring system); the console monitoring system was an interactive OS, and the control program created the virtual machine. Ideally, the CP would run on the mainframe and the user would interact with VM that ran on the CMS. Further, up until then, to achieve a time-sharing computer, memory and other system resources were divided up between users. Virtual machines are advantageous to the time-sharing OS because of their ability to share mainframe resources. VMs also provided better security and overall reliability.

Jumping forward, in the 1990s, Sun Microsystems began Project “Stealth.” It was renamed several times, but eventually the company settled on what we now know as Java. Java was created to address the lack of a universal method for running a network of computers, each with distinct operating systems, on the World Wide Web. So, in 1996 the Java Development Kit (JDK) was released, allowing applications to be developed for the Java platform. This was revolutionary at the time because there had been no language that allowed someone to write an application once and then run it on any computer, via the JRE (Java Runtime Environment).

Additionally, the company VMWare is a succeeding pioneer of Virtualization technology. Via their YouTube channel, VMSpot, they explain how VMWare was started by three Stanford University students and their professor. Using their research paper as a basis, VMWare’s concept was built around an unpopular idea from the 1960s and ’70s, virtual machine monitors, and the students wanted to try it again with the 1990s tech.

Further, the team, consisting of Edouard Bugnion, Scott Devine, Kinshuk Govil and Mendel Rosenblum (professor), hypothesized that they could run multiple copies of an operating system on one computer. This theory proved to have legs when the team successfully ran the IRIS OS on single machine with a mere 13,000 lines of code. (VMSpot, The Unofficial…) After connecting with Rosenblum’s wife in 1998, the group officially founded VMWare and proceeded to acquire a 550 sq-ft space in Palo Alto California. After a few false starts and skepticism about their mission, the first Windows VM was started in March of 1998. The product later went into beta in March of 1999 and the user response was favorable.

In addition to those mentioned, there were other miscellaneous forays into the virtualization tech space. For example, in 1987 the company Insignia Solutions demonstrated an emulator called SoftPC, which allowed DOS applications to run on Unix workstations. This was important because prior to the demonstration, to run MS-DOS, one needed a computer upwards of $1500. With SoftPC virtual desktop, all one needed was a $500 Unix terminal to run MS-DOS. In 1997, Apple also chimed in with their Virtual PC product, distributed by the company Connectix. Virtual PC allowed users to run a copy of Windows on a Mac machine.

In essence, virtualization can be used to make applications easier to access remotely. IBM paved the way with the cp-67 mainframe, but the field seemed to lose steam in the late ’70s and ’80s. However, with Sun Microsystem’s release of Java in the early ’90s followed by VMWare’s entry into market, the Virtualization industry saw a resurgence and seems to be here to stay.

8.2 Connect to a virtual desktop

The virtual desktop is a powerful and useful tool available to Kennesaw State students but to use them, you must access them with a computer that has certain requirements. You must have Windows 7 or higher or Mac OS 10.8, an ethernet cable and display resolution above 800 x 600, and if you are on a Mac, you must have Mac AirPort turned off. Another requirement is that you must have VMware downloaded on your device. Downloads of VMware for both Windows and Mac OS can be found here: https://virtualowl.kennesaw.edu/vmware-view-client.php

Now that you have VMware downloaded on your computer, open the VMware program. After VMware is opened, you will see a button that says, "New Server" (FIG 8.2.1). After pressing that button, a pop-up will appear that will ask you to "Enter the name of the Connection Server" (FIG 8.2.2). For the purposes of a 3410 student, you will enter the site "CSEView.kennesaw.edu" into the text box then press "Connect". After setting up the new server there will be a cloud image with the site name under it that appears on the software (FIG 8.2.1). To connect to that server, click on the cloud icon and it will have a login pop-up where you just must enter your Kennesaw State Username (just the username, not the email) and your accompanying password (FIG 8.2.3). On entering a correct username and password, you will be given 2 choices (FIG 8.2.4). For the purpose of our class, click on the CS 3410 option, because it includes programs which will be helpful for assignments. After clicking on the choice, it will take you to the virtual desktop. After you are done with using the virtual desktop, you can go to the option tab at the top, scroll over it then click on "Disconnect and Log Off" to exit from the virtual desktop (FIG 8.2.5).

FIG 8.2.1

FIG 8.2.2

FIG 8.2.3

FIG 8.2.4

FIG 8.2.5

8.3 Constructing a Database

A relational database management system (RDBMS) is a database management system built on E.F. Codd’s relational model. An RDBMS has several key components, one of which is the table: these are objects in which the data is stored. Tables consists of rows and columns and are essentially a collection of data entries. Further, tables are broken up into smaller units called fields, which are columns in a table designed to maintain specific information on all the table’s records. Consequently, the rows in the table are called records, and these are each individual entry that exists in a table; it is a horizontal entity. Likewise, columns are the vertical entries in a table that contain all the information associated with a particular field.

Moreover, the RDBMS is a basis for SQL and is evident in modern applications. One example of these is MySQL, which is a Swedish product first developed in 1994. Although MySQL has paid versions, it is probably most known for being the open-source (free) database. Another popular RDBMS is SQL server, which is a Microsoft creation that primarily works with T-SQL and ANSI SQL. SQL Server is primarily for enterprise usage and, unlike MySQL, is not open source. Oracle is another major database management system developed by the Oracle Corporation. Oracle begin in 1977, and its strength is its client/server computing aspect. Last is Access, which is one of Microsoft’s most popular products. This is probably because Access is a more beginner-friendly database than the others mentioned. In addition to being inexpensive, it is also powerful and well-suited for smaller projects. Unlike the client-server model utilized by Oracle, Access is a file-server based relational database management system. With this said, we’ll walk through the steps of constructing a database in MS Access.

If Access is closed, the beginning steps are fairly intuitive. However, if a user has Access already open (viewing an existing database for example) then one simply needs to click the External Data tab at the top:

Then, click on New Data Source and follow the prompt to import a local xlsx dataset titled “Impaired Driving Death Rate by Age and Gender 2012 2014 All States.” The result appears as follows:

That is pretty much it to instantiate a database. It should be noted there are other methods of constructing a database: from a high level, the CREATE DATABASE statement can be declared via SQL, relative to which RDBMS is being used. The latter method is beyond the scope of this section.

8.4 Implementing SQL Queries

SQL stands for structured query language and, generally, it is used to communicate with databases. Recognized by ISO and ANSI, SQL is widely used on large databases and even has built-in functionality with Oracle and Microsoft products. Generally, a database’s files are interpreted on a server as a standard relational database. Via a user, client programs can then manipulate these data files by means of tables, columns, rows and fields. Client programs do this by sending SQL statements to the server, which then processes and returns result sets to the client program.

For clarity, a query is a type of SQL statement that returns data. Informally, an SQL query refers to the SELECT statement; when it is implemented the result is in the form of a table that can be viewed via client software (e.g., Microsoft Access) or used within programming languages. Essentially, the syntax of the SELECT statement consists of four clauses, which are uppercase by convention but don’t have to be.

Further, the SELECT clause allows the user to specify a comma-separated list of attribute names that correspond to the columns to be retrieved; an asterisk (*) placed after the clause tells SQL to retrieve all the columns. Secondly, with the FROM clause a developer can specify the table name from which to retrieve rows, granted that the data is all found in one table. Next, the WHERE clause is used to restrict which rows to retrieve; this is done by specifying a Boolean predicate that compares the values of table columns to those of literal values or other columns. Lastly, the ORDER BY clause provides a way to arrange the display of the rows in the result of the statement. It also should be noted that the second two clauses, WHERE and ORDER BY are optional.

With that said, the following is an example of an SQL query on a csv file turned database. It consists of a single table titled Impaired Driving Death Rate by Age and Gender 2012 2014 All States, and Microsoft Access is used to analyze it.

First, we retrieve all the data: