Lecture 6

• As a reminder, riders who use the subway have a CharlieCard they swipe at stations to gain entry. Riders can recharge their cards and in some cases, they need to swipe their cards to leave a station as well. The MBTA does not store information about riders, but only keeps track of the cards.

mysql -u root -h 127.0.0.1 -P 3306 -p

• In this terminal command, -u indicates the user. We provide the user we want to connect to the database as — root (synonymous with database admin, in this case).
• 127.0.0.1 is the address of local host on the internet (our own computer).
• 3306 is the port we want to connect to, and this is the default port where MySQL is hosted. Think of the combination of host and port as the address of the database we are trying to connect to!
• -p at the end of the command indicates that we want to be prompted for a password when connecting.

SHOW DATABASES;

• Creating a new database:

  CREATE DATABASE `mbta`;
  

  Instead of quotation marks, we use backticks to identify the table name and other variables in our SQL statements.
  
• To change the current database to mbta:

  USE `mbta`;
  

CREATE TABLE `cards` (
    `id` INT AUTO_INCREMENT,
    PRIMARY KEY(`id`)
);

SHOW TABLES;

DESCRIBE `cards`;

CREATE TABLE `stations` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL UNIQUE,
    `line` ENUM('blue', 'green', 'orange', 'red') NOT NULL,
    PRIMARY KEY(`id`)
);

• We choose a VARCHAR for the station name because names might be an unknown length. The line that a station is on, however, is one of the existing subway lines in Boston. Since we know the values this could take, we can use an ENUM type.
• We also use column constraints UNIQUE and NOT NULL in the same way as we did with SQLite.

• The amount of precision needs to be specified with the number of bytes, because of floating point imprecision. This means that with a limited amount of memory, floating point numbers can be represented only up to a certain precision. The more the bytes, the more the precision with which the number is represented.

CREATE TABLE `swipes` (
    `id` INT AUTO_INCREMENT,
    `card_id` INT,
    `station_id` INT,
    `type` ENUM('enter', 'exit', 'deposit') NOT NULL,
    `datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `amount` DECIMAL(5,2) NOT NULL CHECK(`amount` != 0),
    PRIMARY KEY(`id`),
    FOREIGN KEY(`station_id`) REFERENCES `stations`(`id`),
    FOREIGN KEY(`card_id`) REFERENCES `cards`(`id`)
);

• Notice the use of DEFAULT CURRENT_TIMESTAMP to indicate that the timestamp should be auto-filled to store the current time if no value is provided.
• The precision we choose for the swipe amount is 2. This is to ensure that cents get added or subtracted without any rounding.
• The column constraints from when we created the table in SQLite remain, including the check to make sure the swipe amount is not negative.

ALTER TABLE `stations` 
MODIFY `line` ENUM('blue', 'green', 'orange', 'red', 'silver') NOT NULL;

• This allows us to modify the line column and change its type, such that the ENUM now includes silver as an option.
• Note also that we use the keyword MODIFY in addition to the ALTER TABLE construct we are familiar with from SQLite.

USE `mfa`;

ALTER TABLE `collections` 
ADD COLUMN `deleted` TINYINT DEFAULT 0;

delimiter //

CREATE PROCEDURE `current_collection`()
BEGIN
    SELECT `title`, `accession_number`, `acquired` 
    FROM `collections` 
    WHERE `deleted` = 0;
END//

delimiter ;

CALL current_collection();

UPDATE `collections` 
SET `deleted` = 1 
WHERE `title` = 'Farmers working at dawn';

CREATE TABLE `transactions` (
    `id` INT AUTO_INCREMENT,
    `title` VARCHAR(64) NOT NULL,
    `action` ENUM('bought', 'sold') NOT NULL,
    PRIMARY KEY(`id`)
);

delimiter //
CREATE PROCEDURE `sell`(IN `sold_id` INT)
BEGIN
    UPDATE `collections` SET `deleted` = 1 
    WHERE `id` = `sold_id`;
    INSERT INTO `transactions` (`title`, `action`)
    VALUES ((SELECT `title` FROM `collections` WHERE `id` = `sold_id`), 'sold');
END//
delimiter ;

CALL `sell`(2);

• Integers

• Serial
  • Serials are also integers, but they are serial numbers, usually used for primary keys.

psql postgresql://postgres@127.0.0.1:5432/postgres

CREATE DATABASE "mbta";

CREATE TABLE "cards" (
    "id" SERIAL,
    PRIMARY KEY("id")
);

CREATE TABLE "stations" (
    "id" SERIAL,
    "name" VARCHAR(32) NOT NULL UNIQUE,
    "line" VARCHAR(32) NOT NULL,
    PRIMARY KEY("id")
);

CREATE TYPE "swipe_type" AS ENUM('enter', 'exit', 'deposit');

CREATE TABLE "swipes" (
    "id" SERIAL,
    "card_id" INT,
    "station_id" INT,
    "type" "swipe_type" NOT NULL,
    "datetime" TIMESTAMP NOT NULL DEFAULT now(),
    "amount" NUMERIC(5,2) NOT NULL CHECK("amount" != 0),
    PRIMARY KEY("id"),
    FOREIGN KEY("station_id") REFERENCES "stations"("id"),
    FOREIGN KEY("card_id") REFERENCES "cards"("id")
);

CREATE USER 'carter' IDENTIFIED BY 'password';

SHOW DATABASES;

GRANT SELECT ON `rideshare`.`analysis` TO 'carter';

USE `rideshare`;

SELECT `id` FROM `users`
WHERE `user` = 'Carter' AND `password` = 'password';

SELECT `id` FROM `users`
WHERE `user` = 'Carter' AND `password` = 'password' OR '1' = '1';

SELECT * FROM `accounts`
WHERE `id` = 1 UNION SELECT * FROM `accounts`;

PREPARE `balance_check`
FROM 'SELECT * FROM `accounts`
WHERE `id` = ?';

SET @id = 1;
EXECUTE `balance_check` USING @id;

SET @id = '1 UNION SELECT * FROM `accounts`';
EXECUTE `balance_check` USING @id;