An Entity–Relationship (ER) model is a conceptual, graphical model of the data requirements of a system, showing the entities about which data is stored, the attributes that describe them and the relationships among them. It is produced at the conceptual design phase (§3) and is later mapped to a relational schema.
| Symbol | Meaning |
|---|---|
| Rectangle | An entity · a thing about which data is stored (Customer, Account) |
| Double rectangle | A weak entity · cannot be identified without an owner entity |
| Ellipse | An attribute |
| Underlined ellipse | A key attribute · uniquely identifies the entity |
| Double ellipse | A multivalued attribute · e.g. several phone numbers |
| Dashed ellipse | A derived attribute · computed, e.g. age from date of birth |
| Diamond | A relationship between entities |
| Line with 1 / N / M | The cardinality · 1:1, 1:N or M:N |
| Double line | Total participation · every instance must take part |
| Single line | Partial participation · participation is optional |
| The wording | What it means |
|---|---|
| "belongs to exactly one" | Cardinality 1 on that side, and total participation (double line) |
| "can have any number of" | Cardinality N, partial participation |
| "one or more" | Cardinality N, total participation |
| "zero or more" | Cardinality N, partial participation |
| "at most one" | Cardinality 1, partial participation |
| "are not required to have" | Explicitly partial · this phrasing is a deliberate hint |
| Entity | Key attribute | Other attributes |
|---|---|---|
| Customer | SSN | name · phone (multivalued · double ellipse) · occupation |
| Account | account-no | balance · type (e.g. savings) |
| Branch | branch-code | location · number-of-employees |
| Employee | SSN | name · salary |
SSN, attributes name, phone (double ellipse, "one or more"), occupation. Account · key account-no, attributes balance, type. Customer–Account is M:N ("an account belongs to one or more customers; a customer can have any number of accounts") with total participation on the account side. Account–Branch is N:1, total on the account side ("exactly one branch") and partial on the branch side ("branches are not required to have accounts"). Branch · key branch-code, attributes location, no-of-employees. Employee · key SSN, attributes name, salary. Employee–Branch is N:1, total both ways ("works for exactly one branch"; "branches have one or more employees"). Employee–Customer is 1:N contact, partial ("zero or more customers"; "at most one employee as a contact").
Customer(SSN, name, occupation, contact_emp_SSN) CustomerPhone(SSN, phone) ← multivalued Account(acct_no, balance, type, branch_code) Holds(SSN, acct_no) ← M:N Branch(branch_code, location, no_of_emps) Employee(SSN, name, salary, branch_code)
Query processing is the set of activities a DBMS performs to translate a high-level query into an efficient sequence of low-level operations and execute it against the stored data.
| # | Stage | Function | Output it hands on |
|---|---|---|---|
| 1 | Parsing and translation | Checks the query's syntax, verifies that the named relations and attributes actually exist in the data catalog, checks the user's access rights, and translates the query into an internal relational-algebra expression | A parse tree / relational-algebra expression |
| 2 | Optimization | Generates the equivalent alternative execution plans and estimates the cost of each using the catalog statistics · table sizes, index availability, selectivity · then chooses the cheapest | The chosen query execution plan |
| 3 | Evaluation / execution | The evaluation engine runs the chosen plan against the actual stored data, applying the physical operators · index scan, table scan, join, sort, aggregate | The query result returned to the user |
If more stages are wanted, split stage 1 into parsing (syntax) and translation (to relational algebra), and add a final result return stage · a five-stage answer of parsing → translation → optimization → evaluation → output is equally acceptable, and it reads better against an 8-mark allocation. Note the tie back to Volume I §6: the optimizer chooses the best execution plan by reducing disk access, reducing CPU usage, and using indexes and statistics · that sentence from the notes is stage 2.
BusDriver(bdno varchar2(5),
bdname varchar2(50), … )
select d_no from BusDriver where d_no = 07;
The fault: the attribute d_no does not exist in the relation BusDriver · the key column is called bdno. The query is therefore rejected at the very first stage of query processing, parsing and translation, when the parser consults the data catalog and cannot resolve the identifier. It never reaches the optimizer.
Secondary fault worth a mark: even with the right name, bdno is declared varchar2(5) · a character type · so comparing it with the numeric literal 07 is a type mismatch. A character comparison would also lose the leading zero.
Solution select bdno from BusDriver where bdno = '07';
BusDriver(bdno, bdname, bdsalary, dno) Depot(dno, dname, daddress) select bdno, bdname from BusDriver, Depot where bdsalary > 5000 and dname = 'Hornsey' and BusDriver.dno = Depot.dno and bdsalary < 2000;
The fault: the WHERE clause is self-contradictory · it requires bdsalary > 5000 and bdsalary < 2000 at the same time. No value can satisfy both, so the predicate is unsatisfiable and the query always returns the empty set. It is syntactically valid and will not be rejected · it simply cannot ever be right, which makes it worse than an error.
Solution · decide which was meant, e.g. select bdno, bdname from BusDriver, Depot where bdsalary > 5000 and dname = 'Hornsey' and BusDriver.dno = Depot.dno;
If the intent was a band, use bdsalary between 2000 and 5000.
The question asks for a work plan giving, for each phase: a description · the inputs · the outputs · a key issue addressed. Answer it as a table · it is faster to write and impossible to mark down for missing a part.
| Phase | Description | Inputs | Outputs | Key issue addressed |
|---|---|---|---|---|
| 1. Requirements analysis | Gather and document what data the business needs to store and what it must do with it, by interviewing users and studying existing documents and systems | Interviews, existing forms and reports, business rules, legacy files | A written requirements specification · data items, volumes, users, rules | Completeness and ambiguity · have all user groups been consulted, and do any two of them contradict each other? |
| 2. Conceptual design | Build a DBMS-independent model of the data: identify entities, attributes and relationships | The requirements specification | A conceptual schema · the ER model / ER diagram | Correct entities and cardinalities · is this really an entity or just an attribute, and is that relationship 1:N or M:N? |
| 3. Logical design | Map the conceptual model onto the data model of the chosen DBMS · for an RDBMS, turn entities and relationships into tables, keys and foreign keys, then normalize | The ER model, plus the choice of DBMS | A logical schema · normalized relations with primary and foreign keys and integrity constraints | Redundancy and the update anomalies · normalization to remove insert, update and delete anomalies (§5) |
| 4. Physical design | Decide how the logical schema will actually be stored and accessed on the chosen hardware | The logical schema, expected query workload, volumes, hardware | Storage structures, indexes, file organization, partitioning, tuning parameters | Performance vs storage trade-off · every index speeds reads but costs space and slows writes (Vol I §5) |
| 5. Implementation and testing | Create the database with DDL, load the data, and validate it against the requirements | The physical design and the source data | A working, populated database plus test results and documentation | Data migration and validation · is legacy data clean and correctly converted? |
professor(profname, deptname) department(deptname, building) committee(profname, commname)
SELECT DISTINCT c.profname
FROM committee c
WHERE c.commname IN
(SELECT commname
FROM committee
WHERE profname = 'Smith');
"Any one" means intersection is non-empty · a simple IN subquery. Add AND c.profname <> 'Smith' if you want to exclude Smith himself; say which reading you chose.
SELECT p.profname FROM professor p, department d WHERE p.deptname = d.deptname AND d.building = 'ICICS';
The only trick is the sentence "a professor's office is in the building in which her/his department is in" · it tells you to join through department rather than look for a building column on professor.
This is relational division: professors for whom there is no committee of Smith's that they are missing. The standard SQL idiom is the double NOT EXISTS.
SELECT DISTINCT p.profname
FROM committee p
WHERE NOT EXISTS (
SELECT s.commname FROM committee s
WHERE s.profname = 'Smith'
AND NOT EXISTS (
SELECT * FROM committee x
WHERE x.profname = p.profname
AND x.commname = s.commname));
Counting alternative, which is easier to write correctly under pressure:
SELECT c.profname FROM committee c
WHERE c.commname IN (SELECT commname FROM committee
WHERE profname='Smith')
GROUP BY c.profname
HAVING COUNT(DISTINCT c.commname) =
(SELECT COUNT(*) FROM committee
WHERE profname = 'Smith');
"No more and no less" = at least all of them (ii) and no others. Easiest as two counts that must agree:
SELECT c.profname FROM committee c
GROUP BY c.profname
HAVING COUNT(*) = (SELECT COUNT(*) FROM committee
WHERE profname = 'Smith')
AND COUNT(*) = (SELECT COUNT(*) FROM committee c2
WHERE c2.profname = c.profname
AND c2.commname IN
(SELECT commname FROM committee
WHERE profname = 'Smith'));
In words: the professor sits on the same number of committees as Smith, and every one of those is one of Smith's.
IN subquery. "at least all" → division, so double NOT EXISTS or a HAVING COUNT comparison. "exactly" → division plus a count equality that forbids extras. Spotting which of the three you have been given is most of the mark.
A functional dependency X → Y exists when, for any two rows that agree on X, they must also agree on Y · the value of X determines the value of Y. X is the determinant.
| Type | Definition and example |
|---|---|
| Full functional dependency | Y depends on the whole of a composite X and not on any part of it. (StudentID, CourseID) → Grade |
| Partial dependency | Y depends on part of a composite key. (StudentID, CourseID) → StudentName is partial, since StudentID → StudentName alone. Removed by 2NF |
| Transitive dependency | X → Y and Y → Z, so X → Z indirectly. StudentID → DeptID → DeptName. Removed by 3NF |
| Trivial dependency | Y is a subset of X, so it holds automatically. (StudentID, Name) → Name |
| Multivalued dependency | X determines a set of Y values independently of other attributes. Removed by 4NF |
Three advantages of functional dependency: it is the basis of normalization and so removes redundancy · it lets keys and integrity constraints be identified formally · it preserves data consistency by making the relationships between attributes explicit.
Take an unnormalized StudentCourse(StudentID, StudentName, DeptName, CourseID, Grade):
| Anomaly | What goes wrong |
|---|---|
| Insert anomaly | You cannot record a new department until some student enrols in it, because the primary key requires a student · data you have cannot be stored |
| Update anomaly | A student's name appears in every row for every course they take. Changing it means changing many rows, and missing one leaves the database inconsistent |
| Delete anomaly | Deleting the last student in a department destroys the department's data too · you lose facts you meant to keep |
| Form | Requirement |
|---|---|
| 1NF | All attribute values are atomic · no repeating groups or multivalued cells |
| 2NF | 1NF and no partial dependency on part of a composite key |
| 3NF | 2NF and no transitive dependency on the key |
| BCNF | Every determinant is a candidate key |
Normalization is the process of organizing tables to reduce redundancy and eliminate the three anomalies, by decomposing one wide table into several related ones joined on keys.
A transaction is a sequence of actions that represent a logical unit of work, transforming the database from one consistent state to another. It is scoped by the delimiters BEGIN TRANSACTION … COMMIT or ROLLBACK. After successful execution the transaction reaches the partially committed state, and once committed the changes are permanent and durable · the point of no return, after which recovery will redo rather than undo them.
| Property | Meaning |
|---|---|
| Atomicity | All or nothing · either every action happens or none does |
| Consistency | The database moves from one valid state to another, respecting all constraints |
| Isolation | Concurrent transactions do not interfere; each sees the database as if alone |
| Durability | Once committed, changes survive failure |
Operations (4): READ · WRITE · COMMIT · ROLLBACK (also BEGIN).
States (5): Active → Partially committed (last statement executed) → Committed (changes permanent); or Failed → Aborted (rolled back to the original state).
Concurrent execution means several transactions from different users are interleaved and executed in overlapping time in a multiuser system, rather than one strictly after another. It is done for throughput and resource utilisation · the CPU can work on one transaction while another waits for disk · and for reduced waiting time, so a short transaction is not stuck behind a long one.
Concurrency control is needed because uncontrolled interleaving destroys consistency. The four classic problems:
| Problem | What happens |
|---|---|
| Lost update | Two transactions read the same value and both write; the second overwrites the first, whose update vanishes |
| Dirty read | One transaction reads a value written by another that then aborts · it read data that never officially existed |
| Unrepeatable read | A transaction reads the same row twice and gets different values, because another committed in between |
| Phantom read | A repeated query returns extra rows inserted by another transaction |
Quote the notes' own sentence as the definition: concurrency control is the DBMS activity of coordinating processes that operate concurrently, access shared data and can potentially interfere with one another; its goal is to allow concurrency while maintaining the consistency of the shared data.
| Approach | How it works |
|---|---|
| Pessimistic locking | Assumes conflict is likely: a transaction acquires a lock before touching data and holds it, so others must wait. Shared (read) locks may be held by many; an exclusive (write) lock by one only. Safe, but risks waiting and deadlock |
| Optimistic locking | Assumes conflict is rare: transactions proceed with no locks, and at commit time the system validates that nothing they read was changed meanwhile. If it was, the transaction is rolled back and retried. Fast when conflicts are rare, wasteful when they are not |
Two-phase locking (2PL) is the standard protocol: a growing phase where locks are only acquired, then a shrinking phase where they are only released. It guarantees serializability.
Commit makes a transaction's changes permanent in a single database. Two-phase commit (2PC) is the protocol used when a transaction spans several databases or sites, so all of them must agree:
The point: it preserves atomicity across sites · never some sites committed and others not. Its weakness is blocking if the coordinator fails between the phases.
A distributed database is a single logical database whose data is physically spread across several sites connected by a network, yet appears to the user as one database.
| Area | Distributed | Centralized |
|---|---|---|
| Data location | Spread across many sites | All at one site |
| Reliability | High · no single point of failure | Low · one failure stops everything |
| Complexity & cost | High · needs replication, fragmentation, distributed control | Lower · simpler to design, secure and administer |
Homogeneous · all sites run the same DBMS software and are aware of each other. Two types: autonomous (sites operate independently) and non-autonomous (a central node coordinates).
Heterogeneous · sites run different DBMS software or data models, needing translation. Two types: federated (sites keep their independence and cooperate) and multidatabase (a layer presents one interface over them).
Fragmentation is the process of dividing a relation into smaller pieces (fragments) which are stored at different sites. Horizontal fragmentation splits by rows, vertical by columns, and mixed/hybrid does both.
Two advantages of replication: availability · data survives a site failure; faster local reads · queries are answered from a nearby copy.
Two advantages of fragmentation: efficiency · only the relevant subset is stored and scanned at each site; locality · data lives where it is used, reducing network cost.
Transparency means the distribution is hidden from the user, who queries the system as though it were a single ordinary database. Its kinds:
| Kind | What is hidden |
|---|---|
| Location transparency | Where the data physically resides |
| Fragmentation transparency | That a relation is split into fragments at all |
| Replication transparency | That several copies exist, and which one answered |
| Naming transparency | That names must be unique across sites |
| Transaction transparency | That a transaction touching several sites is coordinated (by 2PC) |
| Failure transparency | That a site has failed and another copy is serving |
| Feature | What it does and how it protects |
|---|---|
| Complexity | Requires a minimum length and a mix of upper case, lower case, digits and symbols, and forbids dictionary words or the username. Protection: it makes brute-force and dictionary attacks computationally infeasible by enlarging the search space |
| Failed attempts | Counts consecutive wrong passwords and locks the account after a threshold, for a delay or until an administrator releases it. Protection: it defeats online guessing outright, since an attacker gets only a handful of tries regardless of how weak the password is |
| Expired passwords | Forces a change after a set lifetime and blocks reuse of recent passwords. Protection: it bounds the damage window of a password that has been stolen or leaked without anyone noticing |
Close by placing them: all three are authentication controls · the first of the six security controls in Volume I §7 · and they protect the database by keeping unauthorized access out before authorization and encryption ever come into play.
"A dataset from three different countries with varying languages, currencies and date formats." Give an ordered plan, naming the problem each step fixes:
YYYY-MM-DD). DD/MM vs MM/DD is silently destructive · 03/08 is two different days · so convert using each source's known locale, not a guess.Tie it back: this is step 2, data cleaning, of the five-step data science process in Volume I §4 · "removing errors, duplicates, spaces".
(i) Hardware required: a database server with sufficient CPU and RAM · storage (RAID disks or SSD) sized for records and images · backup storage and an off-site copy · client workstations for wards and clinics · networking · switches, cabling, Wi-Fi for mobile devices · UPS and power backup · peripherals · scanners, barcode readers, printers.
Software required: a DBMS (Oracle, MySQL, SQL Server) · an EHR application front end · the operating system · security software · firewall, antivirus, encryption · backup and recovery software · reporting and analytics tools.
(ii) Benefits · quote Volume I: reduced redundancy, data integrity, improved security, easy retrieval and multi-user access (several clinicians reading one record at once), plus the sector benefits: improved patient care, faster diagnosis, reduced errors · and records that cannot be lost or misfiled as paper can.
(iii) Implementation challenges · high cost of hardware, licences and expertise · data migration of years of paper records, which must be digitised and validated · staff training and resistance to change · data privacy and regulatory compliance (HIPAA and local law) · integration with lab and imaging systems · downtime and continuity · a hospital cannot stop · ongoing maintenance and the need for a database administrator.
The relationship is supply and consumption. A database is the organized, reliable store where data is collected and kept with controlled redundancy and integrity; data science is the discipline that extracts insight from that data using algorithms and statistical methods. Data science depends on databases as its main source · the first of its five steps is "data is collected from databases, APIs and sensors" · and without data, analysis is impossible. Conversely, data science feeds back into database design: the queries analysts run drive which indexes are built, which is exactly the physical-design phase in §3. In short: databases answer "what is stored"; data science answers "what does it mean".
Of the four components · data, relationship, constraints, schema · data is the substance and the other three exist only to serve it. Relationships describe correspondences between data elements; constraints are predicates defining the correct state of the data; the schema describes how the data is organized. Without data the database is an empty structure of no value: it is what users query, what decisions are made from, and what the whole DBMS apparatus of storage, security, concurrency and recovery is built to protect.
| Paper | Q | Topic | Answered in |
|---|---|---|---|
| DTS 304 14/07/2026 this lecturer | 1 | Hospital DBMS · hardware, software, benefits, challenges · logical independence · data as a component · consent | Vol III §8 · Vol I §10, §8, §7 |
| 2 | Databases and data science · data masking · data vs information · the data lifecycle | Vol III §8 · Vol I §7, §2, §3 | |
| 3 | Data cleaning plan · indexing at 20 M records · GPS and decision-making · classifying data formats | Vol III §8 · Vol I §5, §11, §4 | |
| 4 | ER diagram for a bank (14 marks) | Vol III §1 | |
| 5 | Committee queries (any one / at least all / exactly) · office building · password policy | Vol III §4 · §8 | |
| 6 | Phases of query processing · debugging two SQL queries | Vol III §2 | |
| 7 | Phases of data design · concurrent execution and concurrency control · define transaction | Vol III §3 · §6 | |
| CMS 445 2022/2023 | 1 | Five SQL queries on an EMP table | Vol II §3 · every one worked |
| 2 | Transaction concept, delimiters, properties, operations | Vol III §6 | |
| 3 | Functional dependency · definition, four types, three advantages | Vol III §5 | |
| 4 | Insert/update/delete anomalies · replication and fragmentation · transparency | Vol III §5 · §7 | |
| 5 | Distributed databases · advantages, comparison, homogeneous vs heterogeneous, fragmentation | Vol III §7 | |
| 6 | Commit and two-phase commit · optimistic and pessimistic locking · transaction states | Vol III §6 | |
| 7 | Define the ER model and the relational model | Vol III §8 · Vol I §9 |