Define a single-table database from given requirements

What is a Single‑Table Database?

A single‑table database stores all data in one table. Think of it as a single spreadsheet where each row is a record and each column is an attribute. 📊

Why Use a Single‑Table Database?

• Simple to design and query.
• Good for small projects or exam practice.
• Reduces the need for joins.

Step‑by‑Step: Define a Single‑Table Database

1. Identify the Entity: What is the main thing you want to store? e.g., Student.
2. List Attributes: What details do you need? e.g., Student ID, Name, DOB, Major, GPA.
3. Choose a Primary Key: A unique identifier for each row. Usually the ID.
4. Decide Data Types: Text, Integer, Decimal, Date.
5. Add Constraints: NOT NULL, UNIQUE, CHECK (e.g., GPA between 0 and 4).
6. Write the CREATE TABLE statement: Put it all together.

Example: Student Records

SQL Statement:

CREATE TABLE Student (
  StudentID VARCHAR(10) PRIMARY KEY NOT NULL,
  Name VARCHAR(50) NOT NULL,
  DOB DATE NOT NULL,
  Major VARCHAR(30),
  GPA DECIMAL(3,2) CHECK (GPA >= 0 AND GPA <= 4)
);

The GPA range can be expressed as $0 \leq \text{GPA} \leq 4$.

Exam Tips 📌

• Always state the primary key explicitly.
• Use appropriate data types; e.g., DATE for dates, DECIMAL for GPA.
• Include CHECK constraints to enforce valid ranges.
• Remember to use NOT NULL for mandatory fields.
• Show the final CREATE TABLE statement clearly.

Quick Practice Question

Design a single‑table database to store Book information with the following requirements:

• Book ID (unique), Title, Author, Publication Year, Genre, and Rating (0–5).

Write the CREATE TABLE statement.