Yan Chai Hospital
Lim Por Yen Secondary School
2011-2012 Computer Application
Public Forum System
Name: Chow Man Hong
Class:7A(2)
Table of content
Object…………………………………3
Analysis……………………………….6
Design…………………………………9
Implementation……………………….13
Testing & Evaluation…………………21
Conclusion & Discussion……………..27
Documentation………………………..29
1. Objective
Nowadays, communication among different people becomes more convenient due to technological advancement. E-mail, instant messaging and public discussion forum are some common communications methods in our society and each of them have numerous pros and cons for people to communicate.
Pros
E-mail: 1.It can be integrated with other application such as calendar and task list.
2. It can sign or encrypt email message by sender so that it provides better security level
Instant messaging: 1.It is a real time communication
2. It supports video chats and video chats
3. Multi-people communication is allowed
4. It allows people to play game with each other
Public discussion forums 1.It is a real time communication
2. It is available for public to post and reply messages.
3. Installing Plug-in applications allows you to view graphics, animation audio and video in the forum
Cons
E-mail:1.The file size that can be attached is limited.
2. One to one communication
3. It is not a real-time conservation
Instant messaging: 1. Instant messaging software such as ’MSN’ need to install and a e-mail account is required to log in.
Public discussion forum: 1.With low creditability, you are not able to read some post.
This time, I need to do a project about public discussion forum, which provides an excellent platform for the public to hold conversations. The public can easily register with the forum and then login to read messages, post messages or reply messages. A forum can contain a number of sub-forums related to different things such as education, fashion, careers and sports, and each sub-forum may have several topics. Users can choose the topic they are interested in.
Apart from the general public discussion forums, there are some discussion forums that focus on some specific topic, for instance, ‘Show’ is a forum famous star. ‘Beauty Exchange’ is a forum only about beauty and fashion. ‘Phone HK’ is a forum about phone.
In order to design a public discussion forum system, we should understand database and SQL concept. Database is used to store user information and forum posts. SQL allows us to access the database. It can be used to define database table structure and to store, select and manage data from the database including data insertion, deletion and modification. Essential information about each post, such as the user ID, IP address, and time of the post should be store for security. Moreover, the information of user can generate a lot of useful statistics and carry out analysis such as posting statistics, user statistics, online traffic analysis and posting habit analysis.
Working Plan:
Date / DetailsDuring summer holiday / Visiting some popular public discussion forums and try to use them
9/10/2011- 22/10/2011 / Objectives and Analysis
23/10/2011-29/10/2011 / Design and Implementation
30/10/2011-12/10/2011 / Testing & Evaluation
13/10/2011-19/10/2011 / Conclusion & Discussion
2. Analysis
In summer holiday, I have visited different forums such as ‘Uwants’and ’Show’. Database is used to store user information and forum posts to generate statistics
Posting statistic: There is the information about the topic of the post, the number of people read/reply the post and the time of the latest reply and so on
User statistic: There is the information of the users such as user ID, user name, date of register and so on.
Online traffic analysis: It shows the number of people who are online
Posting habits analysis: It shows the which kind of topic the users are most interested in.
Actually, there are many ways to generate the statistics and carry out analysis. Different IT tools can be used to store information such as spreadsheet and database.Aspreadsheetis acomputer applicationthat simulates a paper accountingworksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell containsalphanumerictext, numeric values or formulas. Recalculation of the entire sheet would automatically be made after a change to a single cell is made. However, we would not use spreadsheet to store information of a discussion forum. Every day, there are many new registers and many users will read news, post news and reply news. There are too many information has to store. Too much data is simply impractical in a single spreadsheet as it becomes more difficult for user to edit it.
The amount of data that is usually stored in a database is far more than what is contained in a spreadsheet. We can handle the information easily due to database’s clear table structure. In addition, SQL allows us to select information from the table so that accurate posting statistics, user statistics, online traffic statistics and so on could be generated easily.Adatabase management system(DBMS) is a software package withcomputer programsthat control the creation, maintenance, and the use of adatabase. Since we are database designer not the database programmer, we do not need to design the program. There are a lot of well-known and highly utilized products include theOracle Database,Microsoft SQL Server,Microsoft Fox pro, Microsoft AccessIBM DB2, and theopen sourceDBMSs MySQLandPostgreSQL.
TheOracle Database is a very famousDBMS produced byOracle corporation.. It isexpensive and itrequires lots of in-depth knowledge and skill to manage large environments. It is extremely complex but also the function is very powerful and therefore it is suitable formedium or large enterprise. To create a discussion forum system, we do not need this complicate DBMS.
Our school provides Microsoft Access for students and teachers.. Comparing with Oracle, Microsoft Access user can work with databases without being highly skilled in the field. It allows us to obtain the information quicker by providing a few commands to tell the program what is being sought. Reports can be created, too, that pull information out of the database according to the project parameters. For example, information of user ID, IP address, sex, address, email address, time of the post may be listed in the database. Since database is much more use friendly for the people who have just learnt about database and SQL , we would like to use it to design a public discussion system.
3.Design
First of all, we have to design the structure of the database system. We can use an Entity Relationship Diagram (ERD) to show the relationship between four entities
From the above diagram, rectangles represent entities which are users, news, category and topic, oval represent the attributes and rhombuses represent the relation between two entities. For each entity, there is a key attribute which is underlined as a primary key of the entity. To be a primary key, it should be unique and not null. For the users, user id is the primary key, for the news, news id is the primary key, for the category, category id is the primary key, and for the topic, topic id is the primary key. The 1,0,M shows the minimum cardinality and the maximum cardinality. 0 means that the existence of the entity in the relationship is optional, 1 mean that the existence of the entity in a relationship is mandatory, and M means that more than one existence between entities.. From the above ERD, user can read zero news, or many news, and the news can be readby zero user or many users so it is a M to M relationship. Also,user can reply zero news, or many news, and the news can be replied by zero user or many users so it is a M to M relationship. Besides, users can post zeronews or many news, and news can be posted by only one user, so it is 1 to M relationship. Furthermore, users can delete zero news or many news, and news can be deleted by only one user, so it is 1 to M relationship. Moreover, users can edit zero news or many news, and news can be edited by only one user, so it is 1 to M relationship. In addition, each news is belonged to only one topic and each topic can have zero or many news and therefore it is a 1 to M relationship. Last but not the least, each category can be divided into one or many topics and each topic is belonged to only one category and therefore it is a 1 to M relationship. ERD is a result of data analysis and it must be used in the data design process to help generate data schema. For a 1 to M cardinality relationship, model each of the related entities in a separate table and post primary key of the ‘one’ side entity as a foreign key attribute to the table that represents the ‘many’ side entity. For an M to M cardinality relationship, model each of the related entities in a separate table and create a new table and post the primary key of each entity as an attribute in the new table. If the relationship has its own attributes, those attributes are to be stored in the intersection table too. The primary key of the intersection table is a composite key which includes the primary key of each concerned entity type. The schema is shown below:
Users (User id, user name, password, number of post, register date, online status, online duration, credit, email, sex, date of birth)
News(news id,user id, topic id , ip address, date , IP address, number of reply, number of read)
Reading (user id, news id)
Replying (user id, news id,date)
Category (category id, category name)
Topic (topic id, category id, topic name)
4. Implementation
First of all, four tables should be created We can create table in visual form or by SQL
The first table is user. The table contains all information of the user, for instance, user id, user name, password, number of post, register date, online status, online duration credit, email, sex, date of birth.
SQL: create table user ( userid char(10) not null unique, user name char(10), password char(10),number of post int, register date date, online status char (1), online duration int, credit int, email char (50), sex char(1), date of birth date, primary key user id))
The second table is news. This table contains the information of news which includes news id, IP address, date, number of read and number of reply, userid, topicid.
SQL: create table news( newsid char(10), ip address char (20), date date, number of read int, number of reply int, primary key(newsid), foreign key (userid) references to user,foreign key (topicid) references to topic))
The third table is category which includes category id and category name
SQL: create table category (categoryid char(10), category name char(10), primary key(category id))
The forth table is topic which include topic id and topic name. and categoryid
SQL: create table topic (topic id char(10), topic name char(10), primary key(topic id))
After creating the table, we need to enter some date for testing. It is much faster to enter the date to the spreadsheet than enter the data by SQL
Entering the information of users, news, category and topic
Entering the information of news
Entering the information of category
Entering the information of topic
After entering the date, we will import the data as following:
Importing data of user:
Importing data of news:
Importing data of category:
Importing data of topic:
After that, select statement can be used in conjunction with other SQL statement to build sophisticated database queries.
To select user name and email address from the user table,we can use the SQL statement as below:
Select user_name, email from user
To retrieve data with specified selection criteria, ‘where’clause can be appended to the basic select statement to specify the condition that the retrieved data need to fulfill, for example, to select information of users who are female.
SQL: select*from user where sex=‘F’
If we want to find out the user who’ name is begin with ’T’, we can use the SQL statement as below:
Select * from user where user_name like ’t%’
If we want to update the password of the user who’s id is 1, we can use the SQL statement as below:
Update user set password=987654,where userid=’1’
We can see that the password change from 123456 to 987654
5. Testing & Evaluation
In this part, I will test the system, generate statistics and carry analysis of posting statistic, user statistics, online traffic analysis and posting habits analysis.
Posting statistic& Posting habits analysis
First of all, I would like to find out the number of post of each user.
We can see that Ivan post news the most frequently and the number of post of Ivan is 9999.
Secondly, I would like to find out which news has the greatest number of read.
We can see that n 12 and n2 are the most popular news and 5245 people have read this news.
Thirdly, I would like find out which news has the greatest number of reply
We can see that n1 has the greatest number of reply which is 456.
Fourthly, I would like to find out which topic has the greatest number of read
We can see that t2 has the greatest number of read
To find out what t2 is
We can know that it is football
Next, I would like to find out who do not post any news
Finally, I would like to find out which topic has no news
User statistic
Firstly, I would like to find out the users who register after 1/1/1995
Secondly, I would like to find out the user who has register more than 15 years
Thirdly, I would like to find out the user who born in May
Then, I would like to find out the user who’s online duration>10000hours
Next, I would like to find out the user who has the largest credit
Online traffic analysis
I would like to find out the number of user who is online
There are 10 users online
6. Conclusion & Discussion
After visiting the popular public discussion forum, finding out the objective, doing analysis, designing the public discussion forum system, implementing the system and testing the system, I know that this project is not such an easy work. There are many problems during building up the system.
First of all, I only visit the forum ’Uwants’ and ’Show’ before doing the project. Therefore, I have to search for other discussion forum, register as a member and try to use the forum to find out the similarities and differences between different discussion forum system. After visiting different forum, I have a deeper understanding about discussion forum system and therefore I can give a clear description of the problem and the situation.
In the part of analysis, I find it difficult to compare different database software such as Oracle Database,Microsoft SQL Server and Microsoft Fox pro since I have never been used them. Therefore, I have to search the information of different database software in the website and compare the pros and cons of using the software.
Since I have just learnt how to use Microsoft Access at school and it is user-friendly. Therefore, I choose it to design my public discussion forum system.
In the part of design, I find that it is impossible for me to list out all the entities, the attributes of each entity and their relationship. To simplify the situation, there are only 4 entities which are user, news, category and topic in my discussion forum system. Also, in this part I have to draw the ERD carefully and indicate the relationship between the entities carefully. Moreover, without a specific software for drawing ERD, I decide to draw the ERD in the Microsoft Word . However, is it quiet time-consuming. Thus, I decide to scan my draft to the computer.
In the part of implementation, I find that creating a table by using SQL is easy to make mistakes and therefore, I decide to create the table in visual form. In addition, it is difficult to input data to the database directly so I suggest inputting data to spreadsheet and then import to the database as it is much faster. In my discussion forum system, I need to test the system whether can solve the problem or not and therefore it is meaningless for me to enter too many data. Therefore, I just enter 19 users’ data, 14 news’ date, 9 categories’ data and 14 topics’ data for simply testing.
In the part of testing and evaluation, I find that some queries cannot work and I don’t know the reason of it. Then, I try to find my classmate for help and we check together. Finally, we find out that I forget to add primary key in some tables and therefore the queries are not work. Moreover, I have to generate statistic and carry out online traffic analysis and posting habits analysis. Since I do not have professional knowledge of database, I just carry out some simply analysis, for example, find out which topic is the most popular.
In addition, there are a lot of work that can be done to make improvement, for example, I should study the notes and the book hardly so that I can know function of the software, Microsoft Access clearly so that I would not make too many mistakes during building up the discussion forum system. Moreover, I should ask teacher for more suggestions to make my work better.
Although the project is full of challenges and difficulties, I can complete the project ultimately with the clear guideline of the problem and the help of my CA teacher and my classmates. I learn that how database can be applied in the reality and I find that database software is an significant tool for us to store information and carry out analysis. To conclude,I have a great sense of satisfaction after finishing the project as I can apply what I learn to do the project.
7. Documentation
Websites:
Book: