Er Diagram

ER Diagram for JOBS There are a number of tutorial questions on the JOBS database. This can be described with the following ER diagram:

Figure : ER diagram for the JOBS database

ER Diagram for Dressmaker Contents • • • • • • • •

JMCUST DRESS_ORDER ORDER_LINE QUANTITIES GARMENT MATERIAL CONSTRUCTION DRESSMAKER

There are a number of tutorial questions on the DRESSMAKER database. This can be described with the following ER diagram:

Figure: Dressmaker ER Diagram

The dressmaker tables make use of composite primary keys, and therefore has composite foreign keys. A relationship involving a composite key must include all the attributes involved. For instance, a query needing ORDER_LINE, CONSTRUCTION, and DRESSMAKER would need something like: SELECT * FROM order_line JOIN construction ON ( order_line.order_ref = construction.order_ref AND order_line.line_no = construction.line_ref ) JOIN dressmaker ON (dressmaker.d_no = construction.maker) ;

JMCUST This table contains information on the customers who use the dressmaker company, including a unique id, the customer name and house number, and the customer's post code.

DRESS_ORDER If a customer makes an order, it is recorded here. Each order has an order number, and an associated customer number. The date of the order is also recorded. Once all the items in the order have been completed, COMPLETED is set to Y, otherwise it is N. Only uppercase Y or N is used.

ORDER_LINE Each order that a customer places is made up of 1 or more garments. Each garment of the order is recorded in this table. It is called ORDER_LINE as it represents a single element or line of an order sheet. Each garment in an order is given a unique number (line_no). ORDER_REF is the order number. Garments to be build need a style (trousers, shirts, etc), a size (10,12,etc) and a material (silk, cotton, etc).

QUANTITIES QUANTITIES explains how much material is needed to build a particular garment. For instance style 4 in size 16 requires 1.5 linear feet of material. Material is sold in a roll, and so someone needs to measure 1.5 feet off the roll and give that to a dressmaker to make the garment.

GARMENT Each garment has a style number, a description (e.g. trousers), a labour cost and some dressmaker notes (called notions). The labour cost indicates how much money has to be payed to a dressmaker for the time required to make this garment.

MATERIAL The material to make a garment has a material number, and a fabric name (e.g. cotton). Each fabric may be available in different colours and fabric patterns (like stripes). The COST is the price of the material in linear feet. So one foot off the role of material costs so many pounds.

CONSTRUCTION This allocates each item in an order to a particular dressmaker. It includes a start date (when it was allocated) and has a finish date of NULL when it is not finished, or the date when it was finished.

DRESSMAKER Each dressmaker who works for the company is recorded here. Each dressmaker has a name and unique id, plus a house number and a post code. The dressmakers are all freelance, and thus get paid only on completion of a garment.