Objective and starting point
A relational table represents one kind of entity using rows and typed columns. A row might represent a workshop, while columns record its title, tutor identifier and capacity. Begin a query by stating what one input row represents and what one output row should represent. Those are not always the same. This lesson assumes only that you can read a short list of records; its terms are defined here. You will distinguish a key from an ordinary attribute, explain a duplication problem, and follow the conditions in a query before deciding what it returns.
Keys and a reason to separate entities
A primary key uniquely identifies a row in its table. A foreign-key column can refer to a related row in another table. Many child rows may refer to the same parent: repeated references do not make the children identical. Where an appropriate foreign-key constraint is defined, the database can reject a reference to a nonexistent parent. Normalization is a design process that reduces duplicated facts and supports data integrity. It does not mean splitting every value into an unrelated table. Decide which entity owns an attribute, then preserve the relationship needed to retrieve it.
Worked example: one tutor, several workshops
Fictional teaching case: a studio records (WorkshopCode, Title, TutorId, TutorName) in each workshop row. Its two workshops W8 and W9 both name tutor T4, so the tutor’s display name appears twice. When that tutor changes their name, updating only W8 would leave two conflicting versions of the same tutor fact. Separate Tutor(TutorId, TutorName) from Workshop(WorkshopCode, Title, TutorId). Store the tutor name once and keep the reference in each workshop. TutorId identifies a tutor; WorkshopCode identifies a workshop. The repeated T4 in Workshop is valid because the two workshop rows represent different workshops. Check the proposed repair by changing the single TutorName value and following each workshop’s reference: both now lead to the same current tutor record. The repair addresses duplicated tutor facts, not a promise that every possible query becomes faster.
Read the operation before predicting the result
SELECT retrieves data, and its column list determines what is shown. WHERE determines which rows qualify. ORDER BY specifies an output order; without it, do not infer a guaranteed sequence. For the studio, SELECT Title FROM Workshop WHERE TutorId = 'T4' asks for titles of workshops belonging to that tutor, not the tutor’s own details. A JOIN can relate Workshop to Tutor through the declared key condition when both kinds of detail are needed. Count each matching pair; do not silently replace repeated matches with distinct parent keys. Separately, CREATE and ALTER define or change objects, while INSERT, UPDATE and DELETE change rows. These names identify different actions rather than interchangeable ways to ask a question.
Useful objects and their limits
A view exposes the result of a SELECT query as a virtual table. A stored procedure packages SQL statements that can be executed, potentially with parameters. An index can help find rows but adds storage and maintenance work as data changes. None of these labels proves that a query returns the required rows. First verify the logical result, then evaluate any performance design separately. We interpret tiny complete fictional datasets here; we do not run statements against a tenant or a real organizational database.
Transfer check
Try this before reading the basis: a sports club stores CoachPhone separately in every training-session row. Two sessions share the same coach, and one phone copy becomes outdated. What relationship should a repair preserve, and which fact should have one authoritative home? Check basis: identify the coach separately, keep one current phone attribute with that coach, and retain the session-to-coach reference. Different sessions still need separate session identities. Then attempt the two linked SQL tasks without looking at their explanations. For each, state the qualifying rows or matching pairs and explain why the tempting alternative counts something else.
Try it, then explain it.
Complete a short set without looking at the example. After each answer, explain which evidence or rule supports your decision and why the alternatives do not fit.
Sources & authorship
Original OpenExam explanations and examples. References support the concepts; they are not the source of copied questions. Independent educator review is pending.
Describe database objects - Training | Microsoft LearnUnderstand normalization - Training | Microsoft LearnUnderstand relational data - Training | Microsoft LearnExplore SQL - Training | Microsoft Learn