HW#1 --- last modified March 11 2020 06:46:09.

Solution set.

Due date: Feb 12

Files to be submitted:
  Hw1.zip

Purpose:To refresh our memories about CS157a and to experiment with how DBMS implementation can influence the design and deployment of databases.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO3 -- Create a simple query transaction in a modern DBMS system.

Specification:

This homework will consist of two parts, a written part and a coding part. Both parts will be submitted in the Hw1.zip file. The written part should be in a file Hw1.pdf. For this part of the homework you should write solutions for the following questions. In what you turn in, first copy and paste the question, then write your solution to it beneath it.

1. Suppose the Super2020 drive has the following characteristics:
   • There are 3 platters with 250,000 tracks each surface.
   • Tracks have 8000 sectors of 512 bytes each.
   • 8% of each track is used for gaps.
   • The disk rotates at 7200 RPM.
   • The time it takes the tape head to move `n` tracks is `(1 + 0.0003n) mS`.
   Answer the following questions about the Super2020 drive:
   1. What is the capacity of the disk?
   2. Suppose this disk a 2.5in laptop drive, what is the average bit density in the sectors of the outermost track?
   3. What is the maximum seek time?
   4. What is the average rotational latency?
2. Suppose we are scheduling I/O requests for the Super2020 drive given in problem 1, and the head is initially at track 35,000. Further, suppose requests come in according to the table below:

   Cylinder Requested	Time Request Made From Start Time in mS
   60000	0
   1000	2
   44000	11
   7000	19

   At what time is each request serviced fully if: (a) the elevator algorithm is used (initially moving inward)? (b) We use first-come-first-served scheduling.
3. Suppose we have 3 data drives and 1 redundant disk and we are using RAID level 4. Assume blocks are a single byte. (a) What would be the redundant block corresponding to the three data blocks: 10100011, 11010011, 01110101? (b) What would be the redundant block corresponding to the three data blocks: 11000011, 11010011, 10011101?

The coding part of the assignment assumes you have a version of Mysql/MariaDB installed on your computer and have Sqlite 3 installed. If you don't already have these DBMSs installed probably the easiest way to get them is probably to use a package manager. On Linux, you can use the default manager apt, on a Mac, I recommend homebrew, and on a PC, I recommend chocolatey. Then to install software, at a command prompt, you just type the command for the package manager, the word install, and the package you want. For example, on a Mac, to get sqlite3, I might type:

brew install sqlite3

...or to upgrade...

brew upgrade sqlite3

For the coding part, I'd like you to write a simple command line application RecordTester.java to test properties of the record formats of Mysql and sqlite. You can assume the grader has Java 9 SE installed, but not necessarily any particular IDE or build suite. The grader will compile your code from the command line by switching into your folder, and entering the line:

javac RecordTester.java

The top of your program's class file should have any constants the grader needs to tweak to make a JDBC connection to a Mysql DB or sqlite Db. In the case of sqlite, you should as part of your homework folder have a empty file test.sqlite with an empty sqlite database file. Your program will be run from the command line with a command like:

java RecordTester dbms db table_name type num_rows num_columns index_no_index

java RecordTester sqlite "test.sqlite" FOO "VARCHAR(7)" 100000 10 false

This should create in the sqlite database in the file test.sqlite, a table FOO which has 10 columns each of type VARCHAR(7), and then do 100000 inserts of random rows into this table. It should not make an index on the first column.

Once you have written this program I want you to do experiments where you vary the SQL type and the number of rows and columns and for each type try to determine (a) how many bytes the DBMS needs to use for a record header, (b) how many bytes in a record is used to store the given type on average, (c) how mcuh overhead is an index record. To figure these out, you should locate the file on disk in which the table is stored and compare how the file size changes as you vary your programs parameters.

Carefully, write up each experiment you conduct and say what conclusion the experiment let's you draw. Put these write-ups in the file Experiments.pdf, which should also be included in your Hw1.zip file submitted.

Finally, to complete the description of the homework, your Hw1.zip should contain a readme.txt file in which the grader can find out anything else that might be needed to get you program to work as well as a list of all names the members of your homework group (at most three).

Point Breakdown