Lab 9

1. RDBMS stands for Relational Database Management Systems. It is
a program that allows you to create, update, and administer a relational database. 
Most relational database management systems use the SQL language to access the database. 

2. A database schema represents the logical configuration of all or part of a relational 
database. It can exist both as a visual representation and as a set of formulas known as 
integrity constraints that govern a database. These formulas are expressed in a data 
definition language, such as SQL. As part of a data dictionary, a database schema 
indicates how the entities that make up the database relate to one another, including 
tables, views, stored procedures, and more. Two types of database schema are: physical
and logical.

3. An ER (Entity Relationship) Model is a graphical approach to database design. It uses 
Entity/Relationship to represent real world objects. An Entity is a thing or object in 
real world that is distinguishable from surrounding environment. For example each 
employee of an organization is a separate entity. Following are some of major 
characteristics of entities. An entity has a set of properties. Entity properties can 
have values.

4. A one-to-one relationship in a relational database occurs when one parent record or field has either zero or one child record only. These relationships are the easiest to represent in databases because both the parent and child records may be in the same table.

5. In relational databases, a one-to-many relationship occurs when a parent record in one table can potentially reference several child records in another table. In a one-to-many relationship, the parent is not required to have child records; therefore, the one-to-many relationship allows zero child records, a single child record or multiple child records. The important thing is that the child cannot have more than one parent record. 

6. A many-to-many relationship is the opposite of a one-to-many relationship, in which a 
child record can link back to several parent records.  

7. a tuple is one record (one row). The information in a database can be thought of as a 
spreadsheet, with columns (known as fields or attributes) representing different 
categories of information, and tuples (rows) representing all the information from each 
field associated with a single record.

8. The main purpose of a primary key is to implement a relationship between two tables in a relational database; A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). A relational database must always have one and only one primary key. Primary keys typically appear as columns in relational database tables.

9. The primary key must be unique because In order to identify a row, the values of a PK must be unique.

10. SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.

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 int(255)
c_email varchar(255)
PRIMARY KEY (c_id)
); 

12. ALTER TABLE Customers
ADD c_age TINYINT(255) [UNSIGNED] [ZEROFILL]; 

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

14. INSERT INTO Customers (c_firstName, c_city, c_state, c_zip)
VALUES ('Carl', 'Savannah', 'Georgia', '31420');

15. INSERT INTO Products (p_title, p_description, p_price)
VALUES ('Ratty G', 'A rat-shaped bluetooth speaker', '37.99');

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

17. INSERT INTO Orders (o_cid, o_amount, o_date)
VALUES ('40', '37.99', '2019-10-30 06:41:29');

18.SELECT c_firstName FROM Customers; 


19. SELECT p_title FROM Products; 

20. UPDATE Products
SET p_price = '49.99'
WHERE p_title = 'Ratty G';

21. SELECT p_title * FROM Products
WHERE p_price < '100.00';

22. SELECT o_cid FROM Orders
WHERE o_cid = 1;

23. SELECT o_cid * FROM Orders
WHERE p_price > '100.00';

24. DELETE FROM Products WHERE p_id = 99;

25. DROP TABLE DropThisTable_GoneForever;