CMPS 3680
Rodrigo Ortiz
Lab 9

1. A Relational database management system is a database based on the relational model.
    One would use software such as MySQL, Oracle, or Postgres to alter records of this type of database.

2. A database schema is basically the blueprints of a database. There exists a schema for everything relating
    to a database: relational, ER diagram...

3. The Entity-Relationship model is a method that's used to organize entities (things that exist in the world)
    and their relationships to another (can be seen as functions).

4. A one to one (1:1) relationship is where a single type of entity/relation can only be mapped to another single
    type of entity. Example: One Employee Manage One Department (for most companies)

5. A one to many (1:M) relationship is where a single entity can be mapped to many other entities of a particular entity type. Example: One Student may Take Many Courses.

6. A many to many (M:M) relationship is where many entities may be mapped to many other entities.
    Example: Many Employees may Supervise Many other Employees.

7. A tuple is a row\record within a database. A column would be an attribute and a tuple would be the row.
    Example: Column: product_Name Tuple 1: Phone Tuple 2: Cup

8. A primary key is used to uniquely identify an relation/entity.

9. Primary keys are unique because without them an entity/relation can't exist. If someone is defined by
    their Social Security Number and they had no other attributes that identifies them as an entity, they
    can't exist.

10. SQL stands for Structured Query Language and is used for relational databases.



11. CREATE TABLE Customers
(
    c_id int NOT NULL AUTO_INCREMENT,
    c_firstName varchar(255),
    c_lastName varchar(255),
    c_address varchar(255),
    c_city varchar(255),
    c_state varchar(255),
    c_zip varchar(255),
    c_email varchar(255),
    
    PRIMARY KEY (c_id)
);



12. ALTER TABLE Customers
    ADD c_age tinyint;



13. CREATE TABLE Products
(
    p_id int NOT NULL AUTO_INCREMENT,
    p_title varchar(255),
    p_description varchar(255),
    p_price decimal(13,4),
    
    PRIMARY KEY(p_id)
);



14. INSERT INTO Customers (c_id, c_firstName, c_lastname)
    VALUES ('1', 'John', 'Doe');

INSERT INTO Customers (c_id, c_firstName, c_lastname)
    VALUES ('2', 'Jane', 'Doe');

INSERT INTO Customers (c_id, c_firstName, c_lastname)
    VALUES ('3', 'Steve', 'Richardson');



15. INSERT INTO Products (p_id, p_title, p_price)
    VALUES ('1', 'Small Soda', '1.99');

INSERT INTO Products (p_id, p_title, p_price)
    VALUES ('2', 'Large Pizza', '9.99');

INSERT INTO Products (p_id, p_title, p_price)
    VALUES ('3', 'Cookie', '.99');



16. CREATE TABLE Orders 
(
    o_id int NOT NULL AUTO_INCREMENT,
    o_cid varchar(255),
    o_amount decimal(13,4),
    o_date datetime,
    
    PRIMARY KEY (o_id);
);



17. INSERT INTO Orders (o_id, o_amount, o_date)
    VALUES ('1', '18.98', '2019-03-26 03:12:01');

INSERT INTO Orders (o_id, o_amount, o_date)
    VALUES ('2', '11.98', '2018-12-14 11:54:37');

INSERT INTO Orders (o_id, o_amount, o_date)
    VALUES ('3', '2.98', '2019-01-05 08:39:41');




18. SELECT * FROM Customers;

19. SELECT * FROM Products;



20. UPDATE Products
    SET p_price = '49.99'
    WHERE p_id = '3';



21. SELECT *
    FROM Products
    WHERE p_price < 100.00;



22. SELECT * 
    FROM Orders
    WHERE c_id = '1';



23. SELECT *
    FROM Orders
    WHERE o_amount > 100.00;


24. DELETE FROM Products WHERE p_id = '3';

25. DROP TABLE DropThisTable_GoneForever;