This part is to get you familiar with expressing queries in Relational Algebra and to further familiarize you with the tables used to implement the telephone switching database from assignment #2. Relational Algebra provides a good model of how SQL finds data. It is particularly good for modelling performance and query optimization. SQL basically compiles into relational algebra and the theorems of relational algebra are the basis for SQL query optimization. The following proposed set of tables corresponds to the telephone switching database from Assignment 2. This database allows for subscribers (customers) to subscribe to services like "call forwarding", "call waiting" etc. If necessary review the exercise from Assignment 2 and some of the suggested ER model answers provided. Study the proposed tables carefully and then do the questions. CREATE TABLE facilities( portid integer NOT NULL primary key, facility_type varchar(10) --LINE, TRUNK, TREATMENT ); CREATE TABLE lines ( portid integer NOT NULL primary key, areacode varchar(3), --subscriber area code officecode varchar(3), --subscriber office code stationcode varchar(4), --subscriber station code state varchar(4) NOT NULL, --line state BUSY, IDLE foreign key (portid) references facilities(portid) on delete cascade ); CREATE TABLE trunks ( portid integer NOT NULL primary key, foreign_switch varchar(15), --name of switch trunk connects to foreign key (portid) references facilities(portid) on delete cascade ); CREATE TABLE treatments ( tcode varchar(3), --treatement code portid integer NOT NULL primary key, treatment_name varchar(20), --name of treatment foreign key (portid) references facilities(portid) on delete cascade ); CREATE TABLE services ( scode varchar(3) NOT NULL primary key, --service code service varchar(25) --name of service ); CREATE TABLE service_subscribers( line integer NOT NULL references facilities(portid), service varchar(3) NOT NULL references services(scode), primary key(line, service) ); CREATE TABLE subscribers ( portid integer NOT NULL, name varchar(30) NOT NULL, address varchar(50), primary key (portid, name), foreign key (portid) references facilities(portid) on delete cascade ); CREATE TABLE trunk_channels ( portid integer NOT NULL, channel integer NOT NULL, state char(4) default 'IDLE', primary key(portid, channel), foreign key (portid) references trunks(portid) on delete cascade ); CREATE TABLE calls ( call_id integer NOT NULL primary key, orig integer, --originator portid term integer, --terminator portid och integer, --originator channel tch integer, --terminator channel area varchar(3), --dialed number area code office varchar(3), --dialed number offce code stn varchar(4), --dialed number station code foreign key (orig) references facilities(portid) on delete cascade, foreign key (term) references facilities(portid) on delete cascade ); CREATE TABLE trunk_routes ( portid integer NOT NULL,area varchar(3) NOT NULL, office varchar(3) NOT NULL, primary key (portid, area, office), foreign key (portid) references trunks(portid) on delete cascade ); //Sample data for trunks route table PORTID AREA OFFICE 100 416 000 100 416 333 100 416 334 100 905 000 100 613 830 100 613 831 100 613 833 124 416 333 124 416 334 124 416 331 124 416 000 148 819 229 148 819 227 148 819 223 148 514 000 148 405 000 172 514 000 172 405 000 196 416 000 196 905 000 196 000 000 The trunk_routes table is interpreted as follows. The preferred trunks to use in a call are those for which the routing area and routing office code match those of the dialed digits. Otherwise trunks that match the routing area code and have 000 for the routing office code can be used. If there are no matches but a valid phone number was dialed a trunk serving routing area=000 and routing office=000 can be used. This would be the "last resort" trunk. Relational Algebra Notation (for the assignment) For each of the following situations provide the required relational algerbra to produce the required answer relation (table). You may use temporary tables (intermediate variables) if you want. The questions are in no particular order, some are probably quite easy, others are probably more tricky. Use the following notation for your algebra: p[A,B,C](table) //project columns A,B,C p[A(firstName),B(lastName)](table) //project and rename column A to firstName etc. s[boolean condition](table) //select T x S //cartesian product -match each row of T with each row of S T * S //natural join (equality join on column names T and S have in common) T x[T.A = S.B] S //join with boolean condition e.g. column T.A = column S.B T union S //union T ans S must have same column structure T intersect S //intersection T and S must have same column structure T - S //difference T and S must have same column structure count[group columns: column](table) //count non null values avg[group columns: column](table) //average of numeric column min[group columns : column](table) //minimum value of numeric column max[group columns: column](table) //maximum value of numeric column sum[group columns: column](table) //sum of numeric column var = expression //assignment to a variable var[fname,lname] = expression //assignment with column renaming Clarification about what the aggregate functions produce: count[A,B:C](T) where A,B,C are columns of table T will produce a table consisting of columns A,B,COUNT with a row for each distinct combination of values of A,B found in T and the value of COUNT will be the number of rows in the A,B group that do not have a null in column C. e.g. Table T A B C john mary red allan fred blue john mary blue john mary pink allan fred pink COUNT[A,B:C](T) ------------------------ |A |B |COUNT | ------------------------ |john |mary |3 | |allan |fred |2 | Clarification when no grouping criteria is provided: count[:C](T) where A,B,C are columns of table T will produce a table consisting of columns COUNT with a single row and COUNT will be the number of rows in T that do not have a null in column C. e.g. Table T A B C john mary red allan fred blue john mary blue john mary pink allan fred pink COUNT[:C](T) --------- | COUNT | --------- | 5 | e.g of Max(). Table T A B C john mary 4 allan fred 6 john mary 9 john mary 3 allan fred 7 max[A,B:C](T) ------------------------ |A |B |MAX | ------------------------ |john |mary |9 | |allan |fred |7 | e.g of Max(). Table T A B C john mary 4 allan fred 6 john mary 9 john mary 3 allan fred 7 max[:C](T) ------- |MAX | ------- |9 | Markers Note: If they do a natural join, the column names MUST be the same. For set operations like union and intersection the column names need not be the same but must be of the same type (i.e. it must be possible to stack the tables on top of each other and have the columns align.) There are many ways to do the algebra for each question below. Give 2 marks for any totally correct query, 1 for algebra that has problems but appear to be on the right track and 0 for algebra that is illegal or does not solve the problem. Note they must be using correct relational algebra, they cannot make up new operations that don't exist in relation algebra. They cannot do things like compare numbers to tables. Relational algebra simply produces tables from other tables.

