To describe a database schema, explain what its level represents and which design details it includes.

A database schema describes the structure and organization of a database. It sets out how the data is arranged, rather than the particular records stored at that moment.

Three levels of description

At the broadest level, a conceptual schema describes the main entities, their relationships and the business rules. It does not commit to implementation details.

A logical schema sets out how the data will be organized in a chosen database model. For a relational design, this includes tables, attributes and keys.

The physical schema specifies how a particular database management system will implement and store the database.

Abstraction

Abstraction means keeping relevant features while leaving out unnecessary detail. The database stays the same across all three schema levels; the question each level answers changes. What must be represented? How is it structured? How is it implemented?

Changing how a table is stored does not necessarily change its logical structure. Adding a storage index leaves the conceptual rule unchanged too: participants book sessions.

Key terms: database schema, conceptual schema, logical schema, physical schema, abstraction.

To construct an ERD, first identify the entities and assign their attributes and keys. Then mark the permitted relationships at both ends of every connection.

An entity relationship diagram (ERD) shows the entities in a database and the relationships between them. It makes the design rules visible before anyone implements the tables.

An entity is a distinguishable type of thing about which data is stored. A property recorded for an entity is called an attribute.

Cardinality and modality

Cardinality describes the maximum number of entity instances that can participate in a relationship. Common relationship types are one-to-one, one-to-many and many-to-many.

The minimum number of entity instances required in a relationship is its modality. A minimum of zero makes participation optional; a minimum of one makes it mandatory.

A one-to-many label alone doesn't tell you whether the many side may be empty. Modality supplies that missing rule.

Worked construction: workshop bookings

Suppose the service has these requirements:

• A participant may make no bookings or several bookings.
• A session may have no bookings or several bookings.
• Every booking identifies exactly one participant and exactly one session.
• Each session has exactly one tutor; a tutor may teach no sessions or several sessions.
• A participant may book a particular session only once.

Start with PARTICIPANT, SESSION and TUTOR as entities. The relationship between participants and sessions is many-to-many: a participant can attend several sessions, and a session can have several participants.

Now introduce BOOKING as an associative entity: an entity that represents a relationship between other entities. It replaces the many-to-many connection with two one-to-many connections.

Assign attributes and identifiers:

ParticipantID identifies a participant, TutorID identifies a tutor and SessionID identifies a session. A booking is identified by ParticipantID and SessionID together in BOOKING.

Why the diagram matters

An ERD helps you spot missing relationships and incorrect assumptions early. With BOOKING, each attendance relationship has its own place; there's no need to store a list of participants inside SESSION. Keeping TUTOR separate also avoids repeating a tutor's details for every session.

The application's rules must guide the design. If sessions can have several tutors, the TUTOR–SESSION relationship must change. Tidy notation alone doesn't make the diagram correct.

Key terms: entity relationship diagram, entity, attribute, relationship, cardinality, modality, one-to-one, one-to-many, many-to-many.

To outline a data type, state what kind of value it stores and explain why it suits a particular attribute.

A data type sets the kind of value an attribute can store, along with the operations that can meaningfully be applied to it. Database management systems differ in their exact type names and storage limits.

Common relational data types

Character types can have a fixed or variable length. The table covers common choices; it isn't a complete list of every type supported by every database system.

Consistency between related fields

Data type consistency means using compatible types and representations for values that have the same meaning. For example, a referenced SessionID and the SessionID stored in BOOKING should use compatible integer types.

With consistent date types, the database can compare dates as dates. If some dates are stored as text in one ordering and others in a different ordering, comparisons and interpretation become unreliable.

Consequences of a poor choice

Choosing a suitable data type rules out some invalid values, but it won't catch them all. An integer field can still store a negative capacity unless an additional rule prohibits it.

Key terms: data type, data type consistency, integer, decimal, floating-point, character, string, Boolean, date, time, timestamp, binary data.

To construct relational tables, choose attributes with suitable types. Identify the keys, then make each relationship explicit by matching key values.

A primary key is an attribute, or combination of attributes, chosen to identify each row uniquely. Its values must be unique and must not be null.

Use a foreign key to reference a key in another table or in the same table. It consists of an attribute or a combination of attributes, and links rows without copying all the referenced row's details.

A composite key contains more than one attribute. The combination must be unique, even though an individual component may repeat.

The term concatenated key describes multiple fields combined to identify a record; it is often used for a composite key. If the component values are physically joined into one identifier, their boundaries must remain unambiguous.

Simply joining variable-length identifiers, for example, can make different pairs produce the same text. Keep the components in separate columns in a composite key to avoid that ambiguity.

