The Design and Implementation of a Database Managed Website - Assignment Example

Contents Executive Summary 2 Introduction 3 Aims and Objectives of Dissertation 3 Dissertation Structure 4 Literature Review 5 Content Management System 5 Cascade Style Sheets (CSS) 6 PHP Programming 6 MySQL Database 7 Apache Web Server 7 Developing a Database Managed Website 7 Business Case Analysis 9 Introduction to the Company 9 Problems with the Current Website 9 Business Requirements For the New Software System (Website) 10 Software / Website Design 12 Hardware and Software Requirements for Design and Development Work 12 Database Design 13 Screens Design and Layouts 19 Implementation 23 Future Enhancements 27 Conclusion 29 References 30 Executive Summary Development of modern technology, especially internet has affected all areas of human lives. Internet has been a revolution in information technology field and serves as a big pool of information that can be accessed and reviewed any time. We access information on the internet through websites, which are the placeholders for information of all kinds. Several programming languages, databases and other techniques are available to develop websites and publish data on the internet in a quick, effective and efficient way to foster maximum productivity and use. This project "The Design and Implementation of a Database Managed Website" was initiated to develop a customised content management system for a case organisation's website which could provide timely, accurate and standardised information quickly on the website with proper database support at the back end to ensure that no information can be lost even if the web site is unavailable. In addition, the database will also provide the capability to define various relationships among data that might be of some use to the organisation. The case study organisation used for this project is the Street Legal car club. Introduction Aims and Objectives of Dissertation The aim of this project is to develop a website equipped with appropriate database support in a way that provides a convenient and accurate submission of contents on the internet with minimal effort. Some of the objectives of the project are noted below: To analyse an existing business process and identify areas of improvement To develop appropriate design using entity relationship diagrams for a new business process adequately supported by a database To develop a website using PHP, CSS and HTML and write the back end programming code for it using MySQL database To publish the website using Apache web server in order to test various features of the new software To develop a comprehensive and concise report briefly explaining the entire system development methodology and design To provide recommendation for future enhancements in the software that might be brought about if and when required Dissertation Structure This dissertation report is divided into a number of chapters to control the flow of ideas. The next chapter provides a literature review of the needs and requirements of a database managed website in modern day world. It also discusses some of the technologies that can be used to develop a content management website system. Chapter 3 then discusses the case study that has been used for this dissertation. It provides an account of the business process; carries out brief analysis of business requirements and summarises the key requirements for the system. Chapter 4 attempts to develop an entity relationship diagram of the proposed solution and provides information about software design and critical checkpoints in the system from security and confidentiality point of views. Chapter 5 provides key features and functionality of the software along with screen shots of the software. Chapter 6 concludes this dissertation and provides recommendations and areas of improvement of this software for future enhancements. Literature Review This chapter provides an introduction of the topic area - database management systems for internet programming and websites. Several views are presented from various scholars and experts working in the field of online content management systems. Content Management System Paul Browning and Mike Lowndes (2001) have described the problems that organisations face when they have to deal with continuous updates to their websites with lots of data and information on periodic basis. This results into "out of date material, poor control over website design and navigation, and erroneous data" (Browning and Lowndes, 2001). The net effect is the loss of control on the website contents with a simultaneous loss of goodwill among customers and stakeholders. Content Management System (CMS) is a concept that provides an organised approach to organisations for updating their websites with all sorts of data at all times without errors and accuracy losses. In addition, it provides standardisation towards managing websites which is otherwise not present if each new webpage has to be designed manually. Browning and Lowndes (2001) promote the concept of 'self service authoring' as a means through which staff that do not have special skills with web page development and maintenance can be enabled to develop and maintain forms and information on websites with required accuracy. A working definition of a content management system is provided by James Robertson (2003) as "A content management system supports the creation, management, distribution, publishing and discovery of corporate information". A content management system has separate areas for creation of contents, its management and the presentation of contents on the websites. Robertson (2003) has explained the functions of each of these areas. According to him, content creation is the area where a user is provided a wizard sort of template to upload or select the content that has to be placed on the website. This authoring area can make use of a database system to provide pre-written and pre-filled options to the web master who can then just select items from the list to make a web page. The content management area can be used to manage all web pages and to ensure that a standardised website is being maintained. The content presentation deals with publishing the information on the website. Cascade Style Sheets (CSS) Cascade Style Sheets or CSS is used to take care of formatting, web page design and management of contents on the web pages. It is a powerful mechanism that can be integrated easily with any website. PHP Programming PHP is an open source programming language mostly used to code programmes that are executed over the internet. The most important benefit of using PHP is the fact that it can be easily integrated with Hyper Text Markup Language (HTML) and that there are no licensing requirements to be followed for using PHP. MySQL Database MySQL, or "My" Structured Query Language, is a free Database Management System (DBMS) which allows multiple concurrent connections. It provides a fast and easy access to information with an easy to develop and maintain database management features (MySQL AB, n.d.). The software can be easily connected to website and webservers. Apache Web Server The http server application used is Apache. Apache, or Apache HTTP Server, is part of the LAMP software bundle. LAMP originally stands for Linux, Apache, MySQL and PHP, although the P in LAMP has also been known to refer to both Perl and Python. Apache will run on most Unix and Windows operating systems, and as of March 2007 is believed to serve 58% of all websites (Wikipedia, 2007). Developing a Database Managed Website According to Christopher Robbins (2002), "The separation of style from content has become the bugbear of the HTML developer. Traditionally, we've used well-written CSS and XHTML to achieve this separation, and we've seen how much easier it is to update our sites or provide multiple styles when we write our mark-up this way. By adding some very basic PHP to this mix, we can take this modularity a step further, making it easier not only to update our styling, but our structure as well. In essence, we can create our own basic Content Management System". The above comments provide a hint that using simple programming, a company having limited budget and limited requirements can create its own content management system for its website. This database managed website can take care of the problems identified by Browning and Lowndes as stated above and should be able to provide a centralised repository of data for companies where they can publish standard, error free, attractive and well planned contents on their internet websites. The above literature review shows that PHP, CSS and HTML can be used along with a proper database system like MySQL to develop an integrated, consistent, accurate and attractive website with full support of a complete and comprehensive database at the back end. Business Case Analysis This chapter provides an introduction to the business problem which will be used as a case study to describe the development and operations of a database managed website. Introduction to the Company The case company is a car club known as 'Street Legal'. It maintains a website that is used to store information about all members and their cars. All the show attending members are therefore requested to provide their information to the club's web master who then posts this data on the website. Members are required to provide basic information like their personal bio data, information about car make, model, colour and photographs of cars. This data is then manually entered by the webmaster into a web page which is then published online. Problems with the Current Website The current system does not have any database support. Hence, all the fields are manually entered into the webpage form by webmaster. This results into a number of problems, including but not limited to the following: Typographical errors Delays in developing the web page and hence a subsequent delay in posting the information on web site Errors in understanding data No standardisation in the way data is presented on the website Haphazard information about members In addition, the club has no means to identify information like the most favourite car model among its members, the area which has the highest population of Street Legal members, and similar metrics that might be required in future for various purposes. Business Requirements For the New Software System (Website) The major business requirements for the new software system are stated below: Reduction in the amount of time required to develop a particular page for a member. This should be carried out to ensure that there are notable efficiency gains for the webmaster A marked decrease in the number of errors, omissions, and mistakes should be ensured. The software should be able to minimise data entry by webmaster and should provide some mechanism through which data entry mistakes can either be eliminated or reduced significantly Standardised screens should be available throughout the website. Currently, due to non standardisation, data and information presented on the website appear to be cluttered and not easily readable In addition to these major requirements for the new website, other minor requirements include an attractive interface with ease of navigation through various parts of the website. Users should be able to locate the desired information easily and quickly. The database structure should be consistent and should have all the required relationships and integrity constraints in place. Software / Website Design This chapter provides a detailed design of the new website to satisfy requirements as identified in the previous chapter. Hardware and Software Requirements for Design and Development Work Considering the requirements for the website, the proposed design is based on four major software tools. These include: MySQL database - for back end database support and for keeping all records and information Apache Web Server - to act as a web server so that website can be published and tested PHP - as the main programming language to be used in addition to traditional HTML CSS - for formatting and positioning of the web contents and designing the web site In addition to the software tools noted above, a standard Pentium IV personal computer was used with standard memory and other feature. This was used both as the development machine and subsequently as the test server. All of the languages and software used to create this report and the associated program are free to use and require no subscription fees or payments to use them, and were chosen because of their open-source licensing. Database Design The new website will have a complete database behind it. The design of the database will be based on three main tables that will form the basis of the entire application. These tables and their attributes are shown in the following database design figure: Figure 1: A simple database layout As shown in figure 1, their will be three tables each for a specific purpose. These tables are titled as categories, cars and photos. There could have been a fourth table in the database for 'Car Owner' yet it has not been used because the major purpose is to keep information about the cars and not the owners in the database. Categories Table: The purpose of the table 'Categories' is to store data about the type of cars like Renault, BMW, Toyota etc. There are only two fields in the Categories table. These include categoryid and categoryname. The purpose of these fields is self evident - one will store the category of the car, and the other one is just a serial number to uniquely identify one category from others. Hence the composition of Categories table is: CATEGORIES (categoryid, categoryname) A primary key is an attribute in the database table that uniquely identifies one record from another. Photos Table: The Photos table is used to store the IDs of cars along with the location where photo was taken. The primary key of the Photos table itself is photoid. Hence the table structure is PHOTOS (photoid, carid, location) Cars Table: The Cars table has a primary key called carid. In addition, other fields in the table include the following: Make Model Colour EngineSize OwnerName Icemods Interiormods Exteriormods Performancemods Wheelmods Active Foreign Keys: The three tables are linked to one-another by the use of "foreign keys". A "foreign key" is a primary key field from one table that can be found in multiple tables as a secondary identifier i.e. a secondary "key". The "primary key" may be one field in a table (as above) or may be a combination of fields. Duncan Dwelle (1996) states that the only purpose of the primary key in a primary key/foreign key pair is to provide an unambiguous join. Dwelle goes on to say that it is to maintain referential integrity with respect to the "foreign" table that contains the referenced "primary key". This is important to ensure that the data that is referenced by the "foreign key" will be valid. For example, in the diagram above, carid is the primary key for the cars table, but it also appears in the photos table as a foreign key. In the database, the photos table stores the location of the image to be used in the location field, and the carid field stores the primary key carid (of the cars table) of the car that the image relates to, so that when the car profile is loaded, any associated images from the photos table are called and displayed. These are ordered by the Primary key, photoid. In the cars table, there is also a foreign key, called categoryid, which is the Primary key of the categories table. This information is important for the system to decide which of the available categories the car profile should belong to. For example, a Renault car profile should be linked to the Renault category so that selecting to view the Renault category in the system will return only Renault car profiles. Figure 2: How the parts of the database inter-connect In Figure 2, the connections between all three tables become apparent. The photos table and the categories table are not linked to each other, but are linked in-directly through the cars table. The photos table does not need to contain any of the category information that is contained within the cars table or the categories table. Likewise, the categories table is not required to store any information about the location or ID of the images of the cars. Each of the individual field names were chosen to give clarity in regards to which field controls what content. For example, enginesize in this system will contain a numerical value from 0 to 9999, followed by a space, followed by 2 letters, which will be cc, for instance: 1200 cc. This information therefore could not be easily confused with the make or model fields while coding the system or adding data to the database. Table Name Field Name Field Type Key Other Info categories categoryid int primary key length 5, unique, not null categoryname varchar - length 50, not null Cars carid int primary key length 5, unique, not null categoryid int foreign key length 5, not null make varchar - length 25, not null model varchar - length 25, not null colour varchar - length 25, not null enginesize varchar - length 7, not null ownername varchar - length 50, not null icemods longtext - not null interiormods longtext - not null exteriormods longtext - not null performancemods longtext - not null wheelmods longtext - not null active int - length 5, not null photos photoid int primary key length 5, unique, not null carid int foreign key length 5, not null location varchar - length 255, not null The table above is a basic layout of how the database will look, including field names, field types, whether they are a primary or foreign key, and what other attributes each field has, such as whether it can be null (no values entered) and whether there are any data length constraints on the field. The file structure of the system is important to its functionality. There is one global.php file containing database connection information for the entire site, and each individual page is generated from a .tpl (template) file that contains HTML, CSS and also PHP where PHP code is required. This allows for editing of one file when changing the template for the car profile, for instance. Screens Design and Layouts The proposed screens will look like the following. It is assured that the information is arranged such that it remains easily available to users. Figure 3: A basic page layout for the index page Figure 3 outlines the basic layout of how the index page (index.php) will look. The design and layout is simple and easy to use, displaying the section for the list of car manufacturers or categories, and the section for the latest added cars (ordered by the carid in the system). At the bottom of this and every page, there is the navigation for the system. Figure 4: A basic page layout for the categories page Figure 4 outlines the basic layout of how the categories page (category.php) will look. The design and layout is again simple and easy to use, displaying the section for the list of the cars within the category, and displaying the category name. Figure 5: A basic car profile layout Figure 5 outlines the basic layout of how the car profile page (car.php) will look. The design displays the section for the image(s) of the car, information about the make, model, owner's name and other information about the car's modifications. Figure 6: A basic list of cars layout Figure 6 outlines the basic layout of how the list of all cars page (car.phpdo=list) will look. The design displays the section for the list of all of the cars within the database. The above sections described the design features for new website screens and the database layouts along with table structure and relationships. The next chapter will focus on implementation level details, coding and testing of the new website. Implementation This chapter discusses the key features of the new website that is designed with database at its back end to resolve the issues that the organisation was facing with the old website. Some actual screen shots of the application as envisioned in the design chapter are shown below: Figure 7: The index page Figure 7 shows how the Index page (index.php) is laid out in the system. The Car Categories are selected from the database, and the latest cars are sorted in a descending order based on the last profile added to the database, and is limited to the last 5 profiles added. Figure 8: The categories page Figure 8 shows how the Categories page (category.php) is laid out in the system. The category name is selected from the database depending on what the categoryid in the requested URL is. The list is generated by selecting all cars from the cars table which have the corresponding categoryid as a foreign key. Figure 9: The car profile Figure 9 shows how the Car Profile page (car.php) is laid out in the system. The information is selected from the database depending on what the carid in the requested url is. The information is selected from the cars table and displayed in the profile as laid out in the page template. The images are displayed by selecting all images from the photos table which have the corresponding carid as a foreign key. Figure 10: The car list page Figure 10 shows how the Car List page (car.phpdo=list) is laid out in the system. The information is selected from the database from the cars table, and is ordered by the carid. There is no need for any category information to be selected as all the required information is contained within the cars table. Future Enhancements The core focus of this project was to develop and implement a website that can be used to place and publish data with minimum of errors, in quick time and with maximum ease of navigation. These three objectives have been optimally achieved. Some areas which can be improved in future might include the following: A proper access control mechanism can be added to the system. This would ensure that only authorised users are allowed to access, modify and upload information on the website. This can be accomplished using the same database but adding a User table into it with username and password as two fields. Some cosmetic changes to make the website more user-friendly and visually pleasing can also be brought about in future. This would ensure a pleasant experience whenever a user visits the website for information. Automatic scripts can be added to eliminate human intervention completely. These scripts can automatically load the data placed in the system as documents and images, and convert the data into a web page. This web page can then be automatically published on the website. This would ensure accuracy and high speed. One of the limitations of this system is that no detailed testing was carried out to ensure integrity of the database. Though testing was done for the application yet database controls were not checked in sufficient details. Further improvements could be to store the template data within the database itself, and to house all the PHP code within "plugin" files, for example, you can include the "latest cars" section in more than one page if the code is stored in its own separate file and included in other files. Conclusion This project was carried out to apply the theoretical concepts of database managed website and content management system on a case study organisation. During the course of he project, various software were used and a great deal of programming was carried out to develop the website, to test it using a web server and to deploy the working website. This website can be used by Street Legal Car Club to provide a fast mechanism to upload contents to the web with minimal chances of error. References Browning, P. Lowndes, M. (2001) "Content Management Systems". JISC Tech Watch Report [online]. Available from: http://www.jisc.ac.uk/media/documents/techwatch/tsw_01-02.pdf [Accessed 17th August 2007]. Dwelle, D. (1996) Primary and Foreign Keys [online]. Available from: http://www.aisintl.com/case/primary_and_foreign_key.html [Accessed 15th August 2007]. MySQL AB [n.d.] MySQL 5.0 Reference Manual: 1.4.2 History of MySQL [online]. Available from: http://dev.mysql.com/doc/refman/5.0/en/history.html [Accessed 14th August 2007]. Robbins, C. (2002) Manage Your Content With PHP [online]. Available from: http://www.alistapart.com/articles/phpcms/ [Accessed 17th August 2007]. Robertson, J. (2003) So, What is a Content Management System [online]. Available from: http://www.steptwo.com.au/papers/kmc_what/index.html [Accessed 17th August 2007]. Wikipedia (2007) Apache HTTP Server [online]. Available from: http://en.wikipedia.org/wiki/Apache_HTTP_server [Accessed 15th August 2007]. Read More