How to provide relational algebra for the given schema?

Question

EMPLOYEE(PERSONNAME, STREET, CITY)

WORKS(PERSONNAME,COMPANYNAME, SALARY)

COMPANY(COMPANYNAME, CITY)

MANAGES(PERSONNAME, MANAGERNAME)

Find the names of all employees in this database who do not work for First Bank Corporation (assuming that all people work for exactly one company and people are allowed to appear in the database (e.g. in employee) but not appear in works).

Here I do not understand the assumption part. What does it actually mean ?

Wheeze

This site is not to let others do your homework. Please provide your attempts to solve the task and specify a dedicated programming question. – Si

I know that very well. I have figured out the answers. But I do not understand the assumption part mentioned in the question. Could you please explain that part if know anything? Thanks. – Wheeze

They appeaar to be conflicting (exactly one versus one_or_zero) . Best answer could be to answer both versions, IMO. – Bakehouse

Answer 1

As @wildplasser pointed out: The first part of the assumption

assuming that all people work for exactly one company...

declares the case that there is a exact one relation between employees and companies. This would imply that it is not allowed to have data records where an employee is not associated to any company. That is where the second part of the assumptions kicks in

... and people are allowed to appear in the database (e.g. in employee) but not appear in works

So this tells us that is even possible to hold employees not being associated to any company.

For me now i would conclude that the question asks for the zero or one relation between company and employee entities.

Si

Answer 2

TL;DR: Maybe you mean that you "do not understand the assumption part" of querying. That's fine, because there is no part in querying for assumptions.

PS: As pointed out in other answers, the assertions seem contradictory. They are unclear, and so is the query. I suspect that "all people work for exactly one company" means "people in works work for exactly one company"; otherwise the straightforward meaning contradicts the assumption that "people are allowed to appear in [employee] but not appear in works" (hence be unemployed). And I suspect that "names of all employees in this database" means "people in employee", per "in the database (e.g. in employee)".

---

To query you just need to understand what a row states when it is in and when it is not in a table (base variable or query result). Ie how to know a table's meaning aka predicate, a fill-in-the-(named-)blanks statement where the columns are parameters:

-- employee PERSONNAME lives on STREET in CITY

It is traditional in engineering and mathematics to use a name and the parameters as shorthand:

-- EMPLOYEE(PERSONNAME,STREET,CITY)

Each row with the table's columns makes a statement aka proposition by supplying arguments per its column values:

(Frank, 1st Avenue, Mytown) proposition from EMPLOYEE predicate:
    -- employee Frank lives on 1st Avenue in Mytown
    -- EMPLOYEE(Frank, 1st Avenue, Mytown)

The rows that make a true proposition go in a table and those that don't don't. So each present row states its proposition and each absent row states NOT its. A table holds the rows that make its predicate into a true proposition.

The relational operators are designed so that the meaning/predicate of a result is a certain combination of the meaning/predicate of its input(s):

R  -- holds rows where R(...)
S  -- holds rows where S(...)
R JOIN S  -- holds rows where R(...) AND S(...)
R UNION S  -- holds rows where R(...) OR S(...)
R MINUS S  -- holds rows where R(...) AND NOT S(...)
R PROJECT columns to keep  -- holds rows where FOR SOME columns to drop, R(...)
R RESTRICT condition  -- holds rows where R(...) AND condition

(Re using SQL, a (unfortunately unfaithful & awkward) hybrid of these, see this answer.)

Your assignment:

EMPLOYEE(PERSONNAME, STREET, CITY)
WORKS(PERSONNAME,COMPANYNAME, SALARY)
COMPANY(COMPANYNAME, CITY)
MANAGES(PERSONNAME, MANAGERNAME)

Find the names of all employees in this database who do not work for First Bank Corporation

We want the table of rows that make some meaning/predicate true. Rephrase to get the one we want:

-- employee PERSONNAME does not work for FBC

We have to phrase this meaning/predicate in terms of the meanings/predicates we have been given: (I have to guess what those are because you didn't give them.)

-- rough draft with only relevant columns
-- ... [employee PERSONNAME ...]
AND NOT ... [employee PERSONNAME works at COMPANY ... AND COMPANY = FBC]

-- full base predicates but drop/keep columns appropriately
-- FOR SOME STREET & CITY [employee PERSONNAME lives on STREET in CITY]
AND NOT FOR SOME COMPANY & SALARY
        [employee PERSONNAME works at COMPANY for $SALARY AND COMPANY = FBC]

-- shorthand
-- FOR SOME STREET & CITY [EMPLOYEE(PERSONNAME, STREET, CITY)]
AND NOT FOR SOME COMPANY & SALARY
        [WORKS(PERSONNAME,COMPANYNAME, SALARY) AND COMPANY = FBC]

For the table of rows that make this true we convert logic operators to relational operators and convert base table predicates to names:

    PROJECT PERSONNAME (EMPLOYEE)
MINUS PROJECT PERSONNAME (RESTRICT COMPANY = FBC (WORKS))

(Your variant of relational algebra might have different operators.)

Constraints are limitations on what application situations and corresponding database values can arise. They are "assumptions" because they are taken to be true. They are business rules (including relevant procedures, physical laws and mathematical truths and consequences of them). As with descriptions of what a base holds or a query "finds", they can be rephrased as meanings/predicates using natural language about the current situation and/or using corresponding relation expressions about the current base table values. (Since they are always true, the meanings/predicates are statements/propositions. And so their query versions all have as result value the table with no columns and one row. So they don't need base tables.)

Constraints are not needed to query. They always evaluate to a true statement, so conjoining (ANDing) them with a query statement/proposition doesn't affect what rows go into and stay out of bases. (They do imply constraints on query expressions in terms of their constituent base tables. Also, they allow the DBMS to prevent impossible database values and they help users to check understanding of base table meanings/predicates.)

The person who gave you this assignment might not understand this though. You can ask them: If any of those constraints did not hold then how would the query expression be different? (Answer: It wouldn't be.)

The table meanings/predicates haven't been given. Maybe the assertions are an attempt to explain what "employee" means in the query vis a vis the (ungiven) meanings of employee and works.

Furlong

While I suspect isi's answer addresses OP's question, your answer is an excellent recap of fundamental concepts which even experienced practitioners would do well to read. Rows represent propositions about entities, not entities themselves. I wish I could upvote multiple times. – Koralle