Worked construction

Start with the workshop booking rules. Each participant has one ParticipantID, and each session has one SessionID. A participant can book a particular session only once. In the tables below, PK means primary key, FK means foreign key and PK part means one component of a composite primary key.

PARTICIPANT

SESSION

BOOKING

Define the structure, not just the sample rows

There happen to be three bookings in the sample. The key rules must hold for every future row, too.

Data integrity

Data integrity is the accuracy, consistency and validity of stored data.

Every row must have a unique, non-null primary key to satisfy Entity integrity. For Referential integrity, each non-null foreign-key value must match an existing referenced key.

If P9 does not exist in PARTICIPANT, a booking for participant P9 must be rejected. Deleting a referenced participant must not leave a booking pointing to a nonexistent participant either. The database needs an appropriate deletion rule.

Well-defined tables also assign appropriate types and specify which values are required. These controls prevent structurally invalid data. They cannot, however, prove that an otherwise valid participant name is factually correct.

Key terms: primary key, foreign key, composite key, concatenated key, data integrity, entity integrity, referential integrity.

Explain normal forms by identifying the dependency removed at each stage. Show how removing it reduces avoidable duplication and inconsistent updates.

Normalization organizes relational tables according to rules that reduce unnecessary duplication and problematic dependencies.

Atomicity and identification

In table design, Atomicity means that each field holds one value, not a repeating group or list. Whether that value counts as indivisible depends on what the application needs to do with it.

A participant name can count as one value if the application never needs its separate components. By contrast, a field containing several SessionIDs is not atomic when each session must be linked and processed separately.

Unique identification means that a key distinguishes each row from every other row. Simply giving duplicate-looking rows different identifiers does not remove the duplicated facts.

Dependencies

A functional dependency exists when one attribute, or a set of attributes, determines exactly one value of another attribute. Written as SessionID → SessionTitle, it means that a given SessionID determines one SessionTitle.

When a non-key attribute depends on only part of a composite key, there is a partial-key dependency.

A transitive dependency occurs when a key determines a non-key attribute through another non-key attribute. In normalization, this is also called a non-key dependency.

Consider a booking table whose composite key consists of ParticipantID and SessionID. The table contains ParticipantID, SessionID, ParticipantName, SessionTitle, TutorID, TutorName and BookingDate. Assume each session has one tutor. Each participant can book a session only once.

• ParticipantID → ParticipantName.
• SessionID → SessionTitle, TutorID.
• TutorID → TutorName.
• ParticipantID + SessionID → BookingDate.

Comparing the normal forms

A candidate key is a minimal set of attributes that can uniquely identify a row. The primary key is chosen from these candidate keys.

Even in 1NF, a booking table can repeat ParticipantName for every session that participant books. Moving participant details into PARTICIPANT removes their dependency on only part of the booking key and helps establish 2NF.

Removing partial dependencies isn't enough for 3NF. SESSION might still contain SessionID, SessionTitle, TutorID and TutorName: SessionID determines TutorID, which in turn determines TutorName. Moving tutor details into TUTOR removes this transitive dependency.

Why duplication causes problems

An update anomaly occurs when changing one fact requires changes in several rows, potentially leaving conflicting values. A tutor's name repeated across sessions is one example.

If a fact cannot be recorded without supplying an unrelated fact, there is an insertion anomaly. A single booking table may offer no sensible way to record a new tutor before that tutor has a booking.

A deletion anomaly occurs when deleting one fact unintentionally removes another. For example, deleting the last booking for a tutor could erase the only stored record of that tutor's name.

The problem goes beyond storage. These anomalies can leave data missing or create disagreements about which value is correct.

Other dependency concerns

Data dependencies describe how values rely on other values. Functional dependencies are central to deciding which attributes belong together. With Composite key dependencies, take particular care: an attribute may depend on the complete key or on just one component.

A multi-valued dependency occurs when one entity has a set of values for one attribute independently of a set of values for another attribute. Suppose a tutor has several qualifications and several teaching languages. Storing every qualification–language combination repeats both sets unnecessarily.

Separate TUTOR_QUALIFICATION and TUTOR_LANGUAGE tables can represent these independent sets. Reaching 3NF addresses the functional dependencies described above, but it does not guarantee that every possible multi-valued dependency has been removed.

Key terms: normalization, atomicity, unique identification, functional dependencies, partial-key dependencies, composite key dependencies, non-key dependencies, transitive dependencies, multi-valued dependencies, 1NF, 2NF, 3NF.

