Script dbdrop can used drop the tables relational tables partsupp

This assignment is due on Friday, 14 May 2021, 9:00pm (sharp) Singaporean Time (ST).

This assignment is worth 30% of the total evaluation in the subject.

A submission that contains an incorrect file attached is treated as a correct submission with all consequences coming from the evaluation of the file attached.

All files left on Moodle in a state "Draft(not submitted)" will not be evaluated.

(i) find the total number of kilometers made by trains in a given year, departing from the stations locating in a given country and arriving at the stations located in a given country.

(ii) find the total duration of international trips in a given year, that is, trips departing from a station located in a country and arriving at a station located in another country,
(iii) find the total number of trips that departed from or arrived at a given city in a given month of a given year,
(iv) find and average duration of train trips in a given country in a given year, (v) for all trips in a given year, find an average number of passengers on a trip.

Deliverables
A file solution1.pdf that contains
(1) a specification of data cube as a list of names of dimensions, list of hierarchies, list of measures and a list of attributes as a result of task (1),
(2) a perspective drawing of three-dimensional data cube as a result of task (2).

Task 2 (6 marks)
Conceptual modelling of a data warehouse

Each hotel is described by its location (country, city, building number), email address and link to a Web page. A hotel offers the rooms to its customers. A room has a unique number within a hotel. A room number consists of a floor number and a unique number at a floor. For example, room 25 at 5th floor has a number 0525.

Each hotel has a number of employees. An employee has a unique employee number, first name, last name, and date of birth. Staff members belong to either administration group or maintenance group. Among the other duties, administration staff members are allowed to perform check-in and check-out of hotel guests. Maintenance staff members perform the maintenance works in the rooms occupied by hotel guests.

A management of the hotel network would like to get from a data warehouse information about the total number of visits per hotel and per given period of time like day, month, and year, about total number of visits in hotels per city and per country, about total number of check-ins/outs per employee, and about the total number of visits paid per credit card used, total number of customers per hotel, per room, per month per year, total profits per hotel, per city where the hotels are located, average length of stay per year, per month, per hotel, average discount applied per hotel, per month per year.

To draw a conceptual schema, use a graphical notation explained to you in a presentation 11 Conceptual Data Warehouse Design.

To draw a conceptual schema, you must use a graphical notation explained to you in a presentation 11 Conceptual Data Warehouse Design.

To draw your diagram, you can use UMLet diagram drawing tool and apply a "Conceptual modelling" notation, Selection of a drawing notation is available in the right upper corner of the main menu of UMLet diagram drawing tool. UMLet 14.3 software is can be downloaded from the subject's Moodle Web site in a section WEB LINKS. A neat hand drawing is still all right.

Perform a step of logical design to transform a conceptual schema given above into a logical schema (star schema). Use UMLet diagram drawing tool and apply a "Logical modelling" notation to draw a logical schema. Selection of a drawing notation is available in the right upper corner of the main menu of UMLet. Save a diagram of logical schema in a file solution3.uxf and export it to a file solution3.pdf.

Deliverables
A file solution3.pdf with a drawing of a logical schema.

application.tbl.

Use text editor to examine the contents of *.tbl files. The order of columns with values is usually consistent with the order of properties in the entity types of a conceptual schema above. In the case of a file application.tbl an order of columns with values is a bit different. It is your task to discover the most appropriate order. Note, that you may have to "clean" the files. It means that you may have to remove small mistakes in the files. It is called Extract, Transform, and Load (ETL).

Deliverables
A file solution4.rpt with a report from processing of HQL script solution4.hql.

Task 5 (6 marks)
Querying a data cube

The relational tables PARTSUPP, LINEITEM, ORDERS implement a simple two-dimensional data cube. The relational tables PARTSUPP and ORDERS implement the dimensions of parts supplied by suppliers and orders. A relational table LINEITEM implements a fact entity of a data cube.

(1) Implement the following query using GROUP BY clause with CUBE operator.

(3) Implement the following query using GROUP BY clause with GROUPING SETS operator.

Find the smallest price (L_EXTENDEDPRICE) per order year (O_ORDERDATE), and order clerk (O_CLERK).

When ready, save your SELECT statements in a file solution5.hql. Then, process a script file solution5.hql and save the results in a report solution5.rpt.

Deliverables
A file solution5.rpt that contains a report from processing of SELECT statements.

(12) Click at a button Continue

End of specification