Creació de bases de dades

CREATE DATABASE

La primera instrucció DDL que cal conèixer és CREATE DATABASE, que crea un nou contenidor de dades (una nova base de dades) dins del servidor SGBD.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Creació bàsica
CREATE DATABASE gbd_practica;

-- Creació amb opcions
CREATE DATABASE gbd_practica
    WITH
    OWNER = gbd_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'ca_ES.UTF-8'
    LC_CTYPE = 'ca_ES.UTF-8'
    TEMPLATE = template0;

-- Verificar les BD existents
\l
-- O bé:
SELECT datname, pg_encoding_to_char(encoding) AS encoding,
       datcollate, datctype
FROM pg_database;

-- Creació bàsica
CREATE DATABASE gbd_practica;

-- Creació amb opcions
CREATE DATABASE gbd_practica
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Verificar les BD existents
SHOW DATABASES;

-- Seleccionar la BD de treball
USE gbd_practica;

-- Veure la configuració de la BD actual
SELECT @@character_set_database, @@collation_database;

-- Creació bàsica
CREATE DATABASE gbd_practica;

-- Creació amb opcions
CREATE DATABASE gbd_practica
ON PRIMARY (
    NAME = 'gbd_practica_data',
    FILENAME = '/var/opt/mssql/data/gbd_practica.mdf',
    SIZE = 10MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = 'gbd_practica_log',
    FILENAME = '/var/opt/mssql/data/gbd_practica_log.ldf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
);

-- Verificar les BD existents
SELECT name, collation_name, create_date
FROM sys.databases;

-- Seleccionar la BD de treball
USE gbd_practica;
GO

-- A Oracle, la base de dades (instància) es crea durant la instal·lació.
-- En entorns Docker (Oracle XE), la instància ja existeix (XE).
-- La unitat de treball habitual és la PDB (Pluggable Database, Oracle 12c+).

-- Crear una nova PDB dins la CDB (connectat com SYSDBA al CDB$ROOT)
CREATE PLUGGABLE DATABASE gbd_practica
    ADMIN USER pdb_admin IDENTIFIED BY "Pwd_Segur1"
    DEFAULT TABLESPACE users
    DATAFILE '/opt/oracle/oradata/XE/gbd_practica/users01.dbf'
        SIZE 250M AUTOEXTEND ON
    PATH_PREFIX = '/opt/oracle/oradata/XE/gbd_practica/';

-- Obrir la nova PDB
ALTER PLUGGABLE DATABASE gbd_practica OPEN;

-- Connectar-se a la PDB
ALTER SESSION SET CONTAINER = gbd_practica;

-- Verificar les PDBs existents
SELECT pdb_name, status FROM cdb_pdbs ORDER BY pdb_name;

Oracle XE i Docker

A Oracle XE (Express Edition), la PDB per defecte ja creada és XEPDB1. Per a pràctiques, podeu connectar-vos directament a XEPDB1 sense crear una nova PDB: sqlplus system/oracle@//localhost:1521/XEPDB1.

Conjunts de caràcters i collations

Dos conceptes importants en la creació d'una BD:

Conjunt de caràcters (Character Set / Encoding)

El conjunt de caràcters determina quins caràcters es poden emmagatzemar. Per a qualsevol aplicació que hagi de suportar idiomes europeus (inclòs el català amb els seus accents i la l·l), cal usar UTF-8:

PostgreSQL: UTF8 (la base es crea sempre amb un encoding explícit)
MySQL/MariaDB: utf8mb4 (sempre usar utf8mb4, mai utf8, que en MySQL és un subconjunt de 3 bytes i no suporta tots els caràcters Unicode com emojis)
SQL Server: La col·lació Latin1_General_CI_AS o Catalan_CI_AS implica UTF-16 internament

El problema de utf8 a MySQL

En MySQL, el charset utf8 és incorrectament limitat a 3 bytes per caràcter, excloen emojis i alguns caràcters asiàtics. Sempre useu utf8mb4 a MySQL/MariaDB. No fer-ho és un error clàssic que pot causar problemes greus més endavant.

Collation

La collation determina com es comparen i s'ordenen les cadenes de text. Per exemple:

Si 'a' == 'A' (insensibilitat a majúscules: CI = Case Insensitive)
Si 'a' == 'à' (insensibilitat a accents: AI = Accent Insensitive)
L'ordre d'ordenació de caràcters especials ('ç', 'l·l', 'ñ')

Collation	Motor	Significat
`ca_ES.UTF-8`	PostgreSQL	Collation catalana (LC_COLLATE), definida a nivell de BD
`en_US.UTF-8`	PostgreSQL	Collation anglesa, la més habitual quan no cal ordre lingüístic
`C` / `POSIX`	PostgreSQL	Comparació binària pura, sense regles lingüístiques
`UTF8_general_ci`	MySQL	UTF-8, insensible a majúscules
`utf8mb4_unicode_ci`	MySQL	UTF-8 MB4, insensible a majúscules, Unicode complert
`utf8mb4_0900_ai_ci`	MySQL 8.0+	UTF-8 MB4, insensible a majúscules i accents, Unicode 9.0
`Catalan_CI_AS`	SQL Server	Collation catalana, insensible a majúscules, sensible a accents
`Latin1_General_CI_AS`	SQL Server	Collation llatina general, insensible a majúscules, sensible a accents
`CATALAN` (NLS_SORT)	Oracle	Ordre lingüístic català via paràmetre de sessió NLS_SORT
`BINARY` (NLS_SORT)	Oracle	Comparació binària pura (per defecte a Oracle)
`BINARY_CI` (NLS_SORT)	Oracle	Binari insensible a majúscules (Oracle 12c+)

