Login to PostgreSQL PostgreSQL 83 should be already running on by lindash


More Info
									                                                                       School of Information

                                                                  Thursday 10 AM - 12 PM, SIT Lab 114

Login to PostgreSQL: PostgreSQL 8.3 should be already running on each machine in
the School of IT labs. You can login to PostgreSQL as follows:
_ Run Start - Programs - PostgreSQL 8.3 - pgAdmin III
_ Click on the second connection entry, called PG
_ The password is pgstudent09 (and the default user is ’student’)
Documentation is available on the internet: http://www.postgresql.org/docs/8.3/
Important: Note that this is a local installation per machine which will be often re-installed
     overnight – so you must keep a separate SQL file of any data that you create and load
     during the labs on you network drive!
pgAdmin III: Make yourself familiar with this SQL frontend for PostgreSQL. Your own
tables will be created in the public schema. You can directly browse through tables using one
     of the ’Table’ icons in the icon bar, or start an SQL entry form using the ’Notes’-icon.

Q 1: Storage Cost Estimations
Given the following relational schema of a ’City’ table:
countrycode CHAR(3) NOT NULL,
district VARCHAR(100),
population INTEGER);
a) Assume a database system iDB which uses the following internal storage structure:
        •   each record has a fixed-size header of 18 bytes containing, e.g., the
            number of attributes;
        •   the header is followed by an optional NULL bitmap of 2 bytes; each bit
            represents one attribute and is set to 1 if that attribute is NULL; no further
            storage is then used by NULL values;
        •    then follows the attribute directory with one offset pointer of 2 bytes for
            each nonnull attribute;
        •    Finally are all non-null attributes stored in sequence; an INTEGER
            attribute requires 4 bytes, a CHAR attribute 1 byte, and VARCHAR strings
            require between 1 and their maximum length bytes plus 1 bytes.

(a) What is the minimum and maximum size of a city record in iDB?
Answer: The table has five attributes, 3 fixed size and 2 variable size (VARCHAR).
The two last attributes can be null; hence we need a NULL bitmap.
The minimum size is for empty strings and the last two attributes being NULL:
sizemin = header+bitmap+3_offsets+id+name+countrycode = 18+2+3*2+4+1+3 = 34

The maximum size is for full strings and all attributes not NULL:

sizemax = header+bitmap+5_offsets+fattributesg = 18+2+5*2+4+51+3+101+4 = 193

(b) iDB is using a slotted page architecture. Suppose that each page in iDB is 8kB and
has a page header of 20 bytes and that each slot pointer of iDB needs 4 bytes.
What are the minimum storage requirements to store 1000 City records in iDB?
Answer: The usable free space per page in iDB is: 8192 * 20 = 8172. The smallest
      City record is 34 bytes long and each slot needs 2 bytes; i.e. b8172=(34 + 2)c =
      227 records fit on one page. Hence for 1000 records, we need at least
      d1000=227e = 5 pages.

Q(2) The Megatron 777 Disk has following characteristics :
   1. There are 10 surfaces, with 100, 000 tracks each.
   2. Tracks hold an average of 1000 sectirs of 1024 bytes each.
   3. 20% of each gap is used for gaps.
   4. The disk rotates at 10, 000 rpm
   5. The time it takes disk the head to move n tracks is 1+ 0.0002n milliseconds.

Q2(a) What is the capacity of the disk?
Ans: As there 10 surfaces each 100,000 tracks,
     Each tack 1000 sectors
     Each sector 1024 Bytes
The capacity of disk will be 10*100,000*1000*1024 = 1,024,000,000,000 Bytes

Q2(b) If the tracks are located on the outer inch of a 3.5 inch diameter surface, what is the
     average density of the bits in the sector of a tack?
Ans: ((100, 000 * 1000 * 1024 * 8 ) )/( 0.8 *π (3.5 *3.5/4) – π( 3.5 *2.5/4) note area of circle
     is π* r*r and radius is 3.5/2. As we have to compute the density in outer inch of the disk
     so we are calculating in denominator the area of outer one inch and multiplying it with
     0.8 as 20% of the disk is used for the gaps hence 80% is useful for storage.

Q:2(c) Wahts is maximum seek time?
Ans: As given there are 100, 000 tracks
We know that head move in 1+ 0.0002 * n milliseconds for n tracks, hence
Total seek time will be 100, 000 * 1.0002 = 21 ms

Q:2(d) : What is maximum rotational latency?
Ans: 60*1000 /10,000 = 6 ms
Q2(e): If a block is 65, 546 bytes(ie. 64 sectors) What is the transfer time of the block

Ans: if there are 64 sectors it menas there are 63 gaps. As 20% of the track is used as gaps
     360 * 20% = 72 degrees and the rest sector takes 360- 72 degrees = 288 degrees. A
     block takes ( (64*288)/1000 + (63*72)/1000) degress
For 360 degrees it takes 6 ms. So for dgrees ( (64*280)/1000 + (63*72)/1000) it will take
     (( (64*280)/1000 + (63*72)/1000) /360)* 6 = 0.3188 ms

Q2 (f) : What is the average seek time
(100, 000 * 1.00021)/3 as header is assumed to be at less than half of the distance.

Q2(g): What is average rotational latency?
Maximum rotational time /2 = 6/2 = 3 ms

To top