Mаtch the geоmetric shаpe оf the develоpаble surface used by the projection type to the type of projection.
Questiоn 1: Relаtiоnаl Algebrа and SQL Basics Student Table StudentID Name 1 Alice 2 Bоb 3 Charlie 4 David Grades Table StudentID Course Grade 1 Math 85 2 Math 78 3 Math 92 4 Math 80 1 Science 88 2 Science 90 Total Marks: 15 (a) Write the relational algebra expression to retrieve all students who have a grade above 80 in the "Math" course from a Student and Grades table. (5 marks) (b) From (a) write a projection for only Name and Grade of students. (5 marks) Question 2: SQL Queries Products Table ProductID ProductName Price 1 Laptop 1200 2 Phone 800 3 Tablet 600 4 Monitor 300 Employees Table EmployeeID Name Department Salary 1 Alice HR 50000 2 Bob HR 55000 3 Charlie IT 60000 4 David IT 65000 5 Eva Sales 70000 Orders Table OrderID CustomerID Amount 1 101 250 2 102 150 3 101 300 4 103 400 Customers Table CustomerID Name 101 Alice 102 Bob 103 Charlie Total Marks: 20 (a) Write an SQL query to retrieve all records from the Products table where the price is above 500. (5 marks) (b) Using the Employees table, write a query to calculate the average salary by department. (7 marks) (c) Write an SQL query to retrieve all orders with customer names by joining the Orders and Customers tables. (8 marks) Question 3: SQL Joins Customers Table CustomerID Name 1 Alice 2 Bob 3 Charlie 4 David 5 Eva Orders Table OrderID CustomerID Amount 101 1 250 102 2 150 103 NULL 300 104 4 NULL 105 6 500 Total Marks: 15 (a) Show the resulting table if a Left Outer Join is performed between Customers and Orders on CustomerID. (5 marks) (b) Show the resulting table if a Right Outer Join is performed between Customers and Orders on CustomerID. (5 marks) (c) Show the resulting table if an Inner Join is performed between Customers and Orders on CustomerID. (5 marks) Question 4: ER Diagram Total Marks: 20 Draw an ER Diagram for a university database that includes the following entities and relationships as described. Ensure all necessary attributes, relationships, and cardinality are represented correctly. (20 marks) Entities and Attributes Student Attributes: StudentID (Primary Key), Name, DateOfBirth, Major Each student may enroll in multiple courses, and a course may have multiple students enrolled. Course Attributes: CourseID (Primary Key), CourseName, Credits Each course is taught by one instructor, but an instructor can teach multiple courses. Each course can have multiple students enrolled. Instructor Attributes: InstructorID (Primary Key), Name, Department An instructor may teach multiple courses, but each course is taught by only one instructor. Relationships Enrolled In EnrollmentID (Primary Key), EnrollmentDate, Grade This relationship exists between the Student and Course entities. A student can be enrolled in multiple courses, and each course can have multiple students enrolled (many-to-many relationship). Include an associative (junction) entity named Enrollment to handle the many-to-many relationship, with the following attributes: Teaches This relationship exists between the Instructor and Course entities. Each course is taught by only one instructor, but an instructor can teach multiple courses (one-to-many relationship). Additional Requirements Cardinality: For the Enrolled In relationship between Student and Course, show that it’s a many-to-many relationship with the Enrollment associative entity in between. For the Teaches relationship between Instructor and Course, show that it’s a one-to-many relationship. Primary Keys and Foreign Keys: Identify primary keys for each entity. In the Enrollment associative entity, include foreign keys to both StudentID and CourseID to link to the Student and Course tables. Question 5: Normalization to 3NF Table: Students StudentID Name Courses ContactNumbers 1 Alice Math, Science 123-4567, 234-5678 2 Bob History 345-6789 3 Charlie Math, Science, English 456-7890, 567-8901 4 David Math 678-9012 Total Marks: 10 Convert the provided Students table to 3NF. (10 marks) Question 6: JSON Conversion Flight Details PersonID Name Age TripID Origin Destination Date FlightNumber 1 John Doe 34 101 New York London 2024-10-01 BA178 1 John Doe 34 102 London Paris 2024-10-05 AF123 1 John Doe 34 103 Paris Rome 2024-10-10 AZ456 2 Jane Roe 28 201 Chicago Tokyo 2024-11-12 JL009 2 Jane Roe 28 202 Tokyo Sydney 2024-11-15 QF12 Total Marks: 15 (a) Convert the given relational data into JSON format. Question 8: System Job Separation in a Web Application Total Marks: 15 For each task, identify the appropriate layer in the system architecture (frontend, middleware, or backend) and briefly explain why it belongs in that part of the system. Reading data from the database (2 marks) Saving data to the database (2 marks) Taking user input through forms or other interactive elements (2 marks) Applying filters or transformations to data before displaying it to the user (2 marks) Validating data inputted by the user for correctness (2 marks) Handling authentication and authorization (2 marks) Executing complex business logic (3 marks)