Scribd
Upload
8 views12 pages

MBTA Database Schema Changes

The document contains a series of SQL scripts for managing a database schema related to the MBTA system. It demonstrates various operations such as creating, dropping, renaming tables, and adding constraints like primary keys, foreign keys, and data types. The scripts evolve through multiple versions to enhance the database structure and integrity.

Uploaded by

Catalin Niculescu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views12 pages

MBTA Database Schema Changes

The document contains a series of SQL scripts for managing a database schema related to the MBTA system. It demonstrates various operations such as creating, dropping, renaming tables, and adding constraints like primary keys, foreign keys, and data types. The scripts evolve through multiple versions to enhance the database structure and integrity.

Uploaded by

Catalin Niculescu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

alter/alter0.

sql

1 -- Demonstrates removing a table


2 -- Uses [Link]
3
4 -- Removes riders table
5 DROP TABLE "riders";
alter/[Link]

1 -- Demonstrates renaming a table


2 -- Uses [Link]
3
4 -- Renames "vists" table to "swipes"
5 ALTER TABLE "visits" RENAME TO "swipes";
alter/[Link]

1 -- Demonstrates adding a column to a table


2 -- Uses [Link]
3
4 -- Adds "ttpe" column to "swipes" table (intentional typo)
5 ALTER TABLE "swipes" ADD COLUMN "ttpe" TEXT;
alter/[Link]

1 -- Demonstrates renaming a column


2 -- Uses [Link]
3
4 -- Fixes typo using RENAME COLUMN
5 ALTER TABLE "swipes" RENAME COLUMN "ttpe" TO "type";
schema/[Link]

1 -- Demonstrates creating a table schema


2 -- Creates [Link]
3
4 -- Deletes prior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates three tables without specified type affinities
12 CREATE TABLE "riders" (
13 "id",
14 "name"
15 );
16
17 CREATE TABLE "stations" (
18 "id",
19 "name",
20 "line"
21 );
22
23 CREATE TABLE "visits" (
24 "rider_id",
25 "station_id"
26 );
schema/[Link]

1 -- Adds type affinities


2 -- Creates [Link]
3
4 -- Deletes prior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "riders" (
13 "id" INTEGER,
14 "name" TEXT
15 );
16
17 CREATE TABLE "stations" (
18 "id" INTEGER,
19 "name" TEXT,
20 "line" TEXT
21 );
22
23 CREATE TABLE "visits" (
24 "rider_id" INTEGER,
25 "station_id" INTEGER
26 );
schema/[Link]

1 -- Adds primary and foreign key table constraints


2 -- Creates [Link]
3
4 -- Deletes prior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "riders" (
13 "id" INTEGER,
14 "name" TEXT,
15 PRIMARY KEY("id")
16 );
17
18 CREATE TABLE "stations" (
19 "id" INTEGER,
20 "name" TEXT,
21 "line" TEXT,
22 PRIMARY KEY("id")
23 );
24
25 CREATE TABLE "visits" (
26 "rider_id" INTEGER,
27 "station_id" INTEGER,
28 FOREIGN KEY("rider_id") REFERENCES "riders"("id"),
29 FOREIGN KEY("station_id") REFERENCES "stations"("id")
30 );
schema/[Link]

1 -- Adds UNIQUE, NOT NULL as column constraints


2 -- Creates [Link]
3
4 -- Deletes prior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "riders" (
13 "id" INTEGER,
14 "name" TEXT,
15 PRIMARY KEY("id")
16 );
17
18 CREATE TABLE "stations" (
19 "id" INTEGER,
20 "name" TEXT NOT NULL UNIQUE,
21 "line" TEXT NOT NULL,
22 PRIMARY KEY("id")
23 );
24
25 CREATE TABLE "visits" (
26 "rider_id" INTEGER,
27 "station_id" INTEGER,
28 FOREIGN KEY("rider_id") REFERENCES "riders"("id"),
29 FOREIGN KEY("station_id") REFERENCES "stations"("id")
30 );
schema/[Link]

1 -- Updates schema to represent CharlieCard usage


