Databases · Week 1 practice

Answer: the correct one is “a collection of data, typically describing the activities of one or more related organisations”. Of the others, “software designed to assist…” defines a DBMS (the software, e.g. PostgreSQL), “a set of tuples and attributes” is roughly a table/relation, and “a blueprint…” is a data model. The distinction the slides draw: the database is the data; the DBMS is the software that manages it. pgAdmin is neither: it's a client program that talks to the DBMS.

The graph model returns in Week 5 with Neo4j/Cypher. The slide's Table vs Graph example shows the same student data both ways: in the table, “Module Lead: Madasah Shah” is repeated on every row; in the graph, it's one Module node with one hasLead edge, and every student just has an isEnrolledIn edge to it.

1. Data organisation and management. Without: scattered, partly unstructured files; finding one member's full history means searching three places by hand. With: a defined schema (tables with set columns) makes storage and retrieval systematic: one query answers it.
2. Reduced redundancy and inconsistency. Without: a member's phone number sits in all three records; update one, forget another, and the data now contradicts itself. With: stored once, centrally, and linked by a member ID.
3. Concurrent (multi-user) access. Without: two receptionists edit the bookings file at once and one save overwrites the other. With: concurrency control lets many users read and write simultaneously without conflicts.
4. Data integrity and accuracy. Without: nothing stops a duplicate member ID or a negative payment. With: constraints, validation rules and data types enforce rules like “member ID must be unique”.

Answer: Securing. The easy-to-mix pair: Protecting = against software or hardware malfunction (crashes, disk failure); Securing = against unauthorised or malicious access. The full seven: Defining (encoding the structure and constraints), Constructing (storing the data on a medium the DBMS controls), Manipulating (updating, querying, aggregating), Sharing (concurrent or sequential access by many users and apps), Protecting, Securing, Maintaining (evolving the database over its lifecycle).

Answer: Defining: encoding the database structure and constraints. Inserting the rows afterwards would be Constructing (storing the data); SELECT/UPDATE would be Manipulating.

Answer: The slides give three cases where a DBMS may not pay off: very small/simple data (to-do lists, notes, where the overhead outweighs the benefit), highly unstructured or rapidly changing schemas (a relational schema is too rigid), and sometimes purely archival / bulk read-only data (optimised file formats or distributed file systems can be more efficient).

Answer: Edgar F. Codd (IBM), 1970. The slide's timeline: IBM's Information Management System (IMS), 1960, widely used; Bachman's first general-purpose DBMS at General Electric (the slide dates it 1970; most sources say early 1960s, I'd go with the slide in the test); Codd's relational model, 1970; through the 1980s the relational model spreads and SQL becomes the standard. Codd also introduced normalisation (Week 2).

A data model is a blueprint that defines how a database organises, stores and manipulates data, while hiding low-level details such as how bytes are laid out on disk. Its three purposes:

• Abstraction: hides physical storage so you can focus on the logical structure.
• Organisation & storage: defines how data elements relate (tables, keys, fields).
• Operations: specifies the rules and commands to query, insert, update and delete.

The course focuses on the relational data model. The graph model (Week 5) is a different data model for the same job.

Answer: The slide's key line: the schema is NOT the data, only a description of it, i.e. metadata. In the movies example, ACTOR(Name, Surname), DIRECTOR(Name, Surname), MOVIE(Title, Genre, Director, Leading actor) is the schema; “Steven Spielberg” or “Citizen Kane” would be data. (Week 2 adds a second meaning: a PostgreSQL schema such as public is a named container of tables inside one database.)

(a) table → relation; column → domain (informally an attribute); row → association, case or tuple (informally a record). A relational database is a collection of n-ary relations stored as tables; n-ary means each relation links n values.

(b) 3 attributes (Surname, Name, Age), 3 tuples.

