Tables - Collection of related data which along with other tables constitutes a database. It has columns (vertical) and rows (horizontal). The data held can be of various types such as numbers, characters, files, date among others.
Data Definition Language (DDL) - Are the operations done to the table itself to create, modify and drop or truncate the table.
CREATE statement - Command used to generate a new database, table, index, stored procedure, function or any other object that the DBMS has.
DataBase Management System (DBMS) - Software package designed to define, manipulate, retrieve and manage different kinds of data in a database. The most popular DBMS are Oracle, MySQL, PostgreSQL, MSQL, DB2.
DROP statement - Statement use to dump an existing database, table, index, function or any other object. When performing this operation all information contained in the object will be lost.
ALTER statement - This command is used to modify a database object, in the case of a table it can be done to insert new properties, update existing ones (change maximum length of a character field).
TRUNCATE statement - Statement to delete a database object, it has some differences in relation to DROP statement.
Data Manipulation Language (DML) - Are the operations done to the table to select, update, insert and delete information that the table contains.
SELECT statement - Command used to retrieve data from one or various tables, also in most DBMS its used to execute a function or procedure and retrieves the return value.
INSERT statement - This command allows to insert information into a database table, respecting the data type and restrictions put on the table.
UPDATE statement - Command used to modify information stored in a database table, in other objects in most DBMS an update is not used, rather a “CREATE OR REPLACE” statement is used, so if a function or procedure already exists, it will be replaced by the same object but with the changes made. Instead of a “CREATE OR REPLACE”, since functions and procedures are scripts, they can be updated by anteceding a DROP before creating.
DELETE statement - Command used to remove and delete rows from a table.
Entity - Relationship Diagram (ERD) - It’s a graphical model used to show the relations between the tables that compose a database. The type of relation between tables are defined by cardinality.
Cardinality - Refers to the uniqueness of data values contained in a column in relation of another column in a different table. Cardinality between tables can be one-to-one, many-to-one (one-to-many as well) or many-to-many.
One to One Cardinality - It’s when information of one row in table A may only be linked to one row of table B, and vice versa.
One to Many (Many to One) Cardinality - It’s when information of one row in table A may be linked to many rows of table B, and vice versa.
Many to Many Cardinality - It’s when information of many rows in table A may be linked to many rows of table B. In an ERD this relationship is reflected by the use of an intermediary table in order to make this cardinality possible. This intermediary table only contains the keys (constraints) of both tables that are related.
Keys - Constraints - Identifiers that point to a specific table tuple.
Tuple - Term also used to mention database records or rows.
Primary Key - It’s the table’s main identifier column, this value is always unique. When creating the table, the primary key constraint has to be specified.
Unique Key - It’s a table unique defined column, this constraint is not mandatory but depending, it could be needed.
Foreign Key - It’s also a table column used to create the relationship with another table, its value is usually an integer that contains the foreign table primary key value.
Stored Procedure - Executable code, the programming language is known as PL/SQL or PSQL, this code is in the database. It has the same principle as is in programming, within databases it’s used to perform database operations especially DML. Frequently they are used as an application programming interface (API) for security or simplicity. As is in programming it does not provide a return value.
Function - Same as stored procedures and the principles of programming, functions return a value, in databases they are plenty used to retrieve some basic data, for example, given an ID, it’s used to retrieve the tuple information.
COMMIT statement - In most DBMS when applying changes in database, at first, this change is only at memory level, not on database record actually, this command applies changes from memory to real databases.
ROLLBACK statement - The opposite of COMMIT, this operation reverts changes and leave database untouched.
VIEW object - A view is a stored query on the database, it’s pretty much used for generating reports.
SQL JOIN - As in algebra combines columns from one or more tables, in databases there are five kinds of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS.
INDEX - Data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
