Starting from:
$30
CSIT115-Lab 1 Solved
Tasks
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.
It is explained in the Cookbook, Recipe 2.1, Steps 1 and 2 "How to create very simple conceptual schemas with UMLetLet?" how to use UMLetLet to create the diagrams. For a moment, do not worry about the meanings of a diagram created. It will be explained to you later on during the lecture class.
(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.
Deliverables
A file solution2.bmp that contains a diagram above extended with two attributes. A submission of a file solution2.uxf is not expected and it scores no marks. Submission of a file with a different name and/or different extension and/or different type scores no marks as well.
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. It is explained in the Cookbook, Recipe 1.1 Step 2 "How to import an appliance to VirtualBox ?" how to import an appliance.
(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.
Make sure that the saved screen image contains the names of files created by VirtualBox after appliance has been exported in the previous step. Do not delete a file solution3.png. It will be submitted at the end of laboratory exercise.
Deliverables
A file solution3.png that contains a saved screen image with the names of files create by VirtualBox after appliance has been exported.
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.
Before implementation of this task it is strongly recommended to read from the Cookbook all steps of Recipe 3.1 "How to use "mysql" a command based interface to MySQL database server ?".
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;
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. Do not delete a file solution4.rpt. It will be submitted as one of the deliverables from your laboratory work.
tee solution4.rpt command copies the messages and results displayed on a screen into a text file, in this case into solution2.rpt file. notee command stops copying the messages and results displayed on a screen to a file.
source task4.sql command sends SQL statements included in script file task4.sql for processing by MySQL database server.
Deliverables
A file solution4.png and solution4.rpt that contains a saved screen image with the results from processing of a script task4.sql.
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.
Before implementation of this task it is strongly recommended to read from the Cookbook all steps of Recipe 3.2 "How to use a graphical interface to MySQL database server?".
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.
Make sure that the results from processing of a script task5.sql are visible in an image. Do not delete a file solution5.png. It will be submitted at the end of laboratory exercise.
Deliverables
A file solution5.png that contains a saved screen image with the results from processing of a script task5.sql.
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.
It is explained in the Cookbook, Recipe 2.1, Steps 1 and 2 "How to create very simple conceptual schemas with UMLetLet?" how to use UMLetLet to create the diagrams. For a moment, do not worry about the meanings of a diagram created. It will be explained to you later on during the lecture class.
(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.
Deliverables
A file solution2.bmp that contains a diagram above extended with two attributes. A submission of a file solution2.uxf is not expected and it scores no marks. Submission of a file with a different name and/or different extension and/or different type scores no marks as well.
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. It is explained in the Cookbook, Recipe 1.1 Step 2 "How to import an appliance to VirtualBox ?" how to import an appliance.
(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.
Make sure that the saved screen image contains the names of files created by VirtualBox after appliance has been exported in the previous step. Do not delete a file solution3.png. It will be submitted at the end of laboratory exercise.
Deliverables
A file solution3.png that contains a saved screen image with the names of files create by VirtualBox after appliance has been exported.
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.
Before implementation of this task it is strongly recommended to read from the Cookbook all steps of Recipe 3.1 "How to use "mysql" a command based interface to MySQL database server ?".
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;
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. Do not delete a file solution4.rpt. It will be submitted as one of the deliverables from your laboratory work.
tee solution4.rpt command copies the messages and results displayed on a screen into a text file, in this case into solution2.rpt file. notee command stops copying the messages and results displayed on a screen to a file.
source task4.sql command sends SQL statements included in script file task4.sql for processing by MySQL database server.
Deliverables
A file solution4.png and solution4.rpt that contains a saved screen image with the results from processing of a script task4.sql.
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.
Before implementation of this task it is strongly recommended to read from the Cookbook all steps of Recipe 3.2 "How to use a graphical interface to MySQL database server?".
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.
Make sure that the results from processing of a script task5.sql are visible in an image. Do not delete a file solution5.png. It will be submitted at the end of laboratory exercise.
Deliverables
A file solution5.png that contains a saved screen image with the results from processing of a script task5.sql.
1 file (547.6KB)