Problem 1.1 List all the names of subscribers and their directory numbers (the area, office, station code of their lines).

Problem 1.2 List the names, email address and portid's of all subscribers who subscribe to the service named "call forward". The call forward service has scode 'CFD' [Rev 3 Note in the actual sqlite example telephone switch there is a service called "Call Forward Busy" which has a service code of 'CFB'. You can use that instead if you want, however the above original question is still valid; it is just that the datafill of the actual database does not currently have an instance of that service.]

Problem 1.3 List the portID's of all the trunks that have no idle channel. (That is, all their channels are in 'busy' state and none are in 'idle' state.)

Problem 1.4 List the call id's for all calls that both originate and terminate in a foreign switch. That is, whose originator and terminator are both trunks (as opposed to lines or treatments).

Problem 1.5 Find the portID of all lines who are the originator of a call and who are currently getting the busy treatment (tcode = 'BSY').

Problem 1.6 List the portid's of the trunks that currently have at least one idle channel.

Problem 1.7 Find the name and address of subcribers who subscribe to the most services.

Problem 1.8 Suppose the originator of a call dialed the number (905) 238-1243 which is a number not located on the callers switch (so it's a call requiring a trunk). Find all the trunks that can be used to route this call.

Problem 1.9 Produce a table that lists the names of all the services and the number of lines that subscribe to that service.

Problem 1.10 List the portID's of all lines that subscribe to some services but not all of the available services.