Database Systems
SWE · Final · Fall 25
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.