JSON and SQL.

JSON is a popular textual data format that's used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases. The goal of this project is to introduce you to JSON and SQL Databases.

Requirements

  1. Phase 1. Get familiar with JSON. In this task, you will need to read about JSON and get familiar with it.
  2. Phase 2. Export relational data to JSON. The goal of this task is to practice exporting format SQL data or the results of SQL queries as JSON. Different DMBSes have different statements to support it. You will need to read about it and implement it.
  3. Phase 2: Develop JSON parser. Alternative 1: Relational. In this task, you will parse a JSON file, create a relational database dbjson corresponding to the JSON file, and upload the data into the dbjson.
  4. Phase 3. Combine SQL and JSON: Alternative 2 - Document database. The simplest way to store JSON documents in relational database is to create a two-column table that contains the ID of the document and the content of the document. In this task you will implement this scenario.
  5. Phase 4. Combine SQL and JSON: Alternative 3: Hybrid. In this task, given a JSON file, you will need to create a relational schema for your database such that a subset of the attributes of the JSON file will be implemented as traditional table attributes and some of the attributes will be implemented as a JSON attribute/field. This feature is particularly useful when data contain items with different attributes, e.g., products: a laptop and a printer have different attributes despite being both electronics. You can expect the customer data to be about the same for each person.
  6. Studies:
    1. A set of JSON file will be provided.
    2. A set of queries requirements will be provided.
    3. Learn to query JSON data as well as SQL and JSON combined.
    4. Run your queries against the three databases. Collect statistics, like running time and memory.
    5. Take steps to optimize your databases, e.g., introduce indices. You may want to read about indices on JSON attributes.

Deliverables

  • A semester report. However, you will need to upload a weekly version of report on each Monday until the end of the semester. Each subsequent version of the report clarily marks the new parts, e.g., using different font styles or colors. The report starts with a progrss section, which gives a itimized list of the updates to your project and report.
  • Include in your report SMALL pieces (no more than two pages!) of source code that convincingly show that you implemented the tasks.
  • Source code - only after you give an in-person demo.
  • Describe the software packages that you use to implement your algorithms.
  • Describe the difficulties in implementing your algorithms and how you overcame them.
  • Provide statistics about the queries.
  • Include a screen shots that you ran your queries on your computer.
  • And, the most important piece: comparative study over the 3 types of database schemas. Give your system configuration.
  • Give details about your steps to improve the queries. Start early!
    Collaborate! Compare and discuss your approaches. The end product is expected to be an individual effort!