(c) Attributes that link the tables: Movies' DirectorSurname and LeadingActorSurname repeat values from Directors and Actors, so you can navigate from a movie to its director. CARS and CROPS share nothing, so there's no way to move between them. Those linking attributes become foreign keys in Week 2.

(a) Storing the same information more than once.

(b) Repeating data (the same data in multiple places); storing derived attributes (they add no new data, a form of aggregation, but can be handy); adding checksums.

(c) Storage is inefficient (memory wasted) and management is error-prone: you must update several entries, so you're exposed to inconsistencies.

(d) Redundancy you add deliberately because it pays for itself: to protect against attacks/corruption (checksums), to save time when derived attributes are expensive to compute, and to improve query performance. Example: storing an order's total rather than summing its lines every time it's displayed.

1. Requirements analysis: what to store? → (iii)
2. Conceptual design: high-level blueprint → (ii), the ER model
3. Logical design: data strategy, DBMS selection → (i)
4. Schema refinement: optimisation and integrity → (iv), normalisation (Week 2)
5. Physical design: hardware and storage tuning (indexes, disks)
6. Application integration & security: access and governance → (v)

(a) A directory of information (metadata) about the data sets, files and databases managed by the DBMS: table names, column types, constraints, owners. The odd part: the catalog is itself a database, and managing it is complicated enough that some DBMSs have dedicated catalog managers. (psql's \dt and \d are queries against PostgreSQL's catalog.)

(b) An access to a subset of the information in the database; in practice a virtual table built from the base tables. Its content is often not stored permanently but computed on the fly; it can show data stored in base tables and/or data derived from them.

(c) From a booking's flight number you know the airline and the departure/destination countries, which is enough to look up the default allowance. A view such as FlightAllowance(FlightNumber, NumHandbags, SizeHandbags, NumCheckedBags, MaxWeight) works this out whenever it's queried, without storing a redundant allowance on every booking. (You'll write CREATE VIEW on the SQL page.)

(a) A unit of work is any manipulation of the database (e.g. a collection of reads and writes) that is conceptually one thing. A transaction is a unit of work executed by a DBMS: its operations must all happen or none, otherwise the database can be left inconsistent.

(b) It's several steps: check credit, check daily limit, check the ATM has cash, subtract the amount, dispense. If only some happen (balance reduced but no cash dispensed) the data is wrong.

(c)

• Atomic: treated as one unit; it either works completely or fails completely.
• Consistent: leaves the database correct, so an illegal transaction can't corrupt it. (It doesn't promise a transaction is logically correct; incorrect ones simply fail.)
• Isolated: even when run concurrently with others, the result is as if it had run alone.
• Durable: once successful, its effects survive a system failure (power cut, crash).

Answer: Isolation: the result should be the same as if each transaction had run alone, so no one should see another's half-finished state. If instead the transfer itself had stopped halfway and stayed that way, that would be Atomicity. If a committed transfer vanished after a crash, Durability.

(a) CHECK   (b) NOT NULL   (c) UNIQUE   (d) FOREIGN KEY (it “prevents actions that would destroy links between tables”)   (e) DEFAULT   (f) PRIMARY KEY, which the slide describes as a combination of NOT NULL and UNIQUE   (g) CREATE INDEX (strictly a performance structure, not a rule about values).

A constraint is a restriction on which values may be inserted into a relation. Fingerprint image: it must be unique, so it is mathematically usable as a key, but images are slow to read and compare, so it's a poor practical choice. Use a short ID and keep the image as an ordinary attribute.

