Question 1: Relational Algebra and SQL Basics Library Databa…
Question 1: Relational Algebra and SQL Basics Library Database Books Table BookID Title 1 AI Revolution 2 Data Science 101 3 Web Dev Guide 4 Cyber Security Borrow Table BookID StudentID DueDays 1 201 10 2 202 15 3 203 5 4 204 20 1 205 25 Total Marks: 15 (a) Write the relational algebra expression to retrieve all books that have a DueDays greater than 15. (5 marks)(b) From (a), write a projection to display only Title and DueDays. (5 marks) Question 2: SQL Queries Tables Products Table ProductID ProductName Price 1 Headphones 120 2 Keyboard 85 3 Monitor 300 4 Mouse 40 Departments Table DeptID DepartmentName Manager 1 Sales Ryan 2 IT Kim 3 HR Sara Employees Table EmpID Name DeptID Salary 1 Alice 1 60000 2 Bob 1 55000 3 Carl 2 70000 4 Dana 3 58000 5 Eric 2 72000 Customers Table CustomerID Name 401 Mia 402 Noah 403 Emma Orders Table OrderID CustomerID Amount 501 401 400 502 402 250 503 403 600 Total Marks: 20 (a) Write an SQL query to retrieve all products where the price is greater than 100. (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 along with customer names by joining Orders and Customers tables. (8 marks) Question 3: SQL Joins Customers Table CustomerID Name 1 Liam 2 Ava 3 Noah 4 Emma 5 Lucas Orders Table OrderID CustomerID Amount 101 1 200 102 2 300 103 NULL 150 104 4 NULL 105 6 450 Total Marks: 15 (a) Show the resulting table for a Left Outer Join between Customers and Orders on CustomerID. (5 marks)(b) Show the resulting table for a Right Outer Join between Customers and Orders. (5 marks)(c) Show the resulting table for an Inner Join between Customers and Orders. (5 marks) Question 4: ER Diagram Hospital Database (20 marks) Design an ER Diagram for a hospital system including the following entities and relationships: Entities and Attributes Patient: PatientID (PK), Name, DateOfBirth, AddressDoctor: DoctorID (PK), Name, SpecializationAppointment: AppointmentID (PK), Date, Diagnosis, PrescriptionDepartment: DeptID (PK), DeptName Relationships Each patient can have multiple appointments, but each appointment is for one patient. Each doctor can handle multiple appointments, but each appointment is handled by one doctor. Each doctor belongs to one department, but a department can have many doctors. Show cardinalities and identify primary and foreign keys properly. Question 5: Normalization to 3NF Table: Employees EmpID Name Skills Emails 1 John Python, JavaScript john@gmail.com, john@work.com 2 Mia Java mia@gmail.com 3 Noah Python, SQL noah@gmail.com, noah@office.com 4 Emma HTML, CSS, JavaScript emma@work.com Convert the given Employees table into 3NF form, showing each step (1NF → 2NF → 3NF). (10 marks) Question 6: JSON Conversion Travel Details PersonID Name TripID FromCity ToCity Date Airline 1 Alex 501 Dallas New York 2024-04-10 AA101 1 Alex 502 New York Boston 2024-04-12 DL202 2 Lily 601 Chicago Tokyo 2024-06-01 NH12 2 Lily 602 Tokyo Seoul 2024-06-06 KE91 Convert this data into a JSON format grouped by each person. (15 marks) Question 8: System Job Separation in a Web Application E-Commerce Application Scenario (15 marks) Assign each task to the correct layer (Frontend, Middleware, or Backend) and briefly explain why: Displaying a list of products to users. (2 marks) Submitting user orders through an online form. (2 marks) Storing new order data in the database. (2 marks) Applying discount calculations before final checkout. (2 marks) Validating user payment inputs. (2 marks) Managing user login sessions. (2 marks) Processing complex business rules such as loyalty points. (3 marks)
Read Details