ASX database: -- Address contains the registered address of the company (excludi

Question

ASX database:

-- Address contains the registered address of the company (excluding the zip code and country
-- Zip is the zip code of the Address
-- Country is the incorporation country of the company (same as the country for the Address)
CREATE TABLE Company (
Code char(3) primary key check (Code ~ '[A-Z]{3}'),
Name text not null,
Address text default null,
Zip varchar(10) default null,
Country varchar(40) default null
);

-- Person may contain person name, title and/or qualification
CREATE TABLE Executive (
Code char(3) references Company(Code),
Person text,
primary key (Code, Person)
);

CREATE TABLE Category (
Code char(3) primary key references Company(Code),
Sector varchar(40) default null,
Industry varchar(80) default null
);

CREATE TABLE ASX (
"Date" date,
Code char(3) references Company(Code),
Volume integer not null check (Volume >= 0),
Price numeric not null check (Price > 0.0),
primary key ("Date", Code)
);

CREATE TABLE Rating (
Code char(3) references Company(Code),
Star integer default 3 check (Star > 0 and Star < 6)
);

CREATE TABLE ASXLog (
"Timestamp" timestamp,
"Date" date,
Code char(3) references Company(Code),
OldVolume integer not null check (OldVolume >= 0),
OldPrice numeric not null check (OldPrice > 0.0),
primary key ("Timestamp", "Date", Code)
);

Question :Assuming the schema from the ASX database, give the following queries in relational algebra:

1.List all the company names that are in the sector of "Technology".

2.List all the company codes that have more than five executive members on record (i.e., at least six).

3.Output the person names of the executives that are affiliated with more than one company.

4.List all the companies (by their Code) that are the only one in their Industry (i.e., no competitors). Same as Assignment 2, please include both Code and Industry in the output.

Explanation / Answer

1. Query to get company names with Technology sector:

select CO.Name
FROm Company CO
INNER JOIN Category CA
ON CA.Code = CO.Code
Where CA.Sector = 'Technology'

Relational algebra: (Name)(Category.Sector= 'Technology')(Company (Company.Code = Category.Code) Category)

2. all the company codes that have more than five executive members on record

select CO.Code
FROm Company CO
INNER JOIN Executive E
ON E.Code = CO.Code
GROUP BY CO.Code
HAVING COUNT(DISTINCT Person) > 5

Relational algebra: (Code)(COUNT(person)>5)Code,COUNT(Person)(Company (Company.Code = Executive.Code) Executive )

Description:

HAVING is used to filter data here instead of WHERE clause

3. Output the person names of the executives that are affiliated with more than one company.

select E.Person
FROm Company CO
INNER JOIN Executive E
ON E.Code = CO.Code
GROUP BY E.Person
HAVING COUNT(DISTINCT E.Code) > 1

Relational algebra: (Person)(COUNT(Code)>1)Person,COUNT(Code)(Company (Company.Code = Executive.Code) Executive )

Description: INNER JOIN with company is used to validate the company codes

4.

select CO.Code, CA.Industry
FROm Company CO
INNER JOIN Category CA
ON CA.Code = CO.Code
GROUP BY CO.Code, CA.Industry
HAVING COUNT(*) = 1

Relational algebra: (Code,Industry)(COUNT(*)>1)Code,Industry,COUNT(*)(Company (Company.Code = Category.Code) Category)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects