Wine

Task

We want to design a database which stores information about wines and wineshops.

For each wine we will store its name, alcoholic content, quality mark -if available- (igp, doc, docg, etc.) and the name of its producer(s). For each producer the DB will store their name, a picture with the producer's logo, and the region where they are located (let us suppose that is enough to distinguish between five areas: north, south, centre, islands and abroad).

Design an entity-relationship diagram, a relational schema and an instance of the database.

Create also the queries which will return:

1. an alphabetically ordered list of all the wines which are not produced abroad.
2. extract all the data of the producers of "Teroldego".
3. retrieve the names of the wines produced in the South which have an alcoholic content higher than 13 degrees and which have a "doc" quality mark .
4. retrieve the names of all the wines and their producers. Order the list by alcoholic content.
5. extract a list of all the producers (name and logo) who are located in the South or on the islands.