1 Introduction:
A database refers to collection of related data that is highly organized. The data can be on anything that an organization deems important to it. For example, a manufacturing firm could have a database that holds information regarding their customers. This information is very important to the firm since it can be sued for many reasons like finding out which customers deserve discounts based on data regarding sales. The data stored could be either structured or unstructured. Unstructured data may be in form of images, videos etc. while structured data may be in the form of numbers, date etc. Database management systems, on the other hand, refer to software that allows for creation, use and maintenance. These systems also ensure that user applications are able to access the system concurrently.
1.1 Importance of Database Management System:
1. DBMS’s allow for management of data both efficiently and effectively.
2. They allow for better access to data contained in the databases.
3. Eliminates or reduces inconsistencies in data.
1.1.1 DBMS in Commercial Setting:
DBMS is used in a wide range of business enterprises which handle large amounts of business data. In business settings, DBMS is used in increasing efficiency and reducing data redundancy as well as reducing the time taken in processing and retrieving important business information (Chaffey 2002.). Examples of commercial settings where DBMS is conveniently applied include in banking where it is used in managing information in various client accounts where information is stored in a data base. In airline companies, DBMS is applied to provide a timely and convenient means of handling bookings and ticketing systems. DBMS is also used in companies to manage employee payrolls as it makes it easy to manage and retrieve information (Lauden and Traver 2002). Additionally, DBMS is applied in managing information regarding sales and client orders.
1.1.2 DBMS in Non-Commercial Settings
DBMS is applied in various non-commercial institutions like government institutions, non-governmental organizations and faith based organizations. In such institutions DBMS can be used in managing and sharing data among various departments without redundancy (Turban et al 2002).
1.2 Database Environments:
The variables that surround the database are what are referred to as the database environments. These components are used to define and regulate the collection and storage as well as managing data (Morath 2000).
The components that make up the database environment area as follows:
Hardware components: includes the physical components like computers and associated accessories. The hardware components, in this case, refer to the computers, servers that will be used in hosting the website and developing it.
Software: includes the various data management programs, operating systems and other components. Examples of software include application programs that access the database, operating systems on which the DBMS runs on and the DBMS software. Software components are such as MYSQL DBMS for the creation and maintenance of the database, wamp server for developing and hosting the website etc.
Personnel- include system and database administrators, analysts, modelers and programmers as well as end users. The end users in this case are the former students of St. Gregory’s high school, who will use the website to update their contacts and to register as alumni.
Procedures- form the governing principles and guidelines for designing and using the database. Some principles used in this case are that only former students are allowed to register etc.
Data- represents the collection of facts within the database.
The interaction of the database with its environments allows for creation, maintenance and use of the database.
1.3 However, there are a few issues arise during the design and development of such an application. They include:
• The type of operating system on which the database application will run. For example, there exists different operating systems like windows, Linux etc. the different operating systems offer different capabilities to the database system and should be well chosen.
• Computer literacy- the end users are required to be computer literate in order to be able to make use of this application. Otherwise difficulties will arise during the use of the system.
• Other issues relate to the functionality of the application system i.e. what functionality to incorporate and which ones to leave out. This will be judged based on a feasibility study that will indicate whether such functionalities are necessary.
2 Database Software
2.1 Database Use
The database application developed will allow former students to register as alumni and update or change their contact details. The school administration will be able to track former students and invite them, using their contacts, to talks or other events in the school. The following figures represent inputs and outputs that the database application will use in its operations.
2.2 Sample Tables and Forms
Figure 1: Form to register as alumni
Figure 2: Form to Log into the system
Figure 3: Administrator tool to search for students
2.3 Critical Analysis of the Software:
The software as can be seen above will be used in registration of former students into the systems. The GUI will be able to act as an interface between the database and the users. This provides an abstraction meaning that users of the system need not know the backend operations that characterize database access and queries. The system, however, will require little knowledge of computers. This means that computer literacy is required to operate the system. Being that the system will be accessed by users in different locations, the application will have to of a distributed kind. Therefore, a web site is more preferred to a stand-alone application.
3 Database Design:
3.1 Normalization of the Database
The database will be made up of the following facts; First name, second name, email, address, zip code, year of completion, status, current job and course.
Table 1: initial table before normalization of the Data
Id
First Name
Second Name
Year of Completion
Zip Code
Telephone number#1
Telephone#2
Course
Current job
Status
Username
Password
Address #1
Address#2
Email#1
Email#2
The table above has not been normalized. As it can be seen, more and more columns would be required in cases where one has more than one address, telephone number or email address. This is not a feasible venture; therefore, this table will be required to be broken down into simpler tables that are more manageable. This process is what is referred to as normalization. All fields that may require multiple values will be removed from the table to form their own tables. Therefore, this database is expected to have four tables in it. The tables include telephone, email, Address and personal info.
Table 2: Personal information
Id
First Name
Second Name
Year of Completion
Zip Code
Course
Current job
Status
Username
Password
The primary key for this table is the id. This value will be automatically incremented each time a new record is inserted into the table. This will be the value that uniquely identifies the records in this table. The data stored in this table are all related to the student.
Table 3: student’s address
Id
Address
Table 4: student's Email
Id
Email
Table 5: Student's Telephone numbers
Id
Telephone
The tables showing students email, telephone number and addresses posses a foreign key id. This key is used to make reference to the personal info table. This concept is used to ensure that referential integrity is maintained within the database. This means that for any of these tables to be updated the id number must relate to some id number used in the personal info table.
Figure 4: table relationships
The diagram above is used to depict the relationship that exists among the four tables of the database. The table, personal info, has a one-to-many relationship with the rest of the tables. This means that there can be many addresses, emails or telephone numbers for one student in the personal info table.
4 Database Operations:
When a student enters information through the graphical user interface of the application, the data retrieved is distributed among the four tables. There are four queries made to the database:
One to the student info table inserting the first name, second name, year of completion, zip code, course, current job, status, username and password. The second to the telephone table inserting the id and telephone number. The third to the email table inserting the student’s id and email address. The last one is to the address table inserting the student’s id and address.
After all these transactions are made successfully, the user will be informed that he or she is part of the school’s alumni. This may be through a pop up window that displays the message. Success in registration means that one will be able to log into the system and alter their profile if they so desire. This will be done by accessing the login page where the user will be prompted for a user name and password. The values that are input through the graphical user interface will be checked against those stored in the database for verification. This means that there will be a query to the database. This query will retrieve the stored username and password and compare them against those provided. If a match is found, the user will be directed to their profile page where they can alter their information. However, if there is no match the user will be alerted. He or she may try again or request the administrator for password retrieval.
To this point, we have just considered the students as the users of this system. However, this application requires another user to carry out operations like sending invitations to students, sending them information etc. this user will require to have access to user details in order to carry out these operations. This will, therefore, require other queries to the database e.g. retrieval of information regarding a certain student or students who completed their high school in a certain year. A shown in figure 3, this user will be able to search the database for any records that they require.
Other recommendations for the system may be incorporating a utility in the system that will automatically send invitations or information to users through emails or text messages. This will reduce the effort and resources associated with sending postal mail to the users. The user in this case will just need to select the recipients of the invitation then press a button that will send the invitations at once.
5 Conclusion:
The database designed above will be able to hold information required by the school’s administration for a long time depending on the maintenance provided. To ensure this the database administrator should ensure that the servers hosting the application are reliable. Unreliable servers will usually go offline often thus making access to the website or database difficult. Reliable servers should contain a good antivirus software, adequate storage space and adequate memory. All this will ensure that the application is always available for the users and that the system is not attacked maliciously.
