The programming project is a part of the examination of EDAF20. You must work in groups of two or three students, and all members of the group must participate in a peer review session where you’ll discuss your design with other groups and a Teaching Assistent (TA) – those who don’t attend the meeting will not get credits for the project.
The project is divided into several parts:
Database modeling: Your group should create a model for a database for the supervision of production and delivery of cookies produced by the company “Krusty Kookies Sweden AB” (see below).
Design review meeting: Your model will be discussed during the peer review session (see above and below). You should print out and bring your E/R model (UML) to the meeting, and there, you and one or two other groups will discuss your designs with each other, and with your TA.
Implementation of the database: Once your model is reviewed by other groups, and approved by your TA, you can define relations and implement the database.
Development of a REST API: The description of the REST API can be read here. There is already a GUI which lets you try out parts of your service, and there is automated test cases which tests it more comprehensively.
A written report: A precise description of what the report should contain can be found below.
Start date: Your group can start modeling as soon as you like, and must have come up with a design before the design meeting on March 5, 13:15-15:00 (IDA) or 15.15-17.00 (IEA) – but you shouldn’t write any code before the meeting (since you need to know that your design is OK before you start coding…)
Krusty Kookies is a bakery which specializes in cookies, and they need a database to keep track of their production and deliveries. Exactly what parts of their system you have to implement is explained under Program Requirements below.
The company sells different kinds of cookies, each with its own recipe – the recipes may change over time, and each year, depending on sales statistics, some recipes are added and some are removed. The following table shows the ingredients needed for producing 100 cookies of each cookie.
The company has a raw materials warehouse in which all ingredients used in their production are stored.
The cookies are baked in large quantities, and then quickly frozen and packaged in bags with 15 cookies in each bag. The bags are put into boxes, with 10 bags per box. Finally, the boxes are stacked on pallets, where each pallet contains 36 boxes, all containing the same product. This is a sketch depicting the production process, and storage and delivery:
When a pallet is produced and labeled, it is transported to the deep-freeze storeroom – once the pallet reaches the storeroom, the pallet label is read by a bar code reader.
The company only delivers to to wholesale customers, and a typical order looks like “send 10 pallets of Tango cookies, and 6 pallets of Berliners to Kalaskakor AB” – pallets are the unit of all orders (i.e., you can’t break up a pallet in an order). Customers place their orders through a web form or by telephone, and all customers must be registered in the database – the current customers are:
On delivery, pallets are transported from the deep-freeze storeroom via a loading ramp to the freezer trucks – each truck loads 60 pallets. The entry to the loading ramp contains a bar code reader which reads the pallet label. Pallets must be loaded in production date order.
When the truck is fully loaded, the driver receives a loading bill containing customer names, addresses, and the number of pallets of each product that is to be delivered to each customer. A transport may contain deliveries intended for different customers.
The company continuously take random samples among the products, and the samples are analyzed in their laboratory. If a sample doesn’t meet their quality standards, all pallets containing that product which have been produced during a specific time interval are blocked. A blocked pallet may not be delivered to customers.
A pallet is considered to be produced when the pallet label is read at the entrance to the deep-freeze storage. The pallet number, product name, and date and time of production are registered in the database. The pallet number is unique.
At any time, the company must be able to check how many pallets of a product have been produced during a specific time.
When a pallet is produced, the raw materials storage must be updated, and the company must be able to check the amount in store of each ingredient, and to see when, and how much of, an ingredient was last delivered into storage.
All pallets must be traceable, for instance, the company needs to be able to see all information about a pallet with a given number (the contents of the pallet, the location of the pallet, if the pallet is delivered and in that case to whom, etc.). They must also be able to see which pallets contain a certain product and which pallets have been produced during a certain time interval.
Blocked products are of special interest. The company needs to find out which products are blocked, and also which pallets contain a certain blocked product.
Finally, they must be able to check which pallets have been delivered to a given customer, and the date and time of delivery.
Orders must be registered in the database, and, for production planning purposes, the company must be able to see all orders which are to be delivered during a specific time period.
When the loading bill has been printed, the data regarding delivered pallets must be updated with customer data and date of delivery.
The company must be able to test the system without connecting it to the factory. For instance, they do not have access to the bar code readers which inform them about pallet production or pallet delivery. Instead, they want one screen in the user interface where they can simulate pallet production, and another screen where they can simulate pallet delivery (these screens would be removed from the production version of the system, but will be an important part of this project).
The production version of the system will have several programs, each used by different departments, but all using the same database:
One program which handles everything concerning production, blocking and searching of pallets – the test version of this program will incorporate the screen where pallet production is simulated (see above).
One program which handles everything concerning orders and deliveries.
One program which handles everything concerning raw materials and recipes.
Your task is to implement a REST API which will be used by the first of these programs – when we test the program, we make sure that the database contains relevant data so we can demonstrate all of the functionality. Below you can see exactly what we will test.
The REST API is described here.
Your submission must contain:
A diagram which shows your model (using UML) – we prefer machine produced diagrams, a scanned in handwritten diagram is OK only if it’s very, very tidy (drawing by hand makes it much harder to change your model, so we recommend that you use some program to draw your model).
We’re going to be very fuzzy about this diagram, it must be absolutely correct (proper UML-notation, primary keys marked, no foreign keys, weak entity sets marked, all multiplicities written out, etc.).
A file create-schema.sql with the SQL statements needed to create the tables of your database.
A file initial-data.sql with the SQL statements needed to insert test data to the database so we can test the program properly.
A PDF file report.pdf, which contains your report. The report should show:
The UML diagram of your ER-model .
Your relations, with primary keys and foreign keys marked correctly, and proper naming.
Short description of how to setup your server (i.e., how to compile/run the program). Describe if you have changed any other file than Database.java or if you have added new files to the Eclipse project.
The source code for your REST service.
Submission:
The submission must be sent by email to edaf20@cs.lth.se. The subject of the mail must be “Krusty by user1 and user2”. If you did the project on you own the use the title “Krusty by user1”. Replace user1 and user2 with the correct user names. It is the same as listed for the lab groups. (If you are 3 people, use the subject “Krusty by user1 and user2 and user3”.)
Submit the source code of the REST service by uploading a zip file of the Eclipse project to Google Drive or similar, and then add a link to the zip file in the submission email. When using Google Drive, allow anyone with the link to access the file (“Get shareable link” and “Anyone with the link can view”). Alternatively, you can add read access to csz-naf@ulund.org.
Use puccini.cs.lth.se as database server (making it easier to test your submission).