Detailed · Deep study
1Creating the structure
- Import data from existing files (.csv, .txt) to create a table, using the specified field names; check the first row is used for field names if needed.
- Set appropriate data types for each field and their sub-types and display formats (below).
- Create and edit primary and foreign keys, then create relationships between tables (usually one-to-many).
| Data type | Use | Display formats |
|---|---|---|
| text | letters, symbols and numbers not used in calculations (names, phone numbers, IDs) | field size (length) |
| numeric: integer | whole numbers | – |
| numeric: decimal | numbers with a fractional part | number of decimal places; percentage |
| numeric: currency | money | currency symbol and 2 decimal places |
| date/time | dates and times | e.g. short date DD/MM/YYYY, long date, time |
| Boolean/logical | only two values | yes/no, true/false or a checkbox |
2Keys and types of database
| Key | Characteristics |
|---|---|
| primary key | a field that holds a unique value for every record, so each record can be identified; cannot be empty |
| foreign key | a field in one table that is the primary key of another table; it links the tables and need not be unique in its own table |
| Flat file database | Relational database | |
|---|---|---|
| structure | one table holding all the data | two or more tables linked by key fields |
| advantages | simple to set up and use; fine for small amounts of data | little duplicate data, so less storage and fewer inconsistencies; easier to update; more flexible queries |
| disadvantages | data is repeated (redundancy), wasting space and causing errors when updating | more complex to design and set up; needs more skill |
3Data entry forms
A data entry form lets users add or edit one record at a time. Create it with the specified fields, appropriate font styles and sizes, sensible spacing between fields, suitable character spacing (box width) for each field, plenty of white space, and controls such as radio buttons, check boxes and drop-down menus.
- Good form design: a clear title; instructions; fields in a logical order; clear labels next to each box; boxes the right size for the data; controls that restrict choices (fewer errors); not cluttered; consistent fonts and colours; navigation buttons (next, previous, save).
4Exam focus
Key words: import; field; data type; integer; currency; Boolean; primary key; foreign key; relationship; flat file; relational; data entry form.
Quick self-check
Answer in your head first, then reveal.
Primary key uniquely identifies each record in its table (1); foreign key is a field that is the primary key of another table, used to link them (1).
Any two: less duplication; less storage; fewer inconsistencies; easier to update (2).
Any three: clear title; labels; logical order; suitable box sizes; drop-downs/radio buttons; white space (3).
