Databases, spreadsheets and websites
Practical skills for databases, spreadsheets and website authoring: data types, keys and queries; formulas, functions and cell references; and HTML and CSS.
📌 Key points
- A database table is made of records (rows) and fields (columns); a primary key uniquely identifies each record, and a foreign key is a field that links to the primary key of another table.
- A flat-file database has one table; a relational database has linked tables, which reduces data duplication. Field data types: text/alphanumeric, numeric (integer, decimal, currency, percentage), date/time and Boolean/logical.
- Database queries search using criteria (AND, OR, NOT, LIKE with wildcards) and results can be sorted in ascending or descending order; calculated fields work out new values; reports present the results.
- In a spreadsheet, a formula starts with = (e.g. =B2*C2); functions are built-in formulas such as SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTIF, IF, ROUND, INT and VLOOKUP.
- A relative cell reference (e.g. B2) changes when a formula is replicated; an absolute cell reference (e.g. $E$1) stays the same; a named cell or range makes a formula easier to understand.
- Spreadsheets are used for modelling ("what if" questions). Choose charts carefully: line graph for trends over time, bar or column chart to compare categories, pie chart for parts of a whole; add a title, axis labels and a legend where needed.
- Web pages have three layers: the content layer (HTML: the structure and content), the presentation layer (CSS: the formatting and style) and the behaviour layer (scripts, e.g. JavaScript, for interactivity).
- The HTML head section holds the page title (shown in the browser tab), metadata and links to stylesheets; the body section holds the content that is displayed.
- An external stylesheet (.css file) can style every page of a site consistently; if styles conflict, an inline style overrides an internal style, which overrides an external one.
- Hyperlinks can go to another page, a bookmark on the same page, an email address (mailto:) or open in a new window or tab (target _blank). Relative file paths should be used for files within the site so the links still work when the site is uploaded to a web server.
📖 Key terms
- Primary key
- A field that uniquely identifies each record in a table.
- Foreign key
- A field in one table that links to the primary key in another table.
- Absolute cell reference
- A cell reference that does not change when the formula is replicated, e.g. $B$1.
- Relative cell reference
- A cell reference that changes when the formula is replicated, e.g. B1.
- Function
- A ready-made formula in a spreadsheet, e.g. SUM or AVERAGE.
- Presentation layer
- The CSS that controls how a web page looks.
- Relative file path
- A link to a file given in relation to the current page's location.
✏️ Worked example
Cell E1 holds the exchange rate. B2:B20 hold prices in dollars. Write a formula for C2 to convert B2, which can be replicated down to C20.
- The price must change for each row, so B2 is a relative reference.
- The exchange rate is always in E1, so it must be an absolute reference: $E$1.
- Formula in C2: =B2*$E$1
- Replicating it gives =B3*$E$1 in C3, =B4*$E$1 in C4, and so on down to =B20*$E$1 in C20.
If a formula refers to one fixed cell (a rate, a price, a target), use an absolute reference before you replicate it. Forgetting the $ signs is the most common spreadsheet mistake.
Test yourself8 questions from a bank of 17. A new mix every time.
Start the quiz →