This chapter will reviews for software that will be use in this project. First, reviews for database which gives a general idea on what database is. This is follow by introducing SQL, and ADO.NET, which also briefly describe the usage of SQL syntax and some components in ADO.NET. Reviews on AT command, Nokia GSM modem and SKXbee where AT command are the language to communicate between both Nokia GSM modem and SKXbee. Nevertheless, reviews on software used will be discussed. Above mention reviews aims on critical points of current knowledge and methodological approaches on current topic.
2.2 What is database?
Database is a structure collection of records or data that save in a computer system.[1] Typically, a database is made up of many table with column and row, each with specific data. A database can be of any size and varying complexity. For example, a few hundred of records with list of name and address, each with simple structure. On the other hand the computerized catalog of a large library may consist thousands to millions of entries organized under different categories -by author's last name, or under alphabetical order. This huge amount of information must be queried and manipulated by database programs so that user can retrieve, update and search for data as needed.
The database approach include of self-describing collection of integrated records.[2] The description of data know as the meta-data, which is a system catalog or data dictionary. It self-describing the nature of database which known as data independence. As existing structures in database are modified, the application programs that use the database are unaffected. The other approach include of protection between programs and data and data abstraction, which support of multiple view of data, sharing of data and multiuser transaction processing.
DBMS
Software
Users/ Programmers
Application Programs / Queries
Software to process Queries / Programs
Software to Access Stored Data
Stored Database Definition
(Meta-Data)
Stored
Database
Figure2.1 The database approach showing user accessing the database through application program and the DBMS [3]
2.2.1 The Database Management System (DBMS)
DBMS is a collection of application program that enables user to define, create and maintain a database.[2] DBMS allows users to insert, delete, update and retrieve data from database. DBMS provides facility known as mechanism, which allows each user to customized view [1] of database where view is a subset of database. Figure 2.1 illustrates the database approach. It shows how the users or programmers user their application to access the database through the DBMS. The queries and stored data are managed by the DBMS.
DBMSs may use variety of database models such as relational model, network model, flat model and hierarchical model. These models act as a blueprint for developing database. In large systems, DBMS allows user to manage data in a structured way, providing a general inquiry facility to data description called Structured Query Language (SQL [2] ).
2.2.2 The Relational Model
The relational model represents the database as a collection of tables. In relational model, relation holds information about the object that represent in database. The beauty of relational model is that table's information can be accessed or added without reorganizing the tables. A table can have various records and each record can have multiple fields. In relational model terminology, a row is call tuple, a column header is called an attribute and a table is called a relation.[3] Attributes can appear in any order and still having the same relation.
Some well known database products such as Microsoft Access, Microsoft SQL Server and Oracle are the relational model of database design. These databases can further divide into two types, file server and client server. File server database, for example Microsoft Access, data is stored in a file and each user of the database can retrieves, display or modified the data directly from or to the file. On the other hand, Microsoft SQL Server and Oracle are client server database. Data is stored in a file like file server, All operation are mediated through a master program known as server which provide several advantages over file server. These include of better performance, more efficient use of network, minimizing crashes, enforcement of security and features for recovery.[1]
There are no denial that client server had more advantage compare to file server. Yet, when comparison is being carried out among client server, there are plenty of reviews each supporting their most common used database. The two most discuss client server will be Oracle and Microsoft SQL Server. Microsoft SQL Server and Oracle may have many similarities but both retain differences including syntax in SQL languages and platform supports. Oracle database is cross-platform support and provide better performance in field of clustering, partitioning, indexing and memory management.[4] However, Microsoft SQL Server claim to be more user friendly, and being simpler to manage and support[5][6]
Attributes (columns)
Relation Name
Students
Name
ID
Major
GPA
Tuples
(record)
James
10623
Electronic
3.82
Smith
10652
Telecommunication
3.21
Brown
10544
Nano
3.45
Foreign Key
Primary Key
Related columns
University Award
Awards
Position
Name
Tuples
Outstanding Educator
3rd
James
Best Co-curricular
1st
Smith
Singing Contest
3rd
Brown
President
-
James
Young Research
Consolation
Smith
Figure 2.2 Example of students and university award relations.
2.2.2.1 Relational Keys
Relational keys is use to identify the uniqueness of attribute. Among the keys are primary key and foreign key. Primary key identifies one and only one instance of entity. Under the entity integrity rule, no attribute of primary key may accept a null value. If there is a record in the table, the cell with the present of primary key must contain certain value. Another uniqueness of primary key is DBMS will not allow any duplicate value in primary key field. For example, inside a university, attribute name cannot be pick as primary key as there might be same students having the same name. Instead, faculty ID may serve as primary key. Example for figure 2.2 is just for illustration purpose only, the condition only apply if all student names are unique and difference.
Foreign keys on the other hand, are used to create relationships between tables. When a one column appears in more than one table, it's usually appearing the relationship between two records. Foreign keys are enforce with referential integrity rule where no record to be added to table unless related field is logically link. Moreover, referential integrity rule also implies that value of a foreign key must match with primary key of a related table which is shown in figure 2.2.
2.2.3 Entity Relationship(ER) Model
The ER model is a database modeling method designed to communicate the database in form of conceptual schema.[1] A relationship Entity Relationship Diagram(ERD) is created to illustrate the interrelationship between entities in a database. There are three major components of ERD: entities, relation and attributes. Each of these component is use to represent different type of information. The symbols that represent these components are: diamond which represent relations, box or square represent entities while oval represent attribute.
An entity represents "things" that physically exist in the real world. And for each entity, there are attributes which are properties that describe it. For relation, it is naturally associated between entities that are referred as relationships. Taking a family as example, the relationship of brother and sister are family members. Brother is naughty and stubborn while sister is polite and kind. In this example, entities represent both brother and sister, family member represents relations while attributes are naughty, stubborn, polite and kind. The graphical represent is show in figure 2.3
Brother
Family Member
Naughty
Sister
Stubborn
Kind
Polite
Figure 2.3 Example of entity relationship diagram.
2.3 Structured Query Language (SQL)
SQL is a database computer language design for querying and modifying data in relational database management system (RDBMS). SQL is supported by ANSI (American National Standard Institute) standards. Among the database that supports SQL are Microsoft Access, Microsoft SQL Server, Oracle, IBM DB2 and etc. SQL is the language of choice in creates, interrogate and manipulate database which often used in client-server programming and web programming.
There are several characteristics of SQL. Among the benefits are SQL are relatively easy to learn and the command structure consist of simple English word such as "SELECT", "INSERT", "UPDATE" and "DELETE". SQL can be use by wide range of user which include of Database Administrators (DBAs), application programmers and other types of end-users. [2]
SQL consists of three components which are Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL). DDL is a language structure that used to create modifies tables and other objects in database. DML is a language structure that used to manipulate data within a table. DCL is used to create privilege to allow user to access and manipulation of database.
The four commands in DML are:
INSERT -insert or add new data into a database
UPDATE -update or change data of database
DELETE -delete or remove data from a database
SELECT -to retrieve specific data stored in database
SQL language is further divided into several elements, which include of clauses, expressions, predicates, queries and statement. Elements queries are use to retrieve data based on specific criteria. Others elements will be illustrated in figure below
Figure 2.4 SQL language elements in a single statement.
Below are samples of four commonly used quires under data manipulation language. [2]
INSERT INTO TableName[(ColumnList)]
VALUES (dataValueList)INSERT Statement
A single row of data (single or list) is added into column (single or list). Number of data added must match with number of column available.
UPDATE TableName
SET columnName1 = dataValue1, [columnName2 = dataValue2…]
WHERE searchConditionUPDATE Statement
Update for specific rows if search condition if met. If WHERE clause is remove, data is updated for all rows.
DELETE FROM TableName
WHERE searchConditionDELTE Statement
Delete data for specific row if condition is met. If WHERE clause is omitted, all records are deleted from table.
SELECT columnName
FROM TableName
WHERE searchConditionSELECT Statement
Select clause allow user to select specific data from specific column where data is similar to search condition in WHERE clause.
2.4 ADO.NET
ADO.NET is the .NET Framework area that exposed the ability to access data from database such as Oracle and Microsoft SQL Server. ADO.NET supports a variety of development needs, including the creation of front-end database clients by applications, tools and language.[7] ADO.NET provides a rich set classes to support the development of database application which enable data-sharing application to connect, retrieve, manipulate and update data sources. The classes provided are central to developing a professional data-driven application and can be divided into following major components:
Data Provider - for connecting to a database, executing command and retrieving results.
Data Set - memory resident representation of data that provides a consistent relational programming model regardless source of the data it contains.
Data Table - made up of a collection of tables, relationships, and constraints. Data table objects are used to represent tables in a Data Set.
2.4.1 The Architecture of ADO.NET
ADO.NET used structured process flow which driven by components. Data in data source is retrieved using various component of data provider to provide data to an application. These data will then update from application back into database. Architecture of ADO.NET can generally divide into two to perform data query and data accessing from the data source. The first architecture will base on figure 2.3. The data tables are embedded into Data Set as Data Table Collection. Data transaction such as SELECT, INSERT, UPDATE and DELETE between Data Set and Data Provider are made by Data Adapter via own different method: SelectCommand, InsertCommand, UpdateCommand and DeleteCommand respectively. The connection is use to establish connection between application and data source. Command object can simply call different Data Adapter to perform data query by changing different parameters.
Figure 2.5 Example architecture of ADO.NET[1]
The architecture of ADO.NET in Figure 2.4 will be slightly different architecture. The data Table is not embedded into Data Set but is threaded independently as data tables. The data transaction between the Data Provider and the Data Table are done by executing different methods of Command object with associated parameters. When perform data-accessing operation such as INSERT, UPDATE or DELETE, the ExecuteNonQuery() method of Command object with suitable parameters is called.
Figure 2.6 Example of different ADO.NET architecture[1]
2.4.2 Components of ADO.NET
As mention in section 2.3, ADO.NET is composed of three major components: Data Provider, Data Set and Data Table. All components will further describe in this section. As Visual Basic.NET 2010 programming language and Microsoft SQL Server 2008 is used, all counterparts of describe language and components will base Visual Basic language and SQL Server.
2.4.2.1 The Data Provider
Data Provider known as data driver can be use as major component in data-driven [3] application. Different Data Providers are located at different namespaces. For SQL.NET Data Provider, namespace with associated keyword "imports System.Data.SqlClient" is needed. Namespace needs to be import into code as it holds various data classes that needed in a project. Thou different namespace have different prefix, the classes for these Data Provider have similar properties and yet having the same name. For instance, connection string property Open() and Close() is similar for Data Provider such as Oracle, SQL and OleDB.
Data providers contain four subcomponents: Connection, Command, Data Adapter, and Data Reader to perform functionalities [1]
2.4.2.1.1Connection Class
Connection class provides connection between application and database. Connection string is property of Connection Class which provides necessary information to connect application with data source. The "New" keyword is used to create new instance or object of Connection class. To launch a connection, connection string with few parameters is required. These parameters are: provider, data source, database, user ID and password. Parameter names in connection are case insensitive except for parameter password.
The example parameters for SQL server are:
Connection = New SqlConnection ("Data Source = Home;" &_
"Database = TagSystem;" & "User ID = RFID;" &_
"Password = qwer123;")
Open() method of Connection class is called to open a connection to data source specified by connection string. A Try-Catch block is often use to during Open() method to catch possible errors causes by this connection. On contrary, Close() method is use to close a connection between database and application. Connection need to be close if database is not in use to avoid error during reopen. Dispose() method is often use together with Close() method to cleanup resources used by connection object during data access.
2.4.2.1.2 Command and Parameter Class
Command object are use to execute data queries to retrieve, manipulate and update against database. There are two different roles to perform data query or data action as shown in figure 2.4 and figure 2.5. In figure 2.4, Command object is embedded into different data query methods of Table Adapter which are SelechCommand, Insert Command, UpdateCommand and Delete Command. Command object can be executed indirectly where execution command is based on the associated query type. As in figure 2.5, command object executed directly based on the attached parameter collection that created by user. Parameter collection need to create and initialize to Command object before it can be use.
Connection property, CommandType property CommandText property and Parameter property are four common type of Command Class. Connection property hold a valid Connection object. CommanTyper property is used to indicate the type of command stored in CommandText property. CommandText determined the value of CommandType property and Parameter property is used to hold collection of Parameter object.
Dim cmdString As String = "SELECT Item, Location FROM ItemnID WHERE &_ (TagID LIKE @ID)"
Dim paramID As New SqlParameter
Dim cmd As New SqlCommand
paramID.ParameterName = "@ID"
paramID.Value = textbox.text
cmd.Connection = sqlconn
cmd.CommandType = CommandType.Text
cmd.CommandText = cmdString
"Database = TagSystem;" & "User ID = RFID;" &_
"Password = qwer123;")
The samples usage of these Command Class are show as below
The three method Command Class as shown in figure 2.5 are ExecuteReader method, ExecuteScalar Method and ExecuteNonQuery method. ExecuteReader is a data query method used to execute a read-out operation such as SQL SELECT statement to return a row to Data Reader. The ExecuteScalar method is used to retrieve single value from a database whereby is faster and less overhead than ExecuteReader. ExecuteNonQuery method is needed to perform INSERT, UPDATE or DELETE commands.
Dim cmdString As String = ""UPDATE ItemnID SET Status=@status"
Dim cmd As New SqlCommand
cmd.Connection = sqlconn
cmd.CommandType = CommandType.Text
cmd.CommandText = cmdString
cmd.ExecuteNonQuery()
"Database = TagSystem;" & "User ID = RFID;" &_
"Password = qwer123;")
The below sample shows the ExecuteNonQuery method.
2.4.2.1.3 Data Adapter
Data Adapter serves bridge to move data between Data Set and database. Data Adapter uses suitable Command object Commands and assign them to appropriate properties such as SelectCommand to perform data query from database to Dataset. The most common use method in Data Adapter class is Fill. Fill method adds rows in Data Set to match those data in data source using Data Set name and creates a Data Table.[1] This method often use in this project to fill a Data Table.
2.4.2.1.4 Data Reader
Data Reader is used to retrieve and hold data rows that return from a database. This class reads using forward-only stream of row from database. While connection is busy, no operation can be performed unless connection is closed. Data Reader instance can be creates by calling Execute Reader method of Command object which read and hold one row each time.
2.4.2.2 Data Set and Data Table
Data Set is an in-memory cache of data retrieved from database. The Data Set consists of collection of Data Table object that contain relational tabula data. Each table in Data Set represent Data Table can direct map to real table in database. Data Set does not tie to any specific type of database and data can be loaded into Data Set using Table Adapter. Before filling a Data Set, Clear method is use to dispose all old data contain in Data Set.
Data table holds Rows and Columns collection which id direct map to a real data table in database or data source. Data Table object can be populated by executing Fill method of Data Adapter Object which is embedded into Data Set object which mention in sub chapter 2.4.2.1.3. Among few popular properties of Data Table Class are Columns and Rows. Each column and row in Data Table is considered as Column objects and Row Object. By calling this property, object existing in the Data Table can be retrieved. Some favor method often use in Data Table will be Clear to clears DataTable of all data, GetType to gets type of current instance and ToString to get table name.
Dim TableAdapter1 As New SqlDataAdapter
Dim DT as new Data Table
TableAdapter1.SelectCommand = cmd
TableAdapter1.Fill(DT)
Example of filling a Data Table base on example of Command class shown earlier
2.5 AT Command
AT command also known as Haydes command set is a specific command-language originally developed for Hayes Smartmodem. On these modems, modems would adjust the DTE baud rate [4] by trying to receive pattern of voltage transitions to align with supported baud rates. The outcomes of zeros and ones receive pattern results looks similar to ASCII letter "A". The pattern of identified modem's speed would be verified correctly at letter "T".[8] AT command is further develop to use for commands line which used to control modems or device to do their specified function.
AT commands are varies by modem, but traditional size limit is 40 character. AT commands send to modem execute in order (from left to right). If the command executed without error, the modem will return result "OK". If commands that encountered by modem are invalid or legal time error, the return result will be "ERROR".
2.6 HyperTerminal
HyperTerminal is an application for computer to connect with other remote system via serial ports. One can control, test and diagnose his remote device with appropriate AT commands using HyperTerminal. The feature of HyperTerminal also allows user to receive and view text of command typed. Before HyperTerminal is able to communicate with remove system, users are required choose which port to configure, follows by settings bits rate, data bits, parity bits, and stop bits. Figure 2.? illustrate the port setting of HyerTerminal to establish a connection.
Figure 2.7 Interface of HyperTerminal
2.7 Nokia 30 GSM
Nokia 30 GSM is a special type of modem for sending and receiving data using GSM network. Nokia 30 GSM is widely known for sending SMS or text message. GSM modem is generally connected to a computer using serial port to communicate over the mobile network. SIM card is required in order for Nokia 30 GSM to work. The cost of sending text message using GSM modem is the same as sending SMS using mobile phone. Figure below shows the image of Nokia 30 GSM.
Figure 2.8 Nokia 30 GSM
Initialize need to be done in order to connect Nokia 30 GSM modem with a computer. This is done by setting port number, baud rate, data bits, parity bits and stop bits of GSM modem. Table 2.1 shows the setting that needs to initialize a Nokia 30 GSM.
Table 2.1 Nokia 30 GSM setting
Setting
Value
Com Port
Refer to connected port
Baud Rate
115200 bps
Data Bits
8b
Parity Bits
None
Stop Bits
1b
AT command is used as the language to launch the communication between GSM modem and computer. Below table shows usage of several AT commands that need to send a text message.
Table 2.2 AT command for Nokia 30 GSM[9][10]
AT Command
Expected Output
Description
1
AT
OK
Check modem availability
2
AT+CPIN="xxxx"
OK
To enter SIM card pin number
3
AT+CSQ
1<X<31(max)
Check for signal strength
4
AT+CMGF=1
OK
Set Nokia 30 to SMS text mode
5
AT+CMGS="+XXX" <Enter>
> SMS text message here <Ctrl-Z>
+CMGS
OK
Send text message base to phone number XXX
AT command at row 2 can be ignored if SIM card does not set any pin number initially. AT command at row 3 is used to check for signal strength and does not need to be run every time sending a text message.
Nokia 30 GSM has three light indicators (LED) which indicates user on field strength and terminal status. The LEDs appear in two colors, red and green which indicate three different condition of GSM modem. The conditions are start-up, normal operation and special operation. Please refer to Appendix A for GSM modem light indicators.
2.8 SKXbee
SKXbee also known as Starter Kit Xbee is a reliable wireless communication module. Xbee can act as a sender as well as receiver. Xbee offer indoor communication range up to 30m and outdoor line of sight range up to 100m. Feature of Xbee include small in size, long range data integrity, USB plug and play and point-to-point, point-to multipoint and peer-to-peer topologies supported. Below is figure of Xbee module.
Figure 2.9 Xbee module
In order to use Xbee module, initialize need to be done. The initialize condition is almost the same as GSM modem except for baud rate. Table below shows the initialize setting for Xbee module
Table 2.3 Xbee module setting
Setting
Value
Com Port
Refer to connected port
Baud Rate
115200 bps
Data Bits
8b
Parity Bits
None
Stop Bits
1b
To communicate between two Xbee module, source address and destination address need to be set. For example source address of Xbee1 must be match to destination address of Xbee2 and destination address of Xbee1 must match to source address of Xbee2. AT command is use to set address for Xbee module. Below table show the command to set Xbee1 source destination to 1111 and destination address to 2222.
Table 2.4 AT command for Xbee module
Command
Expected Output
Description
1
+++
Ok
To enter AT command mode
2
atmy1111
Ok
Set source address to 1111
3
atdl 2222
Ok
Set destination address to 2222
4
atwr
OK
Send write command
5
atmy
1111
Get source address
6
atdl
2222
Get destination address
7
atcn
OK
Exit AT command mode
First, "+++" is type to enter at command mode. Once "OK" is received, Xbee module is ready to receive AT command. To set source address to 1111, "atwr" is type follow by "atmy1111". To set destination address to 2222, "atwr" is type follow by "atdl2222". Note that to set an address," atwr" is needed to be type, follow by setting address command. After finish setting, "atcn"is type to exit AT command mode. Row 5 and row 6 in table 2.4 is use to check for sources and destination address and therefore "atwr" is not needed.
2.9 Software Review
2.9.1Visual Basic2010 Express
Visual Basic 2010 Express also known as VB.NET 2010 is a high level programming language. Express edition VB.NET is a freeware which allow user to build window application. Syntax of VB is very similar to English language which can be understood easily. VB.NET is object oriented programming which enable user to define a class with consist sets of data.[12] This class is then used to generate object which will use by windows application perform tasks. Window application created using VB.NET is event driven. An event can occur when user interact with an object. For example, a user can change the function of a button by just double clicking on it and rewrite the desire code. An even-driven application execute code that tie to the events. Moreover, VB.NET allow user to drag and drop of an object and this object can easily align without manual configure it. Overall, VB.NET 2010 is user friendly and easy to use software. Below figure shows the interface of VB.NET 2010.
Figure 2.10 Interface of Visual Basic 2010 Express
2.9.2Microsoft SQL Server 2008 R2 Express
SQL Server 2008 R2 is the latest version of SQL server which introduce by Microsoft. The express indicates that it is free database. The release of SQL server 2008 provides a comprehensive data platform that is more secure, reliable and manageable for critical applications. SQL Server 2008 provides rich set of integrated services which enable more data management such as query, search, synchronize, report and analyze. Others features include of quickly build and deploy of database, graphical management tools, reporting capabilities. Nevertheless, SQL Server is user friendly and ease to use. [11] Figure below shows interface of SQL Server 2008 software.
Data Source
Database
Table
Database Design
Records
Figure 2.11 Interface of SQL Server 2008
