Question

5. Consider database system with two tables at different two sites as follow: Site 1 Student Table Student-ID Student -NAME Address 200 Records, each record 50 bytes Student-ID = 10 bytes, Student -NAME 20 bytes, Address 20 bytes Site 2 Course Table Course - ID Course -Name Student-ID 40 records, each record 50 bytes Course -ID 20 bytes, Course -NAME 30 bytes Consider the queryQ For each Student, retrieve student-NAME and Course -Name for all students who study courses Suppose the result is required at site 3 Requirements: 1. Write the relational algebra for this query 2. Find the alternative search strategies for processing the query 3. Find the cost for each strategy

Answer 1

First create the table student syntax is

create table tablename(attribute1,datatype1

attribute2,datatype2.........

attributen,datatypen);

Create table student(Student-ID varchar(10),Student-NAME varchar(20),Address varchar(20));

Table is created ,now insert values into the student table

As an example i will show

Insert syntax:

insert into table tablename values(value1,value2,value3....valuen);

insert into table student values('501','harsha','hyd');

insert into table student values('502','jaanu','mbnr');

Insert 200records in this way!

Now create table course

Create table course(Course-ID varchar(20),Course-NAME varchar(30),Student-ID varchar(10));

Course table is created now insert required number of values into the table

insert into table course values('101','CSE','501');

In this way we have to insert 40records and in student table 200records

Now solution for query:

Select Student-NAME,Course-NAME

from student ,course

Where student.Student-ID=course .Student-ID;

We will get the result.

Alternative query is

Select Student-NAME,Course-NAME from student s ,course c where s.Student-ID=c.Student-ID;

By renaming operation we can minimise the query

Cost of stratergy: both gives the result appropriate..in order to have less number of lines we can use rename operation and follow that query..

@ I find the nanna Relational Algebra: T Student-NAME, course -NAME(Studentu course

Writing queries in realtion algebra are some what tough ,you need to understand everything and then it will be easy