Code, JavaEE, Technical

Conference Database – Part 1

This database will contain conference attendee information to be used for future events marketing.

Initial Environment Tasks

  1. Laptop with Ubuntu installed
  2. Install Sun Java 1.7 latest version
  3. Install Apache Maven latest version
  4. Install IntelliJ IDEA IDE Community Version
  5. Configure .bashrc with environment variables
  6. Install git – sudo apt-get install git
  7. Install MySQL – sudo apt-get install mysql-server mysql-client mysql-query-browser
  8. Create Projects directory

Project Setup

  1. Start development IDE
  2. Create new maven project in Projects directory, name the project conference
  3. Choose the maven quickstart archetype
  4. Use the groupId – com.davidking
  5. Use the artifactId – conference
  6. Update the maven pom.xml file dependency version for junit from 3.8.1 to 4.11, save the file
  7. Enable maven auto import for IntelliJ IDE

.bashrc


# development
export JAVA_HOME=/home/davidking/Tools/jdk1.7.0_21
export M2_HOME=/home/davidking/Tools/apache-maven-3.0.5
export PATH=$JAVA_HOME/bin:$M2_HOME/bin:$PATH

pom.xml should look like the following


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

<groupId>com.davidking</groupId>
 <artifactId>conference</artifactId>
 <version>1.0-SNAPSHOT</version>
 <packaging>jar</packaging>

<name>conference</name>
 <url>http://maven.apache.org</url>

<properties>
 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>

<dependencies>
 <dependency>
 <groupId>junit</groupId>
 <artifactId>junit</artifactId>
 <version>4.11</version>
 <scope>test</scope>
 </dependency>
 </dependencies>
</project>

Database Scripts

This application requires a MySQL Database Server (you installed earlier). The following scripts are required to get started.

conference-createdatabase.sql (login as root for this script)


create database conference;
use conference;

grant all on conference.*
to 'davidking'
identified by 'davidking';

conference-initdatabase.sql (login as davidking/davidking)


use conference;

create table contact (
id int not null primary key auto_increment,
address1 varchar(200) not null,
address2 varchar(200) null default null,
city varchar(100) not null,
state varchar(2) null default null,
zip varchar(5) null default null,
province varchar(150) null default null,
intl char(1) null default 'N',
website varchar(255) null default null,
email varchar(200) null default null,
twitter varchar(20) null default null,
facebook varchar(50) null default null,
linkedin varchar(50) null default null
) engine=innodb;

create table conference (
id int not null primary key auto_increment,
title varchar(180) not null,
theme varchar(180) null default null,
contact_id int(11) null,
foreign key (contact_id) references contact(id)
) engine=innodb;

create table attendee (
id int not null primary key auto_increment,
firstname varchar(100) not null,
lastname varchar(100) not null,
contact_id int(11) null,
foreign key (contact_id) references contact(id)
) engine=innodb;

create table conference_attendee (
id int not null primary key auto_increment,
conference_id int(11) not null,
attendee_id int(11) not null
) engine=innodb;

conference-sampledata.sql (login as davidking/davidking)

</pre>
use conference;

-- create contact
insert into contact (address1, city, state, zip)
values('1000 Test Street','Pembroke Pines','FL','33025');

-- create attendee
insert into attendee (firstname, lastname, contact_id)
values('David','King', 1);

-- create conference
insert into conference (title, theme, contact_id)
values('Advancing the Kingdom Conference 2013','We''re Advancing the Kingdom', 1);

-- create conference attendee
insert into conference_attendee(conference_id, attendee_id)
values(1,1);
<pre>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s