9lessons Programming Blog

This lesson explains how to project a typical relationships-entities model for a database to be used in our web projects. My approach is:

1. Define database entities (tables)
2. Identify attributes for tables
3. Define relationships and cardinality between the instances (records) of tables.

Step 1: define database entities
The first step when you project a database is to identify all entities (tables). For example if we want to project a simplified del.icio.us-like web site, our database will have these entities:

1. - USER (to store data about users, email, password, nickname,...)
2. - SITE (to store data about the sites added by the users)

These are only the main entities required from our project but, take a mind, that we will add other tables to store data about relationships between istances (records) of these tables in case of cardinality (M:M), many to many (see Step 3).

Step 2: define attributes
The next step is to define attributes for the tables USER and SITE. In this semplified example we will have something like this:

USER
-----------
user_id_pk (Primary Key)
user_name
email
password
user_data (user signup date)

SITE
-----------
site_id_pk (Primary Key)
url
description
share_user (total number of users that share a site)

Step 3: define database relationships
Our simple application del.icio.us-like works in this way: an user add a site that can be shared by other users. The relationship's cardinality between USER table and SITE table is:

USER > SITE (M:M) - Many to Many (an user can add many sites).
SITE > USER (M:M) - Many to Many (a site can be shared by many users).

In this case ( cardinality M:M) we have to add a new table (SHARE) that contains all possible combination between all instances of USER table and SITE table . In this new table, SHARE, to identify an user that share a site added by another user or by itself, we will add two Foreign Key:

SHARE
-----------
share_id_pk (Primary Key)
user_id (Foreign Key > USER)
site_id (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)

Implement your database using SQL
Now, our database is ready to be implement with a DBMS (for example using MySQL). The next lesson will explains how to implement this database using SQL language.
Next Lessons:Create Tables and Relationships with SQL

My brother Ravi Tamada request one mail about his college presentation. He is planning to do web dynamic project. So i am giving small explanation about JSP (Java Server Pages) to Mysql Connection structure, Tomcat directory structure and simple database examples.

Login.html

Code :

<body>
<form action="login.jsp" method="post">

User name :<input type="text" name="usr" />
password :<input type="password" name="pwd" />
<input type="submit" />

</form>

</body>

Reg.html

code:

Email :<input type="text" name="email" />
First name :<input type="text" name="fname" />
Last name :<input type="text" name="lname" />
User name :<input type="text" name="userid" />
password :<input type="password" name="pwd" />
<input type="submit" />

</form>

Mysql Create Database Test:
Mysql no doubt about it best open source database http://mysql.com/

Create Table Users:

login.jsp

<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<%
String userid=request.getParameter("user");
session.putValue("userid",userid);
String pwd=request.getParameter("pwd");
Class.forName("com.mysql.jdbc.Driver");
java.sql.Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/
test","root","root");
Statement st= con.createStatement();
ResultSet rs=st.executeQuery("select * from users where user_id='"+userid+"'");
if(rs.next())
{
if(rs.getString(2).equals(pwd))
{
out.println("welcome"+userid);

}
else
{
out.println("Invalid password try again");
}
}
else
%>

reg.jsp

<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<%
String user=request.getParameter("userid");
session.putValue("userid",user);
String pwd=request.getParameter("pwd");
String fname=request.getParameter("fname");
String lname=request.getParameter("lname");
String email=request.getParameter("email");
Class.forName("com.mysql.jdbc.Driver");
java.sql.Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
"root","root");
Statement st= con.createStatement();
ResultSet rs;
int i=st.executeUpdate("insert into users values ('"+user+"','"+pwd+"','"+fname+"',
'"+lname+"','"+email+"')");

%>

welcome.jsp

<%@ page import ="java.sql.*" %>
<%@ page import ="javax.sql.*" %>
<%
String user=session.getValue("userid").toString();
%>
Registration is Successfull. Welcome to <%=user %>

Tomcat Directory Structure
Tomcat open source web server you can download from this link http://tomcat.apache.org/