35.1.1. Background[6]
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
TableGroup of columns
Represents a type
Commonly mapped to a Java class
ColumnSingle piece of data
Represents a property
Commonly mapped to a Java class attribute
Sample (H2) Column Types[7]
INTEGER
DECIMAL
TIME
DATE
TIMESTAMP
VARCHAR
BLOB
CLOB
NOT NULLRow cannot exist without this column value supplied
UNIQUENo other row may have a column with this value
FOREIGN KEYIf supplied, must reference matching column(s) of existing row
Foreign Key JoinColumn within child table references parent
Primary Key JoinForeign key column within child table is child's primary key column. Parent and child table primary keys must match.
Link Table JoinForeign keys to parent/child expressed in separate table
Foreign keys may be defined in child table or link table
Link tables can always be used (at an extra cost) no matter the cardinality
Foreign keys cannot be defined on "one side" of a one-to-many relationship
Link table must be used if foreign key cannot be placed on many side
Link tables must be used in many-to-many relationships
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)
);
alter table JPADAO_BOOK
add constraint JPADAO_BOOK_AUTHOR_FK
foreign key (AUTHOR_ID)
references JPADAO_AUTHOR
create index JPADAO_BOOK_AUTHOR_FKX on JPADAO_BOOK(AUTHOR_ID);
create unique index JPADAO_BOOK_TITLE_IDX on JPADAO_BOOK(TITLE);
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