Skip to content

Database Systems

SWE · Final · Fall 25

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

Read the questions as text

Read off the PDF by AI, so it may have mistakes. The PDF is the original.

Daffodil International University Department of Software Engineering Faculty of Science & Information Technology Final Examination, Fall 2023 Course Code: SE 223; Course Title: Database Systems Sections & Teachers: NH (A), TRT (B, C, E, F), CP (D, O), KRA (F, I, J), SS (G), KMH (H), MMST (K, L), AR (M, N) Time: 2:00 Hrs Marks: 40 Answer ALL Questions [The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.] 1. | Wildlife Tracking RID | RName | Specialty | BCLocation | EID | EDate | HabitatTypes | AID | Species | Observation | TrackingID | HealthStatuses R001 | Dr. Elena Vargas | Ornithology | Amazon Basin | EXP101 | 2023-04-15 | Rainforest | A101,A102 | Macaw, Jaguar | Colorful plumage, Aggressive | TDD01, TDD02 | Healthy, Injured R002 | Prof. Marcus Hale | Mammalogy | African Plains | EXP301 | 2023-09-10 | Grassland | A301,A302 | Lion, Zebra | Pride hunting, Grazing | TDD04 | Healthy, Malnourished R003 | Dr. Sofia Chen | Herpetology | Southeast Asia | EXP401 | 2024-01-05 | Jungle | A401,A501 | Cobra | Venomous bite | TDD00, TDD08 | Recovering RID - ResearcherID RName - ResearcherName BCLocation - BaseCampLocation EID - ExpeditionIDs EDate - ExpeditionDates AID - AnimalID a) Analyze the UNF table above. Identify and explain at least 2 potential anomalies that this table can have due to its unnormalized structure, and also provide detailed reasons for each. [Marks-4] [CLO-4, Level-4] b) "Ignore all the values given in the table; Only work with the given attribute." Normalize the UNF table step-by-step from 1NF to 3NF (and briefly discuss if BCNF is achieved or needed). [Marks-8] c) Describe the concepts of full functional dependency and partial dependency, including how they lead to different normalization issues. [Marks-4] [CLO-4] d) Define the exact rule(s) that a relation must satisfy to be in 1NF, 2NF, 3NF, and BCNF. [Marks-4] 2. Based on the normalized tables from Question 1 (in 3NF), draw an Entity-Relationship Diagram (ERD). Include all entities, attributes, PKs, FKs, and relationships with cardinalities [Marks-5] [CLO-3, Level-3] 3. a) Using the normalized tables from Part 1 (Question 1), assume the following schema has been implemented in a relational database. Write SQL statements to solve the following problems: [Marks-10] [CLO-3, Level-3] a) List all researchers whose name contains "a" and whose BaseCampLocation contains "Asia". b) Display ResearcherName, ExpeditionID, and ExpeditionDate for all expeditions. Order the result by ExpeditionDate descending. c) Find all expeditions that took place between '2023-01-01' and '2024-06-30' (inclusive). Show ExpeditionID, ExpeditionDate, and HabitatType. d) Count how many animals of each species are in the Animals table. Order by count descending. e) List every HabitatType that appears in at least 2 expeditions. Show HabitatType and the number of expeditions. f) List all animals (AnimalID, Species) along with their HealthStatus from the Tracking table. g) Show ResearcherName, Species, and ObservationNote for every observation recorded. Order by ResearcherName. h) All animals currently marked as "Injured" were re-examined and are now "Recovering". Write an UPDATE statement to change their HealthStatus accordingly. i) Find researchers who have recorded more observations than the average number of observations per researcher. (Use Subquery) j) Show ResearcherName and ResearcherSpecialty for every researcher whose BaseCampLocation is 'Amazon Basin' or 'Southeast Asia'. 4. a) Explain the four ACID properties of a database transaction. For each property, give a simple example from the WildLife system above. [Marks-3] [CLO-5, Level-2] b) Consider the following real-life scenario in the WildlifeTracking system, where a researcher is recording a new observation in the jungle using a mobile device. The transaction performs these steps in order: [Marks-2] 1. Insert a new row into the Observations table with ObservationNote = "Elephant calf born - healthy". Answer the following: Explain the difference between Partially Committed and Fully Committed states using the above wildlife scenario.