- Need Help For My Assignment Here 1 (36.65 KiB) Viewed 57 times
Task 1 (5.0 marks) Indexing Consider a relational table: OrderLine(orderNum, lineNum, item, discount, quantity) The primary key of the relational table OrderLine is a composite key consisting of the attributes (orderNum, lineNum), and the primary key is automatically indexed. For each of the select statements specified in (i) to (v) below, find the best possible index for the relational table OrderLine that allow a database system to execute the select statement in a manner described. Write a create index statement to create the index. i. SELECT orderNum, item, quantity FROM OrderLine WHERE orderNum = '0001' AND quantity = 20 AND item = 'Thumb Drive'; Create an index such that the execution of the SELECT statement must traverse the index vertically and it MUST NOT access the relational table OrderLine. (1.0 mark) ii. SELECT item FROM OrderLine WHERE discount = 0.1; Create an index such that the execution of the SELECT statement must traverse the index vertically and then horizontally at the leaf level of the index and it MUST NOT access the relational table OrderLine. (1.0 mark) iii. SELECT sum(sum(quantity)) FROM OrderLine GROUP BY OrderNum HAVING count(LineNum) > 5; Create an index such that the execution of the SELECT statement must traverse the leaf level of the index horizontally and it MUST NOT access the relational table OrderLine. (1.0 mark)
iv. SELECT * FROM OrderLine WHERE discount = 0.1; = Create an index such that the execution of the SELECT statement must traverse the index vertically and then horizontally and it MUST access the relational table OrderLine. (1.0 mark) V. SELECT quantity, discount FROM OrderLine WHERE OrderNum = '0123' AND LineNum = 1 AND item = '27 inch monitor'; Create an index such that the execution of the SELECT statement must traverse the index vertically and MUST access the relational table OrderLine. (1.0 mark)
Task 2 (5.0 marks) Stored PL/SQL procedure Implement a stored PL/SQL procedure PARTSUPPLIER that lists information about the supplier key and supplier name supplying parts. The procedure first computes the total number of suppliers supplying the specified part. The procedure then extracts the supplier's information supplying the specified part. The information to be displayed include the part key, part name, the supplier key and the supplier's name. An example of a segment of the output for the list of suppliers supplying a specified part (part 59396) is as follow: part key: 59396, orange cream sandy lavender drab Number of suppliers supplying the part: 4 supplier Key supplier Name 166 Supplier#000000166 935 Supplier#000000935 1704 Supplier#000001704 2397 Supplier#000002397 It is up to you to decide if you want to handle exception in your procedure. Deliverables Submit your spooled file solution2.Ist (or solution2.pdf) that contains your SQL script and the output from the execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed. Remember to set ECHO option of SQL*Plus to ON!
Task 3 (5.0 marks) Stored trigger Implement a row trigger that enforces the following consistency constraint. The column c_comment in the relational table CUSTOMER of the TPCHR benchmark database is defined as 'NOT NULL'. Create a row trigger that automatically updates the values in the column (c_comment) to 'New customer was created on <the system date>' if the comment of the newly inserted record is left as NULL when a new customer is inserted into the relational table CUSTOMER. Your trigger, once activated, will enforce the consistency constraint described. When ready, process the SQL script solution2.sql and record the results of processing in a file solution 2.1st. Deliverables Hand in the SQL script and the report from execution of scripts. Remember to set ECHO option of SQL*Plus to ON!