Learn
9.1 | DEFINING A DATABASE
01 | A LIBRARY NEEDS TO ANSWER QUESTIONS
A school library has hundreds of books. The librarian needs to know each title, its category and how many copies the school owns. A long paragraph of notes would make questions such as “Which fiction books have at least three copies?” awkward to answer.
A database is an organised collection of related data that can be stored, accessed and updated. Organisation matters: the data follows a structure, rather than being an unexplained pile of values.
For this topic, we focus on a single-table database. Its data is organised into one table, with each row describing one item and each column recording one kind of information about those items.
02 | BEGIN WITH THE STORAGE REQUIREMENTS
Storage requirements describe what information the system must keep. They are the starting point for defining the table, not something to guess after choosing column names.
Our library needs a book identifier, title, category and owned copy count. Each record represents one catalogue entry for a title and edition, not each individual physical copy. CopiesOwned stores how many physical copies belong to that entry.
Stating what one row represents prevents an important design mistake. If the requirement were to track every physical copy’s loan status, this table would not contain enough detail.
| Requirement | Information to store |
|---|---|
| Refer to a catalogue entry | BookID |
| Display its name | Title |
| Distinguish the chosen categories | Category |
| Know the number owned | CopiesOwned |
03 | SEE THE SINGLE TABLE
The Books table contains four catalogue records. Its headings specify the kinds of data stored. The example identifiers and titles are fictional.
Read across B003: the title is “A Journey to Mars”, its category is Fiction and the school owns four copies. Read down CopiesOwned to compare the same kind of fact across entries.
| BookID | Title | Category | CopiesOwned |
|---|---|---|---|
| B001 | The Coding Club | Fiction | 3 |
| B002 | Ocean Science | Non-fiction | 2 |
| B003 | A Journey to Mars | Fiction | 4 |
| B004 | Robots at Work | Non-fiction | 1 |
04 | A FIELD IS ONE KIND OF INFORMATION
A field is a named item of information in the table’s structure, represented by a column. Title is a field; CopiesOwned is another.
Each record has a position for every defined field. A field value is the actual data in that position: for B003, CopiesOwned has value four.
Do not call four a field name or call Title a record. A heading names the kind of information; a cell supplies its value for one record. The same field should have a consistent meaning across the table.
05 | A RECORD DESCRIBES ONE ITEM
A record is the collection of field values describing one item, represented by a row. The B003 record contains its identifier, title, category and copy count together.
A record is not only its title or identifier. Those are individual values within it. The record combines related facts about the same catalogue entry.
The column headings are not an extra book record. A table can have many records without changing its number of fields. Adding a fifth book adds a record, not another Title field.
| Term | In the example |
|---|---|
| Table | Books |
| Field | Category |
| Record | B003, A Journey to Mars, Fiction, 4 |
| Field value | Fiction within B003’s Category field |
06 | EXPLORE A FIELD AND A RECORD
Choose a record and field below. The selected row shows the complete record, the selected column shows the field, and their intersection shows one field value.
| BookID | Title | Category | CopiesOwned |
|---|
The controls inspect this supplied example table; they do not connect to a live database. The next lessons develop data types, primary keys and SQL queries.
07 | DEFINE FIELDS WITH CLEAR MEANINGS
Defining a database means specifying an appropriate structure from the given requirements. Choose fields that keep the required facts, with names that describe their roles.
CopiesOwned is clearer than Number because it states what is counted. Title is separate from Category because they answer different questions. Avoid a single Notes field containing all the information as a paragraph when the system must search and compare individual facts.
Unnecessary fields create work and can collect information the system does not need. A catalogue of books does not require students’ home addresses unless a separate, justified requirement exists.
08 | USE A SIMPLE STRUCTURE DESCRIPTION
A structure definition specifies the fields and their intended properties. It differs from the stored data: “CopiesOwned is a whole-number count” is a rule, while four is a value in one record.
The outline below supports the stated school-library task. This school chooses to own between zero and twenty copies of an entry; that limit is a scenario requirement, not a universal rule for libraries.
| Field | Purpose | Proposed rule |
|---|---|---|
| BookID | Catalogue-entry identifier | Required; pattern B followed by three digits |
| Title | Book title | Required; length 1 to 80 characters |
| Category | Classification used by this school | Fiction or Non-fiction |
| CopiesOwned | Whole-number count | INTEGER from 0 to 20 inclusive |
09 | VALIDATION CHECKS WHETHER DATA FITS RULES
Validation checks whether data satisfies specified rules before it is accepted. It helps reject unsuitable values, such as minus two copies or a missing title.
Validation does not prove the value is true. Entering three copies when the library really owns four can pass a zero-to-twenty range check. It is plausible but inaccurate.
A good design links each validation rule to a requirement. Do not merely add checks because their names sound useful. Consider whether zero is allowed, which fields are required and what formats the system expects.
10 | MATCH THE CHECK TO THE FIELD
A presence check requires a value. A length check limits the number of characters. A type check tests the expected kind of data. A range check checks permitted limits. A format check checks the arrangement of characters.
BookID B003 passes the proposed format, while C003 and B03 do not. Title “Robots at Work” passes the length rule. Category can use a permitted-value list so spellings remain consistent.
A type and range check complement each other: 2.5 is within zero to twenty but is not a valid INTEGER copy count. A length check alone would not establish the B-plus-three-digits identifier pattern.
| Input | Relevant rule | Outcome |
|---|---|---|
| CopiesOwned = -1 | Range 0 to 20 | Reject |
| CopiesOwned = 2.5 | INTEGER type | Reject |
| CopiesOwned = 20 | Type and inclusive range | Accept |
| BookID = B03 | B followed by three digits | Reject |
| Empty Title | Presence | Reject |
| Category = Fiction | Permitted category | Accept |
11 | TRY CHECKING A PROPOSED RECORD
Enter a proposed record and check it against the four rules. Try the boundary copy counts zero and twenty, then an invalid count or a missing title.
This activity validates a proposal but does not save or add it to the table. It checks the stated field rules only. Identifier uniqueness and primary keys are explored in the next lessons.
12 | VALIDATION AND VERIFICATION ARE DIFFERENT
Verification checks that data has been copied accurately from its source. Comparing the entered copy count with the librarian’s stock record is a verification task.
A count of three can pass validation but fail verification if the source says four. A correctly copied minus one can match its source yet fail the application’s non-negative-count validation.
Neither process guarantees the source itself is true. Databases help organise and check information, but their outputs still depend on suitable and accurate input.
13 | DISTINGUISH DATABASE DATA FROM ITS SOFTWARE
A database management system, or DBMS, is software used to create, store, retrieve and manage database data. The database is the organised data; the DBMS is the software managing it.
A form can offer controls for entering a record. A query can ask for matching records, such as Fiction entries with at least three copies. A report can present results for a reader.
In the example, B001 and B003 match that question. You do not need SQL syntax yet to understand the requirement: the Category and CopiesOwned fields make the answer possible.
14 | REVIEW THE DESIGN AGAINST THE REQUIREMENTS
Can the Books table identify a title and edition, show its category and count owned copies? Yes, its fields support those storage requirements.
Can it tell which student borrowed physical copy number two yesterday? No. That information is not recorded. A well-designed table is suitable for its stated purpose; it does not automatically solve every related library task.
For this syllabus, practise defining a single-table design. More complex real-world systems may require several related tables, but that is beyond the single-table focus here.
Primary keys will add a clear way to uniquely identify records. For now, recognise why titles alone can repeat and why an identifier field is useful.
15 | EXPLAIN YOUR FIELD CHOICES
A useful explanation is: “CopiesOwned stores the number of copies for each catalogue entry, therefore the library can identify entries with several copies. An INTEGER and a non-negative range suit that count.”
When given a scenario, state what one record represents, list the required fields, choose clear names and propose checks tied to the requirements. Distinguish a field name, a record and an actual field value.
Common mistakes include counting headings as records, storing several different facts in an unexplained Notes field, claiming validation guarantees truth, or choosing a structure that cannot answer the required questions.
The next lesson focuses on suitable database data types.