| Entity Type | thing about which data is kept |
| ATTRIBUTE | property of entity |
| INSTANCE | an occurrence of an entity type |
| IDENTIFIER | attribute that uniquely identifies an instance |
| • TABLE/RELATION | representation of an entity |
| FIELD/COLUMN | representation of an attribute |
| RECORD/ROW | represents an instance |
| KEY | represents an identifier |
| Relationship | joins tables |
| Field value | assigned to a field |
| Data type | kind of data stored in field |
| Foreign key – | key from one table added to second table to create relationship |
| Database | an organized collection of data |
| Database Management Systems (DBMS) I | a software system that stores, manages, and facilitates access to one or more databases. E.g. Oracle, MS Access, SQL Server, SQLite, mySQL PostgreSQL |
| Schema | collection of objects in a database e.g. tables, views, indexes |
| JOIN | to select data from more than one table vvvv |
| Benefits of Relational Data Base | Reduces data duplication ,Improves integrity ,Facilitates querying Facilitates access contro |
| create database | CREATE DATABASE dbasename
CREATE TABLE tablename
( Col1 type
col2 type …
);
*Constraints may be added to columns e.g. PRIMARY KEY, CHECK, FOREIGN KE |
| Drop Database | DROP DATABASE dbasename;
DROP TABLE tablename; |
| Alter | ALTER TABLE tablename
ADD col type;
DROP COLUMN colname ;
MODIFY COLUMN colname type; |
| Insert into | INSERT INTO tablename
(col1, col2, col3, …)
VALUES
(v1, v2, V3, …), (v1, v2, v3, …), (V1, V2, V3, …), …; |
| Database update | UPDATE tablename
SET col = value
WHERE rowfilter;
*Note the omitting the WHERE clause will cause all rows to be updated |
| Select | SELECT col1, col2, col3, expr1, …
FROM tablename
WHERE rowfilter
ORDER BY column
LIMIT count OFFSET offset
GROUP BY column
HAViNG groupfilter;
-SELECT * to select all columns WHERE, ORDER BY, LIMIT, GROUP BY, HAVING are all optional clauses |
| Delete | DELETE FROM tablename
WHERE rowfilter; |
| Table Joins | Cross
Inner / Natural
Outer( Left Right Full) |
