At university I have been given these three relations: >

> Customer (Name, City, Account)
> Offer (Product_Description Supplier, Price)
> Order (Name, Product_Description, Quantity, Supplier)
>

and this domain calculus: >

> {Name, City | ∃ Account, Product_Description, Supplier, Price, Quantity (
>     customer(Name, City, Account)
>     ∧ offer(Product_Description, Supplier, Price)
>     ∧ order(Name, Product_Description, Quantity, Supplier)
>     ∧ Price ≥ 100 ∧ Supplier = 'Meier' )}
>

I have been asked to express this domain calculus as a sentence and convert it to relational algebra. Sentence: List the name and cities of the customers who ordered a product that has a value greater than or equal to 100 and has supplier Meier. (I didn’t use "customers who ordered greater than or equal to 100" because it's possible that the customer ordered, for example, 2 products (quantity is 2) where each has a value of 50 euros.) Relational Algebra:

π name, city (Customer)
⋈ π name (Order ⋈ Price ≥ 100 ∧ Supplier = 'Meier' (Offer))

Is my relational expression accurate? There are many ways to express it but I tried to find the way with the best efficient execution plan. (Equivalence of expressions.)

I saw a Tuple Relational Calculus formula, and it contained the symbol ``. What does it mean?

I have the following table transactions I need to calculate the percentage of growth for total transaction per day with past same day last week A simple way for my transactions table structure

--------------------------------------
id | value | category_id | date
--------------------------------------
1  | 150   | 1           | 1632769189

Categories table structure

------------------------
id | name | type 
------------------------
1  | money transfer | 1

My current query is now getting this week ( last 7 days ) values and last week but that's not what I need, I need to get only the current week and extra column that will have the growth percentage

WITH week(date) AS (
  SELECT date('now', '-6 day') 
  UNION ALL 
  SELECT date(date, '+1 day') 
  FROM week 
  WHERE date < date('now') 
), lastWeek(date) AS (
  SELECT date('now', '-13 day') 
  UNION ALL 
  SELECT date(date, '+1 day') 
  FROM lastWeek 
  WHERE date < date('now', '-7 day') 
)
SELECT TOTAL(t.value) total, strftime('%d/%m', w.date) date, 'currentWeek' duration
FROM week w 
LEFT JOIN (transactions t INNER JOIN categories c ON t.category_id = c.id)
ON date(t.date, 'unixepoch') = w.date AND t.wallet_id = ? AND c.type = 1
GROUP BY w.date
UNION ALL
SELECT TOTAL(t.value) total, strftime('%d/%m', lw.date) date, 'lastWeek' duration
FROM lastWeek lw
LEFT JOIN (transactions t INNER JOIN categories c ON t.category_id = c.id)
ON date(t.date, 'unixepoch') = lw.date AND t.wallet_id = ? AND c.type = 1
GROUP BY lw.date

> 2. [5 Marks] Let R(A,B,C,D,E) be decomposed into relations with the following three set of attributes {A,B,C}, {B,C,D}, and {A,C,E}. For each of the following sets of FD's, use the chase test to tell whether the decomposition of R is lossless. For those that are not lossless, give an example of an instance of R that returns more than R when projected onto the decomposed relation and rejoined. > > a. A→D, CD→E and E→D. > > b. A→D, D→E and C→D. For the question above, my work for each of the questions is below the concerns. Here are my main concerns: 1. Does the order in which you use the relations matter? 2. Can you end up with less tuples with the chase test? 3. Is my approach correct? -----------------------Part A below---------------------------------- InitialTableau = T₁ ⋈ T₂ ⋈ T₂

+----+----+----+----+----+
| A  | B  | C  | D₁ | E₁ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₂ | E  |
+----+----+----+----+----+

-----------------------ANSWER TO QUESTIONS START NOW----------------------- a) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.

+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₁ | E  |
+----+----+----+----+----+

- Since we do not have an unsubscribed row, this relation is lossy/not lossless. Example of an instance R (Were going to use the final tableau): R₁(A,B,C)

+----+----+----+
| A  | B  | C  |
+----+----+----+
| A₂ | B  | C  |
+----+----+----+
| A  | B₂ | C  |
+----+----+----+

R₂(B,C,D)

+----+----+----+
| B  | C  | D₁ |
+----+----+----+
| B  | C  | D  |
+----+----+----+
| B₂ | C  | D₁ |
+----+----+----+

R₃(A,C,E)

+----+----+----+
| A  | C  | E₂ |
+----+----+----+
| A₂ | C  | E₂ |
+----+----+----+
| A  | C  | E  |
+----+----+----+

After Joining the above relations, we get:

+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D₁ | E₂ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

- **since we have 2 more rows than the original tableau, this decomposition is not lossless.** b) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.

+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E  |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

- Since we have an unsubscribed row, this decomposition is lossless.

