
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:
- an alphabetically ordered list of all the wines which are not produced abroad.
- extract all the data of the producers of "Teroldego".
- 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 .
- retrieve the names of all the wines and their producers. Order the list by alcoholic content.
- extract a list of all the producers (name and logo) who are located in the South or on the islands.