SPL221- Assignment 4: Python and SQL Solved

1           Introduction
In this assignment you are required to implement the database of Pizza Hat, a shop specialized in pizza shaped hats. The assignment is composed of three parts:

Create and populate the database according to a configuration file.
Execute a list of orders, according to a second file.
Print a summary in a third file.
Note that you must use a Persistence Layer, with DTO and DAO, as seen in PS13. Failing to use it will results in a 20 points deduction. ORM is optional.

2           Building the Database
The database will contain the following tables:

2.1         Structure
hats: Hold the information on the hats currently in the inventory.id INTEGER PRIMARY KEY
topping STRING NOT NULL
supplier INTEGER REFERENCES Supplier(id)
quantity INTEGER NOT NULL
suppliers: Holds the suppliers data.id INTEGER PRIMARY KEY
name STRING NOT NULL
orders: Holds the information on the different orders.id INTEGER PRIMARY KEY
location STRING NOT NULL
hat INTEGER REFERENCES hats(id)
2.2         Configuration file
In order to build the database, you will parse a configuration file, the file will have the following structure:

<#1>,<#2>

<hats>

<suppliers>

Where each of the numbers in the first line stands for the number of entries of that type, and each entry has the relevant table details, separated by a comma. For example:

3,2

1,olives,1,10

2,mushrooms,1,20

3,mushrooms,2,10

1,Scrabbles

2,Hatters

In the above example we have 3 type of hats in the inventory and 2 suppliers. Note that there are no spaces, just comas and new lines (Use the attached input file, and do not copy-paste from here).

3           Orders
The orders file will have the following structure:

<location1>,<topping1>

<location2>,<topping2>

<location3>,<topping3>

<location4>,<topping4>

3.1         Executing the Orders
Each order will be executed, if a certain hat topping has two suppliers or more, the inventory from the first supplier (ordered by id) will be used. When executing the orders, you will need to update the quantity of the hats in the database, if the quantity drops to zero, the entry should be removed from the database. Executed orders will be inserted to the orders database, with a unique id, starting from 1, and increasing by 1 on each order.

4           Summary File
After each order a line will be added to the summary, the line should include:

<topping>,<supplier>,<location>

For example, using the above configuration file and the following orders:

Hedera,olives

Hedera,mushrooms

Tel-Aviv,mushrooms

The result summary file will look like:

olives,Scrabbles,Hedera mushrooms,Scrabbles,Hedera mushrooms,Scrabbles,Tel-Aviv