Relational Theory – Attributes After Cross Product

relational-theory

I have a question regarding the resulting attributes in the cross product of two relations in relational algebra…

Generally, the cross product will result in a relation whose attributes is the sum of all the attributes, So number of attributes in R1 x R2 will be attributes in R1 + R2, so R1(a, b, c) x R2(d, e, f) → R(a, b, c, d ,e ,f)…

But, what happens if R1 and R2 have common attributes (attributes of the same name)? since a relation can not have duplicate attributes… so what would happen in the case R1(a, b, c) X R2(a, e, f)? thanks…

Best Answer

To express that relation in algebraic notation you actually need to alias the attribute.

The Cartesian product, by design, does not recognize common attribute names, so much like a single operation doesn't work in a self relation scenario, it's necessary to perform a rename within the notation.

The self join example:

R( a, b, c );

≠ R ⋈ R ( a, b, c );

Using rename:

R( a, b, c );

S = ρ_{a/b, b/b₂, c/c₂} R;

∴ R ⋈ S ( a, b, c, b₂, c₂ );

For instance:

E( EmpID, ManagerID, EmpName );

M = ρ_{EmpID/ManagerID, ManagerID/ManagerEmpID, EmpName/ManagerName} E;

∴ E ⋈ M ( EmpID, ManagerID, EmpName, ManagerEmpID, ManagerName );

The Cartesian product can be expressed using relational algebra in the same way:

R1( a, b, c );

R2( a, e, f );

≠ R1 × R2 ( a, b, c, e, f );

So then with rename:

R1( a, b, c );

R2( a, e, f );

R3 = ρ_a/a₂R2;

∴ R1 × R3 ( a, b, c, a₂, e, f );

Related Solutions

Modeling a ticket system based on a set of disparate kinds of tickets

You mentioned the term "template" in your description but don't use it in your design. So, first, you need table that lists all possible template types - template_type with id and name columns. As an example it may contains the following data:

Id    Name
 1    Removing a discontinued product(s) from online storefronts
 2    Resolving product upload error
 3    Fixing a workstation

Then you need storage for configuration of each template type - template. It contains records for each ticket_key which includes into particular template as well as reference to id column in template_type table. Now each particular ticket should have template_type_id column to reference to its template type.

And now, you can implement your authorization system by setting access rights for particular ticket_key in particular template.

EDIT: corrected according comment to the answer.

What Happens when Cartesian Product is applied to Relations with same attribute name

By way of example, using SQL Server, the simplest answer is an INNER JOIN:

USE tempdb;
GO
CREATE TABLE Names1
(
    FirstName varchar(50)
);
CREATE TABLE Names2
(
    LastName varchar(50)
);
INSERT INTO Names1 (FirstName) VALUES ('Max');
INSERT INTO Names1 (FirstName) VALUES ('Vernon');
INSERT INTO Names2 (LastName) VALUES ('Max');
INSERT INTO Names2 (LastName) VALUES ('Vernon');

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.FirstName = N2.LastName;

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;

The results:

enter image description here

However, if you are prepared to complicate your query unnecessarily, you could do the following:

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.FirstName = n2.LastName;

Results:

enter image description here

In your question, you ask if using the same field names is going to be an issue; plainly it is not:

USE tempdb;
GO

CREATE TABLE Names1
(
    [Name] varchar(50)
);
CREATE TABLE Names2
(
    [Name] varchar(50)
);
INSERT INTO Names1 ([Name]) VALUES ('Max');
INSERT INTO Names1 ([Name]) VALUES ('Vernon');
INSERT INTO Names2 ([Name]) VALUES ('Max');
INSERT INTO Names2 ([Name]) VALUES ('Vernon');

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.[Name] = N2.[Name];

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;


SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.[Name] = n2.[Name];

Results:

enter image description here

Best Answer

Related Solutions

Modeling a ticket system based on a set of disparate kinds of tickets

What Happens when Cartesian Product is applied to Relations with same attribute name

Related Question