Database design.

DDL og DML: Struktur og indhold af databaser

Vi vil kigge på hvordan designer en database i forhold til at lave relationer mellem tabeller, sikre data integritet og lave CRUD operationer fra vores Java programmer.

Oversigt over emner og essourcer:

Emne Ressourcer
1: Relationelle databaser og SQL Lynda.com video kursus om MySql: Se kapitel 2-6 + 8, Workbench: Reference materiale til opslag.
2: DML Data Manipulation Language: CRUD operationer - MySQL Tutorial: Grundig online MySql bog som i kan læse i når der er tid.
3: Complex SELECT statements: Inner, Left/Right join - Aggregate functions: AVG, COUNT, MIN , MAX, SUM - Sub queries - Operators: AND, OR, NOT, =, <>, >=, BETWEEN, IN, LIKE with WILDCARDS and ALIAS - ANY vs. ALL - UNION
4: Modelering Domain model udledt fra kravsspecifikationen (Conceptual model)->(Logical model)-> (Physical Database model). Data modeling in workbench, Reverse engineering in workbench.
5: DDL DDL: Data Definition Language - CREATE, DROP, ALTER: Database, Table, View, SQL-constraints: Primary key, Foreign key and MySQL Cascade, NOT NULL, Unique, MYSQL Data types, auto_increment
6: Indexing Indexing
7: NF 3 Normalformer: 1NF, 2NF, 3NF
8: JDBC JDBC: Connection, Statement, PreparedStatement og SQL Injection, ResultSet, Transactions i Mysql og Transactions i java med JDBC.
9: SQL Exceptions SQL Exceptions og hvordan man opsætter og læser error logs (Configurationsfilen ligger på: /etc/mysql/mysql.conf.d/mysqld.cnf og logfilen på sudo tail /var/log/mysql/mysql.log)
10: Backup Backup og Restore database.

Database exercises

Database exercises

Hvad kan vi bruge databaser til?

  1. Persistens og CRUD operationerne
    1. Tilstand (‘state’)
  2. Data integritet med constraints
    1. data typer
    2. fremmed nøgler (foreing keys)
  3. Udtræk af data
    1. Komplekse queries.
  4. JDBC
    1. Eksempler på mapning (Relationelt data vs. java objekter)

MYSQL WORKBENCH / SCRIPTING

From business (Domain Model) to database model Conceptual model / Logical model / Physical model

Model to script <-> Script to model
Backup database / Test database

SQL / Data query (DQL)

SELECT <columns>
FROM <tables>
WHERE <filter conditions> 
ORDER BY <sort column(s)>
GROUP BY <collections>
HAVING <filter conditions>

DATABASE DESIGN

Constraints

  1. entity integrity (primary key constraint)
  2. referential integrity (foreign key)
  3. data
  4. Data types
  5. enum and set
  6. invalid data
  7. date format
  8. not null

DDL operationer:

  1. Create table
  2. Drop table if exists (Order of drop table statements in reverse)
  3. Alter table
  4. Composite primary key
  5. Foreign key constraints
  6. Circular references
  7. Creating a DDL script for the application
  8. MySql View

Circular reference example

Employees

    EmployeeID <PK>  
    DepartmentID <FK> NOT NULL

Departments

DepartmentID <PK>  
EmployeeID <FK> NOT NULL

Or within a table we could have a foreign key (representing a manager of a particular employee). The foreign key would then point to a primary key of an employee (within the same table).

Data manipulation

Eksempler på SQL queries her

NORMALIZATION / OPTIMIZATION

Det kan være svært at huske hvad der er hvad i de 3 normalformer. Et huske trick er dette:

“The key, the whole key, and nothing but the key, so help me Codd.” (Edgar F. Codd var manden, der opfandt konceptet med relationelle databaser).

Tekst Forklaring
THE KEY Alle kollonner og alt data skal være afhængig af en primær nøgle. DVS at all data i en række er relateret direkte til en primærnøgle og dermed kan identificeres unikt. I denne første normalform er reglen også at data skal være atomisk i en enkelt kolonne (ikke flere værdier adskilt eks. af komma)
THE WHOLE KEY I tabeller med sammensatte primærnøgler må data i de andre kolonner ikke være afhængig af kun en del af den sammensatte nøgle. (Eksempel: Primærnøgle for ordrelinje tabel: prod_id, order_id. Hvis tabellen desuden har kolonne. product name, så er product name kun afhængig af en del af nøglen: nemlig prod_id)
AND NOTHING BUT THE KEY Data i de andre kolonner må ikke være afhængig af data i en kolonne, der ikke er primærnøglen. (Eksempel: adresse, som har postnr og by, da by er afhængig ikke af adressen men af postnummeret).

Violation of Second NF: