Bradley Atkin
Web 2 
CS 3680

1. An RDBMS is a program/framework by which you can maintain, manage, or manipulate a database.
2. SQL stands for structured query language, it's a standardized language for querying databases.
3. ER model stands for Entity Relationship model, it's used to demonstrate database schema.
4. One to one: like an injective function, every entity is related to exactly one other
   One to many: one entity (A) is related to N other entities, all of which relate to the same (A)
   Many to many: instances of many Entities relating to up to N other Entites
5. A primary key ensures that each database entry is unique and searchable

6. CREATE TABLE IF NOT EXISTS Customers (
	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	firstName VARCHAR(50) NOT NULL,
	lastName VARCHAR(50) NOT NULL,
	address VARCHAR(50) NOT NULL,
	city VARCHAR(50) NOT NULL,
	state VARCHAR(2) NOT NULL,
	zip INTEGER(5) UNSIGNED NOT NULL,
	email VARCHAR(255) NOT NULL
   );

7. ALTER TABLE Customers
   ADD age INTEGER UNSIGNED NOT NULL;

8. INSERT INTO Customers VALUES(1, 'Jack', 'Dawson', '123 Fakestreet', 'Fakesville', 'AK', 51426, "JackieD@gmail.com');
   INSERT INTO Customers VALUES(2, 'Joseph', 'Dawson', '123 Fakestreet', 'Fakesville', 'AK', 51426, "JoeyD@gmail.com');
   INSERT INTO Customers VALUES(3, 'Shelly', 'Dawson', '123 Fakestreet', 'Fakesville', 'AK', 51426, "Shellstation@gmail.com');

9. SELECT * FROM Customers;

10. CREATE TABLE IF NOT EXISTS Products (
	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	description VARCHAR(255) NOT NULL,
	price DECIMAL(5, 2) UNSIGNED NOT NULL
   );

11. INSERT INTO Products VALUES(1, 'Fellowship of the Ring', 'The first book in the lord of the rings trilogy', 9.99);
    INSERT INTO Products VALUES(2, 'The Two Towers', 'The secondbook in the lord of the rings trilogy', 12.99);
    INSERT INTO Products VALUES(3, 'Return of the King', 'The third book in the lord of the rings trilogy', 13.99);

12. SELECT * FROM Products;

13. UPDATE Products
    SET price = 49.99
    WHERE id = 1;

14. SELECT * FROM Products WHERE price < 100;

15. DELETE FROM Products WHERE id = 3;

16. CREATE TABLE IF NOT EXISTS Orders (
	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	CID INTEGER NOT NULL UNSIGNED FOREIGN KEY REFERENCES Customers(id),
	amount DECIMAL(10, 2) UNSIGNED NOT NULL,
	date DATETIME DEFAULT CURRENT_TIMESTAMP
    );

17.  INSERT INTO Orders(id, cid, amount) VALUES(1, 1, 3);
     INSERT INTO Orders(id, cid, amount) VALUES(2, 2, 3);
     INSERT INTO Orders(id, cid, amount) VALUES(3, 3, 3);

18. SELECT * FROM Orders WHERE cid = 1;

19. SELECT * FROM Orders WHERE amount > 100;

20. DROP TABLE Orders;