The schema of a database system is its structure described in a formal language supported by the database management system. In relational database, the schema defines the tables, the fields, relationships, views, indexes, packages, procedures, functions, queues, triggers, sequences, materialized views, synonyms, database links, directories, java, XML schemas, and other.
Schemas are generally stored in a data dictionary. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure. In other words, schema is the structure of the database that defines the objects in the database. [1]
Information_Schema provides access to database metadata. Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.
Information_Schema is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.
In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them. [2]
In the LMS web application development, I am using phpMyAdmin GUI in WampServer package to create LMS database in MySQL.
1st Step: A new database named as lms needs to be created from phpMyAdmin GUI as shown in Figure 4.1. Please refer to Figure 2.2 WampServer Tray Icons on how to access phpMyAdmin GUI.
Figure 4.1 Creation of a new 'lms' Database in phpMyAdmin
SQL for Creating a new Database:
CREATE DATABASE ` LMS`;
Next step, proceed to creating table (entity) in LMS database as in 2nd Step.
2nd Step: Start with creating a new employee table in lms database from phpMyAdmin GUI as shown in Figure 4.2. After creating the first employee table for the LMS database, creation of subsequent tables will use the same method as the first table.
Figure 4.2-1 Creation of a new 'employee' Table in 'LMS' database
Figure 4.2-2 Creation of Field & Type in 'employee' Table
Figure 4.2-3 'employee' Table Created
SQL for Creating a new Table:
CREATE TABLE `lms`.`employee` (
`Employee_ID` SMALLINT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Type` VARCHAR( 1 ) NOT NULL COMMENT 'A/S',
`First_Name` VARCHAR( 50 ) NOT NULL ,
`Last_Name` VARCHAR( 50 ) NOT NULL ,
`Username` VARCHAR( 20 ) NOT NULL ,
`Password` VARCHAR( 50 ) NOT NULL ,
`Department_ID` SMALLINT( 2 ) NOT NULL ,
`Position_ID` SMALLINT( 4 ) NOT NULL ,
`Tel_No` INT( 15 ) NOT NULL ,
`Email_Add` VARCHAR( 60 ) NOT NULL ,
`MaritalStatus` VARCHAR( 1 ) NULL DEFAULT NULL ,
`Gender` VARCHAR( 1 ) NULL DEFAULT NULL ,
`NoofChildren` INT( 11 ) NOT NULL
) ENGINE = MYISAM ;
After creating the first employee table for the LMS database, the subsequent tables' creation will use the same method as the first table.
Population of Database Tables
After created the LMS database and all required tables from 4.1 Creation of Database Tables, the population (data entry) of tables as shown in Figure 4.3
Figure 4.3-1 Population of Data in 'employee' table
Figure 4.3-2 Insertion of Value in Fields of 'employee' table
Figure 4.3-3 Data Values Inserted in 'employee' table
SQL for Population of 'employee' Table:
INSERT INTO `demo.lms`.`employee` (`Employee`, `Type`, `First_Name`, `Last_Name`, `Username`, `Password`, `Department_ID`, `Position_ID`, `Tel_No`, `Email_Add`, `MaritalStatus`, `Gender`, `NoofChildren`) VALUES ('4', 'S', 'Lim', 'Tan', 'limtan', '', '1', '1', '01245645654', '[email protected]', 'M', 'M', '2'), ('5', 'S', 'Ali', 'Mohd', 'alimohd', '', '2', '2', '01265456488', '[email protected]', 'S', 'M', '0');
(Refer to Appendix 2 - Populated Data from LMS Database)
Web Page Design and Implementation - PHP Code
For the web design and development of the LMS web pages, I am using Adobe Dreamweaver (DW) CS4 software (Refer to Figure 4.4) to develop it. PHP is the server-side technologies (language) used.
Figure 4.4 Typical Screen of a Dreamweaver CS4 Screen
The following PHP files (with some JavaScript functions) are the coding for the LMS System (Refer to Appendix 3):
add-dept.php
Purpose: -
- Add new department (admin only)
add-leave.php
Purpose:
- Add leave entitlement for each leave type corresponding to position type (admin only)
add-position.php
Purpose:
- Add new position (admin only)
add-staff.php
Purpose:
- Add new staff (admin only)
amend-dept.php
Purpose:
- Edit existing department description (admin only)
amend-position.php
Purpose:
- Edit existing leave position (admin only)
calendar_form.php , calendar.js and tc_calendar.php
Purpose:
- Calendar function for leave application
delete-staff.php
Purpose:
- Delete existing staff (admin only)
dept.php
Purpose:
- View, add and edit Department (admin only)
footer.php
Purpose:
- Footer
functions.php
Purpose:
- Store all the below function to be called
Function DBConnect () - to connect to the database
Function LoginCheck () - to check both username and password supplied on login
Function UserLogin () - to check user is known and return Employee_ID, Position_ID, Username, First_Name, Last_Name and Type
Function Authenticate () - to check authentication on each separate page
Function CheckUsername () - to check a given username exists
Function CheckDepartment () - to check a given department description exists
Function CheckPosition () - to check a given position description exists
Function UserStatus () - to check user status to determine admin or staff
Function UserStatusApp () - to check user status to is approver or not
Function GetUser () - to retrieve user details from database
Function Getleave () - to retrieve a leave transaction details from database
Function GetPosition () - to retrieve a position details from database
Function FormDept () - to retrieve details of department and display them in a table
Function FormPosition () - to retrieve details of position and display them in a table
Function FormStaff () - to retrieve details of staff and display them in a table
Function LeaveForm () - to display the leave application form
Function FormLeave1 () - to display the leave that need approval/reject for the approver
Function FormLeave2 () - to amend the leave that is active
Function getDaysInWeek () - to get days in week
header.php
Purpose:
- Hold header information e.g. System name
home.php
Purpose:
- Main page of the system after login
index.php
Purpose:
- Login page
- 2 Types of login: Administrator or Staff
leave.php
Purpose:
- View existing leave entitlement (admin only)
leave-ammend.php
Purpose:
- Amend the leave that is selected (staff only)
leave-approve.php
Purpose:
- To approve or reject an leave application (approving staff only)
leave-status.php
Purpose:
- To view and select all leave application that needs action (approving staff only)
leave-active.php
Purpose:
- View and select a leave that is active to amend (staff only)
position.php
Purpose:
- To add or view and select an existing position to amend
staff.php
Purpose:
- To add or view and select an existing position to amend or delete
logout.php
Purpose:
- To log out of the system
admin-menu.php
Purpose:
- Menu for admin
approver-menu.php
Purpose:
- Menu for approver staff
menu.php
Purpose:
- Menu for staff
