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) |