Consider the database of an online business selling DVD, which stores
the sales and orders . The relational schema of the database appearing on the
next page . As shown in the figure, NW stores information for
The different titles DVD (Products) that the firm has . each title
belongs to a class .
Clients (Customers) of the business. For each customer stored
personal data (name , surname , etc. ) , credit card (type,
number , expiration date ) and data access to online business
orders (Orders) that makes every client of the firm , for which
saved the order date , the pre-tax amount , tax , and
total amount of each order .
The products contained in each order (Orderlines) the amount of any
product and date order.
You are given a large data set (dataset) real ( anonymised )
data. On this, run the following questions :
Question 1 ( 50 % ) . Relational vs. Object- relational databases
(a) Enter the above data into a properly designed NW in DBMS which
you wish (PostgreSQL or Oracle). The NW at this stage will follow
necessarily the relational schema as given to you. The deliverable
sub-question is the commands and files used for
data entry and screenshots that will show their paintings
the DB loaded with data .
(b) Then anaschediaste the above relational schema using the
object- relational model . For the conceptual description of the new shape
please provide the UML class diagram of the DB . After creating the appropriate
collections , complex data types and tables they contain ,
Implement appropriate PL / SQL scripts to provision the object-
relational schema of the data sub-question 1 (a). The deliverable
sub-question is the PL / SQL scripts needed to create the
complex data types , the object- relational tables and uploading
data from their respective relational .
(c) Perform the following questions (queries) in the relational DB , having set where
and if necessary, appropriate indexes.
i. "Who is the one (or several in case of a tie )
customer with the highest total value of orders ? " The result will
show 2 columns : client code , total value of orders.
ii. "Who are the customers who have made at least one order containing
DVD of at least 3 categories ? " The result will appear 2
columns: customer number , order number ( with at least 3 categories
iii. "What is the total value of orders per month , country and category
products ? '. This result will be shown 4 columns: Month , country name
category , total value of orders. (d) Perform the questions sub-question 1 (c) the object - relational DB that
graphed in sub-question 1 (b). Expand composite objects
object - relational DB with appropriate methods (object methods), they're
necessary to support the resolution of these questions. The
Reply to this sub-question will include , for each query: screenshot by
PL / SQL block to be followed by an appropriate justification and the screenshot
(e) Identify , analyze and explain the differences in performance
execution of queries , consulting and implementation plans (query plans)
producing each system (using the command EXPLAIN).
Question 2 ( 35 % ) . Records and Indexes , Processing & Optimization
The relations Customers, Orders, Orderlines apply the following assumptions : (i) the number of
tuples of the relation is Orderlines ROrderlines = [url removed, login to view], (ii) the number of tuples of
Orders relationship is ROrders = [url removed, login to view], (iii) the number of tuples of the relation Customers
is RCustomers = [url removed, login to view], (iv) the page size disk (block) is b = [url removed, login to view] bytes.
Now consider the following SQL query :
FROM Customers AS C, Orders AS O, Orderlines AS OL