Databases - Revision history

Suhridk: /* Referential Constraints */

2014-03-15T07:58:46Z

Referential Constraints

2014-03-15T06:21:08Z

OLAP Queries

2014-03-15T06:18:23Z

OLAP Queries

2014-03-15T06:10:33Z

OLAP Queries

2014-03-15T06:06:18Z

OLAP Queries

2014-03-15T05:48:05Z

OLAP Queries

2014-03-15T05:45:54Z

OLAP Queries

2014-03-15T05:45:00Z

OLAP Queries

2014-03-15T05:36:11Z

OLAP Queries

2014-03-15T05:33:39Z

OLAP Queries

@@ Line 419: / Line 419: @@
 * Above options work in the same way for update. Update to the referenced value will cause same updates in referencing value.
 * If referential constraints are set up, then drop table of referencing table also wont be allowed.
-* Example : create table Apply(sID int references Student(ID) '''on delete set null''', cname text references College(cname) '''on update cascade''', major text, decision text). For student update and college delete, the default constraint is restrict.
+* Example : For student update and college delete, the default constraint is restrict.
 * referential constraints can also be defined within the same table.

← Older revision		Revision as of 06:21, 15 March 2014
Line 752:		Line 752:

	* With rollup : Makes sense for hierarchical dimensions. e.g state, county, city : all combo's of state, county,city wont occur since only some combos are valid.		* With rollup : Makes sense for hierarchical dimensions. e.g state, county, city : all combo's of state, county,city wont occur since only some combos are valid.
		+	* So will rollup in the group by clause order. With cube will do it for all combos - but not all will be meaningful. e.g. we dont all combo's of State and BLR, since BLR occurs in only one state.

	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">

@@ Line 746: / Line 746: @@
 where C.storeID = S.storeID and C.itemID = I.itemID
 and state = 'CA' and color = 'blue' and custID is null;
 </syntaxhighlight>

@@ Line 737: / Line 737: @@
 * Running queries directly on the cube table :
 <syntaxhighlight lang="sql">
-select sum(p)
+select C.*
 from Cube C, Store S, Item I
 where C.storeID = S.storeID and C.itemID = I.itemID

← Older revision		Revision as of 06:06, 15 March 2014
Line 718:		Line 718:

	* OLAP app's need to query the cube directly. Create the cube using a mat. view or as a separate table.		* OLAP app's need to query the cube directly. Create the cube using a mat. view or as a separate table.
		+
		+	<syntaxhighlight lang="sql">
		+
		+	create table Cube as
		+	select storeID, itemID, custID, sum(price) as p
		+	from Sales
		+	group by storeID, itemID, custID with rollup
		+	union
		+	select storeID, itemID, custID, sum(price) as p
		+	from Sales
		+	group by itemID, custID, storeID with rollup
		+	union
		+	select storeID, itemID, custID, sum(price) as p
		+	from Sales
		+	group by custID, storeID, itemID with rollup;
		+
		+	</syntaxhighlight>

	* Running queries directly on the cube table :		* Running queries directly on the cube table :
		+
		+	<syntaxhighlight lang="sql">
		+
		+	select sum(p)
		+	from Cube C, Store S, Item I
		+	where C.storeID = S.storeID and C.itemID = I.itemID
		+	and state = 'CA' and color = 'blue' and custID is null;
		+
		+	</syntaxhighlight>

@@ Line 705: / Line 705: @@
 * SQL constructs added to support OLAP are "with cube" and "with rollup". These are added to the group by clause.
 * WITH CUBE : Adds faces, edges, and corners of data cube : sum of price for a given item, customer across all stores, sum of price for a store for all items etc.
@@ Line 717: / Line 716: @@
 </syntaxhighlight>

@@ Line 708: / Line 708: @@
 * WITH CUBE : Adds faces, edges, and corners of data cube : sum of price for a given item, customer across all stores, sum of price for a store for all items etc.
-<syntaxhighlight lang="java5">
+<syntaxhighlight lang="sql">
 select storeID, itemID, custID, sum(price)

← Older revision		Revision as of 05:45, 15 March 2014
Line 703:		Line 703:

	* Rolling up : Want to have less detail (more aggregation), take out attributes from group by clause.		* Rolling up : Want to have less detail (more aggregation), take out attributes from group by clause.
		+
		+	* SQL constructs added to support OLAP are "with cube" and "with rollup". These are added to the group by clause.
		+
		+
		+	* WITH CUBE : Adds faces, edges, and corners of data cube : sum of price for a given item, customer across all stores, sum of price for a store for all items etc.
		+
		+	<syntaxhighlight lang="java5">
		+
		+	select storeID, itemID, custID, sum(price)
		+	from Sales
		+	group by storeID, itemID, custID with cube;
		+
		+	</syntaxhighlight>

← Older revision		Revision as of 05:36, 15 March 2014
Line 690:		Line 690:

	</syntaxhighlight>		</syntaxhighlight>
		+
		+	* Dice : Slice the data cube in two dimensions to give a chunk of the cube
		+
		+	<syntaxhighlight lang="sql">
		+	select F.storeID, I.itemID, custID, sum(price)
		+	from Sales F, Store S, Item I
		+	where F.storeID = S.storeID and F.itemID = I.itemID
		+	and state = 'WA' and color = 'red'
		+	group by F.storeID, I.itemID, custID;
		+
		+	</syntaxhighlight>
		+
		+	* Rolling up : Want to have less detail (more aggregation), take out attributes from group by clause.

← Older revision		Revision as of 05:33, 15 March 2014
Line 678:		Line 678:

	* Drill down : If more details are required. e.g. in the above query add item to the select and group by clause to get item details.		* Drill down : If more details are required. e.g. in the above query add item to the select and group by clause to get item details.
		+
		+	* Slicing : Analyze a slice of the data cube.
		+
		+	<syntaxhighlight lang="sql">
		+
		+	select F.storeID, itemID, custID, sum(price)
		+	from Sales F, Store S
		+	where F.storeID = S.storeID
		+	and state = 'WA'
		+	group by F.storeID, itemID, custID;
		+
		+	</syntaxhighlight>