| Time you have | Do this |
|---|---|
| 3 hours | Sit the real paper (§1) closed-book, choosing five questions exactly as you will on the day → mark yourself against §2 and Volume III → re-read whatever you could not attempt → §5 traps → sleep. |
| 90 minutes | §1 questions 1–3 written out in full → the SQL drills (§3) → the ER drill (§2 Q4) → §5 traps. |
| 45 minutes | Answer plans in §2 read as a checklist → rapid-fire recall (§4) → §5 traps → §6 walk-in sheet. |
| 10 minutes at the door | §6 only. |
The single most valuable habit for this paper: every scenario question gets two paragraphs · the first defines the concept in the notes' own words, the second applies it to the hospital, supermarket, bank or ride-sharing firm you were given. Students who write only the first get half marks; students who write only the second get less.
RIVERS STATE UNIVERSITY · FACULTY OF SCIENCE · DEPARTMENT OF COMPUTER SCIENCE
DTS 304: Data Management I · 2025/2026 session · 14/07/2026 · Time allowed: 3 hours
Instructions · attempt any FIVE (5) questions · Lecturers: Dr. Igiri Chima and the lecturer Ominini M.
| Q | Question | Marks |
|---|---|---|
| 1 | (a) A hospital replaces its paper records with a DBMS. i. Identify the required hardware and software. ii. Explain benefits. iii. Discuss implementation challenges. (b) i. Explain how adding new database fields affects logical independence. ii. Describe the importance of data as a database component. iii. Why is user consent important when collecting customer data? | 8 + 6 |
| 2 | (a) i. Explain the relationship between databases and data science. ii. Describe how data masking protects sensitive information. iii. A supermarket chain records millions of customer transactions daily · explain the difference between data and information using this scenario. (b) A healthcare company stores patient records for ten years before deletion. Outline and discuss the stages of the data lifecycle involved. | 6 + 8 |
| 3 | (a) As a data scientist you received a dataset from three different countries with varying languages, currencies and date formats. Outline a comprehensive data cleaning plan. (b) i. A bank database contains over 20 million customer records · explain how indexing improves query performance. ii. A ride-sharing company collects GPS locations every second · explain how this data supports business decision-making. iii. A social media platform stores videos, images and comments, XML documents, audio recordings, emails, a customer database and a JSON file · classify each under the data formats you have studied. | 8 + 6 |
| 4 | Design an ER diagram for a bank storing customers, accounts, branches and employees. Customer identified by SSN, with name, multiple phone numbers and occupation. Account identified by account number and its branch, with balance and type. An account belongs to one or more customers; a customer may have any number of accounts; an account belongs to exactly one branch; branches may have multiple accounts but are not required to have any. Branch identified by branch code, with location and number of employees. Employee identified by SSN, with name and salary; works for exactly one branch; branches have one or more employees; an employee is contact for zero or more customers; every customer has at most one employee as contact. | 14 |
| 5 | (a) Given professor(profname, deptname), department(deptname, building), committee(profname, commname): i. professors in any one of Smith's committees; ii. professors in at least all of Smith's committees; iii. professors in exactly Smith's committees; iv. professors with an office in the ICICS building. (b) Describe complexity, failed attempts and expired passwords in a password policy and explain how each protects the database. | 9 + 5 |
| 6 | (a) With the aid of a good diagram describe the phases of query processing in a DBMS, stating the function of each stage. (b) State what is wrong with each query and give a solution: i. select d_no from BusDriver where d_no = 07; on BusDriver(bdno varchar2(5), bdname varchar2(50), …). ii. select bdno, bdname from BusDriver, Depot where bdsalary > 5000 and dname = 'Hornsey' and BusDriver.dno = Depot.dno and bdsalary < 2000; | 8 + 6 |
| 7 | (a) As leader of the data design team for an inventory tracking system, bring a work plan identifying the phases of data design, giving for each: a description, the inputs, the outputs and a key issue addressed. (b) What is meant by concurrent execution of database transactions in a multiuser system, and why is concurrency control needed? (c) Explain the meaning of "transaction" in the context of database interactions. | 8 + 4 + 2 |
(a) Split into hardware (server, storage/RAID, backup, workstations, network, UPS, scanners) and software (DBMS, EHR application, OS, security software, backup software, reporting tools) · a list each, not a paragraph. Benefits: give the five DBMS advantages from Volume I §5 plus the healthcare benefits · improved patient care, faster diagnosis, reduced errors. Challenges: cost, data migration of paper records, staff training and resistance, privacy and compliance, integration with lab and imaging systems, downtime, and the need for a DBA. FULL TEXT IN VOL III §8
(b) Three short answers: logical independence · adding a field changes the conceptual schema but the external schema and applications are unaffected, which is precisely what logical data independence means, and it fails only where an application depends on schema shape (e.g. SELECT *) · data as a component · of the four components, data is the substance; relationships, constraints and schema exist only to organize and protect it · consent · the first principle of information privacy, protects against identity theft and misuse, is a legal requirement under GDPR/HIPAA/Nigerian law, and builds the trust that keeps customers sharing data.
(a) Databases and data science · supply and consumption: the database is the organized store, data science extracts insight from it; step 1 of the data science process is "collect from databases"; without data, analysis is impossible. Masking · hides part of a sensitive value (xxx 1234) so the record stays usable but the sensitive digits are never shown, protecting against exposure to staff, testers and attackers. Data vs information · the table from Volume I §2, then the supermarket applied: raw till records vs "bread sales rose 18% in June".
(b) The eight lifecycle stages walked through a patient record, with the ten-year retention called out at the archiving stage as a legal requirement and deletion justified by data minimization. 8 marks means eight stages · write all eight.
(a) The eight-step cleaning plan (Vol III §8): profile → standardize dates to ISO → normalize currency at a stated rate → fix language and encoding → deduplicate → handle missing values → correct types and units → validate and document. Name the problem each step fixes · that is where the marks are.
(b) Indexing · without it a linear search checks all 20 million rows; the index (a B-tree) is searched instead, the pointer located and only the matching row retrieved; the WHERE clause benefits; the cost is extra storage and slower writes. GPS · the six DDDM steps applied: route optimization, demand forecasting, dynamic pricing, driver safety monitoring, accurate ETAs, expansion planning. Formats · unstructured: videos, images, comments, audio; semi-structured: XML, emails, JSON; structured: the customer database.
Work in this order and you cannot go wrong: 1. four rectangles · Customer, Account, Branch, Employee. 2. key attributes underlined, then the rest; phone as a double ellipse. 3. four diamonds · has, held at, works for, contact. 4. cardinalities: M:N, N:1, N:1, 1:N. 5. double lines where participation is total. 6. a one-line note under the diagram justifying each cardinality by quoting the requirement it came from · that sentence is what separates 14 from 9. DRAWN IN VOL III §1
Q5(a) · recognise which of the three shapes you have: "any one of" → IN; "at least all" → division (double NOT EXISTS or HAVING COUNT); "exactly" → division plus a count equality. (iv) is a plain join through department. Q5(b) · three features, each with what it is and what attack it stops.
Q6(a) · draw parser → optimizer → evaluation engine, with catalog/statistics feeding the optimizer and data feeding the engine; name the output of each stage. Q6(b) · (i) the column does not exist, so it fails at parsing; (ii) the predicate is self-contradictory, so it always returns empty.
Q7(a) · the five-phase table. Q7(b) · define interleaved execution, give the reason (throughput, reduced waiting), then the four problems it causes. Q7(c) · two marks, one sentence: a sequence of actions representing a logical unit of work.
Paper A question 1 gives EMP(empno, deptno, ename, salary, Designation, joiningdate, DOB, city). These five are the most likely shape of any SQL asked of you.
| # | Required | Query |
|---|---|---|
| i | Employee name and number in increasing order of salary | SELECT ename, empno FROM EMP ORDER BY salary ASC; |
| ii | Employee name and number, department-wise | SELECT deptno, ename, empno FROM EMP ORDER BY deptno, ename; |
| iii | Total salary of all employees | SELECT SUM(salary) AS total_salary FROM EMP; |
| iv | Employees whose name starts with "S" and who work in deptno 1002 | SELECT ename FROM EMP WHERE ename LIKE 'S%' AND deptno = 1002; |
| v | Increase salary by 20% for employees who joined after 1/1/2005 | UPDATE EMP SET salary = salary * 1.20 WHERE joiningdate > '2005-01-01'; |
StudentDB table| Required | Query |
|---|---|
| Names of students aged 30 or over | SELECT Name FROM StudentDB WHERE Age >= 30; |
| All female students | SELECT * FROM StudentDB WHERE Sex = 'F'; |
| How many students there are | SELECT COUNT(*) FROM StudentDB; |
| Average age per department | SELECT Dept, AVG(Age) FROM StudentDB GROUP BY Dept; |
| Departments with more than one student | SELECT Dept FROM StudentDB GROUP BY Dept HAVING COUNT(*) > 1; |
| The three oldest students | SELECT Name, Age FROM StudentDB ORDER BY Age DESC LIMIT 3; |
| Students joined to their state and LGA | SELECT s.Name, s.Dept, t.State, t.LGA FROM StudentDB s JOIN Table2 t ON s.MatNo = t.MatNo; |
| Add a new student | INSERT INTO StudentDB (Name, MatNo, Age, Dept, Sex) VALUES ('Uche','2025/PGD/006',27,'Law','F'); |
| Correct a department | UPDATE StudentDB SET Dept = 'Maths' WHERE MatNo = '2025/PGD/003'; |
| Remove a withdrawn student | DELETE FROM StudentDB WHERE MatNo = '2025/PGD/005'; |
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. Nothing else parses.WHERE filters rows before grouping; HAVING filters groups after. You cannot put COUNT(*) in a WHERE.deptno = 1002 but ename LIKE 'S%'.SELECT * when the question named the columns it wants.LIKE patterns worth memorising: 'S%' starts with S · '%s' ends with s · '%an%' contains "an" · '_a%' has "a" as the second character. % is any run of characters, _ is exactly one.| Prompt | Answer |
|---|---|
| Data, in one line | Raw, unprocessed facts, figures or symbols with no meaning on their own |
| Information, in one line | Processed and organized data that is meaningful, useful and supports decisions |
| Data by nature | Quantitative (measured) · qualitative (descriptive) |
| Six core components of information management | Collection · storage · organization · processing · retrieval · security |
| Eight stages of the data lifecycle | Creation · collection · storage · processing · analysis · distribution · archiving · deletion |
| Data science, in one line | A multi-disciplinary field using data, algorithms and scientific methods to extract insight and knowledge |
| Five steps of using data in data science | Collect · clean · analyse · build models · decide |
| Six steps of DDDM | Collection · processing · analysis · interpretation · decision · prediction |
| Metadata | Data about data · describes, explains and locates an information resource |
| Three data collection techniques | Manual · sensor-based · API-based |
| Three data formats | Structured · semi-structured · unstructured |
| Where JSON, XML and email sit | Semi-structured |
| File-based storage's five weaknesses | Redundancy · inconsistency · difficult sharing · poor security · no relationships |
| Five DBMS advantages | Reduced redundancy · integrity · security · easy retrieval · multi-user access |
| Five components of an IRS | Data collection · indexing system · query processor · search engine · user interface |
| Four types of index | Primary · secondary · clustered · non-clustered |
| Two costs of indexing | Extra storage · slower updates |
| B-tree vs hashing | B-tree is balanced, sorted and supports range queries; hashing is very fast look-up but collides and cannot do ranges |
| Prompt | Answer |
|---|---|
| Six techniques for efficient queries | Use an index · avoid SELECT * · use WHERE properly · use JOIN efficiently · limit results · use aggregation carefully |
| What the query optimizer does | Chooses the best execution plan by reducing disk access, reducing CPU usage and using indexes and statistics |
| Five principles of privacy | Consent · purpose limitation · data minimization · transparency · accountability |
| Three privacy regulations | GDPR (Europe) · HIPAA (healthcare) · local data protection laws (Nigeria) |
| Five data protection techniques | Encryption · access control · backup and recovery · data masking · firewalls and antivirus |
| Five security threats | Malware · phishing · unauthorized access · SQL injection · data breaches |
| Six security controls | Authentication · authorization · encryption · firewalls · monitoring and auditing · update and patching |
| Four components of a database | Data · relationship · constraints · schema |
| Definition of a database | A collection of interrelated data stored with controlled redundancy to serve one or more applications in an optimal fashion |
| Seven services of the DBMS | Transaction · concurrency · recovery · security · language interface · storage · data catalog management |
| Three limitations of the file-based system | Segregation and isolation · duplication · data dependency |
| Four database models (plus two) | Hierarchical · network · relational · object oriented; also object-relational and deductive |
| What makes the network model different | Many-to-many (n-n) · records may have more than one parent |
| Three levels of the ANSI/SPARC architecture | External/view · conceptual/logical · internal/physical |
| Two types of database architecture | Client–server · three-tier |
| Logical vs physical independence | Change the logical schema without touching applications · change the storage without touching the logical schema |
| The four SQL sub-languages | DQL SELECT · DML INSERT/UPDATE/DELETE · DDL CREATE/ALTER/DROP · DCL/TCL GRANT/REVOKE/COMMIT/ROLLBACK |
| Transaction, ACID and its delimiters | A logical unit of work · atomicity, consistency, isolation, durability · BEGIN … COMMIT / ROLLBACK |
| # | Statement | T / F | Correction |
|---|---|---|---|
| 1 | Data and information mean the same thing in a database context. | F | Data is raw and meaningless on its own; information is processed, organized and useful for decisions. |
| 2 | An email is unstructured data. | F | Email is semi-structured · it has headers and fields, so it has some structure without being a table. |
| 3 | Indexing makes every database operation faster. | F | It speeds reads but costs extra storage and slows inserts and updates, which must maintain the index. |
| 4 | Hashing is the best choice for range queries. | F | Hashing is not suitable for range queries · that is the B-tree's strength. |
| 5 | All leaf nodes of a B-tree are at the same level. | T | Correct · that is what "balanced" means, and it is why performance is consistent. |
| 6 | The hierarchical model supports many-to-many relationships. | F | It does not · a child has one parent. The network model introduced n-n. |
| 7 | The DBA works at the external level of the ANSI/SPARC architecture. | F | The DBA works at the conceptual/logical level. The external level is what users see. |
| 8 | Physical data independence means applications survive a change to the logical schema. | F | That is logical independence. Physical independence is surviving a change to storage · e.g. HDD to SSD. |
| 9 | DELETE is a DDL command. | F | DELETE removes rows and is DML. DROP removes the table and is DDL. |
| 10 | COMMIT makes a transaction's changes permanent. | T | Correct · and ROLLBACK returns the transaction to its original state. |
| 11 | Concurrency control exists to make transactions run faster. | F | Concurrency exists for speed; concurrency control exists to preserve consistency while allowing it. |
| 12 | A cache of one transaction reading another's uncommitted data is called a dirty read. | T | Correct · and if that transaction then aborts, the reader used data that never existed. |
| 13 | Encryption and data masking are the same technique. | F | Encryption converts data into an unreadable format that can be decrypted; masking hides part of a value and is not reversed for the viewer. |
| 14 | A weak entity is drawn as a double rectangle. | T | Correct · and a multivalued attribute is a double ellipse, total participation a double line. |
| 15 | An M:N relationship maps to a foreign key in one of the two tables. | F | M:N needs a new table holding both keys. Only 1:N maps to a posted foreign key. |
| 16 | Fragmentation means keeping copies of the same data at several sites. | F | That is replication. Fragmentation is dividing a relation into pieces stored at different sites. |
SELECT · DML INSERT/UPDATE/DELETE · DDL CREATE/ALTER/DROP · DCL/TCL GRANT/COMMIT/ROLLBACKConsent · purpose limitation · data minimization · transparency · accountability. Every privacy or consent question is answered by naming these and applying two or three.
Write the definition, then the numbered list that belongs to it, then one example, then apply it to the scenario in the question. Every answer on this paper is built from those four moves, and each scores on its own.