Chris Pollett>
CS157b |
HW#1 --- last modified February 13 2023 22:08:33.Due date: Feb 13 Files to be submitted: 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: Remember to include in your Hw1.zip a readme.txt listing all your teammates! 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.
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 at least Java15 SE installed, but not necessarily any particular IDE or build suite or newer version of Java. 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 Here dbms is the type of database management system to connect to (either mysql (even if use MariaDB) or sqlite); db is the database to connect to; table_name is a table to create if it doesn't already exists, and drop and create if it already exists; type is a SQL attribute of TEXT, DECIMAL(n), or CHAR(n), num_rows is the number of rows of random data to be generated, num_cols is the number of columns of that type each row should have, finally, index_no_index, is a boolean which says whether or not to make an index for the first column. An example set of inputs the grader might use is: java RecordTester sqlite "test.sqlite" FOO "CHAR(99)" 50000 15 false This should create in the sqlite database in the file test.sqlite, a table FOO which has 15 columns each of type CHAR(99), and then do 50000 inserts of random rows (a random run means make a string of 99 chars out of characters "0" and "1", similar ideas should be used for random TEXT where you pick a random length first, then a random string, and DECIMAL) 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 much 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. Each experiment should have clearly stated, purpose, hypothesis, results, conclusion. Put these write-ups in the file Experiments.pdf, which should also be included in your Hw1.zip file submitted. Point Breakdown
|