(d) Descendants by gender: For all persons who have a descendant, compute the number of descendants by gender. The result should have the schema (ancestor, gender, num_descendants). Sort results by ancestor, breaking ties by gender. For example, if Ann has 1 female (gender = ‘F’) and 1 male (gender = ‘M’) child, 2 female grandchildren, and no other descendants, the result should contain 2 tuples for Ann: (‘Ann’, ‘F’, 3) and (‘Ann’, ‘M’, 1). (e) Relatives: Compute all pairs of relatives. Ann and Bob are relatives if Ann is a parent or ancestor of Bob, or if Bob is a parent or an ancestor of Ann, or if they are siblings, or if they have a common ancestor, no matter how far removed. The result should have the schema (person1, person2). Sort results by person1, breaking ties by person2. Return each pair once, such that the name of person1 is lexicographically lower than the name of person2.
drop table Parents_Children;
drop table Persons;
create table Persons (
name varchar(64) primary key,
gender char(1) not null
);
create table Parents_Children (
parent varchar(64),
child varchar(64),
primary key (parent, child),
foreign key (parent) references Persons(name),
foreign key (child) references Persons(name)
);
insert into Persons (name, gender) values ('Ann', 'F');
insert into Persons (name, gender) values ('Bob', 'M');
insert into Persons (name, gender) values ('Cory', 'M');
insert into Persons (name, gender) values ('Dave', 'M');
insert into Persons (name, gender) values ('Emma', 'F');
insert into Persons (name, gender) values ('Fred', 'M');
insert into Persons (name, gender) values ('Gab', 'Q');
insert into Persons (name, gender) values ('Hil', 'F');
insert into Persons (name, gender) values ('Ian', 'M');
insert into Parents_Children (parent, child) values ('Ann', 'Bob');
insert into Parents_Children (parent, child) values ('Ann', 'Cory');
insert into Parents_Children (parent, child) values ('Bob', 'Dave');
insert into Parents_Children (parent, child) values ('Bob', 'Emma');
insert into Parents_Children (parent, child) values ('Cory', 'Fred');
insert into Parents_Children (parent, child) values ('Cory', 'Gab');
insert into Parents_Children (parent, child) values ('Ian', 'Bob');
insert into Parents_Children (parent, child) values ('Hil', 'Gab');
Consider relational schema and instance in the above SQL code. In each question below (d-e), write a single SQL query that computes the required answer. The resulting query should not include any duplicates, but should not use “distinct” unless it's necessary. Each answer will be submitted as its own SQL file (i.e. 1d.sql, and 1e.sql). The following commands will be executed for each question respectively:
psql -h localhost -p 5432 -U abc123 abc123_db < 1d.sql > resd.txt
psql -h localhost -p 5432 -U abc123 abc123_db < 1e.sql > rese.txt
Make sure these commands run and produce outputs in the files resd.txt, and rese.txt respectively.
(d) Descendants by gender: For all persons who have a descendant, compute the number of descendants by gender. The result should have the schema (ancestor, gender, num_descendants). Sort results by ancestor, breaking ties by gender. For example, if Ann has 1 female (gender = ‘F’) and 1 male (gender = ‘M’) child, 2 female grandchildren, and no other descendants, the result should contain 2 tuples for Ann: (‘Ann’, ‘F’, 3) and (‘Ann’, ‘M’, 1).
(e) Relatives: Compute all pairs of relatives. Ann and Bob are relatives if Ann is a parent or ancestor of Bob, or if Bob is a parent or an ancestor of Ann, or if they are siblings, or if they have a common ancestor, no matter how far removed. The result should have the schema (person1, person2). Sort results by person1, breaking ties by person2. Return each pair once, such that the name of person1 is lexicographically lower than the name of person2.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
