A-Level Computer Science / Unit 8: Relational Databases and SQL

8.3.1 Defining Database Structures with SQL

🔒 Lesson slides are available to signed-in users. Sign in

8.3.1 Defining Database Structures with SQL

Before a relational database can store any tuples, its structure must be defined. The DBMS uses Data Definition Language (DDL) statements to create a database, create tables, assign suitable data types, add keys and modify an existing table definition.

By the end of this section, you should be able to:

  • Explain the relationship between SQL, DDL and a DBMS.
  • Read and write simple CREATE DATABASE statements.
  • Write CREATE TABLE statements with suitable attribute definitions.
  • Select from CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE and TIME.
  • Use ALTER TABLE to change an existing table definition.
  • Add primary-key and foreign-key constraints.
  • Explain why referenced and referencing attributes need compatible definitions.
  • Identify and correct common DDL errors.
Scope note: This lesson defines database structures. Retrieving, grouping and joining data are covered in 8.3.2 and 8.3.3. Inserting, updating and deleting tuples are covered in 8.3.4.

SQL, DDL and the DBMS

Structured Query Language (SQL) is the industry-standard language used with relational databases. SQL includes commands for both defining structures and working with stored data.

SQL

Data Definition Language

Creates or changes the database structure.

CREATE DATABASE CREATE TABLE ALTER TABLE
SQL

Data Manipulation Language

Queries or maintains the tuples stored in tables.

SELECT INSERT INTO UPDATE DELETE FROM
DDL: the part of SQL used by the DBMS to create or modify the logical structure of a database.
Common mistake: CREATE TABLE does not add any records. It creates the table definition only.

A Reliable DDL Workflow

A database design should already identify the tables, attributes and relationships. DDL translates that design into a structure the DBMS can implement.

1

Read the design

Identify tables, attributes, keys and relationships.

2

Choose data types

Match each attribute to the kind of value it stores.

3

Create structures

Create the database and its tables.

4

Add constraints

Assign primary keys and foreign keys.

5

Check the script

Verify names, commas, brackets and references.

Exam tip: Translate one table at a time. Check every attribute against the design before moving to the next statement.

Choosing Suitable SQL Data Types

Each attribute definition must include a data type. The type controls which values can be stored and which operations are meaningful.

Data type Suitable use Original example Important distinction
CHARACTER Text with a fixed length. RegionCode CHARACTER(2) Useful when every value has the same number of characters.
VARCHAR(n) Text whose length can vary up to n. SiteName VARCHAR(50) The value does not have to use all n characters.
BOOLEAN A two-state value. Active BOOLEAN Use for values such as true/false, not for descriptive text.
INTEGER A whole number. SampleInterval INTEGER No decimal part is stored.
REAL A number that may contain a fractional part. Latitude REAL Suitable when decimal values are required.
DATE A calendar date. InstalledOn DATE Do not store dates as ordinary text when a date type is available.
TIME A time of day. DailyCheckTime TIME Represents a time rather than a duration.
Common mistake: An identifier made only of digits is not automatically an INTEGER. A code such as 004812 may need a character type so that leading zeroes are preserved and arithmetic is not performed on it.
Exam tip: Justify a data type from the meaning of the attribute, not from the appearance of one sample value.

Creating a Database

CREATE DATABASE creates a new named database. The statement does not yet define its tables. 

CREATE DATABASE EcoTrack;
Part Meaning
CREATE DATABASE The DDL command.
EcoTrack The chosen database name.
; Marks the end of the statement.
Statement: one complete SQL instruction, normally terminated by a semicolon.

Creating a Table and Its Attributes

CREATE TABLE names the table and lists its attributes inside parentheses. Each attribute definition contains an attribute name followed by its data type. 

CREATE TABLE ResearchSite (
    SiteID VARCHAR(6),
    SiteName VARCHAR(50),
    RegionCode CHARACTER(2),
    Latitude REAL,
    Active BOOLEAN
);
Table name ResearchSite
Attribute name SiteName
Data type VARCHAR(50)
Separator ,
Definition list ( ... )
Statement end ;

A second table can be defined in the same script: 

CREATE TABLE Sensor (
    SensorID VARCHAR(8),
    SiteID VARCHAR(6),
    SensorType VARCHAR(24),
    InstalledOn DATE,
    DailyCheckTime TIME,
    SampleInterval INTEGER
);
Common mistake: Do not place a comma after the final attribute definition before the closing parenthesis.

Adding Primary Keys

A primary key uniquely identifies each tuple. The syllabus syntax uses PRIMARY KEY (field). It can be added after table creation with ALTER TABLE. 

ALTER TABLE ResearchSite
ADD PRIMARY KEY (SiteID);

ALTER TABLE Sensor
ADD PRIMARY KEY (SensorID);

The field named inside the parentheses must already exist in the table.

Primary-key constraint: a rule requiring the selected key value to be unique and present for every tuple.
Exam tip: Write the table name after ALTER TABLE, but write the key attribute inside PRIMARY KEY (...).

Adding Foreign Keys and Relationships

A foreign key links a child table to the primary key of a parent table. Here, Sensor.SiteID refers to ResearchSite.SiteID. 

ALTER TABLE Sensor
ADD FOREIGN KEY (SiteID)
REFERENCES ResearchSite (SiteID);

The referencing and referenced attributes should have compatible definitions. In this example, both are VARCHAR(6). The referenced table and field must exist when the foreign-key constraint is added.

