Saturday, April 14, 2012

Supplier-Parts Database SQL Script

Below is a DDL script to create Chris Date's supplier-parts database. If Chris Date can explain SQL using this simple database it is worth trying. There is another another version of this database with an additional table that I may post here eventually. A few points of interest in the DDL below:  1) in the Parts table note the use of a unique constraint at the table level to define the uniqueness of the the combination of pname, color and city, 2) in the SupplierParts table there is again a table level constraint as the primary key is the combination of pnum and snum; in the same table definition note that the foreign key references clause does not include a referenced field name because the field names are the same.

CREATE TABLE Suppliers
  (
 SNUM int NOT NULL PRIMARY KEY,
 SNAME varchar(16) NOT NULL UNIQUE,
 STATUS int NOT NULL,
 CITY varchar(20) NOT NULL
  );


CREATE TABLE Parts
  (
 PNUM int NOT NULL PRIMARY KEY,
 PNAME varchar(18) NOT NULL,
 COLOR varchar(10) NOT NULL,
 WEIGHT decimal(4,1) NOT NULL,
 CITY varchar(20) NOT NULL,
 UNIQUE (PNAME, COLOR, CITY)
  );


CREATE TABLE SupplierParts
  (
 SNUM int NOT NULL REFERENCES Suppliers,
 PNUM int NOT NULL REFERENCES Parts,
 QTY int NOT NULL,
 PRIMARY KEY (SNUM, PNUM)
  );


  INSERT INTO Suppliers VALUES (1, 'Smith', 20, 'London');
  INSERT INTO Suppliers VALUES (2, 'Jones', 10, 'Paris');
  INSERT INTO Suppliers VALUES (3, 'Blake', 30, 'Paris');
  INSERT INTO Suppliers VALUES (4, 'Clark', 20, 'London');
  INSERT INTO Suppliers VALUES (5, 'Adams', 30, 'Athens');

  INSERT INTO Parts VALUES (1, 'Nut', 'Red', 12, 'London');
  INSERT INTO Parts VALUES (2, 'Bolt', 'Green', 17, 'Paris');
  INSERT INTO Parts VALUES (3, 'Screw', 'Blue', 17, 'Oslo');
  INSERT INTO Parts VALUES (4, 'Screw', 'Red', 14, 'London');
  INSERT INTO Parts VALUES (5, 'Cam', 'Blue', 12, 'Paris');
  INSERT INTO Parts VALUES (6, 'Cog', 'Red', 19, 'London');

  INSERT INTO SupplierParts VALUES (1, 1, 300);
  INSERT INTO SupplierParts VALUES (1, 2, 200);
  INSERT INTO SupplierParts VALUES (1, 3, 400);
  INSERT INTO SupplierParts VALUES (1, 4, 200);
  INSERT INTO SupplierParts VALUES (1, 5, 100);
  INSERT INTO SupplierParts VALUES (1, 6, 100);
  INSERT INTO SupplierParts VALUES (2, 1, 300);
  INSERT INTO SupplierParts VALUES (2, 2, 400);
  INSERT INTO SupplierParts VALUES (3, 2, 200);
  INSERT INTO SupplierParts VALUES (4, 2, 200);
  INSERT INTO SupplierParts VALUES (4, 4, 300);

  INSERT INTO SupplierParts VALUES (4, 5, 400);

Sunday, April 8, 2012

Database scripts

Our scripts can be found at http://tinyurl.com/SQLScripts. This will take you to box.net which has the scripts we have posted to the internet. This will eventually include scripts to create and load most of the databases we uses in our classes or teaching materials.

Thursday, April 5, 2012

Xeround

I finally signed up for the Xeround free cloud MySQL database. It was a fairly painless process although it seemed to take a couple of attempts to get the password to take. It uses the standard PhpMyAdmin tool for administering the database. The Xeround account page is shown below. I loaded my standard bookstore script into the PhpMyAdmin SQL window and after a few modifications was able to get the database created and loaded. I will say this is pretty handy. I am sitting in my home office accessing the database which I actually created an hour or two ago at our main office.

Below is a screenshot of the main account page.



With Cube

Background
SQL does not normally put total lines in the same result set as the detail like an accounting spreadsheet. By adding with cube to the group by clause you will get the total lines as shown below. It is similar to with rollup but subtotals each dimension. For our examples we will use a tiny fact table from the pets database. You can download the database script here. It is a tiny fact table that summarizes animals available by store. We originally obtained the table from the internet, I forget where. At some point we may add dimension tables to get a true star schema.

Notes
The result set returned by a group by with cube is a valid SQL result set. It can be filtered or manipulated like any other result set. The total line or lines returned are not the total of the groups shown. They are the totals in the table. An example below will make this clearer. A NULL is placed in the dimension column where a summary row has been inserted.

Syntax
SELECT statement...
...
GROUP BY groupbylist
WITH CUBE

Example 1
Show the animals available by store with subtotals for each store type and a grand total. In addition show the subtotals for each animal type.























Example 2
Same above but retain only rows with number available greater than 10. Note that the grand total row still shows 63 animals available.