CSIT115-Lab 1 Solved

Task 1  
 

1. Using this Lunch Room Data Report, answer the questions that follow 

Date 
Student  Sales 
Faculty/Staff Sales 
Hamburger/Taco Bar 
Pizza Bar 
Soup/Salad Bar 
12/02/2003 
497 
23 
335 
122 
63 
12/03/2003 
440 
19 
285 
126 
48 
12/04/2003 
447 
30 
301 
126 
50 
12/05/2003 
442 
27 
325 
107 
37 
12/06/2003 
330 
12 
229 
83 
30 
 

(a)     What does this report mean? 

(b)    What data was collected? 

(c)     What information does this table provide from the data collected? 

(d)    How do you think this information is used by those reading the report? 

(e)     Generate at least two conclusions based on the data provided. 

(f)     Generate at least two questions that you would ask about the data provided. 

 

Deliverables
A file solution1.pdf that contains 6 short, numbered and clear answers to be saved and submitted.  Submission of a file with a different name and/or different extension and/or different type scores no marks as well. 

Task 2  
An objective of this task is to show you how to create conceptual database schema with UMLet 14.3 

 

Perform the following steps,  

 

(1)     Start UMlet application (an icon consists of a red circle crossed with a horizontal dark blue bar).  

 

(2)     Implement in UMLetLet the following diagram. 

 

 

  

 

 

 

 

(4)     Add one more attribute to a class STORE and one more attribute to a class JOB. The names of attributes are up to you. Make one of the attributes optional. 

 

(5)     Use option "File-Save" to save your diagram in a file solution2.uxf. Do not delete a file solution2.uxf.  

 

(6)     Us as option File à Export as … to export your diagram into a file solution2.bmp in BMP format.  Do not delete an exported file. You will submit it as one of the deliverables from your laboratory work. 

 


 

Task 3  
Objective of this task is to learn how to import and export an appliance into Oracle VM Virtual Box.  Also learn how to connect USB drive to Virtual Box. 

 

Perform the following steps. 

(1)     Start and connect to your Oracle VM VirtualBox. A shortcut to VirtualBox is available on a desktop. It is explained in the Cookbook, Recipe 1.1, Step 1 "How to start VirtualBox ?" how to start VirtualBox. 

 

(2)     Import an appliance Ubuntu18.04-64bits-MySQL8.0.13-07-NOV-2018. 

 

(3)     Connect your USB drive to VirtualBox. It is explained in the Cookbook, Recipe 1.1 Step 3 "How to connect USB drive to VirtualBox ?" how to connect USB drive to VirtualBox. 

 

(4)     Export a virtual machine to your USB drive. It is explained in the Cookbook, Recipe 1.1 Step 5 "How to export an appliance from VirtualBox ?" how to export a virtual machine. Note, that exporting a virtual machine takes some time, usually more than 5 minutes. 

 

(5)     When your virtual machine is exported, navigate to a folder on your USB drive where the exported files have been saved.  

 

To take an image of a screen, simultaneously press "Shift" and "Print Scrn" buttons.  

 

Next, start Paint program to save the image in a file. A path to start Paint program is: "Start button-Accessories-Paint". When started, simultaneously press the keys CTRL V to load the image into Paint. To save the image in a file solution3.png use "Save" button (small disk icon in the topmost menu). When saved you can quit Paint and move to the next task. 

 

                                                 

Task 4  
An objective of this task is to learn how to use a command line interface mysql to process SQL scripts and how to create and save the reports from processing of SQL scripts. 

 

 

Perform the following steps.  

 

If an appliance Ubuntu18.04-64bits-MySQL8.0.13-07-NOV-2018 is already imported then skip step (1) and (2) and start from a step (3). 

  

(1)       Otherwise, start start Oracle VM VirtualBox. A shortcut to VirtualBox is available on a desktop. Cookbook, Recipe 1.1, Step 1 "How to start VirtualBox ?" how to start VirtualBox. 

 

(2)       Import an appliance Ubuntu18.04-64bits-MySQL8.0.13-07-NOV-2018 located. It is explained in Cookbook, Recipe 1.1 Step 2 "How to import an appliance to VirtualBox ?" how to import an appliance. 

 

(3)       Power on a virtual machine Ubuntu18.04-64bits-MySQL8.0.13-07-NOV2018. It is explained Cookbook, Recipe 1.1 Step 4 "How to power on a virtual machine?" how to power on a virtual machine.  

 

(4)       When prompted by Ubuntu 18.04 operating system to type in a password to login as csit115 operating system user enter csit115 and press Enter key. Then, wait until operating system displays a column of icons on the left hand side of a screen. 

 

