Please answer all these questions. Last assignment the professor notice some errors when y'all answered it. I have already started answering the questions already
Microsoft Word - Exam 1 - v2.docx Name: Instructions 1- Please read instructions carefully. 2- The exam time is 75 minutes. 3- The exam is an open book written exam. You are allowed to use any printed resources during the exam. No electronic resources or access to MySQL is allowed during the exam. 4- If you need a clarification regarding any of the questions, raise your hand. I will come to you, and you can ask your question. I reserve the right not to answer your question, if the answer you are seeking is part of what you are tested on. 5- The exam has 6 questions and 8 pages, including this page. Check you packet and make sure that you have all 8 pages. 6- Questions have varying number of points. Start by looking through the whole exam to decide on plan. 7- Write solutions in the designated spaces. You can use the back of each page as scratch, if needed. Make sure you write neatly and use the correct symbols for the relational algebra expressions. 8- Try to provide the simplest solutions. Correct answers that are unnecessarily complex will not receive full grades. Grade Question Points Score 1 4 2 13 3 8 4 2 5 4 6 6 Total 37 Question 1 (4 points) Choose the answer that best suits each of the following statements 1) A synonym for the relational model term tupple is a) Record b) Row c) Table d) a and b 2) The following is an example of an action a DBMS would take to enforce entity integrity a) Reject the insertion of a row in a table X, if a is the primary key of X, and the value of a is not defined in the row being inserted b) Reject the deletion of a row in a table X, if a is the primary key of X, and the value of a in the row being deleted matches the value of a in another table Y, where a is a foreign key connecting Y to X c) Reject the insertion of a row in a table X, if a is the primary key of X, and the value of a in the row being inserted matches the value of a in another row that already exists in X d) a and c 3) The following is an example of an action a DBMS would take to enforce referential integrity (choices a through d are the same as in 2) a) Reject the insertion of a row in a table X, if a is the primary key of X, and the value of a is not defined in the row being inserted b) Reject the deletion of a row in a table X, if a is the primary key of X, and the value of a in the row being deleted matches the value of a in another table Y, where a is a foreign key connecting Y to X c) Reject the insertion of a row in a table X, if a is the primary key of X, and the value of a in the row being inserted matches the value of a in another row that already exists in X d) a and c 4) In relational algebra, a join operation is equivalent to: a) The union of two relations b) A selection on the union of two relations c) A selection on the Cartesian product of two relations d) A projection on the Cartesian product of two relations Question 2 (13 points) Consider the following relational schema course (courseNum, deptName) enroll (studentID, courseNum) Where courseNum is the primary key for course and the (studentID, courseNum) pair is the primary key for enroll. courseNum in enroll is a foreign key that ties it to course. If a course gets deleted from course, all student enrollment records in enroll should also be deleted. A) Write a mySQL command to create the relational schema described above. You can make assumptions on missing information as long as you write your assumptions (5 points). B) Write a relational algebra expression and the equivalent MySQL command to find the IDs of all students who are not enrolled in courseNum 124. (4 points) C) Write a relational algebra expression to find IDs of all students who are enrolled in one or more courses in the CSC department (2 points) D) Write a MySQL command to find names of departments who have courses with 0 enrollments (2 points) Question 3 (8 points) Given the instance of a relational schema in the two tables below, what is the output of the relational algebra expressions in A through C and the MySQL command in D A) COURSE SECTION B) COURSE COURSE.Course_number = SECTION.course_number, Credit_hours = 04 SECTION C) COURSE COURSE.Course_number = SECTION.course_number, Credit_hours = 04 SECTION D) SELECT Course_number FROM COURSE WHERE Course_number IN (SELECT Course_number from SECTION) Question 4 (2 points) Write a MySQL command equivalent to the one in Question 3, D, using JOIN instead of a subquery Question 5 (4 points) Consider a relational database schema with two relations: X(a,b), Y(b,c). Without making any assumptions about primary keys, and assuming that a, b, c, and d are integers, consider the following three relational algebra expressions A) Õ a,c (X s b=1(Y)) B) Õ a ( s b=1(X)) × Õ c (s b=1(Y)) C) Õ a,c(Õ a (X) × s b=1(Y)) Two of these three expressions are equivalent, while one of them can produce a different answer. Which query can produce a different answer? Give a simple relational schema instance (example of tables X and Y) where the answer would differ. Show the answer for each of the expressions on the example you provide Question 6 (6 points) Consider relational database schema with two relations: X(a, b, c, d), Y(b, c, e, f), where a is the primary key of C and b is the primary key in Y. Consider an instance of that relational database schema where C has cardinality c1, and Y has cardinality c2. Draw lines to connect each of the relational algebra expressions in the left column with the expression in the right column that represents the cardinality of the relation it will produce. s a = 1 (X) <= c1="" +="" c2="" õb="" (y)="c1x" c2="" õc(y)=""><= c1="" x="" x="" y=""><= c1="" x="" c2="" x="" y="" õb="" (x)="" u="" õb="" (y)="c2"><= c2 1 1 1 c2="" 1="" 1="">