The varying attributes is an attribute dimension that maps to multiple dimensions. The concept of varying attributes in a relational environment is depicted by creating a mapping table. In this recipe, we will build a mapping table that joins the SALESMAN table to the Product and Market tables. We will also see how this format works for a varying attribute.
To get started, open SQL Server Management Studio, and add a database called TBC
. In this recipe, we are using T-SQL, but the PL\SQL equivalent is provided in the examples.
Create and populate a
SALESMANtable with following script. We can change the script below to PL/SQL by replacingintwith INTEGER andvarchar()with VARCHAR2():--This is the syntax in T-SQL Create Table SALESMAN( SALESMANID int NOT NULL, SALESMANNAME varchar (80) NULL, Constraint PK_SALESMAN_SALESMANID Primary Key (SALESMANID) ); go --This scripts enter the data into the SALEMAN table Insert Into SALESMAN Values(1, 'John Smith'); Insert Into SALESMAN Values(2, 'Jose Garcia'); Insert Into SALESMAN Values(3, 'Johnny Blaze');
Create and populate a
PRODUCTStable:--This is the syntax in T-SQL Create Table PRODUCTS( PRODUCTID int NOT NULL, SKU varchar(15) NULL, SKU_ALIAS varchar(25) NULL, Constraint PK_PRODUCTS_PRODUCTID Primary Key (PRODUCTID) ); --Insert data into PRODUCTS table Insert Into PRODUCTS Values(1, '100-10', 'Cola'); Insert Into PRODUCTS Values(2, '100-20', 'Diet Cola'); Insert Into PRODUCTS Values(3, '100-30', 'Caffeine Free Cola');
Create and populate a
MARKETStable:--This is the syntax in T-SQL Create Table MARKETS( STATEID int NOT NULL, STATE varchar(25) NULL, Constraint PK_MARKETS_STATEID Primary Key (STATEID) ); --Insert data into MARKETS table Insert Into MARKETS Values(1, 'New York'); Insert Into MARKETS Values(2, 'Massachusetts'); Insert Into MARKETS Values(3, 'Florida');
Create and populate a
SALESMANMAPtable:--This is the syntax in T-SQL Create Table SALESMANMAP( SALESMANID int NOT NULL, STATEID int NOT NULL, PRODUCTID int NOT NULL, Constraint PK_SALESMANMAP Primary Key (STATEID, PRODUCTID) ); --Insert data into the SALESMANMAP table Insert Into SALESMANMAP Values(1,1,2); Insert Into SALESMANMAP Values(1,2,3); Insert Into SALESMANMAP Values(2,1,1); Insert Into SALESMANMAP Values(2,2,2); Insert Into SALESMANMAP Values(3,3,1); Insert Into SALESMANMAP Values(3,3,3);
Execute the following scripts to join the tables:
Alter Table SALESMANMAP Add Constraint FK_SALESMANMAP_PRODUCTID Foreign Key(PRODUCTID) References PRODUCTS (PRODUCTID); Alter Table SALESMANMAP Add Constraint FK_SALESMANMAP_SALESMANID Foreign Key(SALESMANID) References SALESMAN (SALESMANID); Alter Table SALESMANMAP Add Constraint FK_SALESMANMAP_STATEID Foreign Key(STATEID) References MARKETS (STATEID);
Execute the following query to see the relationship between the
Market,Product, andSalesman:Select T4.SALESMANID, T2.SALESMANNAME, T4.STATEID, T3.STATE, T4.PRODUCTID, T1.SKU_ALIAS From PRODUCTS T1, SALESMAN T2, MARKETS T3, SALESMANMAP T4 Where T1.PRODUCTID = T4.PRODUCTID and T2.SALESMANID = T4.SALESMANID and T3.STATEID = T4.STATEID and T2.SALESMANNAME = 'John Smith'
The relationships between the tables created and populated in steps 1 through 4 are shown in the following diagram:
The SALESMANMAP table joins the other three tables of which SALESMAN is the varying attribute. Salesman varies depending on the state and product you want to report on. We can retrieve the relationship between tables using the query shown in step 5. The results for this query are displayed in the following screenshot:
The important thing to note about this varying attribute example is that it is possible for two Salesmen to conduct business in the same Market, but it is not possible for the same two Salesmen to sell the same Product in the same Market. This logic is maintained by the constraints placed on the SALESMANMAP mapping table.