(5)       Start Terminal program (a black rectangle icon with white frame in a column of icons). Next, in Terminal window type gedit task4.sql and press Enter key to open a text editor with a new file task4.sql.  

 

(6)       Type into gedit window the following lines. 

 

SELECT CURDATE() "Today is:" 

FROM DUAL; 

 

       Next, insert another select statement such that it prints your name. 

             

and save a file task4.sql. Note, that there is no blank between "CURDATE" and "()" 

! Quit gedit editor. 

 

(7)       Type at command prompt: 

 

 mysql csit115 –p –v 

 

       and press Enter key to start command based interface to MySQL database server. 

 

(8)       When started execute a command: 

 

use csit115; 

 

at mysql prompt to select csit115 database. 

 

(9)       Next, execute a command: 

 

 source task4.sql; 

 

       at mysql prompt to submit a script task4.sql for processing by MySQL. 

 

(10)    When ready shrink a bit window with VirtualBox and save screen image in a file solution4.png in the same way as you did it in a Task 3.  

 

 Make sure that the results from processing of a script task4.sql are visible in an image. Do not delete a file solution4.png. It will be submitted at the end of laboratory exercise. 

 

(11)    At mysql prompt type exit; 

 

(12)    It is also possible to submit a script task4.sql for processing by MySQL and to save a report from processing of the script in a file solution4.rpt.  Please proceed with Step 13. 

  

(13)    Repeat steps 7 and 8 

 

(14)    Next, process the following commands in Terminal window at mysql prompt  

 

 tee solution4.rpt;         source task4.sql; 

        notee; 

 

 

             


Task 5  

An objective of this task is to learn how to use a graphical user interface (GUI) MySQL Workbench to create and to process SQL scripts. 

 

 

It is assumed that after implementation of Task 4 your appliance is up and running. If not, then start VirtualBox, import your appliance, and run it. 

 

Perform the following steps. 

 

(1)   Power on a virtual machine Ubuntu18.04-64bits-MySQL8.0.13-07NOV-2018. It is explained in Cookbook, Recipe 1.1 Step 4 "How to power on a virtual machine ?" how to power on a virtual machine.   

 

(2)   When Ubuntu 18.04 operating system displays an orange box with a name of  CSIT115 user press Enter key and then type csit115 into a Password field.  Then, wait until operating system displays a column of icons on the left-hand side of a screen 

 

(3)   To start MySQL Workbench graphical interface click at a blue icon with a silhouette of a white dolphin. 

 

(4)   Next, click at a small "+" sign located after "MySQL Connections" string to create a new connection. Type into a field Connection Name your connection name, into a field Username csit115, and into a field Default Schema csit115. 

Finally, click at OK button. 

 

(5)   To open your new connection left click at a rectangle that represents a new connection in a front panel of MySQL Workbench. Next, type in a password: 

csit115 of csit115 database user and click at OK button. 

 

(6)   Type into Query 1 sub window the following lines. 

 

CREATE TABLE CUSTOMER(name    VARCHAR (30) NOT NULL, 

                                                              (id          INTEGER(4) NOT NULL); 

 

INSERT INTO CUSTOMER VALUES(‘Jimmy, 1234); 

INSERT INTO CUSTOMER VALUES(‘Mary’,456); 

 

COMMIT; 

 

SELECT * , NOW(); 

FROM CUSTOMER; 

(7)   Save the text typed in the previous step as SQL script in a file task5.sql. To do so either use File-Save Script As …  menus or "floppy disk" icon in Query 1 sub window (the 2nd icon from left, btw do you know what is a "floppy disk"? ). Do not delete a file task5.sql. You will submit it as one of the deliverables from your laboratory work 

 

(8)   To submit a script for processing by MySQL database server left click at the first line of a script and later on left click at "yellow lightning" icon just above a workspace with the script. You should get the results of processing in a workspace below.  If the results do not show up you may have to expand the sub windows with the results and the messages. To do so, move a cursor to a bottom line of sub window task5 with a text of a script task5.sql to a location where a cursor changes its shape to a vertical double (up and down) arrow. Then press a left button and while it is pressed move a cursor up. Repeat such procedure for both results and messages sub windows. You may need to ask a tutor about help.  

 

(9)   Note, that it is also possible to process a single SQL statement from your script independently on the other statements. To do so, left click at one of INSERT statements and then left click at "yellow lightning" icon with a silhouette of "white cursor" just above a workspace with the script (the 4th icon from left). A technique of processing the individual SQL statements in one-by-one mode is very useful at the debugging stages. 

 

(9) When ready shrink a window with VirtualBox and save screen image in a file solution5.png in the same way as you did it in a Task 3.