DATABASE HOMEWORK

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 following :  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 (username, password).  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 DVD). 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 results obtained. (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 Question The relations Customers, Orders, Orderlines apply the following assumptions : (i) the number of tuples of the relation is Orderlines ROrderlines = 60.350, (ii) the number of tuples of Orders relationship is ROrders = 12.000, (iii) the number of tuples of the relation Customers is RCustomers = 20.000, (iv) the page size disk (block) is b = 4.000 bytes. Now consider the following SQL query : SELECT * FROM Customers AS C, Orders AS O, Orderlines AS OL [login to view URL]%CE%95%CF%81%CE%B3%CE%B1%CF%83%CE%AF%CE%B5%CF%82/[login to view URL] You must translate it!