er.md

ER Diagrams

Resources

• drawdb: generates code to create db
• Link to working drawdb file

---

ER Diagram

SQL for generating tables

• first follow along Lab 5's instructions on creating a DB in cPanel

CREATE TABLE `user` (
	`id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
	`is_admin` BOOLEAN NOT NULL,
	`user_name` VARCHAR(255) NOT NULL,
	`password` VARCHAR(255) NOT NULL,
	PRIMARY KEY(`id`)
);


CREATE TABLE `reviews` (
	`id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
	`user_id` INTEGER NOT NULL,
	`movie_id` INTEGER NOT NULL,
	`rating` INTEGER NOT NULL,
	`review` VARCHAR(500),
	PRIMARY KEY(`id`)
);


CREATE TABLE `movie` (
	`id` INTEGER NOT NULL UNIQUE,
	`title` VARCHAR(255) NOT NULL,
	`release_date` DATE NOT NULL,
	`overview` VARCHAR(700) NOT NULL,
	`backdrop_path` VARCHAR(255) NOT NULL,
	`poster_path` VARCHAR(255) NOT NULL,
	`vote_average` FLOAT NOT NULL,
	PRIMARY KEY(`id`)
);


CREATE TABLE `watchlist` (
	`user_id` INTEGER NOT NULL,
	`movie_id` INTEGER NOT NULL,
	PRIMARY KEY(`user_id`, `movie_id`)
);


ALTER TABLE `user`
ADD FOREIGN KEY(`id`) REFERENCES `reviews`(`user_id`)
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE `movie`
ADD FOREIGN KEY(`id`) REFERENCES `reviews`(`movie_id`)
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE `user`
ADD FOREIGN KEY(`id`) REFERENCES `watchlist`(`user_id`)
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE `movie`
ADD FOREIGN KEY(`id`) REFERENCES `watchlist`(`movie_id`)
ON UPDATE NO ACTION ON DELETE NO ACTION;

---

Example SQL Queries

Query a movie by id

SELECT * FROM movie where id = ?

Query user by id

SELECT * FROM user where id = ?

Query watchlist by user_id

SELECT * FROM watchlist where user_id = ?

Insert a row to a table

Updating a value in a row

Deleting a row

---

_{< Back to Docs << Back to Main Page}