Assignment Four on Packages and Physical Data Structures | CSCI 4227, Assignments of Computer Science

Download Assignment Four on Packages and Physical Data Structures | CSCI 4227 and more Assignments Computer Science in PDF only on Docsity! CSCI 4227/5225 Advanced Database Assignment Four – Packages and Physical Data Structures Name: Your Name Here Due: April 15, 11:59pm. Objectives:  To know the elements of a PL/SQL package  To be able to create public and private functions/procedures for PL/SQL packages.  Reinforce Data Dictionary usage.  Reinforce Physical Data Structures Concepts. Turn in: Submit, via D2L, a two files: the first file, lastname_HW4.sql, that will create the Physical package (include both the header immediately followed by the “show errors” command and the body immediately followed by the “show errors” in this single file) ; and Word document, lastname_HW4.doc, containing the responses to the questions below. A hardcopy of this sheet with typed responses may be turned in on Thursday morning for hand grading. We wish to create a package called PHYSICAL, that will automatically calculate the following the search times (linear and binary search) based on:  table name (all caps),  seek time (in milliseconds)  rotation speed (rpm),  blocks per track,  bytes per track,  number of tracks,  number of records,  recordsize We will assume that the granularity is track using as many single sided platters as necessary to hold the file. The following questions will (if processed in order) will accomplish this. Show all code necessary to answer the following. 1. [5 pts] Create and show the header for the package Physical to declare the procedure showAll with no parameters. CSCI 4227/5225 Advanced Database Assignment Four – Packages and Physical Data Structures 2. [10 pts] a. Show the code for a package body for the package Physical to create the following hidden variables: Variable Type Default Values tablename varchar2(50) “Staff” seektime (milliseconds) number 50 diskspeed (rpm) number 7,200 blockspertrack number 100 tracksize (bytes per track) number 100,000 numberoftracks (per disk) number 100 numberofrecords number 20,000 recordsize (bytes) number 300 b. and declare a procedure called showAll to create the following report (blank fields will be filled in later): Disk Data: Rotational speed: 7200 Seek time: 50 Rotational delay: Block transfer time: Table Data: Table name: STAFF Number of records: 20000 Record size: 300 Records per blocks: Total blocks: Linear search time: Binary search time: 3. [5 pts]Show the code for a function called getRd to determine the Rotational Delay (Rd). The function will get the rpm from the package variable established by you. Modify the showAll procedure to call getRd at the appropriate time. Do not print the showAll procedure at this time. 4. [5 pts] Show the code for a function called getBTT to determine the Block Transfer Time (BTT) using the necessary package variables. Modify the showAll procedure to call getBTT at the appropriate time. Do not print the showAll procedure at this time. 5. [10 pts] Using the data dictionary, write a procedure called setRecSize (tablename) to replace the default record size for the given tablename. Call the setRecSize procedure at the beginning of the showAll procedure. Do not print the showAll procedure at this time. 6. [5 pts] Write a function called getBFR that will return the blocking factor for the records. Modify the showAll procedure to call getBFR at the appropriate time. Do not print the showAll procedure at this time. 7. [5 pts] Write a function called getLinear that will return the average linear search time for the table. You may assume that this is a contiguous read by tracks. Modify the showAll procedure to call getLinear at the appropriate time. Do not print the showAll procedure at this time.