To construct a 3NF database, first state the business rules and identify the keys and dependencies. Then decompose the tables without losing facts or the links between them.

Worked example: workshop bookings

Assume each participant has one recorded name and each session has one title and one tutor. A participant can book a given session only once. Each booking has its own BookingDate.

The starting booking sheet lists sessions for each participant. Expand the list into one row per participant–session pair. This gives atomic values, with the pair serving as the primary key.

Step 1: establish 1NF

This table is in 1NF. Its primary key combines ParticipantID and SessionID; every other column is a non-key attribute.

Repeated values don't prove a dependency. Here, the rule that each session has one tutor establishes SessionID → TutorID; the sample alone would not prove it.

Step 2: remove partial-key dependencies for 2NF

ParticipantName depends only on ParticipantID. SessionTitle, TutorID and TutorName depend on SessionID, not the complete booking key. Move these attributes out of BOOKING.

Keep BookingDate in BOOKING. It describes the participant's booking of that session, rather than the participant or session alone.

Step 3: remove the transitive dependency for 3NF

SESSION still contains the dependency SessionID → TutorID → TutorName. Create TUTOR, leaving TutorID in SESSION as a foreign key.

PARTICIPANT

TUTOR

SESSION

BOOKING

PARTICIPANT and SESSION each relate to BOOKING; TUTOR relates to SESSION. A participant or session may have zero or many bookings, but each booking requires exactly one of each. A tutor may teach zero or many sessions. Each session requires exactly one tutor.

Using the foreign keys, you can reconstruct the original information by joining related rows. Splitting the tables must not discard a relationship or introduce false pairings.

Applying the method to inventory management

Different applications need different entities. The dependency checks stay the same. Suppose an equipment store records stock at several depots. Each item has one category, and each category has one name.

Start with this 1NF stock record structure:

These rules establish the dependencies:

• ItemID → ItemName, CategoryID.
• CategoryID → CategoryName.
• DepotID → DepotName.
• ItemID + DepotID → Quantity.

For 2NF, move item details into ITEM and depot details into DEPOT. Quantity stays in STOCK: it depends on both the item and the depot. To reach 3NF, move CategoryName into CATEGORY because it depends on CategoryID rather than directly describing the item.

One category can describe zero or many items; each item has exactly one category. Each STOCK row references exactly one item and one depot. An item or depot may have zero or many STOCK rows.

Apply the same process elsewhere: work out what each fact depends on. Don't split tables simply because they look large.

Key terms: 1NF, 2NF, 3NF, atomicity, primary key, foreign key, composite key, functional dependency, partial-key dependency, transitive dependency.

To evaluate denormalization, weigh the benefit to actual read operations against the extra storage it needs, the maintenance work it creates and the risk of inconsistent data.

Denormalization deliberately introduces redundancy into a database design to improve the performance or simplicity of particular operations. It is a design trade-off, not an accidental failure to normalize.

What changes in the design?

In the normalized workshop database, a booking report gets ParticipantName from PARTICIPANT, SessionTitle from SESSION and TutorName from TUTOR. It joins these tables through their keys.

One alternative is a denormalized BOOKING_REPORT table that stores ParticipantID, SessionID, ParticipantName, SessionTitle, TutorName and BookingDate together. The report becomes simpler to retrieve. The cost is that names and titles repeat across many rows.

Advantages and disadvantages

When the trade-off may be worthwhile

A read-intensive application performs substantially more retrieval than modification. If measurements show that repeated joins cause a performance problem, a frequently requested reporting screen can be a good candidate for denormalization.

For the workshop service, users may repeatedly read a mostly unchanged archive of past bookings for reporting. Storing selected reporting values together may reduce repeated processing, especially when updates are rare and the application can tolerate a defined refresh delay.

Live booking data that changes frequently makes a less convincing case. Correcting a tutor name could require updates to many report rows. If only some copies change, the reports become inconsistent.

Reaching a justified decision

Begin with a normalized design so that the facts and dependencies are clear. Identify the particular query that needs improvement, then compare the read benefit with the cost of keeping duplicated data correct.

Don't assume denormalization will be faster: larger records and additional writes can introduce new costs. If the problem concerns a single reporting need, a separate denormalized reporting structure may be preferable to changing every operational table.

To justify the decision, specify which data will be duplicated and which copy is authoritative. Explain how copies will be synchronized and whether temporarily outdated results are acceptable. Where accurate, frequent updates matter more than simpler reads, retaining the normalized structure is usually the stronger choice.

Key terms: normalization, denormalization, read-intensive applications, query structures, data redundancy, data integrity, performance.