ISIT312/ISIT912 Big Data Management
Scope
This laboratory includes the tasks related to the simple applications of the software
systems that wil be used in a subject ISIT312/ISIT912 Big Data Management in Autumn
sesion 2017 and a simple data warehouse design.
Important mesage
Please bring with you to a laboratory clas USB memory stick or USB external drive
needed to save an exported virtual machine with Oracle 12c R2. Your external persistent
memory must have at least 16 Gbytes of free space.
More implementation related information can be found in "How to … ?" Cookbook
available through Moodle or at:
http://www.uow.edu.au/~jrg/312/COOKBOOK.
The outcomes of the laboratory work are due by Wednesday, 9 August, 2017, 7.00 pm
(sharp).
This laboratory contributes to 1% of the total evaluation in the subject.
A submision procedure is explained at the end of specification.
Specification of each task starts from a new page.
It is recommended to solve the problems before atending an enrolled laboratory clas in
order to eficiently use supervised laboratory time later on.
Tasks
Prologue
Please read information listed below about a virtual machine running Oracle 12 c R2
database server (Oracle-Linux-Oracle-12c-R2-Plug-312-15-JUL-2017)
for a subject ISIT312/912 Big Data Management in Spring sesion 2017. The virtual
machine hosts Oracle Linux with Gnome and Oracle 12c R2 with so caled pluggable
database instaled. Al paswords are: oracle .
SID of pluggable database used is orcl (wel, such information is always displayed at
the welcome screen).
Two clients are available to aces Oracle database server:
(1) SQL*Plus - Command Line Interface (CLI)
(2) SQLDeveloper – Graphical User Interface (GUI)
The following Oracle users can be connected as while working on the laboratories and
asignments in ISIT312/912:
SYSTEM (Oracle DBA user)
SYS (Oracle DBA user)
NORTHWIND (Owner of Northwind data warehouse)
TPCHR (Owner of TPC-HR benchmark database, http://www.tpc.org)
TPCDS (Owner of TPC-DS benchmark data warehouse http://www.tpc.org)
A folder NORTHWIND located in a home folder contains al SQL scripts needed to create
and to load the relational tables.
- A script. tbscreate.sql creates NORTHWIND tablespace (must be run as SYSTEM
or SYS user ).
- A script. usrcreate.sql creates NORTHWIND user (must be run as SYSTEM or
SYS user).
- A script. dbcreate.sql creates the relational tables (must be run as NORTHWIND
user).
- A script. dbload.sql loads data into the relational tables (must be run as
NORTHWIND user).
- A script. dbdrop.sql drops the relational tables (must be run as NORTHWIND user).
- A script. usrdrop.sql drops NORTHWIND user (must be run as NORTHWIND user).
- A script. tbsdrop.sql drops NORTHWIND tablespace (must be run as SYSTEM or
SYS user).
- A script. list.sql lists the names of the tables owned by NORTHWIND user (must
be run as NORTHWIND user).
- A script. dbcount.sql counts the total number of rows in each relational table
(must be run as NORTHWIND user).
- A script. sfs.sql lists storage consumption in the tablespaces (must be run as
SYSTEM or SYS user).
- A script. fs.sql lists information related to files used for the tablespaces (must be
run as SYSTEM or SYS user).
- A script. setplustrace.sql runs a script. plustrce.sql and grants
PLUSTRACE role to NORTHWIND user (must be run as SYS user)
A folder TPCHR located in a home folder contains al SQL and shel scripts and files
needed to create a sample TPC-HR benchmark database.
- The scripts with the same names as in NORTHWIND folder play same roles as the
scripts in NORTHWIND folder.
- The files tpcr.pdf and tpcr.doc contain a conceptual schema of the database.
- The files *.tbl contain synthetic data to be loaded to the database.
- The files *.ctl contain control statement for Oracle SQL Loader (sqlldr).
- A shel script. dbload.sh loads synthetic data into the database.
- The database occupies approximately 300+Mbytes.
A folder TPCDS located in a home folder contains all SQL and shel scripts and files
needed to create a sample TPC-DS benchmark data warehouse.
The scripts with the same names as in NORTHWIND folder play same roles as the scripts
in NORTHWIND folder.
A script. dbri.sql add referential integrity constraints to the relational tables (must be
run as TPCDS user).
A script. dbsource.sql create "source" relational tables for the data warehouse (must
be run as TPCDS user).
A script. ls.sql lists the names of al relational tables (must be run as TPCDS user).
A script. tbsexpand.sql can be use to expand a tablespace through expansion of a file
(must be run as SYSTEM or SYS user).
The files *.dat contain synthetic data to be loaded to the data warehouse.
The files *.ctl contain control statement for Oracle SQL Loader (sqlldr).
A shel script. dbload.sh loads synthetic data into the data warehouse.
The TPC-DS benchmark data warehouse at the moment occupies 1+Gbytes (when
necesary we can generate more ;) ) .
A standard procedure for recreating the users and data base/warehouses is the following:
Connect as a user SYSTEM.
Proces SQL script. tbscreate.sql.
Connect (for instance) as a user NORTHWIND .
Proces SQL script. dbcreate.sql.
Load data (for Northwind data warehouse proces SQL script. dbload.sql, for TPC-
HR and TPC-DS run a Linux shel script. dbload.sh at command prompt).
Proces SQL script. setplustrace.sql as a user SYS.
The connections to the users SYS, SYSTEM , NORTHWIND, TPCHR, TPCDS have been
already ready on SQLDeveloper.
Task 1 (0.2 mark)
Perform. the following steps.
(1) Start and connect to your Windows 7 system and then start Oracle VM VirtualBox.
A shortcut to VirtualBox is available on a desktop. It is explained in the Cookbook,
Recipe 1.1 "How to use VirtualBox ?" how to use VirtualBox.
(2) When VirtualBox is ready import an appliance Oracle-Linux-Oracle-12c-
R2-Plug-312-15-JUL-2017 located in a shared folder
\adeis.uow.edu.au\EISPUB\Public\ISIT312\. It is explained in
Cookbook, Recipe 1.1 Step 2 "How to import an appliance to VirtualBox ?" how to
import an appliance. It wil take some time to import the appliance.
Note, that at this point you can copy a file Oracle-Linux-Oracle-12c-R2-
Plug-312-15-JUL-2017.ova to your USB drive, such that after instalation of
VirtualBox on your system at home you are able to import and to use the appliance at
home in the same way as you can use it at Uni.
(3) Power on a virtual machine Oracle-Linux-Oracle-12c-R2-Plug-312-
15-JUL-2017. It is explained Cookbook, Recipe 1.1 Step 4 "How to power on a
virtual machine ?" how to power on a virtual machine. It wil also take some time to
power the virtual machine. Please be patient.
(4) When ready read information listed in Terminal window and then type exit to
close the window.
(5) Navigate through the menus Applications->Terminal to open a new
Terminal window. Use a command cd NORTHWIND to move to NORTHWIND
folder.
(6) Start SQL*Plus client (it is explained in Cookbook in Recipe 2.1 " How to aces
Oracle database server from SQL*Plus client running on Oracle Linux ?" how to
start and how to use SQL*Plus CLI to Oracle database server.
(7) While connected as user NORTHWIND proces SQL script. list.sql to list the
names of relational table. You can also use describe command to list the names
and type of columns in the tables. A file nortwind.pdf contains an extended
logical schema of Northwind data warehouse. You can also use gedit editor to
read a file dbcreate.sql and learn more about the structures of Northwind data
warehouse.
(8) Implement the following queries as SELECT statements of SQL and save the
statements in SQL script. file solution1.sql.
(i) Find the dates of all orders submited by the customers living in a city Buenos
Aires.
(ii) Find the addreses of all customers who submited no orders in 1998.
(ii) Find the identifiers of all customers who submited orders in 1996 and in 1998
and in 1997.
(iv) Find the identifiers of all customers who submited more than 25 orders together
with the total number of orders submited by each one of them.
When ready, proces SQL script. solution1.sql and save a report from
procesing in a file solution1.lst. It is explained in Cookbook, Recipe 2.4 "
How to use SQL*Plus client ?", Step 9 how to create and save a report from
procesing of SQL script.
Deliverables
A file solution1.lst that contains a report from procesing of a script.
solution1.sql.
A report must list al SQL statements procesed. To do so put a command SET ECHO
ON at the beginning of SQL script. solution1.sql.
A report that contains no listing of the procesed SELECT statement scores no marks.
Submision of a report with the procesing errors scores no marks.
Submision of a file with a diferent name and/or diferent extension and/or diferent type
scores no marks.
Task 2 (0.2 mark)
Perform. the following steps.
(1) If a virtual machine with Oracle server is not started yet then perform. the steps (1) –
(4) listed in Task 1.
(2) Start SQLDeveloper GUI (it takes some time to start it, please be patient). It is
explained in Cookbook, Recipe 2.2 " How to aces Oracle database server from
SQL Developer ?" how to use SQL*Developer.
(2) Open a connection to TPCHR user and use a graphical interface to investigate the
structures of TPC-HR benchmark database. A conceptual schema of the database is
included in a file tpchr.pdf and CREATE TABLE statements are included in a
file dbcreate.sql.
(3) Implement in SQL the following actions and save your SQL statements in a script.
file solution2.sql.
(i) Find the total number of orders submited each year. List year and the total
number of submited orders. Skip the years with no orders. Sort the results in the
ascending order of the total number of orders.
(ii) Create a new relational table ORDERS1995 and copy into it information about
the orders submited in 1995. There is no need to enforce any consistency
constraints on the new table.
(ii) Add to a relational table ORDERS1995 a column COMPLETED of type
CHAR(1) and fil the entire column with a value 'Y'.
(iv) Drop all relational tables created in this task.
When ready, proces SQL script. solution2.sql and save a report from procesing
in a file solution2.lst. It is explained in Cookbook, Recipe 2.2 " How to aces
Oracle database server from SQL Developer ?" how to use SQL Developer. Use
gedit and Copy/Paste technique to transfer a report from SQL Developer window to
a file solution2.lst.
Deliverables
A file solution2.lst that contains a saved report from procesing of a script.
solution2.sql.
A report must list al SQL statements procesed.
A report that contains no listing of the procesed SELECT statement scores no marks.
Submision of a report with the procesing errors scores no marks.
Submision of a file with a diferent name and/or diferent extension and/or diferent type
scores no marks.
Task 3 (0.1 mark)
(1) Use UMLetlet application to draw the following conceptual schema of a simple data
warehouse.
UMLetlet 14.2 is available on the systems in a laboratory clas on drive D: in a folder
ISIT312. The drawing tool is located in a shared folder
\adeis.uow.edu.au\EISPUB\Public\ISIT312\UMLetlet14.2. It is explained
in the Cookbook, Recipe 11.1, "How to create the components of data warehouse
conceptual schema with UMLetlet ?" how to use UMLetlet to create the diagrams.
(2) When ready, use an option File->Save of UMLetlet to save your diagram in a file
solution3.uxf. Do not delete a file solution3.uxf.
(3) Use an option File->Export as… to export your diagram into a file
solution3.bmp in BMP format. Do not delete an exported file. You wil submit
it as one of the deliverables from your laboratory work.
Deliverables
A file solution1.bmp that contains a diagram above.
A submision of a file solution1.uxf is not expected and it scores no marks.
Submision of a file with a diferent name and/or diferent extension and/or diferent type
scores no marks as wel.
Task 4 (0.5 mark)
Consider the following specification of a simple data warehouse domain.
A university administration would like to record time spent by the students in the lecture clases.
To achieve that the administration instaled at the entries to the lecture halls the devices that can
read and recognize student cards. Now, each time a student enters and leaves a lecture hall
he/she swaps a card against a device and the total time spent by a student in a lecture hall is
computed and recorded.
The administration has the operational databases that contain information about students,
subjects, timetables and enrolments. The administration would like to use these databases and
information obtained from scaning of student cards to create a data warehouse. It should be
possible to get from the warehouse information about the total time spent by each student in
lecture clases per given period of time like per day, per week, per month, per session, and per
year. It should be posible to get information about the total number of times each student
attended the lectures per day, per week, per month etc. It should be possible to get information
about time spent by each student in the lecture classes per subject, per degree enrolled, per
schol that ofers the subjects. It should be also possible to get information about the total
number of students who attended lectures per subject, per degre enrolled, per schol that ofers
the subjects.
(1) Find a data cube that should be implemented by the university to create a data
warehouse. In your specification of a data cube, list a fact, the names of dimensions,
hierarchies, measures, and sample atributes used to describe a data cube.
(2) Pick any three dimensions from a data cube found in the previous step and at least 3
values in each dimension and draw a sample three dimensional data cube in a
perspective view similar to a view included in a presentation 03 Data Warehouse
Concepts, slide 6.
Deliverables
A file solution4.pdf that contains specification of a data cube found in step (1) and
a perspective drawing of sample data cube created in step (2). You can create a Word
document with a specification of a data cube, include a scanned had-drawing of
perspective view of sample data cube and print the document into pdf file.
Submision
Note, that you have only one submision. So, make it absolutely sure that you submit
correct files with the correct contents. No other submision is possible !
Submit the files solution1.lst, solution2.lst, solution3.bmp, and
solution4.pdf to Moodle in the following way:
(1) Aces Moodle at http://moodle.uowplatform.edu.au/
(2) To login use a Login link located in the right upper corner the Web page or in
the middle of the bottom of the Web page
(3) When logged select a site ISIT312/ISIT912 (S217) Big Data
Management
(4) Scroll down to a section Week 3
(5) Click at a link In this place you can submit the outcomes of
Laboratory 1
(6) Click at a button Add Submission
(7) Move a file solution1.lst into an area You can drag and drop
files here to add them. You can also use a link Add…
(8) Repeat step (7) for the files solution2.lst, solution3.bmp, and
solution4.pdf.
(9) Click at a button Save changes
(10) Click at a button Submit assignment
(11) Click at the checkbox with a text atached: By checking this box, I
confirm that this submission is my own work, … in order to
confirm the authorship of your submision.
(12) Click at a button Continue
A policy regarding late submisions is included in the subject outline.
Only one submision of the outcomes of Laboratory 1 is allowed and only one
submision per student is acepted.
A submision marked by Moodle as "late" is treated as a late submision no mater how
many seconds it is late.
A submision that contains an incorrect file atached is treated as a correct submision
with al consequences coming from the evaluation of the file atached.
Al files left on Moodle in a state "Draft(not submitted)" wil not be evaluated.
It is expected that al tasks included within Laboratory 1 wil be solved individually
without any cooperation with the other students. If you have any doubts, questions, etc.
please consult your lecturer or tutor during lab clases or office hours. Plagiarism wil
result in a FAIL grade being recorded for that asesment task.
End of specification