Computer Science › Section J · Database systems › Topic J.1
Database fundamentals
A modern organisation runs on shared data: one record of each customer, used by everyone who needs it. Database systems make that sharing possible while controlling who may see what.
One fact, many copies
Picture a small regional theatre before it had a database. The box office keeps a file of ticket buyers, the fundraising office keeps its own list of donors, and the volunteer who posts the newsletter keeps a third list of addresses. When a patron moves house, three separate records must change, and if one is missed the theatre now holds two different addresses for the same person. Worse, nobody can easily answer a question that crosses the files, such as which donors have not bought a ticket this year. Duplicated, disconnected files waste effort and breed inconsistency.
What makes a database different
A database is an organised collection of data whose entries are linked, so the same information can be viewed from many perspectives. A flat file, by contrast, holds records in one arrangement that suits one purpose, such as a mailing list sorted by surname. An integrated database stores each patron once, each performance once, and the links between them: who booked which seat, who gave which gift. From those links the theatre can list every show a patron has seen, every patron at a given show, or every donor who also subscribes. Many websites are, at heart, a friendly front end to such a database.
The database management system
Software that uses a database is usually split into two layers. The application layer talks to the user: a booking website, a staff screen or a report generator. The database management system (DBMS) is the layer that actually stores, searches and changes the data. The application never touches the stored files directly. It asks the DBMS for what it needs, phrased in terms of how the data looks conceptually, and the DBMS works out how to fetch or update it on the storage devices. PostgreSQL, MySQL and SQLite are widely used DBMSs. The split is another case of abstraction.
Worked example: following one request
A patron books seat F12 for Saturday's show on the theatre's website. Steps: (1) The browser sends the request to the web server, which runs the application software. (2) The application asks the DBMS whether F12 is free for that performance. (3) The DBMS consults its description of the database, finds where booking records are kept, and searches them. (4) The seat is free, so the application asks the DBMS to record a booking linking this patron to this performance. (5) The DBMS writes the new record and reports success. (6) The application builds a confirmation page and sends it back. The application never needed to know which disk or machine holds the bookings.
Schemas and subschemas
The DBMS works from a schema, a description of the whole database: which kinds of record exist, what items each contains and how they are linked. Not every user should see all of it. A subschema describes only the part one group of users needs, and the DBMS refuses requests that go beyond it. Because every access passes through the DBMS, the subschema acts as a gatekeeper. In SQL databases, subschemas are usually built from views, which are stored queries that present selected data, together with permissions saying who may read or change what. Sharing data and restricting access are equally important jobs.
Worked example: two subschemas for the theatre
The theatre's schema links patrons, performances, bookings, gifts and card payments. Design subschemas for box-office staff and for fundraisers. Steps: (1) List what each group needs. Box office: patron names and contact details, performances, seat bookings. Fundraising: patron names and contact details, gift history. (2) List what each must not see. Box office has no reason to see gift amounts; fundraisers have no reason to see card payments. (3) Box-office subschema: patrons, performances, bookings. (4) Fundraising subschema: patrons and gifts. (5) Check: both share the patron records, so an address corrected by either team is seen by both, yet neither can reach the other's sensitive data.
Data independence
Separating applications from the DBMS brings a further benefit, data independence: the database's organisation can change without rewriting the programs that use it. Suppose the box office starts recording each patron's access requirements, such as wheelchair seating. The designer adds the new item to the schema and to the subschemas of the staff who need it. The fundraising software, whose subschema is unchanged, keeps working without a single edit. Without this separation, every program that read patron records directly might need rewriting because the record layout had changed.
Database models
A database model is the conceptual picture of the data that a DBMS presents to applications, whatever the real storage looks like. In the relational model, data appears as tables of rows and columns; the topic "The relational model" develops it. In the object-oriented model, data appears as linked objects, as in "Object-oriented databases". The DBMS contains routines that translate requests phrased in the model into actions on the real storage. A general-purpose language such as Python has no database instructions of its own, but a program can call these routines through a library, for example Python's built-in sqlite3 module.
Distributed databases
A database need not live on one machine. A distributed database spreads its data over several computers linked by a network, while the DBMS presents it as one coherent whole. Data can be fragmented, with different parts stored in different places: a retail chain might keep each country's staff records on a server in that country. Data can also be replicated, with copies of the same part held at several sites, so that reads are fast and one failure loses nothing. Each choice brings new problems: hiding where the data really is, and keeping replicas identical when one copy is updated.
Metadata: data about data
Metadata describes other data. A library catalogue entry is metadata about a book; a photo file often records when it was taken, with what camera and sometimes where. A DBMS keeps metadata too: the schema itself, stored in a catalogue listing every table, item and permission. Metadata makes data easier to search, but it can reveal more than people expect. The location tag on a holiday photo, or a log of who phoned whom and for how long, can expose habits and relationships even when the picture or the conversation is never seen. The topic "Social impact of database technology: privacy" returns to this.
Common mistakes to avoid
Thinking the application software reads the stored data directly; it asks the DBMS. Confusing schema and subschema: the schema describes everything, a subschema one user group's portion. Assuming a subschema hides data only for convenience; it is also an access-control tool that the DBMS enforces. Believing data independence means the data never changes; it means the organisation can change without rewriting unaffected programs. Mixing up replication and fragmentation: replicas are copies of the same data, fragments are different parts.
Key takeaways
A database integrates an organisation's data, storing each fact once and linking entries so they can be viewed in many ways. Applications ask a DBMS, which does all the storing, searching and updating. The schema describes the whole database; subschemas give each user group a controlled portion. Separating applications from the DBMS simplifies software, enforces access rules and provides data independence. A database model, relational or object-oriented, is the conceptual view the DBMS presents. Distributed databases fragment or replicate data across machines. Metadata is data about data: useful, and sometimes revealing.
Flashcards
Flashcard
An organised collection of linked data that can be viewed from many perspectives
Test your understanding
Question 1 of 5
A theatre keeps separate patron files in three offices. A patron's new address is entered in only two of them. What does this illustrate?
Question 2 of 5
In a two-layer database application, which part actually searches the stored data?
Question 3 of 5
Fundraisers must see gift histories but never card payment records. Which tool provides this?
Question 4 of 5
An access-needs item is added to patron records, and the fundraising software keeps working without any edits. Which benefit is this?
Question 5 of 5
A retail chain keeps a full copy of its product catalogue on servers in three cities. What is this, and what is its main difficulty?
