\n CS631 Fall \u00a02021\u200bAssignment 3 – Relational Algebra\u200bDue: 11\/22\/2021\n <\/p>\n
\n Submission Requirements:\n <\/p>\n
\n 1.\u00a0This assignment is due\u00a0Monday\u00a0November\u00a022, 2021 at 6pm. The late penalty is 10 points per day.2.\u00a0It must be submitted via Canvas.3.\u00a0Submit one document only, unzipped.a.\u00a0Handwritten problems will not be accepted unless permission is granted by ME.\u200b4.\u00a0All work must be your own. The only person you may discuss the assignment with is\u00a0me (Professor Forman)a.\u00a0You may\u00a0NOT\u00a0discuss problems with any other student.b.\u00a0You may\u00a0NOT\u00a0get answers from sites such as Chegg or Homework Hero or any other online\u00a0site.\n <\/p>\n
\n Guidelines:\n <\/p>\n
\n 1.\u00a0All solutions are expected to be written in Relational Algebra. Do not even mention SQL in your answer. \u00a0It has no value for this assignment and will be ignored.2.\u00a0The format for the relational algebra functions is attached on the final page.\u00a0USE ONLY THESE FUNCTIONS PLUS THE\u00a0CROSS FUNCTION AND THE\u00a0AGGREGATE FUNCTIONS INCLUDED IN THE TEXT.3.\u00a0Unless specifically stated, DO NOT ASSUME that the table cannot increase in size.\u00a04.\u00a0Problem outputs\u00a0(when requested)\u00a0can be based on the snapshot of the table presented in the problem\n <\/p>\n
\n 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.\n <\/p>\n
\n Problems 1 – 3\n <\/p>\n
\n The table\u00a0shown below\u00a0is a snapshot of a larger table containing the great philosophers of our time\u00a0who are also members of the Brilliant Society\u00a0and is to be used to\u00a0create relational algebra\u00a0queries for problems 1- 3.\u00a0Assume you are looking at a representative snapshot of the table which has many more records\u00a0but only the attributes shown.\u00a0Also,\u00a0you can assume an individual will only be listed once per area.\n <\/p>\n
\n Schema:\n <\/p>\n
\n PID = philosopher identifier (Primary Key)\n <\/p>\n
\n FName = philosopher\u2019s first name\n <\/p>\n
\n LName\u00a0=\u00a0philospher\u2019s\u00a0last name\n <\/p>\n
\n AEX= Philosopher\u2019s area of expertise (multiple\u00a0multiple\u00a0areas are possible)\n <\/p>\n
\n JailTime\u00a0= year\u2019s spent in jail for each philosopher (integer)\n <\/p>\n
\n Philisopher(PK =\u00a0PID)\n <\/p>\n
\n PID\n <\/p>\n
\n FName\n <\/p>\n
\n LName\n <\/p>\n
\n AEX\n <\/p>\n
\n JailTime\n <\/p>\n
\n 119\n <\/p>\n
\n Bob\n <\/p>\n
\n Dylan\n <\/p>\n
\n Music\n <\/p>\n
\n 18\n <\/p>\n
\n 120\n <\/p>\n
\n Jim\n <\/p>\n
\n Morrison\n <\/p>\n
\n Music\n <\/p>\n
\n 0\n <\/p>\n
\n 121\n <\/p>\n
\n Lenny\n <\/p>\n
\n Bruce\n <\/p>\n
\n Comedy\n <\/p>\n
\n 7\n <\/p>\n
\n 122\n <\/p>\n
\n Jane\n <\/p>\n
\n Fonda\n <\/p>\n
\n Movies\n <\/p>\n
\n 15\n <\/p>\n
\n 123\n <\/p>\n
\n Miriam\n <\/p>\n
\n Maisel\n <\/p>\n
\n Comedy\n <\/p>\n
\n 1\n <\/p>\n
\n 124\n <\/p>\n
\n Bart\n <\/p>\n
\n Simpson\n <\/p>\n
\n Cartoons\n <\/p>\n
\n 12\n <\/p>\n
\n 125\n <\/p>\n
\n Marlon\n <\/p>\n
\n Brando\n <\/p>\n
\n Movies\n <\/p>\n
\n 45\n <\/p>\n
\n 126\n <\/p>\n
\n Donald\n <\/p>\n
\n Trump\n <\/p>\n
\n Politics\n <\/p>\n
\n 3\n <\/p>\n
\n 127\n <\/p>\n
\n Kanye\n <\/p>\n
\n West\n <\/p>\n
\n Music\n <\/p>\n
\n 0\n <\/p>\n
\n 122\n <\/p>\n
\n Jane\n <\/p>\n
\n Fonda\n <\/p>\n
\n Television\n <\/p>\n
\n 15\n <\/p>\n
\n Problem 1 (15\u00a0points):\n <\/p>\n
\n 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\u00a0ejected from the society will be given\u00a0an electric guitar autographed by Bruce Springsteen.\u00a0List those characters that\u00a0will\u00a0receive the autographed electric guitar. The column headings should be the items\u00a0exactly as\u00a0shown in Bold Face.\u00a0PhilID(=PID),\u00a0FirstName\u00a0(=FName),\u00a0LastName\u00a0(=LName),\u00a0Expertise (=AEX),\u00a0JailTime.\n <\/p>\n
\n You can base your output\u00a0(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.\n <\/p>\n
\n Problem 2 (15\u00a0points):\n <\/p>\n
\n Compute the\u00a0count and\u00a0average\u00a0jail time for each area of expertise. Your code should NOT assume that the only areas of expertise are the ones shown.\u00a0Show the results\u00a0based on the snapshot. Assume\u00a0zero\u00a0decimal places for\u00a0the average jail time. Remember to show column headings.\n <\/p>\n
\n Problem 3 (15 Points)\n <\/p>\n
\n Let\u2019s define visual entertainment as movies and television.\u00a0Write a relational algebra query to display all of the\u00a0characters\u00a0who\u00a0areinvolved in\u00a0both areas of\u00a0visual entertainment.\u00a0Show the results of the query\u00a0based on the snapshot. You can assume the definition of visual entertainment will never change.\u00a0Use the headings listed as column headings.\n <\/p>\n
\n FirstName\n <\/p>\n
\n LastName\n <\/p>\n
\n Visual1\n <\/p>\n
\n Visual2\n <\/p>\n
\n Write your code so that it is not limited to what is shown in the table snapshot:\n <\/p>\n
\n 1.\u00a0Do not assume that the table is limited to just the records shown.2.\u00a0You can assume the definition of visual entertainment will never change.3.\u00a0Base your\u00a0output on the snapshot provided.4.\u00a0The output must show both\u00a0visual entertainment\u00a0titles in the same row. You can assume that an individual will appear at-most\u00a0twice in the table.\u00a05.\u00a0DO NOT USE A JOIN ANYWHERE IN THIS PROBLEM\n <\/p>\n
\n For Problem 4, Problem 5,\u00a0Problem 6,\u00a0and Problem 7\u00a0use the table provided below. Assume that the table\u00a0MAY\u00a0change in the future.\n <\/p>\n
\n Problems 4, 6\u00a0and\u00a07\u00a0do NOT have the same solution.\n <\/p>\n
\n Table =\u00a0PlayerPos\n <\/p>\n
\n PlayerID\n <\/p>\n
\n PFirst\n <\/p>\n
\n Plast\n <\/p>\n
\n Position\n <\/p>\n
\n 3713\n <\/p>\n
\n Michael\n <\/p>\n
\n Conforto\n <\/p>\n
\n RF\n <\/p>\n
\n 4806\n <\/p>\n
\n Francisco\n <\/p>\n
\n Lindor\n <\/p>\n
\n SS\n <\/p>\n
\n 5620\n <\/p>\n
\n Daryl\n <\/p>\n
\n Strawberry\n <\/p>\n
\n RF\n <\/p>\n
\n 7028\n <\/p>\n
\n Roger\n <\/p>\n
\n Maris\n <\/p>\n
\n RF\n <\/p>\n
\n 7286\n <\/p>\n
\n Mike\n <\/p>\n
\n Piazza\n <\/p>\n
\n C\n <\/p>\n
\n 8128\n <\/p>\n
\n Giancarlo\n <\/p>\n
\n Stanton\n <\/p>\n
\n LF\n <\/p>\n
\n 8128\n <\/p>\n
\n Giancarlo\n <\/p>\n
\n Stanton\n <\/p>\n
\n RF\n <\/p>\n
\n 0267\n <\/p>\n
\n Jeff\n <\/p>\n
\n McNeil\n <\/p>\n
\n 2B\n <\/p>\n
\n 3714\n <\/p>\n
\n Brandon\n <\/p>\n
\n Nimmo\n <\/p>\n
\n LF\n <\/p>\n
\n Problem 4 (15 points):\n <\/p>\n
\n List\u00a0the\u00a0indviduals\u00a0who have played\u00a0ANY\u00a0position\u00a0that\u00a0Giancarlo Stanton\u00a0has\u00a0played. Display\u00a0PlayerID,\u00a0Pfirst,Plast,\u00a0Position\u00a0for that individual. Do not show\u00a0Giancarlo\u00a0in the output. Show the results\u00a0based on the snapshot of the table.\u00a0Do not assume that\u00a0Giancarlowill not\u00a0play other positions\u00a0in the future. Your code must reflect this.\u00a0Assume that the table may change in the future.\n <\/p>\n
\n Problem 5 (10\u00a0points)\n <\/p>\n
\n The following relational algebra problem was given to SpongeBob:\n <\/p>\n
\n List the\u00a0PlayerIDs\u00a0of those players who have played All of the positions\u00a0that\u00a0Giancarlo Stanton\u00a0has\u00a0played. Do not\u00a0assume\u00a0the table will not increase in size.Display\u00a0PlayerID. Show the actual results\u00a0based on the snapshot of the table that was given.\n <\/p>\n
\n Note that this is different than Problem 4 in the following way. Problem 4 basically requires that\u00a0the programmer\u00a0display\u00a0everyone who has played\u00a0anyPosition\u00a0that\u00a0Giancarlo\u00a0has\u00a0played. This means display any\u00a0player\u00a0that has\u00a0played\u00a0RF\u00a0or\u00a0LF\u00a0or others that\u00a0Giancarlo\u00a0plays\u00a0in the future.\u00a0In Problem 5,\u00a0SpongeBob\u00a0is\u00a0required to display\u00a0any player\u00a0that has\u00a0played\u00a0all of the\u00a0positions\u00a0that\u00a0Giancarlo\u00a0has\u00a0played, which means he must have both\u00a0RF\u00a0and\u00a0LF.\n <\/p>\n
\n SpongeBob\u00a0submitted the following relational algebra\u00a0code.\u00a0He has made\u00a0at least\u00a0two mistakes. What are they? Explain in detail.\u00a0Note: You do not have to rewrite the solution. Just explain\u00a0why SpongeBob\u2019s solution will not solve the problem as stated.\n <\/p>\n
\n A\u00df\u00a0(Select\u00a0<Position=\u2019RF\u2019 AND Position=\u2019LF\u2019>(PlayerPos))\n <\/p>\n
\n Project A\n <\/p>\n
\n Problem 6 (10\u00a0points)\n <\/p>\n
\n Write a relational algebra expression to solve Problem 5.\u00a0You MUST use the division function. Do not assume the table will not increase in size.\n <\/p>\n
\n Problem\u00a07\u00a0(10\u00a0points)\n <\/p>\n
\n Write a relational algebra expression to solve Problem 5.\u00a0You MAY NOT use the division function.\u00a0You MAY assume the table will not increase in size.\n <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
CS631 Fall \u00a02021\u200bAssignment 3 – Relational Algebra\u200bDue: 11\/22\/2021 Submission Requirements: 1.\u00a0This assignment is due\u00a0Monday\u00a0November\u00a022, 2021 at 6pm. The late penalty is 10 points per day.2.\u00a0It must be submitted via Canvas.3.\u00a0Submit one document only, unzipped.a.\u00a0Handwritten problems will not be accepted unless permission is granted by ME.\u200b4.\u00a0All work must be your own. The only person you may […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_joinchat":[]},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/290732"}],"collection":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/comments?post=290732"}],"version-history":[{"count":0,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/290732\/revisions"}],"wp:attachment":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/media?parent=290732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/categories?post=290732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/tags?post=290732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}