Using the four tables provided: Farmer(SIN, name, age, sex, vname) Village(name,

Question

Using the four tables provided:

Farmer(SIN, name, age, sex, vname)
Village(name, area, population, province)
Kids(SIN, f_sin, m_sin, s_name)
School(sname, vname, no_classes)

Where: sname corresponds to school name, vname corresponds to village name, and f_sin and m_sin stand for the father's and mother's SIN, respectively.

Code the following queries in relational algebra :

a) Find SIN of farmers who have some kids going to school?
   b) Find each school attended only by kids from the same village where the school is located?
   c) Find the name(s) of the most crowded village(s) (most population)?
   d) Find farmers who have at least one kid in every school registered in the database?
   e) Find name(s) of village(s) with no schools?

Explanation / Answer

a)
Find the fathers who have kids going to school. Then find the mothers who have kids
going to school
R1 SIN(Farmers)
R2 R1 SIN=F_SIN Kids
R3 R1 SIN=M_SIN Kids
R4 R2 R3
R5 SIN(R4)

b)
Assuming all kids go to school – but this can be
handled checking for a null value in the Kids school column
-Also assuming that Kids live in the same village as both their parents, and that each kid has
two parents. With a ton of work this can be worked around as well.
-Using R5 From above
R1 Farmers SIN=M_SIN Kids //using one parent here
R2 R1 SNAME=S_NAME AND R1.VNAME=SCHOOL.VNAME School
R3 R1 SNAME=S_NAME AND R1.VNAME!=SCHOOL.VNAME School
R4 SNAME(R2)
R5 SNAME(R3)
R6 R4 – R5

We have the school name as the final result. If we wanted all the school information, R6 is
joined with School over sname to get it

c)

The method below uses Aggregation. Any reasonable attempt was given credit.
R1(POPULATION) MAXIMUM POPULATION(Village)
R2 R1*Village //Joining over population. If there are ties, then more than one
//is returned.
R3 NAME(R2)

d)

Find the SIN,S_NAME pairs for parents. This gives each school they have a kid
registered in. Divide by all schools.
R1 SIN(Farmers)
R2 R1 SIN=F_SIN Kids
R3 R1 SIN=M_SIN Kids
R4 R2 R3
R5 SIN,S_NAME(R4)
R6(S_NAME) SNAME(School)
R7 R5 ÷ R6

e)

Find all villages, then all villages with a school. Subtract
R1(VNAME) NAME(Village)
R2 VNAME(School)
R3 R1 – R2

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.