2 -- Creates [Link]
3
4 -- Delete sprior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "cards" (
13 "id" INTEGER,
14 PRIMARY KEY("id")
15 );
16
17 CREATE TABLE "stations" (
18 "id" INTEGER,
19 "name" TEXT NOT NULL UNIQUE,
20 "line" TEXT NOT NULL,
21 PRIMARY KEY("id")
22 );
23
24 CREATE TABLE "swipes" (
25 "id" INTEGER,
26 "card_id" INTEGER,
27 "station_id" INTEGER,
28 "type" TEXT,
29 "datetime" NUMERIC,
30 "amount" NUMERIC,
31 PRIMARY KEY("id"),
32 FOREIGN KEY("station_id") REFERENCES "stations"("id"),
33 FOREIGN KEY("card_id") REFERENCES "cards"("id")
34 );
schema/[Link]

1 -- Adds NOT NULL column constraints


2 -- Creates [Link]
3
4 -- Deletes prior version of tables
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "cards" (
13 "id" INTEGER,
14 PRIMARY KEY("id")
15 );
16
17 CREATE TABLE "stations" (
18 "id" INTEGER,
19 "name" TEXT NOT NULL UNIQUE,
20 "line" TEXT NOT NULL,
21 PRIMARY KEY("id")
22 );
23
24 CREATE TABLE "swipes" (
25 "id" INTEGER,
26 "card_id" INTEGER,
27 "station_id" INTEGER,
28 "type" TEXT NOT NULL,
29 "datetime" NUMERIC NOT NULL,
30 "amount" NUMERIC NOT NULL,
31 PRIMARY KEY("id"),
32 FOREIGN KEY("station_id") REFERENCES "stations"("id"),
33 FOREIGN KEY("card_id") REFERENCES "cards"("id")
34 );
schema/[Link]

1 -- Adds DEFAULT column constraint


2 -- Creates [Link]
3
4 -- Deletes prior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "cards" (
13 "id" INTEGER,
14 PRIMARY KEY("id")
15 );
16
17 CREATE TABLE "stations" (
18 "id" INTEGER,
19 "name" TEXT NOT NULL UNIQUE,
20 "line" TEXT NOT NULL,
21 PRIMARY KEY("id")
22 );
23
24 CREATE TABLE "swipes" (
25 "id" INTEGER,
26 "card_id" INTEGER,
27 "station_id" INTEGER,
28 "type" TEXT NOT NULL,
29 "datetime" NUMERIC NOT NULL DEFAULT CURRENT_TIMESTAMP,
30 "amount" NUMERIC NOT NULL,
31 PRIMARY KEY("id"),
32 FOREIGN KEY("station_id") REFERENCES "stations"("id"),
33 FOREIGN KEY("card_id") REFERENCES "cards"("id")
34 );
schema/[Link]

1 -- Adds CHECK column constraint to validate swipes


2 -- Creates [Link]
3
4 -- Deletes prior tables if they exist
5 DROP TABLE IF EXISTS "riders";
6 DROP TABLE IF EXISTS "stations";
7 DROP TABLE IF EXISTS "visits";
8 DROP TABLE IF EXISTS "swipes";
9 DROP TABLE IF EXISTS "cards";
10
11 -- Creates tables with updated schema
12 CREATE TABLE "cards" (
13 "id" INTEGER,
14 PRIMARY KEY("id")
15 );
16
17 CREATE TABLE "stations" (
18 "id" INTEGER,
19 "name" TEXT NOT NULL UNIQUE,
20 "line" TEXT NOT NULL,
21 PRIMARY KEY("id")
22 );
23
24 CREATE TABLE "swipes" (
25 "id" INTEGER,
26 "card_id" INTEGER,
27 "station_id" INTEGER,
28 "type" TEXT NOT NULL CHECK("type" IN ('enter', 'exit', 'deposit')),
29 "datetime" NUMERIC NOT NULL DEFAULT CURRENT_TIMESTAMP,
30 "amount" NUMERIC NOT NULL CHECK("amount" != 0),
31 PRIMARY KEY("id"),
32 FOREIGN KEY("station_id") REFERENCES "stations"("id"),
33 FOREIGN KEY("card_id") REFERENCES "cards"("id")
34 );

You might also like