Clause Purpose
ALTER TABLE Sensor Selects the table that will contain the foreign key.
FOREIGN KEY (SiteID) Identifies the referencing attribute in Sensor.
REFERENCES ResearchSite (SiteID) Identifies the parent table and referenced primary-key attribute.
Common mistake: Do not reverse the relationship. The foreign key is in Sensor, so the statement begins with ALTER TABLE Sensor.

Changing an Existing Table Definition

ALTER TABLE changes a structure that already exists. Besides adding key constraints, it can add a new attribute. 

ALTER TABLE Sensor
ADD LastServiced DATE;

This changes the schema by adding LastServiced to every Sensor tuple definition. It does not automatically provide a service date for existing tuples.

Structure change: a modification to the schema, such as adding an attribute or constraint. It is different from changing the value stored in one tuple.
Common mistake: ALTER TABLE changes the table definition. UPDATE, studied later, changes values stored in tuples.

Worked Example: From Design to DDL

A conservation team needs a database to record monitoring sites and the sensors installed at each site.

ResearchSite(SiteID, SiteName, RegionCode, Latitude, Active)

Sensor(SensorID, SiteIDFK, SensorType, InstalledOn, DailyCheckTime, SampleInterval)

One research site can have many sensors. Each sensor belongs to one research site.

Step 1: Choose data types

Attribute Chosen type Reason
SiteID VARCHAR(6) It is an identifier that may contain letters and digits.
RegionCode CHARACTER(2) Every region code contains exactly two characters.
Latitude REAL The value may contain a fractional part.
Active BOOLEAN The site is either active or inactive.
InstalledOn DATE A calendar date is required.
DailyCheckTime TIME A time of day is required.
SampleInterval INTEGER The interval is stored as a whole number of minutes.

Step 2: Write the complete script

CREATE DATABASE EcoTrack;

CREATE TABLE ResearchSite (
    SiteID VARCHAR(6),
    SiteName VARCHAR(50),
    RegionCode CHARACTER(2),
    Latitude REAL,
    Active BOOLEAN
);

CREATE TABLE Sensor (
    SensorID VARCHAR(8),
    SiteID VARCHAR(6),
    SensorType VARCHAR(24),
    InstalledOn DATE,
    DailyCheckTime TIME,
    SampleInterval INTEGER
);

ALTER TABLE ResearchSite
ADD PRIMARY KEY (SiteID);

ALTER TABLE Sensor
ADD PRIMARY KEY (SensorID);

ALTER TABLE Sensor
ADD FOREIGN KEY (SiteID)
REFERENCES ResearchSite (SiteID);

Step 3: Check the script

  • Every table and attribute has a valid identifier.
  • Every attribute has a data type.
  • Definitions are separated with commas.
  • Each statement ends with a semicolon.
  • Primary-key fields exist in their tables.
  • The foreign key and referenced key both use VARCHAR(6).
  • The parent table and child table have both been created before the relationship is added.
Exam tip: A final line-by-line check often finds missing commas, misspelled field names and reversed foreign-key references.

Interactive: DDL Structure Builder

Match requirements to data types, build the EcoTrack structure in a safe order and test whether key relationships are valid.

Choose the most suitable data type

Select a data type and check your answer.

Common Mistakes and Misconceptions

  • Mixing DDL with DML: DDL defines structures; DML works with tuples.
  • Using a numeric type for an identifier that must preserve leading zeroes.
  • Forgetting the size in VARCHAR(n).
  • Omitting a data type from an attribute definition.
  • Leaving out a comma between attribute definitions.
  • Adding a comma after the final attribute.
  • Forgetting the semicolon at the end of a statement.
  • Writing a key attribute name that was not defined in the table.
  • Reversing the parent and child tables in a foreign-key statement.
  • Using incompatible data types for the foreign key and referenced key.
  • Using ALTER TABLE when the intention is to change a tuple value.

Practice

Original practice tasks

  1. Explain the difference between SQL, DDL and DML.
  2. Choose suitable types for the following attributes: MuseumCode, ExhibitName, OnDisplay, WeightKg, AcquisitionDate and OpeningTime.
  3. Write a statement to create a database named MuseumStore.
  4. Write DDL to create: Gallery(GalleryID, GalleryName, FloorNumber). Use suitable types but do not add the key yet.
  5. Write DDL to create: Exhibit(ExhibitID, GalleryID, ExhibitName, InsuredValue, AcquisitionDate, OnDisplay).
  6. Add GalleryID as the primary key of Gallery.
  7. Add ExhibitID as the primary key of Exhibit.
  8. Add the foreign key that implements the relationship from Exhibit to Gallery.
  9. Write a statement to add LastInspected DATE to Exhibit.
  10. Find and correct every error in this statement: 
    CREATE TABLE Device
    DeviceID VARCHAR
    DeviceName VARCHAR(40),
    Active BOOLEAN,
);

Review

DDL feature Purpose Example pattern
CREATE DATABASE Creates a named database. CREATE DATABASE DatabaseName;
CREATE TABLE Defines a table and its attributes. CREATE TABLE TableName (...);
Data type Defines the kind of value an attribute stores. FieldName VARCHAR(30)
ALTER TABLE Changes an existing table structure. ALTER TABLE TableName ADD ...;
PRIMARY KEY Uniquely identifies each tuple. ADD PRIMARY KEY (FieldName)
FOREIGN KEY Links a child table to a referenced parent table. ADD FOREIGN KEY (...) REFERENCES ... (...)
Final exam tip: Build a DDL answer in this order: database → tables → attributes and types → primary keys → foreign keys → final syntax check.