Com s'aplica la Collation a PostgreSQL i Oracle

A PostgreSQL, la Collation (LC_COLLATE) es defineix en el moment de crear la base de dades i no és pot canviar posteriorment sense recrear-la. A partir de PostgreSQL 12 també es pot definir Collation a nivell de columna amb COLLATE "ca_ES.UTF-8". Per defecte, PostgreSQL hereta la Collationdel sistema operatiu.

A Oracle, no hi ha un paràmetre COLLATE a CREATE DATABASE. En comptes d'això, l'ordre lingüístic es controla amb els paràmetres NLS (National Language Support):

-- A nivell de sessió
ALTER SESSION SET NLS_SORT = 'CATALAN';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';

-- A nivell de columna (Oracle 12c R2+)
CREATE TABLE clients (
    nom VARCHAR2(100) COLLATE CATALAN_CI
);

El charset de la base de dades Oracle (AL32UTF8 per a Unicode) es defineix a CREATE DATABASE i tampoc es pot canviar sense recrear-la.

Esquemes (Schemas)

Un esquema (schema) és un espai de noms dins d'una base de dades que permet organitzar i agrupar objectes (taules, vistes, funcions). És similar a una carpeta o directori.

Usos dels esquemes:

Separar objectes de diferents parts d'una aplicació (per exemple, vendes.clients vs rrhh.empleats).
Gestió de privilegis: podem donar accés a un esquema sencer o només a taules específiques.
Evitar conflictes de noms entre objectes de diferents aplicacions.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Esquema public és el per defecte
-- Crear un nou esquema
CREATE SCHEMA vendes;
CREATE SCHEMA rrhh;

-- Crear una taula en un esquema específic
CREATE TABLE vendes.clients (
    id_client SERIAL PRIMARY KEY,
    nom       VARCHAR(100) NOT NULL
);

-- Consultar taules d'un esquema
SELECT tablename, schemaname
FROM pg_tables
WHERE schemaname IN ('vendes', 'rrhh', 'public');

-- Canviar el search_path (esquema per defecte de la sessió)
SET search_path TO vendes, public;

-- A MySQL, 'schema' i 'database' son sinònims
CREATE SCHEMA vendes;
-- Equivalent a:
CREATE DATABASE vendes;

-- Les taules s'ubiquen directament a la BD
-- Per a separació lògica, s'usen prefixos de nom:
CREATE TABLE vendes_clients (...);
CREATE TABLE rrhh_empleats (...);

-- SQL Server suporta esquemes reals
CREATE SCHEMA vendes;
CREATE SCHEMA rrhh;

-- Crear taula en esquema específic
CREATE TABLE vendes.clients (
    id_client INT IDENTITY(1,1) PRIMARY KEY,
    nom       NVARCHAR(100) NOT NULL
);

-- L'esquema per defecte és 'dbo'
-- SELECT * FROM clients = SELECT * FROM dbo.clients

-- A Oracle, un esquema = un usuari.
-- Crear l'esquema 'vendes' vol dir crear l'usuari 'vendes'.
CREATE USER vendes IDENTIFIED BY "Pwd_Segur1"
    DEFAULT TABLESPACE users
    QUOTA 100M ON users;

CREATE USER rrhh IDENTIFIED BY "Pwd_Segur2"
    DEFAULT TABLESPACE users
    QUOTA 100M ON users;

-- Crear una taula dins l'esquema d'un usuari
-- (des del propi usuari o amb privilegi CREATE ANY TABLE)
CREATE TABLE vendes.clients (
    id_client NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom       VARCHAR2(100) NOT NULL
);

-- Consultar taules d'un esquema
SELECT owner, table_name
FROM dba_tables
WHERE owner IN ('VENDES', 'RRHH')
ORDER BY owner, table_name;

-- Crear sinònim privat per evitar el prefix
CREATE SYNONYM clients FOR vendes.clients;
-- Ara: SELECT * FROM clients funciona sense prefix

Usuari = Esquema a Oracle

A Oracle no hi ha CREATE SCHEMA independent: crear un usuari crea automàticament el seu esquema. Tots els objectes propietat d'un usuari pertanyen al seu esquema, que té el mateix nom que l'usuari.

AC0372/03/03 — Miniactivitat

RA3 · CA3.1, CA3.2

Connecteu-vos a PostgreSQL i creeu una base de dades gbd_nom_cognom amb encoding UTF8 i dos esquemes: vendes i productes. Creeu una taula senzilla en cada esquema. Verifiqueu amb consultes al catàleg que els esquemes i les taules s'han creat correctament.