created at 2022-10-06

What is schema?

DB schema : a metadata that how data is organized within a relational database
this is inclusive of logical constraints such as, table names, fields, data types, and the relationships between these entities

Steps of modeling DB(modeling schema)

External Schema
- step for defining (1)entity, (2)attribute, (3)relation
- result will be “entity relation diagram” which is ERD diagram
Conceptual Schema
- step for Normalization
- DB normalization : split table for reducing duplication
- Normalization has 3 stages(i.e. 1,2,3)
  1. First Normal Form : split the table so that each column of the table has an atomic value(one value)
  2. Second Normal Form : split the table so that column’s data cannot be defined by parts of composite keys
  3. Thrid Normal Form : split the table to eliminate transitive dependencies
    (transitive dependencies : A -> B -> C = A -> C)
    split table as A -> B, B -> C

Here is example for each steps.

Internal Schema
- step for how to express stored data items( ex) “id” = bigint )

CREATE TABLE "accounts" (
  "id" bigserial PRIMARY KEY,
  "owner" varchar NOT NULL,
  "balance" bigint NOT NULL,
  "currency" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);
CREATE TABLE "entries" (
  "id" bigserial PRIMARY KEY,
  "account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);
CREATE TABLE "transfers" (
  "id" bigserial PRIMARY KEY,
  "from_account_id" bigint NOT NULL,
  "to_account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);
CREATE INDEX ON "accounts" ("owner");
CREATE INDEX ON "entries" ("account_id");
CREATE INDEX ON "transfers" ("from_account_id");
CREATE INDEX ON "transfers" ("to_account_id");
CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");
COMMENT ON COLUMN "entries"."amount" IS 'it cannot be negative or positive';
COMMENT ON COLUMN "transfers"."amount" IS 'it must be positive';
ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");
ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");
ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");