The SSN is supposed to be a unique identifier, but it was not declared UNIQUE above. Read the file block-by-block, using a DBM database to check whether the SSN has been seen before. Report any duplicates.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

RDBMS and Database

The RDBMS for this project is :

A program in Python using the dBm or shelve modules for indexing.

The database format is a binary file of disk blocks. The disk block size is 4,096 bytes, and the blocking factor bfr is 10. Each record is the equivalent of the following SQL DDL statement:

CREATE TABLE Person

(

    first_name VARCHAR(20) NOT NULL,

    last_name VARCHAR(20) NOT NULL,

    job VARCHAR(70) NOT NULL,

    company VARCHAR(40) NOT NULL,

    address VARCHAR(80) NOT NULL,

    phone VARCHAR(25) NOT NULL,

    birthdate DATE NOT NULL,

    ssn VARCHAR(12) NOT NULL,

    username VARCHAR(25) NOT NULL,

    email VARCHAR(50) NOT NULL,

    url VARCHAR(50) NOT NULL

);

Strings are composed of ASCII characters and are null-terminated. Dates are stored as three 32-bit integers in native byte order representing the day, month, and year.

There are two test databases: small.bin.gz, of size 40,960 bytes, containing 100 records, and large.bin.gz, of size 4 GiB, containing over 10 million records. These files are compressed with GNU GZip for download, and should be uncompressed before use.

Indexes will be created as DBM files using one of the libraries listed above.

Platform

You may use any platform to develop and test your code,

Libraries

The Python 3 standard library

Reading binary files

You may use any method to read binary files, but you may find the following useful:

Python: read() into a bytes object, then decode with the struct module.

Queries

Each of the following queries should be implemented as separate programs. For queries that use an index, write two separate programs - one to build the index, and one to use the index to run the query.

In each case, test your program using small.bin first to verify that it works correctly before attempting the query on large.bin.

Use the UNIX time command to measure how long each query takes, and include the results in your submission.

Tip: based on the time you measure for small.bin, you may want to do a back-of-the-envelope estimate before starting queries on large.bin.

Query 1 - Table scan

Read the file block-by-block, list the SSN, first name, and last name of all users under age 21.

Query 2 - Uniqueness check

The SSN is supposed to be a unique identifier, but it was not declared UNIQUE above. Read the file block-by-block, using a DBM database to check whether the SSN has been seen before. Report any duplicates.

Query 3 - Secondary index

Use a DBM database to create a secondary index on birthdate, then loop through all items in the index to find the location on disk of all users under age 21. Read only the relevant disk blocks in order to list the SSN, first name, and last name of all users under age 21.

Query 4 - Clustered index

Create a clustered index on birthdate by sorting the data file and creating sparse DBM index entries for each disk block. Use this index to repeat the previous query.

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme