In this homework, we will take a fsimage file and store its inode and inode directory information (as illustrated below) in MySQL and write SQL queries to retrieve or analyze the content. Remember that XML contents are case-sensitive and need to be stored in MySQL databases as such too. For example, FILE needs to be stored as FILE instead of file, DIRECTORY needs to be stored as DIRECTORY instead of directory. The homework assumes that you have created a database dsci551 on your EC2 MySQL, a user dsci551@localhost (with password Dsci-551), and grant all privileges on dsci551.* (i.e., objects in dsci551) to the user. You can log in as root and execute the following to satisfy the assumption:

1. [25 points] Write one SQL script create.sql that creates the following 3 tables in a MySQL database dsci551. The script may assume that the database dsci551 already exists. Note that you should properly define the primary key and foreign key(if applicable) for each table and choose a suitable data type for each attribute according to the screenshot. If the above tables already exist in the database, your script should be able to recreate them. Your code should run without error and satisfy all requirements stated in the question. Its only required for you to define PK, FK and data type for each attribute. Its up to you whether to add unique/ NOT NULL/ CHECK/ FK CASCADE, etc.

a. A table inode which stores the information about the inodes in a fsimage file. The table should have following attributes: id, type, name, replication, mtime, atime, preferredBlockSize, and permission.

b. A table blocks which stores the block information for a file. The table should have the following attributes: id, inumber, genstamp, numBytes, where inumber is inode id of the file and id is a block id. Note that it is possible that a file has multiple blocks. You can assume that every block has a unique id. No need to store storagePolicyID.

c. A table directory which has two attributes: parent and child, where parent is the inumber of parent directory and child is the inumber of file/directory stored under the parent directory.

create database dsci551; create user dsci551@localhost identified by "Dsci-551"; grant all privileges on dsci551.* to dsci551@localhost