Do-It-Yourself

Task

You are to design a database for a section of a DIY shop which sells planks of raw wood. The database must store the following details for each item: dimensions (length, depth, width), code, type of wood (pinewood, birch, ash), price, stock.

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

Create also the queries which will return:

1. code and price of all pinewood items in stock
2. code, dimensions and price of all pinewood items in stock (sorted by price, high-to-low)
3. a list of the types of wood of the items in stock only
4. all the information relating to items deeper than 5 mm
5. a list of all the items out of stock (dimensions and type of wood) sorted by dimension (length, width and then depth) in ascending order.