Enterprise Java Development@TOPIC@

Chapter 35. RDBMS Schema

35.1. RDBMS
35.1.1. Background
35.1.2. Tables/Columns
35.1.3. Constraints
35.1.4. Relationships
35.1.5. Indexes
35.2. Data Definition Language (DDL)
35.2.1. Create Table
35.2.2. Drop Table
35.2.3. Create Foreign Key Constraint
35.2.4. Drop Foreign Key Constraint
35.2.5. Create Index
35.2.6. Drop Index
35.3. DDL Files in Maven Module

35.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 35.1. Tables and Columns

Tables and Columns

Sample (H2) Column Types[7]

  • 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

35.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;

35.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

[6] RDBMS Wikipedia Page

[7] H2 Data Types