HW#1 --- last modified January 27 2019 04:57:22..Due date: Feb 12
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: 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 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_columnsHere 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 INTEGER, BOOLEAN, or VARCHAR(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. An example set of inputs the grader might use is: java RecordTester sqlite "test.sqlite" FOO "VARCHAR(7)" 100000 10 This should create in the sqlite database in the file test.sqlite, a table FOO which has 10 columns each of type CHAR(7), and then do 100000 inserts of random rows into this table. 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 and (b) how many bytes in a record is used to store the given type on average. To figure this 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
|