Connor: Created page with "

Dressmaker database

Graduated questions

Easy questions 1..5
Medium questions 6..10
[[..."
2012-07-19T14:23:38Z

Created page with "<h1>Dressmaker database</h1> <p>Graduated questions</p> <ul> <li>Easy questions 1..5</li> <li>Medium questions 6..10</li> <li>[[..."

New page
<h1>Dressmaker database</h1>
<p>Graduated questions</p>
<ul>
<li>[[Dressmaker easy |Easy questions 1..5]]</li>
<li>[[Dressmaker medium |Medium questions 6..10]]</li>
<li>[[Dressmaker hard |Hard questions 11..15]]</li>
</ul>
ER diagram for the dressmaker database:
[[Image]]
<p>[[SQL Source for the database]]</p>

<h2>Some sample queries</h2>

<div class='qu'>
The "central" table in this database is order_line - every garment ordered takes one line in this table. Many of the fields in this table are references to other tables. The fields of this table have the following meaning:
order_ref
<p>This is a link to the dress_order table. We can join the dress_order table to find information such as the the date of the order and the customer number for a given garment order.</p>
line_no
<p>The line number is used to distinguish different items on the same order - for example order number 5 has three lines - 1, 2 and 3.</p>
ol_style
<p>Indicates the article of clothing ordered. For example ol_style 1 indicates trousers - we can see this by joining to the garments table. Line 1 in the garment table is trousers.</p>
ol_size
<p>The size of the item ordered is given here - this is particularly important when it comes to working out how much material is required to build the item. We can see from the quantities table that trousers (style 1) in size 8 takes 2.7 meters - whereas trousers in size 12 needs 2.8 meters.</p>
ol_material
<p>Each order specifies the material to be used. We can join to the material table to find a description or cost per meter. Material 1 is Silk, Black, Plain costing £7 per meter.</p>
<source lang='sql' class='def'>
SELECT order_ref, line_no, ol_style, ol_size, ol_material
FROM order_line
</source>
</div>

<div class='qu'>
A sample join:

In order to translate the numbers in order_line into meaningful values we need to join a related table. For example if we want to access the descriptions of the materials we need to join the material table.

To achieve the join we include the table material on the FROM line and the join condition as a WHERE clause.

For each pair of tables there is a join condition between them (if they are linked). To find the join condition between order_line and material we look at the order_line table CREATE statement and notice the line that specifies that ol_material references the material table. This link will always be to the primary key of material table.

CREATE TABLE order_line (
order_ref INTEGER NOT NULL REFERENCES dress_order
,line_no INTEGER NOT NULL
,ol_style INTEGER REFERENCES garment
,ol_size INTEGER NOT NULL
,ol_material INTEGER REFERENCES material
,PRIMARY KEY (order_ref, line_no)
,FOREIGN KEY (ol_style, ol_size) REFERENCES quantities
);

<source lang='sql' class='def'>
SELECT order_ref, line_no, fabric, colour, pattern, cost
FROM order_line, material
WHERE ol_material = material_no
</source>
</div>

<div class='qu'>
To get a description of the garment we need to join the garment table. The join condition is that the ol_style in order_line matches the style_no in garment.
<source lang='sql' class='def'>
SELECT order_ref, line_no, description
FROM order_line, garment
WHERE ol_style = style_no
</source>
</div>

<div class='qu'>
If we need both the description and the fabric we can join both material and garment to the order_line table.
The join conditions are combined with "AND"
<source lang='sql' class='def'>
SELECT order_ref, line_no, fabric, description
FROM order_line, material, garment
WHERE ol_style = style_no
AND ol_material = material_no
</source>
</div>

<div class='qu'>
The quantities table tells us how much material is required for every garment for every size available.
The join between the order_line and quantities is unusual in that it involves two fields.
This can be seen by the fact that quantities has a composite key.
<source lang='sql' class='def'>
SELECT order_ref, line_no, quantity
FROM order_line, quantities
WHERE ol_style = style_q
AND ol_size = size_q
</source>
</div>

<div class='qu'>
Customers place orders - each order contains many lines - each line of the order grefers to a garment:
<source lang='sql' class='def'>
SELECT c_name, order_date, order_no, line_no
FROM jmcust, dress_order, order_line
WHERE jmcust.c_no = dress_order.cust_no
AND dress_order.order_no = order_line.order_ref
</source>
</div>

Dressmaker - Revision history

Connor: Created page with "

Dressmaker database