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.

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. 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. The LineItem class will represent an individual

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

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of