I have designed a schema and generated a few SQL queries. I'm using PostgreSQL. For example: CREATE TABLE train ( train_code SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE journey ( journey_id SERIAL PRIMARY KEY, int INTEGER, train_code REFERENCES train(train_code) ); CREATE TABLE price ( journey_id REFERENCES journey(journey_id), price INTEGER ); Give me all train names that commence from train_code NYC or SFO that are priced $50 or more. (assume the price table is in $ already). SELECT train.name JOIN train JOIN journey on train.train_code = journey.train_code JOIN price on price.journey_id = journey.journey_id WHERE price.price >= 50 AND journey.train_code IN ('NYC', 'SFO') AND journey.int = 1 -- (first train of the journey) I am unsure how to convert this into a relational algebra and/or calculus query. I have looked at a few converters e.g. http://dbis-uibk.github.io/relax/calc.htm but in this calculator 'join' for example and 'in' is not allowed.

How can I calculate the cost of the query, if there is a between condition? Select * From A Where A.id between 10 and 50 If Index not exists and there is only one condition: 1. Search in B+ tree to find the first block that meets the condition (Binary search). **Cost**: log_2(b_A). 2. Search in the following blocks that meets the condition. (Suppose half of the blocks meet the condition). **Cost**: b_A/2. - If the condition was Where A.id > 10, then the cost would be: log_2(b_A) + b_A/2 - If the condition was Where A.id < 50, then the cost would be: log_2(b_A) + b_A/2 What is the correct way to calculate cost of 2 ranges of the same field?

Hi I am new to databases and relational algebra. I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same keys but different value. e.g. I want to keep only [1, 5] and [4, 9] but remove everything else. Key | Value -------|------- 1 | 5 2 | 6 2 | 7 2 | 8 4 | 9 Thanks.

I am working on the following exercise: > Using tuple relational calculus, find names of persons who own at least one house in each city in Canada. The relevant relation schemas are: - City (**city-name**, country-name, area, population) - House (**hno**,#rooms, stno, owner-name) - Street (**stno**, city-name, length) **Note**: Bolded are keys. This is what I came up with: { h.owner-name | House(h) and forAll c (City(c) and c.name="Canada") -> (Exists s(Street(s) and s.city-name = c.name) and Exists x(House(x) and x.stno = s.stno and x.owner-name = h.owner-name) ) } How I read this aloud: - "For all *Canadian* cities, there exists a street with a HOUSE *x* that has the same owner as the HOUSE *h*." Is this correct? I've peen pulling my hair out trying to understand tuple relational calculus.

In the book Database System Concepts 6th Edition, Chapter 2 (Relational Algebra), it states that there are three formal query languages, the relational algebra, the tuple relational calculus and the domain relational calculus, **which are declarative query languages** based on mathematical logic. But then, on another page it states that **relational algebra is procedural**, whereas the **tuple relational calculus and domain relational calculus are non-procedural**. Which of these is the right statement? Could you provide simple examples illustrating the difference?

I came upon a question like, Given the following relational schemas

Student (studId, name, age, sex, deptNo, advisor)

Department (deptId, DName, hod, phoneNo)

Which of the following will be the tuple relational calculus query to obtain the *department names that **do not** have any girl students*? The correct answer given is {d.Dname | department(d) ∧ ~ ((∃(s)) student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)} My interpretation to it is the for every department, the inner logic is trying to find at least one student who is from the same department and is a female. Now if it fails to find one, it return FALSE and the negation will make it TRUE and thus that Dname will be printed. This is perfectly fine. But if I negate the logic, i.e flow the negate sign then the inner logic becomes, ((∀s ∈ student) s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId) How does this mean the same as above? I cannot imagine it. Any help is appreciated. Thanks in advance.

I was reading my notes, and I came across this: > Another example would be to find the names of the lecturers teaching all modules. We might reach for the following incorrect expression: > **{ | ∃M ∀C ∀MN ∀Cr (lecturer(M, N) ∧ module(C, MN, Cr) ∧ teach(M, C))}** Instead, the following is more accurate: > **{ | ∃M ∀C ∀MN ∀Cr (lecturer(M, N) ∧ (module(C, MN, Cr) → teach(M, C)))}** The first one states that as long as there exists a lecturer who has taught a module that exists in the database, the name will be returned (which is not what we want). Or at least that is how I interpreted this. Is there any reason why the first expression is incorrect and (more importantly) the second one is correct?

Suppose I have a table of students, containing their ID and their grade: ----------------- | id | grade | ----------------- | 1 | 83 | | 2 | 94 | | 3 | 92 | | 4 | 78 | How do I write a Tuple Relational Calculus formula that refers to the student with the highest grade? My attempt: Thinking in terms of SQL, I would write a query that does a cartesian product of the table with itself, take every grade that is less than some other grade, and then subtract from the original table. However, in Tuple Relational Calculus, it's not possible to build sub-tables in sub-queries, which is why I'm stuck. However, I've attempted something in this direction: { | Ǝ grade1 ∈ students (id, grade) ⋀ Ǝ grade2 ∈ students (id, grade2) ⋀ grade1 > grade2} I believe this would get me the lower grades, but how do I subtract this all from the original table of students? I'm not allowed to insert this statement into another TRC query. Thanks in advance for your help!

I have these two "tables": Station (station_id, city) // Stations details Rainfall (station_id, date, amount) // Rainfall measurements With **Tuple Relational Calculus** (TRC), I need to get the IDs of all stations that have related measurements on every date in which *any* measurement was taken. So with the following tables I'm supposed to select 5 because this station has measurements on every date that has a measurement. Station Rainfall ------- -------- --station_id-----city-- --station_id-----date-----amount-- 5 LA 5 01/01 4 7 NY 7 02/02 8 5 02/02 3 I've tried this but I'm not quite sure if this would work if there are no measurements at all (in this case I'm supposed to select **all** station IDs): { t | ∃s ∈ Station (t[station_id] = s[station_id] ∧ ∀r ∈ Rainfall (∃q ∈ Rainfall (r[date] = q[date] ∧ s[station_id] = q[station_id])) ) } I need your help in understanding if what I did is correct.

Good evening! I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately): I'm at the point of creating the relational sketch of it which I created this way: Yet, it creates some issues when trying to ask to the database *which are the clients that never ordered product number one?* Indeed, I don't know how to do it in SQL as far as it seems that there is no relations between **Client**, **Commande** and **Produit**. It gived the following sql code: CREATE TABLE Client ( IDClient INT NOT NULL, AdresseClient VARCHAR(255)NOT NULL , NomContact VARCHAR(255)NOT NULL, NumeroSIRET VARCHAR(14) NOT NULL, CONSTRAINT cclient PRIMARY KEY (IDClient) ); CREATE TABLE Produit ( IDProduit INT NOT NULL , PrixVente INT NOT NULL , QuantiteEnStock INT NOT NULL , CONSTRAiNT cproduit PRIMARY KEY (IDProduit) ) ; CREATE TABLE Commande ( IDNumeroCde INT NOT NULL , Date DATE NOT NULL , CONSTRAINT ccommande PRIMARY KEY (IDNumeroCde) ); In relational algebra and calculus it gives: The fact that I can't explain it in relational calculus let me think that I've done a mistake.

