Schema definition Example As shown in i members is the schem

Schema definition Example: As shown in (i), \"members\" is the schema (table) name and within parenthesis \'()\', the column definitions are listed for each schema. Each column (field) definition consists of column name and datatype (size). As for example \"mem_email_id\" is a column name and varchar (255) is the datatype and size for this column. Please note Primary key field is highlighted in red color. After every table schema definition, the description of each field (column) has been provided in a separate table.

(i) members (mem_email_id varchar (255), mname varchar(45),gender char(1),

birthdate date , hometown varchar(255), current city varchar (255))

Field name

Description – Constraint

Member\'s Email ID – PK

Member\'s name

Member\'s birth date

Member\'s Home Town

current City

Member\'s Current City

\'PK\' means Primary Key and \'FK\' means foreign key

(ii) location_stayed (mem_email_id varchar (255), stayed_at_city varchar (255), fromdate date, foreign key (mem_email_id) references members (mem_email_id));

Following table shows the Meaning of fields (columns) of \'location stayed\' table schema

Field name

Description – Constraint

Member\'s Email ID – PK (also FK)

Name of City, where member had stayed in the past. - PK

Date since which member is staying in this city

(iii) friends (mem_id varchar (255), friend_id varchar (255), since date, foreign key (mem_id) references members(mem_email_id) , foreign key (friend_id) references members(mem_email_id))

Following table shows the meaning of fields (columns) of \'friends\' table schema

Field name

Description – Constraint

Member\'s Email ID – PK (also FK)

Email id of member\'s friend ( who is also a member) – PK (also FK)

Date since which member (mem_email_id) is in a friendship with other member (friend_id)

(iv) published_posts (post_id int (11), mem_email_id varchar (255), postdesc longtext, postdate date, privacy varchar (15), foreign key (mem_email_id) references members (mem_email_id))

Following table shows the meaning of fields (columns) of \'published_posts\' table schema

Field name

Description – Constraint

unique ID of member\'s post– PK

Id of member, who has published the post (owner of the post) (FK)

Description of the post

Date of publishing this post

Privacy setting of this post (private/public/friends)

(v) tagged_friends (post_id int(11), tagged_friend_id varchar(255) , foreign key (post_id) references published_posts(post_id) , foreign key (mem_email_id) references members(mem_email_id))

Following table shows the Meaning of fields (columns) of \'tagged_friends\' table schema

Field name

Description – Constraint

unique ID of member\'s post for which other members are tagged– PK (also FK)

Id of a friend, who has been tagged for this post - PK (also FK)

A) Considering the above schema, write the Queries for following points 1 to 7 in Relational Algebra.

B) Write the SQL statements for the following queries listed in point 1 to 8.

Queries:

Please note

All date fields have data saved in YYYY-MM-DD\' format

All data in the tables is imaginary.

1) Select the name, hometown and current city of members, who do not have any friends.

2) Select the member name, post description and post publishing date columns for all the published posts of male members, who are born after \'1981-1-1\' (date is in YYYY-MM-DD format)

3) Select all the members, who are tagged in public posts.

4) Select all the members\' name and their locations, where they have stayed after 13^th august 2014.

5) Select all distinct members who have published at least one post or who is at least tagged in one post. (Use a set operator)

6) Select the mname, postdesc and postdate columns of all female members, who belong to hometown of \"bocaraton\" and display the names in the alphabetical order.

7) Select the members, who had published at least one post but has never tagged any friends in a post. (To write the query in relational algebra use a set operator)

8) Select the names of \'Nikita Henderson\' friends, whose name starts with \'N\'

Field name	Description – Constraint
mem_email_id	Member\'s Email ID – PK
mname	Member\'s name
birthdate	Member\'s birth date
hometown	Member\'s Home Town
current City	Member\'s Current City

Solution

2. SELECT m.manme, p.postdesc, p.postdate from members m, published_post p where gender = \"MALE\" and p.postdate = \'1981-1-1\'

DescriptionWhere in this I am using alias name for the tables involved and these are connected together by refrence (mem_email_id in published post table).

3   SELECT mname from members m where m.mem_emailid IN (SELECT mem_email_id from published_post where privacy =\"public\")

4. SELECT m.mname, l.stayde_at_city from memebers m, location_stayed l where l.from_date > to_date(\'2014-08-13\',\'YYYY-MM-DD\')

5. select distinct mnames from members UNION (select p.mem_id from published_post p, tagged_friend t where t.post_id = 0)

6. select m.mname, p.postdesc, p.postdate from memeber m, published_post p where m.gender = \'FEMALE\' and m.hometown = \'bocaraton\' order by m.mname ASC