1. Guideline 1: attributes with clear semantics → (a): which surname is the director, which the actor? Fix: DirectorSurname, LeadingActorSurname.
2. Guideline 2: do not repeat information → (b): Bloom's name is stored per movie (update anomaly risk), and adding an actor with no movie forces a NULL Title, an insertion anomaly.
3. Guideline 3: avoid attributes in a base relation whose values may frequently be NULL → (c): you can always merge tables into one big one, but NULLs waste memory, blur what attributes mean, and complicate aggregation (operations have to handle NULL). You can always split instead (the slide splits PHONE_DIRECTORY's sparse Landline/Mobile the same way).
4. Guideline 4: avoid generating spurious tuples → (d): see the next question.

A spurious tuple is a false row produced when tables are joined back together that wasn't in the original data. A × B (the Cartesian product) pairs every element of A with every element of B, so it has |A| × |B| rows.

(a) 2 × 3 = 6 tuples: (a,1), (a,2), (a,3), (b,1), (b,2), (b,3). C contains 4 of them, so the spurious ones are (a,2) and (b,3).

(b) 3 × 2 = 6: (x,1), (x,2), (y,1), (y,2), (z,1), (z,2). Spurious: (x,2) and (y,1).

(c) Scenario 1: store only A and B and build C on the fly by combining them, which saves memory but produces spurious tuples you must filter “manually”. Scenario 2: store C explicitly as well, which costs memory but has no spurious tuples. Scenario 2 is the better design. On the SQL page, CROSS JOIN is exactly this A × B.

Answer: an insertion anomaly (slide: “insertion anomaly due to adding unwanted NULL values”). Week 2 covers all three anomaly types in detail.

Answer: double ellipse (e.g. a researcher's Department). The rest of the notation: entity type = rectangle; attribute = ellipse; relationship = diamond; derived attribute = dashed ellipse; key attribute = underlined name; composite attribute = an ellipse with its parts attached as further ellipses.

The slide draws FirstName and LastName as two separate simple attributes. You could also model “Name” as composite; just be consistent. A simple/atomic attribute can't be divided; a composite one splits into parts with independent meanings. A single-valued attribute has one value per entity (one BirthDate); a multivalued one can have several.

(a) Both ProgramID and CallYear are underlined: together they form the key, because the combination is unique (program 7 runs a call every year; 2026 has calls from many programs). The slide's general point: there may be more than one key attribute.

(b) e.g. an exam sitting identified by (module_code, academic_year); a seat identified by (row, seat_number) in one venue. In SQL this becomes a composite primary key PRIMARY KEY (program_id, call_year). Composite keys are also where partial dependencies (Week 2's 2NF) can happen.

Cardinality = how many instances can take part in the relationship.

A single column holds one value, not a list.

• Attempt 1, module_id in STUDENT: fine if a student takes one module, but they take several. You'd need module_id_1, module_id_2, …, and how many? That's the repeating-group problem Week 2's 1NF forbids.
• Attempt 2, student_id in MODULE: the same problem in reverse; a module has many students.

The junction table ENROLMENT(student_id FK, module_id FK, enrolled_on) has one row per (student, module) pair. In the lecture's words, it turns one hard problem (many-to-many) into two easy ones (one-to-many, twice): one student → many enrolment rows; one module → many enrolment rows. It's also where attributes of the relationship itself go (enrolled_on, a grade).

(a)

Entities
  MEMBER (member_id, full_name, email, date_of_birth, membership_type, [age = derived])
  WALL   (wall_id, name, height_metres)
  ROUTE  (route_id, grade, colour, set_on)

Relationships
  WALL  —has—        ROUTE    1:M
  MEMBER —completes— ROUTE    M:M

Keys: the scenario gives none for member or route, so introduce surrogate IDs (member_id, route_id), the same move the Week 2 slides make with StudentID. email and wall name are unique too. Age would be derived from date of birth, so don't store it.

(b)

member (member_id PK, full_name, email UNIQUE, date_of_birth, membership_type)
wall   (wall_id PK, name UNIQUE, height_metres)
route  (route_id PK, grade, colour, set_on, wall_id FK → wall)        -- 1:M, FK on the many side
ascent (member_id FK → member, route_id FK → route, completed_on,     -- M:M junction table
        PRIMARY KEY (member_id, route_id))

Where should a completion date go? It belongs to the pair (this member, this route), so it can only live in the junction table. Q28 and Q30 write this same model in JDL.

Answer: JDL = JHipster Domain Language. You describe entities (like a Java/UML class with attributes but no operations) and relationships as text; JHipster (a web application stack generator, run with the jhipster CLI) generates the database tables, backend, APIs and basic frontend from it. Reasons the slides give for JDL: text-based, easy to modify, version-control friendly, less ambiguous than hand-drawn diagrams, generates code, integrates with JHipster. It bridges the gap between an ER diagram (data only) and a real app (behaviour, APIs, services, frontend). Try it at JDL Studio.

(a) BigDecimal, exact decimals; never Float/Double for money, which can't store 0.10 exactly (the same reason you'd use BigDecimal for money in Java). (b) LocalDate, a date without time zone. (c) TextBlob, a large block of text. (d) ImageBlob. (e) Boolean. (f) Instant, a moment in time (or ZonedDateTime if the time zone matters). (g) Integer (Long for huge counts). (h) UUID.

Types are capitalised class-like names. The real parser rejects int or string: type names must start with a capital letter.

enum MembershipType {
  STUDENT, STAFF, PUBLIC
}

entity Member {
  fullName String required maxlength(60),
  email String required unique,
  dateOfBirth LocalDate,
  membershipType MembershipType required
}

entity Wall {
  name String required unique,
  heightMetres Float min(3) max(20)
}

Each field is fieldName Type [validations]. Modifiers from the slides: required (can't be null/empty), unique, minlength(n)/maxlength(m) for strings, min(x)/max(y) for numbers. An enum is a fixed set of named values, used as a field's type. No id field: JHipster adds id Long automatically (shown as “id : Long (PK)” in JDL Studio). I parsed this with the JHipster JDL parser to check the syntax (it also confirmed the commas between fields are optional, as on slide 67).

Answer: JDL has no List or Set field types: “use relationship instead”, e.g. a Department entity and relationship ManyToMany { Researcher{departments} to Department{researchers} }. Storing them comma-separated in one String “works” but breaks 1NF (Week 2): one cell holding several values.

relationship OneToMany {
  Wall{routes} to Route{wall}
}

relationship ManyToMany {
  Member{completedRoutes} to Route{completedBy}
}

Syntax: relationship TYPE { EntityA{fieldOnA} to EntityB{fieldOnB} }. The name in {} is the field generated on that side.

For OneToMany, the “one” entity is written first. Plural field names on both sides are the giveaway for ManyToMany. Syntax checked with the JDL parser. (The exact generated join-table name depends on the JHipster version; I'd want to verify that detail.)

Rule: put {name} only on the side your code actually needs to navigate from. Ask: will my code ever say thisEntity.getOtherEntity()? The getters are called on objects (lowercase student), not the class (Student), which is the lecture's “car vs myCar” point.

1. string → String. Type names must start with a capital (real parser error: “typeName must match /^[A-Z][A-Za-z0-9]*$/, got string”).
2. List<String>: JDL has no List/Set types (syntax error on <). Make OfficeHour an entity with a relationship.
3. int → Integer, same capitalisation rule.
4. The relationship is reversed. OneToMany { Module … to Lecturer … } says one module has many lecturers. It should be Lecturer{modules} to Module{lecturer}: the “one” side first.

Fixed version:

entity Lecturer {
  name String required
}
entity Module {
  code String required unique,
  credits Integer
}
relationship OneToMany {
  Lecturer{modules} to Module{lecturer}
}

(Errors 1–3 confirmed by running the JDL parser; the fixed version parses.)

The lecture's test of understanding: if you can draw the ER diagram you should be able to predict the JDL, and from the JDL predict the generated code. (Set<Module> is a Java collection with no duplicates; it's why JDL itself never needs a List type.) The Java/JPA mapping returns in Week 4 (ORM, Hibernate).