Shows how to access a database and execute a parameterized query. You will need add a jar to handle MySQL for this program. For instructions on adding JARS, see link.
Shows how to read a csv file and insert two rows for each record read. Illustrates how to use autoincrement with a primary key and foreign key relationship. You will need add a jar to read a csv file for this program. For instructions on adding JARS, see link.
The following four tables are used with Art Collection
CREATE TABLE artist (
artistID int(11) NOT NULL AUTO_INCREMENT,
firstName varchar(25) DEFAULT NULL,
lastName varchar(25) DEFAULT NULL,
birthyear int(11) DEFAULT NULL,
deathyear int(11) DEFAULT NULL,
nationality varchar(25) DEFAULT NULL,
PRIMARY KEY (artistID));
CREATE TABLE art (
artID int(11) NOT NULL AUTO_INCREMENT,
title varchar(50) DEFAULT NULL,
length decimal(6,2) DEFAULT NULL,
breadth decimal(6,2) DEFAULT NULL,
year int(11) DEFAULT NULL,
artistID int(11) NOT NULL,
PRIMARY KEY (artID),
KEY fk_art_artist1 (artistID),
CONSTRAINT fk_art_artist1 FOREIGN KEY (artistID) REFERENCES artist (artistID));
CREATE TABLE painting (
artID int(11) NOT NULL,
style varchar(25) DEFAULT NULL,
medium varchar(25) DEFAULT NULL,
KEY fk_table1_art (artID),
CONSTRAINT fk_table1_art FOREIGN KEY (artID) REFERENCES art (artID));
CREATE TABLE sculpture (
artID int(11) NOT NULL AUTO_INCREMENT,
height decimal(6,2) DEFAULT NULL,
material varchar(25) DEFAULT NULL,
PRIMARY KEY (artID),
KEY fk_sculpture_art1 (artID),
CONSTRAINT fk_sculpture_art1 FOREIGN KEY (artID) REFERENCES art (artID));
The following three tables are used with Map Collection.
CREATE TABLE country(
cntrycode CHAR(2) PRIMARY KEY,
cntryname VARCHAR(20));
CREATE TABLE map(
mapid CHAR(4) PRIMARY KEY,
mapscale INT,
maptype VARCHAR(20));
CREATE TABLE mapCountry(
mapid CHAR(4),
cntrycode CHAR(2),
PRIMARY KEY(mapid,cntrycode),
CONSTRAINT fk_map FOREIGN KEY(mapid) REFERENCES map(mapid),
CONSTRAINT fk_country FOREIGN KEY(cntrycode) REFERENCES country(cntrycode));
INSERT INTO country VALUES ('at', 'Austria');
INSERT INTO country VALUES ('de', 'Germany');
INSERT INTO country VALUES ('li', 'Liechtenstein');
INSERT INTO country VALUES ('ch', 'Switzerland');
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |