Multiple choiceQuestion 1 · Recall
Loading…
By the end you can name the main tools found inside a DBMS, say what each one is for, and explain how a real organisation would use them when building and running a system.
A DBMS such as MySQL, SQL Server, Oracle or Microsoft Access is a package. The database engine stores the data and enforces the rules; around it sit tools for the people who design, build, use and maintain the system.
Different people use different tools:
| Person | Main tools |
|---|---|
| database designer / developer | developer interface, data modelling tools, data dictionary, query builder |
| office staff entering data | forms |
| managers reading results | reports, charts |
| database administrator (DBA) | security, backup and recovery, import/export, monitoring |
Developer interface. The screen or command line where the developer creates tables, writes SQL, tests queries and sees errors. Everything else in this lesson is reached through it.
Data dictionary. The DBMS's own record of the database: every table, every field, its data
type and size, which fields are keys, which are NOT NULL, the validation rules, and who may
access what. It is metadata — data about the data. Because the DBMS keeps it, the schema and
the programs cannot drift apart.
Data modelling tools. Diagram tools for designing the structure before it is built, usually entity–relationship (E–R) diagrams: entities such as Customer and Order, their attributes, and the relationships between them. Designing on paper (or on screen) first avoids rebuilding tables later.
Query builder / query designer. A graphical way of writing a query: pick the tables, drag the fields, set criteria, and the tool generates the SQL. Useful for occasional users; the SQL it produces can be inspected and edited.
Forms. Screens for entering and viewing one record at a time, with labels, drop-down lists and validation. Forms reduce typing errors and hide fields the user should not see — a clerk entering an order does not need the customer's medical notes.
Report generator. Produces formatted output — grouped, sorted, totalled, with headings and dates — for printing or export. A monthly sales report is a saved report, not a new program.
Security management. Creates user accounts and roles and grants rights per table, per field or per action (read, insert, update, delete). This is where the DBMS's central control of access actually happens.
Backup and recovery tools. Schedule full and incremental backups, and restore the database after a failure using the backup plus the transaction log, which records every committed change.
Import and export utilities. Move data in and out in standard formats (CSV, SQL scripts, spreadsheets) — for example loading a supplier's price list, or sending results to a spreadsheet for a presentation.
Macros and automation. Stored sequences of actions (open a form, run a query, print a report) that run at the click of a button, so routine tasks do not need a programmer.
Think of a library. The books are the data; the catalogue is the data dictionary. Without the catalogue you can still read a book you are holding, but you cannot find anything, check what exists, or know who may borrow it.
Because the DBMS keeps the catalogue itself, every tool can rely on it: a form is built from the dictionary's field list, a query checks the field names against it, and security is applied to the objects it describes.
Worked example: Worked example: a gym's membership system
A gym wants a database for members, classes and bookings. Describe how it would use the tools of a DBMS from design to daily operation.