brightoolhub. Français

Computer science · 06 / 06

Databases: tables, keys and queries

Final-year concepts · Worked example and 4 exercises with solutions, at your pace.

Need a foundation?Binary: position determines weightAlgorithms: inputs, steps, output
UnderstandSee an exampleGuided exercise3 progressive challenges

The principle, simply

Relational tables contain rows and typed attributes. Primary keys identify rows uniquely; foreign keys relate tables. Queries filter, select columns or join relations. Conditions and cardinality determine meaning; joins may yield multiple rows per entity.

THE PICTURE TO REMEMBERDatabases: tables, keys and queries
  1. 01Table and types
  2. 02Primary key
  3. 03Relationship
  4. 04Query and cardinality

WORKED EXAMPLE

From principle to reasoning

Student has id and name; Enrolment has student_id and course_id. Filtering course_id=3 finds enrolments; joining on the matching key retrieves names.

Solve a problem in this subject

  1. Specify the contract

    Define inputs, types, outputs and forbidden cases. State whether a search algorithm requires sorted data.

  2. Trace a small example

    Tabulate variables, tested conditions and actions using a small hand-checkable example.

  3. Test boundaries

    Consider empty inputs, one item, duplicates, missing values and numeric limits where relevant. Define the output or explain rejection.

  4. Justify

    Explain termination and correctness. Distinguish operation counts, measured time and memory use.

YOUR TURN

01 · Try before checking

Why is name alone often a poor primary key?

One hint

Different people can share a name.

Reveal and understand the solution

Names lack guaranteed uniqueness and can change. Use a suitable stable unique nonnull identifier.

Compare method, conditions and answer; identify the exact part to revisit.

IMPROVE THROUGH PRACTICE

Three additional challenges with solutions

Start with the first challenge. Write your method before opening a hint; use the solution to find the first step that needs work.

Reflect after practising
02 · Recognise and understandLink to this exercise

A Student table has id, name and class. Why is name alone a poor key?

A hint to get started

People can share a name.

Step-by-step solution
  1. A key must uniquely identify a row.
  2. Duplicate names make name nonunique.
  3. A suitable unique identifier can serve as primary key.

Which step did you miss? Retry without help, then explain why the method works.

03 · Apply the methodLink to this exercise

What does SELECT nom FROM Eleve WHERE classe = '2A' return?

A hint to get started

Separate selected columns from row filtering.

Step-by-step solution
  1. WHERE keeps rows whose class is 2A.
  2. SELECT nom returns their name column.
  3. This query reads data without modifying rows.

Which step did you miss? Retry without help, then explain why the method works.

04 · Go furtherLink to this exercise

Why can a Student–Class join duplicate results when the Class key is not unique?

A hint to get started

Every matching pair can produce a result row.

Step-by-step solution
  1. One student may match multiple Class rows.
  2. The join returns each matching combination.
  3. Check keys, cardinalities and join conditions rather than arbitrarily removing duplicates.

Which step did you miss? Retry without help, then explain why the method works.

The trap to avoid

Foreign keys do not automatically imply one-to-one relationships. This lesson explains models, without a live database.

Without notes, can you…

  • Explain the principle in your own words?
  • Rebuild the diagram and its connections?
  • Solve the exercise and justify the method?

Choosing a state saves it in this browser. This is your assessment, not a grade.

Back to the subjectMy notebook