Grocery Store Chain Data Mining

You have been hired to develop a database intensive application for a large grocery chain, "Food Heaven". The brand offers its customers a value card. Presenting this card at the register, entitles the customers to exclusive savings. Therefore it is a no-brainer for the usual customers of this brand to request a card, and also carry it in every single time they do go grocery shopping. The cashiers will politely ask for the card, if available.

The card is free, the only task the customers have to do is fill out an application card, that will mainly collect the demographics information, like name, address, and phone number, but also sex, salary range, hobbies. Of course most of the surveyed items are optional, however people generally fill the entire form out. There is nothing wrong with that, however now the grocery chain is in possession of invaluable information. It is a pretty inovative concept. A grocery store, which is actually able to trace most of its transactions back to known customers it holds some information on.

This will allow management to better stock the stores, and also be able to better market their products. Again, you have been designated to play an important role in storing, managing and collecting this information.

1. Design the necessary relations and their dependencies in order to support the above described. Enumerated is a list concepts that are mandatory to be part of this schema:

• stores (location, region)
• product categories
• products
• transactions
• details of each transaction (itemization)
• customers

Obviously you will have to make the right assumptions and you are required to have primary key and foreign key constraints, potentially NOT NULL constraints, defined for your schema.

2. All input to and output from your application will be done in XML format. You will have to define your own DTD (Document Type Description) or XSD schema, it is up to you if you have a mapping between XML and the DDL schema or you use pretty much the same names for the fields when applicable.

3. You will have to create all your test data.

4. Each team will consists of 2 students, in the case of black sheep or solitaires you will be paired with the instructor.

5. You will need to provide the ability to load data into your database, and also the ability to query data. The format should be XML. The emphasis will not be set on the User Interface, unless you are getting bored with the server side, i.e. works well and you still feel like working on your project. You will need some user interface, however it doesn't have to be web based, or even graphical, it can be command line.

6. The mandatory queries will be detailed and communicated soon. You will need to accommodate at least these, and feel free to be creative and provide even more queries.

• Daily revenues by region, daily revenues by grocery store, where the region and grocery store are specified by the user.
• Most profitable product category per region, per month.
• Best selling product for a specified grocery store (user input).
• The ability to query per store, per customer sex, per hour on which product category was bought the most.

    Only the query results have to be provided in XML format, the query parameters can be entered command line, or through user interaction.

7. The technologies to be used are JDBC, accessing an Oracle database, and using the JAXP API for XML parsing.

8. There is no preferred solution for this project. Obviously some will be better or more detailed than others. Try to do your best, clearly documenting your assumptions.

9. Additional details will be provided in class.