Enterprise Java Development@TOPIC@

Chapter 4. RDBMS Schema

4.1. RDBMS
4.1.1. Background
4.1.2. Tables/Columns
4.1.3. Constraints
4.1.4. Relationships
4.1.5. Indexes
4.2. Data Definition Language (DDL)
4.2.1. Create Table
4.2.2. Drop Table
4.2.3. Create Foreign Key Constraint
4.2.4. Drop Foreign Key Constraint
4.2.5. Create Index
4.2.6. Drop Index
4.3. DDL Files in Maven Module

4.1. RDBMS

  • DBMS based on a relational model

  • Introduced by E. F. Codd in 1970s

  • Some challenges by other forms but still remains a standard for corporate data stores

Table
Column

Figure 4.1. Tables and Columns

Tables and Columns

Sample (H2) Column Types[2]

  • INTEGER

  • DECIMAL

  • TIME

  • DATE

  • TIMESTAMP

  • VARCHAR

  • BLOB

  • CLOB

NOT NULL

Row cannot exist without this column value supplied

UNIQUE

No other row may have a column with this value

FOREIGN KEY

If supplied, must reference matching column(s) of existing row

Foreign Key Join

Column within child table references parent

Primary Key Join

Foreign key column within child table is child's primary key column. Parent and child table primary keys must match.

Link Table Join

Foreign keys to parent/child expressed in separate table

4.2. Data Definition Language (DDL)

Used to manipulate schema in RDBMS

create table JPADAO_AUTHOR (
    ID integer generated by default as identity,
    FIRST_NAME varchar(16) not null,
    LAST_NAME varchar(32) not null,
    primary key (ID)
);
create table JPADAO_BOOK (
    ID bigint generated by default as identity,
    DESCRIPTION varchar(1000),
    PAGES integer,
    TITLE varchar(32) not null,
    AUTHOR_ID integer,
    primary key (ID)
);
drop table JPADAO_AUTHOR if exists;
drop table JPADAO_BOOK if exists;
alter table JPADAO_BOOK 
    add constraint JPADAO_BOOK_AUTHOR_FK 
    foreign key (AUTHOR_ID) 
    references JPADAO_AUTHOR
alter table JPADAO_BOOK drop constraint JPADAO_BOOK_AUTHOR_FK;
create index JPADAO_BOOK_AUTHOR_FKX on JPADAO_BOOK(AUTHOR_ID);
create unique index JPADAO_BOOK_TITLE_IDX on JPADAO_BOOK(TITLE);
drop index JPADAO_BOOK_AUTHOR_FKX if exists;
drop index JPADAO_BOOK_TITLE_IDX if exists;

4.3. DDL Files in Maven Module

src/main/resources/
`-- ddl
    |-- book-create.ddl
    |-- book-drop.ddl
    |-- book-tuningadd.ddl
    `-- book-tuningremove.ddl
target/classes/
`-- ddl
    |-- book-create.ddl
    |-- book-drop.ddl
    |-- book-tuningadd.ddl
    `-- book-tuningremove.ddl

[1] RDBMS Wikipedia Page

[2] H2 Data Types