Edit with the new tables

I read all the links you provided and therfore add some comments and changes on my tables, can you tell me if I'm right on my assumptions? **But the last ones have the same primary key then. is it a problem?**

In my Database Design course we are learning both Relational Algebra and Relational Calculus. I can see where Relational Algebra could be useful since it is closely tied to SQL. Our professor said that Relational Calculus was used as an alternative to SQL in some RDMBS, most of which are not around any more. Is there still a practical use for Relational Calculus, or is much of this theoretical?

Let be the following simplified database sketch: - R1(P,S),R2(S,T) and R3(T,S) with P= people S = Sport and T= Sports Center How to find in relational algebra and relational calculus which couples such that each one has at least a sport that the other do and and another that the other do not. I tried: Πp1,p2(σs1!=s2, p1!=p2(R1))P→P1,P→P2, S→S1, S→S2 But I know this is wrong, It doesn't express the second condition at least and I'm not sure on how to express variables renaming. **Can you help me express such inclusive and exclusive relations?**

Well, here's a SQL query: select Employee.Name,Department.Name from Employee,Department where Employee.Dept_no=Department.No I have tried a tuple relational calculus on it as: {t|Ǝ s ∈ Employee (s[Name]=t[Name] ^ Ǝ u ∈ Department(u[Name]=s[Name]^u[No]=s[No]))} Is my approach correct? if not, then can anyone help me understand?

I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus. Vote: #PollID, #ElectorID → VoteID From the following relational scheme: - Candidate(**CandidateID**, Name, PoliticalGroup) - Poll(**PollID**, Date, Round) - Elector(**ElectorID**, Name, #PollingStationID) - Vote(**VoteID**,#PollID, #CandidateID, #ElectorID) ## My attempt ### 1. In relational calculus ### 2. In SQL CREATE ASSERTION Vote BEFORE COMMIT t.voteID FROM Vote t CHECK u.CandidateID FROM Candidate u WHERE EXISTS v.PollID FROM Poll v WHERE u.ElectorID=t.ElectorID AND t.PollID=v.PollID

Given the following schema: - Staff (**sid**, sname) - Project (**pid**, pdesc, pfrom, pto) - Asset (**aid**, acat, adesc) - Workfor (**sid, pid**, wfrom, wto) - Assignment (**aid, sid, pid**, afrom, ato) Note: The **bold** attributes are the keys. Find the names of the staffs who worked or are working for more than one project, in Tuple Relational Calculus. I was thinking of using count() but it is not allowed in TRC (or according to my Prof). Any answers appreciated.

Suppose this state for the relation schema Company I'm stuck trying to understand **how**, **why** and **when** I should use Quantifiers (Existencial and Universal) **Examples:** Here I understand how the existencial is working, all the formula bounded to him would evaluate to TRUE, if at least one of all the possible tuples that d can take evaluates to TRUE (please tell me if this reasoning is okay) Here is where things get messy, I don't understand what's actually doing that Universal Quantifier in NOT(PROJECT(x)), and why is it using x as a tuple in PROJECT in this part NOT(x.Dnum = 5), how we know that x is in PROJECT ? Is something about the first part with the Universal Quantifier? And why the Universal Quantifier is just bounding NOT(PROJECT(x))?