Assignment-2

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 If you do not read the two sections below, you will probably be giving away points. Guidelines: 1. All problems must be solved using Relational Algebra unless told differently. Do not submit any SQL code. It will be ignored. 2. Do not assume files/tables will not change in size in the future unless stated so. 3. Please state any assumptions you are making. Assumptions are required to be “real- world”, reasonable, and accurate. 4. The only permissible Relational algebra syntax is that shown in the textbook. a. The format for the relational algebra functions is attached on the final page. USE ONLY THESE FUNCTIONS PLUS THE AGGREGATE FUNCTIONS INCLUDED IN THE TEXT. 5. Problem outputs (when requested) can be based on the snapshot of the table presented in the problem unless stated differently. 6. Certainly, if you have questions, you are welcome to call or text me (908-418-6078) or send an email (bforman13@gmail.com). Submission Requirements: 1. This assignment is due Wednesday November 22, 2023, at Midnight. 2. The assignment must be submitted via Canvas. a. Submit one document only, unzipped. b. It must be readable, and it is your responsibility to confirm this. If I cannot read it, you run the risk of getting a zero. c. Handwritten problems will not be accepted unless permission is granted by ME. 3. All work must be your own. The only person you may discuss the assignment with is me (Professor Forman) a. You may NOT discuss problems with any other student. b. You may NOT get answers from sites such as Chegg or Homework Hero or any other online site. c. Anything not mentioned, that constitutes “ not doing your own work” will be considered cheating. d. Violation of these requirements will result in a grade of 0. Problems 1 - 4 1

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 Using the snapshot of the Yankees Team table, shown below, create relational algebra programs for problems 1- 4. Assume you are looking at a representative snapshot of the table which has MANY more records. Schema: YankeesID = identifier of Yankees player FirstName = first name of player FamilyName = family name of player Position = Position played Age = Age of player Table = YankeesTeam(PK = YankeesID, Position) YankeesID FirstName FamilyName Position Age 1 Martin Billy 2B 93 2 Derek Jeter SS 44 3 Babe Ruth LF 120 4 Lou Gehrig 1B 115 5 Joe DiMaggio CF 105 6 Clete Boyer 3B 83 7 Mickey Mantle CF 92 8 Yogi Berra C 100 9 Roger Maris RF 81 7 Mickey Mantle 1B 92 Problem 1 (10 points) List only the YankeesID, FirstName, and FamilyName for those Yankees who are older than 100. Show the output as a table based on the snapshot of the YankeesTeam table shown above. Show your code plus the output. Ans. We want the YankeesID, firstname, and family name of members who are part of Yankees Team and are older than 100. So, we will use selection operator to list those with an age of more than 100, and we will make use of the projection operator to print out the YankeesID, firstname, and family name. So, the relational algebra will be :  (YankeesID, FirstName, FamilyName) (  ((Age) >100) (YankeesTeam)) Output Table : YankeesID FirstName FamilyName 3 Babe Ruth 4 Lou Gehrig 5 Joe DiMaggio Problem 2 (10 points): 2

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 C 1 100 CF 1 92 1B 1 92 RF 1 81 Problem 4 (15 Points) Only certain players are eligible for the Yankees Annual World Series Bash. These players must have a position of CF plus one other position. Write a relational algebra query to display all the players who are eligible for the Bash. Show the results of the query in tabular form based on the data from the snapshot shown above. Use the headings listed below for the table format. Sample output format: FirstN LastN Pos1 Pos2 Xxxx Yyyyy ZZ VV Write your code so that it is not limited to what is shown in the table snapshot: 1. Do not assume that the table is limited to just the records shown. 2. Show code and output. 3. You can assume that the only positions played for the whole table are those listed in the snapshot. You cannot assume that there will be no new players added to the table, but if there are, you can assume they will play one of the positions listed. 4. Base your output on the snapshot provided. 5. The output must show both positions in the same row. You can assume that an individual will appear at most twice in the table. 6. DO NOT USE THE JOIN COMMAND ANYWHERE IN THIS PROBLEM – Using the JOIN command will cost 8 points. Ans. We have a restriction against using joins. We aim to identify players who have the position CF and one additional position. To achieve this, we will start by duplicating the main table, renaming the columns in the first instance as 'Position1' and in the second instance as 'Position2.' Afterward, we will combine these two tables using the union operator. The resulting table will include YankeesID, FirstName, FamilyName, Position1, Position2, and Age. Subsequently, we will apply the selection operator to filter rows where Position1 is CF and Position2 is different from CF. Finally, we will use the projection operator to display FirstName, FamilyName, Position1, and Position2. 4

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23  (FirstN, LastN, Pos1, Pos2) ( (  (Position1=CF)^(Position2  CF)^(Position2 IS NOT NULL) ) (( (  (YankeesID1 = YankeesID2) )ρ (YankeesID1, FirstName1, FamilyName1, Position1, Age1) (YankeesTeam) * ρ (YankeesID2, FirstName2, FamilyName2, Position2, Age2) (YankeesTeam) )) ) OutPut Table : FirstN LastN Pos1 Pos2 Mickey Mantle CF 1B For Problem 5, Problem 6, Problem 7, and Problem 8, use the table provided below. Assume that the table MAY change in the future unless told differently. 5

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 DID Fname Lname ComedyAward 4864 Lucy Ricardo 74 4771 Rachel Greene 74 7279 Phoebe Boufee 74 Problem 6 (10 points) Student Ray Romano was given the following assignment: Write a relational algebra expression to list the DIDs with ALL of the ComedyAwards that the Frasier Crane shown in the table has won. Display DID. Show the actual results based on the snapshot of the table that was given. Note that this is different than Problem 5 in the following way. Problem 5 basically requires that the programmer display any ComedyAward that Frasier Crane has. This means display any DID that has either ComedyAward 47, 74, or others that Frasier picks up in the future. In this problem, Ray is required to display any Comedy characters that have all the ComedyAwards that Frasier has, which means he must have both 47 and 74, plus any other awards that Frasier might receive in the future. Ray submitted the following relational algebra statement. Show the output of this statement. A(CA)  Project <ComedyAward> (Select <ComedyAward=47 AND ComedyAward = 74> (Comedy)) Ans. Now, The answer to the Ray submitted Relational Algebra Expression is : The output relation A(CA) resulting from the provided algebraic expression is an empty set. This outcome 7

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 stems from the logical impossibility of the condition ComedyAward=47 AND ComedyAward=74 being false. This is due to the fact that a single attribute in a tuple (row) represents a single value. Consequently, the resulting selection is an empty set, and the projection follows suit, resulting in an empty set. Problem 7 (10 points)” Write a relational algebra expression to solve the problem Ray Romano was given in Problem 6 which is re-stated directly below. Write a relational algebra expression to list the DIDs with ALL of the ComedyAwards that the Frasier Crane shown in the table has won. Display DID. Show the actual results based on the snapshot of the table that was given. Show your code plus the output. You MUST use the division function. Assume the table will increase in size. Show the output based on the snapshot of the table shown above. Ans. We need to formulate a relational algebra expression to list the DIDs associated with all the ComedyAwards that the Frasier Crane has won, based on the table. We need to display only the DID. To get that, we will first start by using selection operator to retreive details of Frasier Crane. Then, we will use the projection operator to display relevant information about him. And using the division operator, we will ensure that only the DID is printed. Lastly, we will use Projection operator to print the DID of those Comedy characters.  (DID) (  (Fname = Fasier ^ Lname = Crane) ) (Comedy) ÷  (ComedyAward ) (  (Fname = Fasier ^ Lname = Crane) (Comedy) Output Table : DID 9279 8

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 Problem 8 (10 points) Write a relational algebra expression to solve Problem 6. Write a relational algebra expression to list the DIDs with ALL of the ComedyAwards that the Frasier Crane shown in the table has won. Display DID. Show the actual results based on the snapshot of the table that was given. You MAY NOT use the division function. You MAY assume the table will not change in any way. Ans. Assuming that the table doesn't grow, we can directly filter the rows where the first name is Frasier and the last name is Crane, and then project only the DID column.  (DID) (  (Fname = Fasier ^ Lname = Crane) ) (Comedy) Output Table : DID 9279 10

CS631-101 Fall 2023 Assignment 2 - Relational Algebra Due: 11/22/23 11 You may also use the CROSS function and the applicable aggregate functions, which are not listed here.