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

database-designrelational-divisionrelational-theory

I understand that the Cartesian product (X) operation on two databases does not need to be UNION compatible. So,if there is a same attribute called name in two relations R and S where name in R is the first name and name in S is the second name, how can the related values be identified by the following selection operation?

Q=RxS

I want to get the set of tuples whose firstname = lastname. How am I supposed to write the selection statement?

σ Name=Name(Q)

Will there be a problem using the same attribute name in the selection operation?

I'm just referring here to the problem of writing the relational expression (selection) when same attribute name is used.

Best Answer

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

Related Question