Fundamentals of Databases

What Fundamentals of Databases covers

Entity relationship modelling, relational databases and their keys, normalisation to third normal form, SQL, and managing concurrent access in a client server database. Examined on Paper 2.

The 5 subtopics

Each one has its own page.

• Conceptual data models and entity relationship modellingRecall questions on entities and their occurrences, the three degrees of relationship, and resolving a many-to-many relationship with a link entity.Open →
• Relational databasesRecall questions on attributes, primary, composite and foreign keys, which table carries the foreign key in a one-to-many relationship, and referential integrity.Open →
• Database design and normalisation techniquesRecall questions on why databases are normalised, the conditions for first, second and third normal form, functional dependency, and normalising a given table.Open →
• Structured Query Language (SQL)Recall questions on SELECT, FROM, WHERE and ORDER BY, joining tables on their keys, LIKE and its wildcards, and writing INSERT, UPDATE and DELETE statements.Open →
• Client server databasesRecall questions on why concurrent access threatens integrity, the lost update problem, and record locking, serialisation, timestamp ordering and commitment ordering.Open →

What you need to know

Answer each one before you reveal it.

Take Fundamentals of Databases further

The same content, four other ways.

Commonly confused

Pairs that lose marks when they are blurred together.

• Primary key vs Foreign key

  Primary key: an attribute or set uniquely identifying each record. Foreign key: an attribute referencing a key in another table.

• Entity vs Attribute

  Entity: a category of thing about which data is stored. Attribute: a named characteristic recorded about that category.

• Locking vs Timestamping

  Locking: preventing conflict by making other transactions wait. Timestamping: letting transactions proceed and only intervening when an operation is found to be out of order.

Questions from this topic

Each one with the model answer underneath.

1. 1

   What is a data model, and what does producing one from a set of data requirements involve?

   Model answer

   A data model is a description of the data a system must hold, independent of how it will eventually be stored. Producing one from given requirements means reading the description of the scenario and identifying the entities — the distinct kinds of thing data must be kept about; the attributes of each entity — the individual items of data recorded about it; an identifier for each entity — the attribute or attributes whose values distinguish one occurrence from every other; and the relationships between the entities, together with the degree of each. The result is usually presented as an entity relationship diagram plus a set of entity descriptions.

2. 2

   What is an entity, and how does an entity differ from an occurrence of that entity?

   Model answer

   An entity is a category of thing about which data is stored — Student, Book, Booking. An occurrence, sometimes called an instance, is one particular member of that category — the student Aisha Khan, the copy of a book with accession number 41207. The entity is what becomes a table in the eventual database; the occurrence is what becomes one row in it. Entity names are written in the singular.

3. 3

   Describe how an entity relationship diagram is drawn.

   Model answer

   Each entity is drawn as a rectangle containing the entity name. A relationship between two entities is drawn as a straight line joining their rectangles, and the line is labelled with the nature of the relationship, read in a direction, for example 'a member borrows a copy'. The degree of the relationship is shown by the way each end of the line is marked: a plain single end means one, and a crow's foot — the line splitting into three short prongs where it meets the rectangle — means many. So a line with a plain end at Member and a crow's foot at Loan means one member has many loans.

4. 4

   Describe the three degrees of relationship and how each appears on an entity relationship diagram.

   Model answer

   One-to-one: each occurrence of the first entity is related to at most one occurrence of the second, and the other way round — for example Employee to CompanyCar where each employee has at most one car and each car is assigned to at most one employee. Drawn as a plain line with a single end at both entities. One-to-many: one occurrence of the first entity relates to many of the second, but each occurrence of the second relates to only one of the first — for example one Customer places many Orders. Drawn with a plain end at Customer and a crow's foot at Order. Many-to-many: occurrences on each side may relate to many on the other — for example Student to Course, since a student takes several courses and a course has several students. Drawn with a crow's foot at both ends.

5. 5

   Why can a many-to-many relationship not be implemented directly, and what is done about it?

   Model answer

   It cannot be implemented directly because there is nowhere to put the link. To connect the two tables one of them would have to hold a key value of the other, and with many on each side a single row would need to hold many such values, which breaks the requirement that each cell holds one atomic value. The relationship is therefore resolved by introducing a link entity between the two, which holds one occurrence for each pairing. The original many-to-many becomes two one-to-many relationships, one from each original entity into the link entity, and any data belonging to the pairing itself is stored there.

6. 6

   Resolve the many-to-many relationship between Student and Course, and describe the resulting diagram.

   Model answer

   Introduce a link entity Enrolment, holding one occurrence for each student taking each course. The diagram then has three rectangles: Student, Enrolment and Course, with Student joined to Enrolment and Course joined to Enrolment. Each of those two lines has a plain end at the original entity and a crow's foot at Enrolment, because one student has many enrolments and one course has many enrolments, while each enrolment concerns exactly one student and one course. The entity descriptions are Student(StudentID, Forename, Surname, TutorGroup), Course(CourseCode, CourseTitle, Level) and Enrolment(StudentID, CourseCode, DateEnrolled), with StudentID underlined in Student, CourseCode underlined in Course, and both StudentID and CourseCode underlined in Enrolment.

What is inside Levo

Six ways to work the same material.

Fundamentals of Databases revision questions

What does Fundamentals of Databases cover in AQA A-Level Computer Science?

5 subtopics: conceptual data models and entity relationship modelling, relational databases, database design and normalisation techniques, structured query language (sql) and client server databases. Each one has its own page with questions and model answers.

How is Fundamentals of Databases organised?

By the AQA 7517 specification, subtopic by subtopic, so it matches the order you are taught it in.