Implementation Creation Of Database Tables Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

The schema of a database system is its structure described in a formal language supported by the database management system (DBMS). 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', 'lim.tan@vilbert.com', 'M', 'M', '2'), ('5', 'S', 'Ali', 'Mohd', 'alimohd', '', '2', '2', '01265456488', 'Ali.Mohd@vilbert.com', '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

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.