CS631 Fall 2021​Assignment 3 – Relational Algebra​Due: 11/22/2021 Submission Requirements: 1. This assignment is due Monday November 22, 2021 at 6pm. The late penalty is 10 points per day.

CS631 Fall  2021​Assignment 3 – Relational Algebra​Due: 11/22/2021

Submission Requirements:

1. This assignment is due Monday November 22, 2021 at 6pm. The late penalty is 10 points per day.2. It must be submitted via Canvas.3. Submit one document only, unzipped.a. Handwritten problems will not be accepted unless permission is granted by ME.​4. 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.

Guidelines:

1. All solutions are expected to be written in Relational Algebra. Do not even mention SQL in your answer.  It has no value for this assignment and will be ignored.2. The format for the relational algebra functions is attached on the final page. USE ONLY THESE FUNCTIONS PLUS THE CROSS FUNCTION AND THE AGGREGATE FUNCTIONS INCLUDED IN THE TEXT.3. Unless specifically stated, DO NOT ASSUME that the table cannot increase in size. 4. Problem outputs (when requested) can be based on the snapshot of the table presented in the problem

List all assumptions that you feel are necessary, but do not undo any assumptions stated in the problem. As usual, all assumptions must make sense and not significantly change thee difficulty of the problem.

Problems 1 – 3

The table shown below is a snapshot of a larger table containing the great philosophers of our time who are also members of the Brilliant Society and is to be used to create relational algebra queries for problems 1- 3. Assume you are looking at a representative snapshot of the table which has many more records but only the attributes shown. Also, you can assume an individual will only be listed once per area.

Schema:

PID = philosopher identifier (Primary Key)

FName = philosopher’s first name

LName = philospher’s last name

AEX= Philosopher’s area of expertise (multiple multiple areas are possible)

JailTime = year’s spent in jail for each philosopher (integer)

Philisopher(PK = PID)

PID

FName

LName

AEX

JailTime

119

Bob

Dylan

Music

18

120

Jim

Morrison

Music

0

121

Lenny

Bruce

Comedy

7

122

Jane

Fonda

Movies

15

123

Miriam

Maisel

Comedy

1

124

Bart

Simpson

Cartoons

12

125

Marlon

Brando

Movies

45

126

Donald

Trump

Politics

3

127

Kanye

West

Music

0

122

Jane

Fonda

Television

15

Problem 1 (15 points):

The Brilliant Society has just created a rule that will eject any member who has spent 15 or more years in jail. In addition, musicians ejected from the society will be given an electric guitar autographed by Bruce Springsteen. List those characters that will receive the autographed electric guitar. The column headings should be the items exactly as shown in Bold Face. PhilID(=PID), FirstName (=FName), LastName (=LName), Expertise (=AEX), JailTime.

You can base your output (but not your relational algebra code)on the current snapshot of the table. You can assume an individual will only be listed once per area.

Problem 2 (15 points):

Compute the count and average jail time for each area of expertise. Your code should NOT assume that the only areas of expertise are the ones shown. Show the results based on the snapshot. Assume zero decimal places for the average jail time. Remember to show column headings.

Problem 3 (15 Points)

Let’s define visual entertainment as movies and television. Write a relational algebra query to display all of the characters who areinvolved in both areas of visual entertainment. Show the results of the query based on the snapshot. You can assume the definition of visual entertainment will never change. Use the headings listed as column headings.

FirstName

LastName

Visual1

Visual2

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. You can assume the definition of visual entertainment will never change.3. Base your output on the snapshot provided.4. The output must show both visual entertainment titles in the same row. You can assume that an individual will appear at-most twice in the table. 5. DO NOT USE A JOIN ANYWHERE IN THIS PROBLEM

For Problem 4, Problem 5, Problem 6, and Problem 7 use the table provided below. Assume that the table MAY change in the future.

Problems 4, 6 and 7 do NOT have the same solution.

Table = PlayerPos

PlayerID

PFirst

Plast

Position

3713

Michael

Conforto

RF

4806

Francisco

Lindor

SS

5620

Daryl

Strawberry

RF

7028

Roger

Maris

RF

7286

Mike

Piazza

C

8128

Giancarlo

Stanton

LF

8128

Giancarlo

Stanton

RF

0267

Jeff

McNeil

2B

3714

Brandon

Nimmo

LF

Problem 4 (15 points):

List the indviduals who have played ANY position that Giancarlo Stanton has played. Display PlayerID, Pfirst,Plast, Position for that individual. Do not show Giancarlo in the output. Show the results based on the snapshot of the table. Do not assume that Giancarlowill not play other positions in the future. Your code must reflect this. Assume that the table may change in the future.

Problem 5 (10 points)

The following relational algebra problem was given to SpongeBob:

List the PlayerIDs of those players who have played All of the positions that Giancarlo Stanton has played. Do not assume the table will not increase in size.Display PlayerID. Show the actual results based on the snapshot of the table that was given.

Note that this is different than Problem 4 in the following way. Problem 4 basically requires that the programmer display everyone who has played anyPosition that Giancarlo has played. This means display any player that has played RF or LF or others that Giancarlo plays in the future. In Problem 5, SpongeBob is required to display any player that has played all of the positions that Giancarlo has played, which means he must have both RF and LF.

SpongeBob submitted the following relational algebra code. He has made at least two mistakes. What are they? Explain in detail. Note: You do not have to rewrite the solution. Just explain why SpongeBob’s solution will not solve the problem as stated.

Aß (Select <Position=’RF’ AND Position=’LF’>(PlayerPos))

Project A

Problem 6 (10 points)

Write a relational algebra expression to solve Problem 5. You MUST use the division function. Do not assume the table will not increase in size.

Problem 7 (10 points)

Write a relational algebra expression to solve Problem 5. You MAY NOT use the division function. You MAY assume the table will not increase in size.