Database

DB — DataBase
AB – Andme Baas(id)
DB – Databases

DBMS – Database Management Systems

Main tasks of the database

• Ensuring that all necessary information is stored in the database
• Ensuring the ability to obtain data for all necessary requests.
• Reduce data redundancy and duplication
• Ensuring data integrity: eliminating inconsistencies in data content.

Data types

1. Numeric –
   int, smallint, tinyint, decimal(4,1) – 1 decimal place, 4 – in total
2. Text or symbol –
   varchar(10) where 10 is the max. number of characters
   char(11) – only 11 characters
   TEXT
3. Logic –
   boolean, bool, bit, true/false
4. Temporary –
   date
   time
   datetime
   interval – to store a time interval

Row = Record
Columns = Fields
Table = Entity/Ole

SQL – Structured Query Language

Query – Request – Päring
DDL – Data Definition Language – creating and defining tables
CREATE TABLE, DROP TABLE, ALTER TABLE
DML – Data Manipulation Language – adding, deleting and editing a database
INSERT, UPDATE, SELECT

creates tables

123456

CREATETABLEopilane(    opilaneID intPRIMARYKEYAUTO_INCREMENT,    eesnimi varchar(20) notnull,    perenimi varchar(30) notnull,    isikukood char(11),    synniaeg date)

adds data to the table

1	INSERTINTO`opilane` (`opilaneID`, `eesnimi`, `perenimi`, `isikukood`, `synniaeg`) VALUES(NULL, 'Yarik', 'Yekasov', '50511190683', '2005-11-19');

adds a column with place of birth

1	ALTERTABLEopilane ADDCOLUMNsynnikoht varchar(20)

updates place of birth by ID

12	UPDATEopilane SETsynnikoht='Tallinn'WHEREopilaneID=1; SELECT* FROMopilane;

Primary KEY – PK – attribute / or a set of them that uniquely defines a row; there are no two identical primary key values. AUTO_INCREMENT – automatic filling of the key field with increasing values ​​1,2,…

Foreign KEY – FK – Secondary key – relationship between tables. The secondary key contains a reference to the PK of another table

123456

CREATETABLEhindamine(    hindamineID intprimarykeyAUTO_INCREMENT,    opilaneID int,    foreignkey(opilaneID) referencesopilane(opilaneID),    oppaine varchar(12),    hinne int)

1	ALTERTABLEopilane ADDryhmID int

1	UPDATEopilane SETryhmID=

1	ALTERTABLEopilane ADDCONSTRAINTfk_ryhm2 FOREIGNKEY(ryhmID) REFERENCESryhm(ryhmID)

Test2