3. System Design 41‐67
3.4 Data Dictionary
Table Name: ActivityLog
Description : store all the activity done by user in the system Fields Data Type Null/Not
Null
Default
Value Rules
LogId int Not null - -
LogEmp nchar(10) Not null - -
LogTime datetime Not null - -
LogActivity varchar(MAX) Not null - -
Field Example Data
LogId(PK) 113 LogEmp M0001
LogTime 2011-02-16 16:35:07.000
LogActivity Information of PublisherID P0003 has been updated
Table Name: Admin
Description : store the information of user who used the library system Fields Data Type Null/Not
Null
Default
Value Rules
Admin_ID nvarchar(50) Not null - - Admin_Name nvarchar(50) Not null - - Admin_Level nvarchar(50) Not null - Format : 1,0 Password nvarchar(50) Not null - - Admin_ic nvarchar(50) Not null - - admin_contact nvarchar(50) Not null - - admin_email nvarchar(50) Not null - - admin_address nvarchar(MAX) Not null - -
Table Name: Book
Description : store the information of the books Fields Data Type Null/Not
Null
Default
Value Rules
ISBN nvarchar(50) Not null - -
BookTitle nvarchar(50) Not null - -
Author nvarchar(50) Not null - -
PublisherID nvarchar(50) Not null - - Language nvarchar(50) Not null - -
Category nvarchar(50) Not null - -
Description nvarchar(MAX) Not null - - BookCover nvarchar(MAX) Not null - -
Field Example Data
ISBN(PK) 9781587132049 BookTitle Handphone King Author C.Y
PublisherID P0001 Language Chinese Category Technology
Description A book which show the latest information of all brand handphone BookCover Handphone.JPG
Table Name: BookComment
Description : to store the comment for particular book Fields Data Type Null/Not
Null
Default
Value Rules
CID Int Not null - -
ISBN Nvarchar(50) Not null - -
UserID Nvarchar(max) Not null - -
Comment Nvarchar(max) Not null - -
Field Example Data
CID 13
ISBN 1234567891234 UserID M0001
Table Name: BookCopy
Description : to store the quantities of books and the detail of each book Fields Data Type Null/Not
Null
Default
Value Rules
BarcodeID nvarchar(50) Not null - -
ISBN nvarchar(50) Not null - -
Status nvarchar(50) Not null - Format : L, A, N PurchasePrice Money Not null - -
purchaseDate Datetime Not null - -
Field Example Data
barcodeID 978158713204901 ISBN 9781587132049 Status L
PurchasePrice 200.0000
PurchaseDate 2011-02-16 00:00:00.000
Table Name: News
Description : to post the latest news at web site’s main page Fields Data Type Null/Not
Null
Default
Value Rules
ID Int Not null - -
Date Date Not null - -
[content] Nvarchar(50) Not null - -
Field Example Data
ID 9781587132049 Date 2011-01-13
[content] The popular book ‘The Lord of the Ring’ is now available !!!
Table Name: LibraryDetail
Description : to store the information of the library Fields Data Type Null/Not
Null
Default
Value Rules
libno Char(10) Not null - -
Libname varchar(50) Not null - -
Libadd1 varchar(50) Not null - -
Libadd2 varchar(50) Not null - -
Libposcode Char(5) Not null - -
Libstate varchar(50) Not null - -
Libtown varchar(50) Not null - -
Libphone varchar(50) Not null - -
Libfax varchar(50) Not null - -
Libemail varchar(50) Not null - Format : [email protected]
Libweb varchar(50) Not null - -
Field Example Data
libno 001
Libname Chen Library
Libadd1 30, lintang perai 5, Libadd2 Taman putang, Libposcode 13506
Libstate Pulau Pinang Libtown Butterworth Libphone 04-3859451 Libfax 04-3225645
Libemail [email protected] Libweb www.google.com
Table Name: Reservation
Description : to record the book reservation for the member Fields Data Type Null/Not
Null
Default
Value Rules
UserID Nvarchar(50) Not null - -
BarCodeId Nvarchar(50) Not null - -
DateReserve date Not null - -
Field Example Data
Fields Data Type Null/Not Null
Default
Value Rules
ID int Not null - -
Userid varchar(50) Not null - -
Barcodeid varchar(50) Not null - -
ISBN varchar(50) Not null - -
LostDate Date Not null - -
Field Example Data
ID 3 Userid M0002
Barcodeid 978158713204901 ISBN 9781587132049 LostDate 2011-02-16
Table Name: RetalInfo
Description : to keep the book transaction detail Fields Data Type Null/Not
Null
Default
Value Rules
userID nvarchar(50) Not null - -
BarCodeID nvarchar(50) Not null - -
DateReturned Datetime - - -
DateRented Datetime Not null - -
DateDue Datetime Not null - -
Total Fine money - - -
Field Example Data
userID M0003 BarCodeID 123456789123401 DateReturned 2011-03-02 00:00:00.000 DateRented 2010-09-09 00:00:00.000
Table Name: Publisher
Description : to store the publisher so it is available when register book Fields Data Type Null/Not
Null
Default
Value Rules
PublisherId Nvarchar(50) Not null - - PublisherName nvarchar(50) Not null - -
Field Example Data
PublisherId P0001 PublisherName Tan Khen Khen
Table Name: User
Description : to store the information of the member Fields Data Type Null/Not
Null
Default
Value Rules
UserID Char(10) Not null - -
Username varchar(50) Not null - -
Useraddress varchar(50) Not null - - UserPhone varchar(50) Not null - -
UserIC Char(5) Not null - -
UserRegDate varchar(50) Not null - - AvailableBook varchar(50) Not null - -
Userpass varchar(50) Not null - -
Userphoto varchar(50) Not null - -
Useremail varchar(50) Not null - Format : [email protected] UserExpiredDate varchar(50) Not null - -
Field Example Data
UserID M001 Username Ooi Yee Neng
Useraddress 30 lintang talang 2, taman perai. 13600 Perai, Penang. UserPhone 04-3568956 UserIC 880407-35-5266 UserRegDate 2011-02-16 00:00:00.000 AvailableBook 4 Userpass 12345678 Userphoto Tan_chen_khen.JPG
3.5 Summary
In this chapter discuss about normalization, Entity Relationship Diagram (ERD), interface design and also database design.
For the interface design, because it is website and it for member and librarian, the website must design user friendly so that every user can easily learn how to use the website in a short period of time. When I am designing this website, there are few problems occur. One of the problems is the alignment of the website. When I design at Visual studio 2008 the lay out should be find but when run it, the alignment is not correct. I have to readjust it. Second problem is the browser problem. Different browser may cause different interface when run. I faced internet explorer 7 problem. When run the website, the alignment is not correct compare to Mozilla Firefox and also Google Chrome when run. After research and also friend helps, I used script to control so that when it comes to different browser it will run the particular browser alignment.
Besides that, I met a big problem on normalization. Because of the database design has already designed, we have to normalize from 3rd to 1st normalization form. In this normalization, I found out that some of the table does not naming properly. Example Staff table normally for ID would be StaffID but we name it as ID. In this case, We could not know this ID is for which table but it actually is staff id. After discuss with my group mate, we change the naming of the database to overcome the problem.
Chapter 4
4.0 Programming
This chapter will discuss about coding for the system. In the development, logic and coding skill is required. A good coding can enhance the efficiency of the system so that when other people wanted to implement the coding they can easily understand the code. Following would discuss the sample programming and description of the book.
4.1 Coding
1. Database Class using System.Data.OleDb; public class DBConnector
{
OleDbConnection con;
public OleDbDataReader dtr; public DataSet DS = new DataSet();
public DBConnector(string strProvider, string strDBPath) {
con = new OleDbConnection("Provider = SQLNCLI10; server = 127.0.0.1 ;Database=LibrarySystem; user id = keat; Password = 1234;");
}
public bool con_Open() { con.Open(); try { return true; } catch { return false; } }
cmd = new OleDbCommand(strQuery, con); dtr = cmd.ExecuteReader(); if (dtr.HasRows) { return true; } else { return false; } }
public bool DB_Command(string strQuery) {
OleDbCommand cmd;
cmd = new OleDbCommand(strQuery, con); cmd.ExecuteNonQuery(); return true; try { } catch { return false; } }
public bool DB_DS(string strQuery,string strTableName) {
OleDbDataAdapter adp = new OleDbDataAdapter(strQuery, con); adp.Fill(DS, strTableName); try { return true; } catch { return false; } } }
The coding above about is a database class. At first we need to import a OLEDB class. It is to ease to reuse this class to connect the database. In this class, it has created a few
from the database. DB_Reader() required to insert a SQL query to get the data . DB_DS() is a dataset. It use to store the data that has been retrieve from the database. It required to insert a SQL query and a data table name. DB_Command() is database command allow it read the execute insert, update and delete in the database. Below is the example of the query used.
DBConnector DBase = new DBConnector();
DBase.con_Open(); Dbase.con_Close();
This code use when we want to open a connection to database. Dbase.con_Open() is to open the connection. Dbase.con_Close(), is to close the database connection.
DBase.DB_Reader("SELECT * FROM BOOK")
DBase.DB_DS("SELECT * FROM BOOK",”BookTable”);
This code use when we want to get data from database to data reader and data set. Data set need one more parameter because data set needed table name.
DBase.DB_Command("INSERT INTO [Admin] VALUES [admin_address]='" + address + "'); DBase.DB_Command("UPDATE [Admin] SET [admin_address]='" + address + "');
DBase.DB_Command("DELETE FROM [Admin] WHERE [admin_id]=’123’ );
This code use when we want to execute a insert or update, or delete into database. It require a insert or update or delete command at this function.
2. Search Function
DBConnector DBase = new DBConnector();
DBase.con_Open();
DBase.DB_DS("SELECT dbo.Book.ISBN, dbo.Book.BookTitle, dbo.Book.Author,
dbo.Book.Language, dbo.Book.Category, dbo.Publisher.PublisherName FROM dbo.Book INNER JOIN dbo.Publisher ON dbo.Book.PublisherID = dbo.Publisher.PublisherID WHERE [" + type + "] LIKE '%"
+ search + "%'", "Book");
gvSearch.DataSource = DBase.DS; gvSearch.DataBind();
DBase.con_Close();
This is the search function of the website. It will first put the data into the data set and after that bind the data to gvSearch gridview. When the button search click, this query will be execute.
<asp:GridView ID="gvSearch" runat="server" AutoGenerateColumns="False" onrowcommand="gvSearch_RowCommand"
OnPageIndexChanging="gvSearch_PageIndexChanging" AllowPaging="True" onselectedindexchanged="gvSearch_SelectedIndexChanged">
<Columns>
<asp:BoundField DataField="ISBN" HeaderText="ISBN" />
<asp:BoundField DataField="BookTitle" HeaderText="BookTitle" /> <asp:BoundField DataField="Author" HeaderText="Author" />
<asp:BoundField DataField="PublisherName" HeaderText="PublisherName" /> <asp:BoundField DataField="Category" HeaderText="Category" />
<asp:ButtonField CommandName="Detail" HeaderText="Detail" Text="Detail" /> </Columns>
</asp:GridView>
This is a gridview called gvSearch. By putting in onselectedindexchanged= gvSearch_ SelectedIndexChanged”, it enable the gridview to put in the button. In there I have put the detail button so that when the user search the book they can click the detail button to see the book detail.
protected void gvSearch_RowCommand(object sender, GridViewCommandEventArgs e) {
string command = e.CommandName.ToString(); if ((command).ToUpper() == ("detail").ToUpper())
} }
This is the detail button which allows the user to click and transfer to another page. So when the button is click, the id of the row will be store and transfer to other page. So that when the book description page show they will know which the book that has to be show is.
3. Email
using System.Net.Mail;
MailMessage message = new MailMessage();
message.To.Add("[email protected]"); message.Subject = txtsubject.Text;
message.From = new System.Net.Mail.MailAddress(txtfrom.Text); message.Body = TextArea1.Value;
SmtpClient sc = new SmtpClient();
sc.Credentials = new System.Net.NetworkCredential("[email protected]",
"XXXXXXXXX");
sc.Port = 587;
sc.Host = "smtp.live.com"; sc.EnableSsl = true; sc.Send(message);
The coding above is about Email. At first we have to import Mail class in order to use Email. This function allows us to send an email. The thing we need to prepare is a email and find a smtp server. At here I used hotmail as my smtp server and
[email protected] as my hotmail. The port to hotmail server is 587 and the host
address for hotmail is smtp.live.com. After prepare, we will just have a subject, a sender mail, a receiver mail and content. This part I have implement in the FAQ part and also
4. Log in / Log out
private bool Login(string ID, string password, string Type) {
DBConnector DBase = new DBConnector(); string msg; bool valid; if (DBase.con_Open()) { if (Type == "Admin") {
if (DBase.DB_Reader("SELECT * FROM [Admin] WHERE Admin_ID='" + ID + "'")) {
DBase.dtr.Read();
if (DBase.dtr["password"].ToString() == password) {
msg = "Successful Login";
Session["ID"] = DBase.dtr["Admin_ID"].ToString(); Session["level"] = Type.ToString();
valid = true; } else { msg = "Incorrect Password."; valid = false; } } else { msg = "Unregistered ID."; valid = false; } } else {
if (DBase.DB_Reader("SELECT * FROM [User] WHERE UserID='" + ID + "'")) {
DBase.dtr.Read();
if (DBase.dtr["userpass"].ToString() == password) {
msg = "Successful Login";
Session["ID"] = DBase.dtr["UserID"].ToString(); Session["level"] = Type.ToString();
valid = true; } else { msg = "Incorrect Password."; valid = false;
{ msg = "Unregistered ID."; valid = false; } } DBase.con_Close(); } else {
msg = "Failed to connect to server"; valid = false; } if (valid) { lblError.Text = msg; } else { lblError.Text = msg; } return valid; }
This coding is about log in and log out. Because it have two type of user which are admin and member, the code has separate two parts. If users choose admin, it will go to admin table to validate but if users choose member, it will go member table to validate. After log in the type and ID will be store in the session value which is Session[“level”] and Session[“ID”].
protected void btnLogOut_Click(object sender, EventArgs e) {
Session["level"] = "guest"; Session["id"] = "";
mvSecurity.ActiveViewIndex = 0; tbllogout.Visible = false;
5. Comment
DBase.DB_Reader("SELECT BookComment.CID, BookComment.ISBN, BookComment.UserID,
BookComment.Comment, [User].UserName FROM BookComment INNER JOIN [User] ON BookComment.UserID = [User].UserID WHERE (BookComment.ISBN = '" + ISBN + "')");
Repeater1.DataSource = DBase.dtr; Repeater1.DataBind();
This statement is to retrieve the comment from the database. It will base on the data return row and bind in to the repeater. So if there are 2 record, it will show 2 rows.
<asp:Repeater ID="Repeater1" runat="server"
onitemcommand="Repeater1_ItemCommand"
onitemdatabound="Repeater1_ItemDataBound" ><ItemTemplate> <table style=width:100%;>
<tr bgcolor = DDDDFF > <td class=style20> <span class=style22><%#
((System.Data.Common.DbDataRecord)Container.DataItem)["UserID"].ToString()%></span><br
class=style2 /> <span class=style21><%#
((System.Data.Common.DbDataRecord)Container.DataItem)["Comment"].ToString()%></span><br /> <hr />
<div style=text-align:right;><asp:LinkButton ID="btnLinkDel" runat=server >Delete</asp:LinkButton></div>
</td> </tr> </table> </ItemTemplate> </asp:Repeater>
This statement is the repeater for the comment. So when the book 3 comment, the repeater will loop the data and put it inside the this code.
protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e) {
string CID;
CID = e.CommandArgument.ToString();
string UserID = Session["ID"].ToString().ToUpper();
if (DBase.dtr["UserID"].ToString().ToUpper() == UserID || Session["level"].ToString().ToUpper() == "Admin".ToUpper())
{
if ((MessageBox.Show("Are you sure want to delete this comment ?", "Confirm", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Yes))
{
DBase.DB_Command("DELETE FROM [BookComment] WHERE [CID]=" + CID); Label9.Text = "";
DBase.DB_Reader("SELECT BookComment.CID, BookComment.ISBN,
BookComment.UserID, BookComment.Comment, [User].UserName FROM BookComment INNER JOIN [User] ON BookComment.UserID = [User].UserID WHERE (BookComment.ISBN = '" + ISBN + "')");
Repeater1.DataSource = DBase.dtr; Repeater1.DataBind();
if (!DBase.dtr.HasRows) {
Label9.Text = "There is no comment on this book"; }
} } else {
MessageBox.Show("You does not have permission to delete this comment!",
"Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); }
}
DBase.con_Close(); }
This statement is to delete the comment of the book. To identify the book comment CID is created and store the id at each row. So when delete, it can identify which comment. The comment only can delete by admin users and the user’s own comment. If-Else statement created to identify that which of the users have the permission to delete the comment.
6. Validation Class Email Validation
using System.Text.RegularExpressions;
public bool ValidEmail(string validatingstring) {
if (Regex.Match(validatingstring, @"^[a-zA-Z][a-zA-Z0-9_-]+@[a-zA-Z]+[.]{1}[a-zA- Z]+$").Success) { return true; } else { return false; } }
This code is to validate the e-mail. We need to pass a string parameter to check the string see if there got @ and . . So when the string does not have the two characters, it will not valid.
Text field empty validation
static public bool isEmpty(string strTest) { if (strTest == "") { return true; } else { return false; } }
This code is to validate the field that is empty or not. So we have to also pass in a string parameter to check the string see that the string is empty or not. If it is empty then it is not valid. It apply at search function, edit profile, reservation and more place which does not
Check numeric validation
static public bool isNumeric(string strTest) { try { long.Parse(strTest); return true; } catch { return false; } }
This code is to check numeric only which mean the particular field only allow number. Other number such as alphabet is not allows. It apply help to check IC number and also phone number.
7. Reservation
if (DBase.DB_Reader("SELECT TOP (20) dbo.Book.BookTitle, dbo.Book.Author, dbo.Book.Language, dbo.Book.Category, dbo.Publisher.PublisherName, dbo.Book.ISBN FROM dbo.Book INNER JOIN
dbo.Publisher ON dbo.Book.PublisherID = dbo.Publisher.PublisherID WHERE [" + type + "] LIKE '%"
+ search + "%'")) { } else { Label1.Visible = true;
Label1.Text = System.Environment.NewLine + "Could not find any book"; }
gvSearch.DataSource = DBase.dtr; gvSearch.DataBind();
This code is to find the book and bind the data into the gridview. So that the users allow to reserve the book.
protected void gvSearch_RowCommand(object sender, GridViewCommandEventArgs e) {
string command = e.CommandName.ToString(); int reservecount = 0;
string ISBN = gvSearch.Rows[int.Parse(e.CommandArgument.ToString())].Cells[4].Text; DBConnector DBase = new DBConnector("Microsoft.ACE.Oledb.12.0",
HttpContext.Current.Server.MapPath("../App_Data/season.accdb")); DBase.con_Open();
if ((command).ToUpper() == ("reserve").ToUpper()) {
if (DBase.DB_Reader("SELECT Availablebook - (SELECT COUNT(UserID) AS Reservecount FROM Reservation WHERE (UserID = '" + Session["ID"].ToString() + "')) AS TotalCount FROM [User] WHERE (UserID = '" + Session["ID"].ToString() + "')"))
{
DBase.dtr.Read();
reservecount = int.Parse(DBase.dtr["TotalCount"].ToString()); }
if (reservecount > 0) {
if (DBase.DB_Reader("SELECT TOP (1) dbo.BookCopy.BarcodeID FROM dbo.Book INNER JOIN dbo.BookCopy ON dbo.Book.ISBN = dbo.BookCopy.ISBN WHERE (dbo.BookCopy.Status = 'a') AND (dbo.BookCopy.ISBN = '" + ISBN + "')"))
{
DBase.DB_Command("Insert Into Reservation Values ('" + Session["ID"].ToString() + "', '" + Barcode + "', GETDATE() + 14)");
System.Windows.Forms.MessageBox.Show("Reserve Succesful! Please take this barcode to search the book : " + Barcode, "Sucess", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
Server.Transfer("../Home/Home.aspx"); }
else {
System.Windows.Forms.MessageBox.Show("Sorry, the book is out of stock.",
"Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); }
} else {
System.Windows.Forms.MessageBox.Show("Sorry, you cannot reserve anymore.",
"Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); }
}
DBase.con_Close(); }
This code allow the user to reserve book. When the button reserve click, the id of the row will be store into a variable so that we know which book is it. After that, by using the id, update the database book rental status from a to r so that the book will not borrow by other people.
8. SMS
Set Up the connection to a GSM modem or a GSM phone private int port;
private int baudRate; private int timeout;
public void SetData(int port, int baudRate, int timeout) {
this.port = port;
this.baudRate = baudRate; this.timeout = timeout; }
public void GetData(out int port, out int baudRate, out int timeout) {
port = this.port;
baudRate = this.baudRate; timeout = this.timeout; }
This is to setting up the port, baudRate and timeout for connection a GSM modem or phone. The port can found in control panel, Phone and Modem Setting. When we connected the GSM modem or phone, there will show the port number such as COMM4. So we need to find which port we are using. After that set the baud rate and timeout, it will ready to connect.
Connect GSM modem or GSM phone int port = GsmCommMain.DefaultPortNumber; int baudRate = 9600;
int timeout = GsmCommMain.DefaultTimeout;
frmConnection dlg = new frmConnection();
dlg.StartPosition = FormStartPosition.CenterScreen; dlg.SetData(port, baudRate, timeout);
if (dlg.ShowDialog(this) == DialogResult.OK) {
dlg.GetData(out port, out baudRate, out timeout);
CommSetting.Comm_Port=port;
CommSetting.Comm_BaudRate=baudRate;
CommSetting.Comm_TimeOut=timeout;
}
Cursor.Current = Cursors.WaitCursor;
CommSetting.comm = new GsmCommMain(port, baudRate, timeout);
Cursor.Current = Cursors.Default;
CommSetting.comm.PhoneConnected += new EventHandler(comm_PhoneConnected);
bool retry; do { retry = false; try {
Cursor.Current = Cursors.WaitCursor;
CommSetting.comm.Open();
Cursor.Current = Cursors.Default;
}
catch(Exception) {
Cursor.Current = Cursors.Default;
if (MessageBox.Show(this, "Unable to open the port.", "Error",
MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning) == DialogResult.Retry)
retry = true; else { Close(); return; } } } while(retry);
This part of the coding is use the data transfer from previous form which is the port, baud rate and the timeout to make the connection to the phone. When the phone or modem is connected there will be a status shown at the screen said the phone is connected.
Cursor.Current = Cursors.Default;
This is the coding to SMS. It send the coding from our connected modem or phone to the