Ceng 356 Lecture Example Project

Term Project “Construction Engineering

Information System, CEIS”

By Đhsan Tolga MEDENĐ

ÇANKAYA Spring, 2010-2011

1

Table of Contents

Table of Contents .................................................................................................................. 1

Introduction ........................................................................................................................... 3

ER Diagram .......................................................................................................................... 4

Schema Design ..................................................................................................................... 5

Employee Schema ............................................................................................................ 5

GuestBook Schema ........................................................................................................... 5

Log_In Schema ................................................................................................................. 5

Message Schema ............................................................................................................... 6

Page Schema ..................................................................................................................... 7

PageRole Schema ............................................................................................................. 7

Project Schema ................................................................................................................. 8

Project_Employee Schema ............................................................................................... 8

ProjectTask Schema .......................................................................................................... 9

Role Schema ................................................................................................................... 10

Task Schema ................................................................................................................... 11

TaskFlow Schema ........................................................................................................... 12

Version Schema .............................................................................................................. 12

Populated Tables ................................................................................................................. 15

Employee Table .............................................................................................................. 15

GuestBook Table ............................................................................................................ 15

Log-In Table ................................................................................................................... 15

Message Table ................................................................................................................ 16

Page Table ...................................................................................................................... 16

PageRole Table ............................................................................................................... 16

Project Table ................................................................................................................... 16

ProjectEmployee Table ................................................................................................... 17

ProjectTask Table ........................................................................................................... 18

Role Table ....................................................................................................................... 19

Task Table ...................................................................................................................... 20

TaskFlow Table .............................................................................................................. 21

Version Table .................................................................................................................. 21

Screens ................................................................................................................................ 22

General Screens .............................................................................................................. 22

2

Log-In Screen ............................................................................................................. 22

GuestBook .................................................................................................................. 24

Admin Screens ................................................................................................................ 26

Admin Home .............................................................................................................. 26

Admin-Employee Management .................................................................................. 27

Admin-Project Management ....................................................................................... 32

Admin Project Employee Management ...................................................................... 35

Admin-Role Management .......................................................................................... 38

Admin-User Accounts ................................................................................................ 43

Admin-Message and GuestBook ................................................................................ 45

Admin-Web Page Management .................................................................................. 49

Employee Screens ........................................................................................................... 52

Non-Project Manager Screens .................................................................................... 53

Tasks ........................................................................................................................... 55

Message ...................................................................................................................... 61

User ............................................................................................................................. 63

Project Manager Screens ............................................................................................ 65

Task Update and Delete .............................................................................................. 69

Summary ............................................................................................................................. 72

Exception Handling ........................................................................................................ 72

Cascade on Delete and Update and Referential Integrity of CEIS ................................. 72

Direct Approach .......................................................................................................... 72

In-Direct Approach ..................................................................................................... 75

Screen Based Insert, Delete and Update Operations ...................................................... 75

Views .............................................................................................................................. 76

Some Example Queries ................................................................................................... 77

One More Query ......................................................................................................... 78

3

Introduction The construction engineering is one of the most important disciplines under the civil engineering. However, most of the computer systems are focused on Computer Aided Design program and most of them are not open to the collective work of employees. In Construction Engineering Information System(CEIS), our aim is to build a structure that can achieve the following goals;

• Version Management

• Collective Working Environment

• Web Based System

• Interoperable with the designed CAD system.

In here, actually the CAD system is build, on the other hand, this part is still continues to work. Our CEIS is just the minor part of the system, but when it is fully integrated with the existed part, it will work. In our system we could not concentrate on the necessary parts like security, a better locking mechanism and supporting some constraints like not allow work on same task by other engineers. This systems only aim is to demonstrate and execute some of the expected functionality of this system. In our CEIS the following functionalities are given;

• A Log-In mechanism for distinguish the user groups, such as Admin, Project

Manager, Employee and Guest;

• Some Admin functionalities, Employee Management, Project Management, Role

Management, Page Management, Message Guestbook management and User

Accounts Management;

• Member User functionalities, Message sending, Very Simple Project Manager

page, Task Management, User Accounts Management ;

• For the Guest user, a page to publish their comments.

In this project we developed the screens with Visual Studio 2008 IDE. For the database management system, we used SQL Server 2008 Enterprise edition.

4

ER Diagram

5

Schema Design The schema and their table properties are developed in the SQL Server 2008 management tool. Let us give the table by table design properties;

Employee Schema

EID is the Employee ID, that is primary key of this table. Name is name of the employee, Surname is the surname of the employee. Gender is sex indicator of the employee. DateofBirth keeps the information of employee’s birth date. Job Title is the title of the employee, like Computer Engineer or driver. EmploymentDate keeps the information of employee’s starting date of the department.

GuestBook Schema

GBID is the GuestBook Id, the primary key. [User] is the user name of the guest that is given instantly while user entering his comments. Comment is the part that is filled by the user. This table has no connection between the main system schemas.

Log_In Schema

6

Actually, for this table, we do not require a primary key. But when we look at the condition, because of EID unique structure, we can assign it as a primary key. This is also a foreign key that is referenced to the Employee table. If an employee does not exist, we cannot give a username and password to the specific employee. The connection with the employee table as follows;

Employee *EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Log_In *UserName

Password

EID

Message Schema

In this schema, MID, message ID is the primary key. [From] is the Role ID of the sender and [To] is the role ID of the receiver. Message_Title is the title of the message, and message is the body of the message. Date gives the information of when the message is created and [Read] is giving the information of the message is read or not. Actually, under this project, [Read] information does not used, but in the future applications it will be included our structure. The message Schema is using Role table with [From] and [To] as foreign keys.

7

Message *MID

[From]

[To]

Message_Title

Message

Date

[Read]

Role *RID

EID

PID

RoleName

StartDate

EndDate

Page Schema

This schema is responsible for keeping the information of each page in the system. PageID is the primary key. Page is giving the page information and PageURL is keeping the information of page URL.

PageRole Schema

This schema is a relational schema. In here we can say that, we keep the access rights of the employee in specific page. In our scenario, for the “project manager” page, we are using the information of this schema.In here PageID is the foreign key that is referenced to the Page Schema and the RID is the foreign key that is referred to the Role table. The relation between the schemas as follows;

8

Page

PageID

Page

PageURL

PageRole

PageRoleID

PageID

RID

Role *

RID

EID

PID

RoleName

StartDate

EndDate

Project Schema

Project Schema is responsible for keeping the general information of the each project. PID, Project ID is the primary key in this schema. Name is the project name, contructor_Name is person who is responsible for the project. PStartDate is the project’s predefined project start date. PDeadLine is the project’s expected end date.

Project_Employee Schema

9

This schema is a relational schema that is connecting project table and employee table. Through this table a person can reach the information of which employee is in which project. PEID, project employee ID is the primary key, PID is the foreign key that is referred to the Project Table, and EID is also a foreign key that is referred to the Employee table. We can visualize the relations between these three tables as follows;

Employee

EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Project

PID

Name

Contructor_Name

PStartDate

pDeadLine

Project_Employee

PEID

PID

EID

ProjectTask Schema

This schema is also a relational schema, which is connecting Project and Task Schemas. Through this schema someone can see which task is related with which task and which project has which tasks information. In here PTID, project task ID is the primary key, PID is the foreign key that is referred to the Project schema and TID is the foreign key that is referred to the Task schema. The relations between these schemas as follows;

10

ProjectPID

Name

Contructor_Name

PStartDate

pDeadLine

ProjectTask

PTID

PID

TID

TaskTID

TaskType

Creator

CreationDate

Condition

TaskName

Approved_Version

DueDate

Role Schema

This schema keeps the information of the employees’ role in different project. Actual, in the original design, an employee has different roles in different project and also an employee has more than one role in a project. The basic roles are Project Manager, Engineer and Draftsman. In here RID , role ID, is the primary key, EID is the foreign key that is referred to the Employee schema, the PID is also the foreign key that is referred to the project schema. RoleName is the name of the role, the predefined roles. Also in the future with the startdate and enddate attributes we will be able to define roles for a period of time. But in this project, we are not using these attributes in our system. The relationship between the other schemas as follows;

11

Employee

EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Project *

PID

Name

Contructor_Name

PStartDate

pDeadLine

Role *

RID

EID

PID

RoleName

StartDate

EndDate

Task Schema

Task schema actually is an important table to achieve the task flow and the versioning. The TID is the primary key of this schema. Task type define the type of the task, Creator is the RID of the employee who defined the task (this means this attribute is a foreign key that is referred to the Task Schema), CreationDate, the assignment date of the task, condition is the stage that is showing the condition of the task, task name is the specific name of the task, approved_version is the last version that is approved by the project manager or the role that is responsible of the root task and DueDate is the deadline for the task. In here we are not using condition and Approved_Version attributes, but these will be activated in the future. The relations of this schema as follows;

Role *RID

EID

PID

RoleName

StartDate

EndDate

Task *TID

TaskType

Creator

CreationDate

Condition

TaskName

Approved_Version

DueDate

12

TaskFlow Schema

This schema’s purpose is to define work flows between the employees of a project through versions of the tasks. In here what is in the flow is the versions of the tasks. TFID, task flow ID, is the primary key, RID the foreign key referred to the Role schema, that gives the information of receiver, VID is the version in the flow, [From] is the foreign key referred to the Role schema the creator, the previous owner of the version. Comment is the specific comment included to the version. And date is date when the version putted in to the flow. The relation of the TaskFlow and Role schemas is given as follows;

Role *RID

EID

PID

RoleName

StartDate

EndDate

TaskFlowTFID

RID

VID

[From]

Comment

Date

Version Schema

13

Version schema is responsible for keeping main information of the tasks in the work flow. VID, version ID is the primary key, Name is the name of the version, TID is the foreign key that connects the Task table, PreVer is the previous version foreign key that is connecting the version table itself, Creator is the RID of the version creator, CreationDate is date version created, Lock is giving the information of the version is locked or not and Locker gives information of who locked the version. The relations in this schema given as follows;

Role *RID

EID

PID

RoleName

StartDate

EndDate

Task *TID

TaskType

Creator

CreationDate

Condition

TaskName

Approved_Version

DueDate

Version

VID

Name

TID

PreVer

Creator

CreationDate

Lock

Locker

14

We can summarize all the relations between the schemas as follows;

Project

PID

Name

Contructor_Name

PStartDate

pDeadLine

ProjectTask

PTID

PID

TID

Task TID

TaskType

Creator

CreationDate

Condition

TaskName

Approved_Version

DueDate

TaskFlow

TFID

RID

VID

[From]

Comment

Date

Version

VID

Name

TID

PreVer

Creator

CreationDate

Lock

Locker

Employee *

EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Role

RID

EID

PID

RoleName

StartDate

EndDate

GuestBook

GBID

[User]

Comment

Log_In *

UserName

Password

EID

Message

MID

[From]

[To]

Message_Title

Message

Date

[Read]

Page PageID

Page

PageURL

PageRole

PageRoleID

PageID

RID

Project_Employee

PEID

PID

EID

15

Populated Tables To populate the table we use the test records. Also these records updated in the development. The freeze versions of these tables and their record, the populated tuples, are given in this section;

Employee Table

GuestBook Table

Log-In Table

16

Message Table

Page Table

PageRole Table

Project Table

17

ProjectEmployee Table

18

ProjectTask Table

19

Role Table

20

Task Table

21

TaskFlow Table

Version Table

22

Screens In this part, we try to define our screens like a technical manual. In here we divided our screen based on two user perspectives, admin and member users, with including two additional screens, the Log-In screen and Guestbook screen under general screens part. We can say that there is a hierarchical structure between users and screens, the following figure can give us an idea about this structure;

Each screen will be given with its screen shot, it’ related tables, and the queries used in each. Let’s start with the general screens

General Screens

Log-In Screen

23

Log-in screen is the first screen in our Construction Engineering Information System (CEIS). Its solo purpose is to authorize users and direct them based on their roles in the CEIS system. There are two buttons used to activate its functionalities, the log-in button and GuestBook button. Member users of the system can log in to the system in log-in and guest users directed to the guestbook through GuestBook button. Only query work for this page work through Log-In table;

In this table, EID is the foreign key that is referred to Employee table. The query in this table as follows; SELECT EID FROM Log_In

WHERE UserName='" + user + "'

AND Password='" + pass + "'

In here, user and pass are taken from the screen. If the user and password is defined in the table the EID will return. When we try with the user=”anil” and password=”123456” the result will be as follows;

24

If a user entered a wrong user name or pass, on the screen the following error message will be displayed;

GuestBook

GuestBook screen purpose is to keep comments and important thinking of the non-member users of the system. After a user enters his comments and email address, publishing button run an insert query. Others button run a select query to display other comments.

25

This screen only uses one table, the GuestBook table.

This table is not connected with any other table. The queries are as follows; INSERT INTO GuestBook

([User],Comment) Values('" + email + "','" + comment + "')

The values of email and comments are taken from the screen. The result of this query as follows;

Select [User],Comment

From GuestBook

This query calls all the comments that are previously published by the other quest users, the result of the query as follows;

26

Admin Screens

Admin Home

This page serves for admin user to use functionalities of the admin tasks. As you can see, for admin user, there are 8 main functions. (We will see in details in the following screens. In this page there is only one query call with again select statement, the part that we display “Welcome Admin” part. The related table is the employee table, and for this function, it is not using any foreign key connection.

27

The query as follows;

SELECT Name,Surname

From Employee

Where EID='" + EID + "'

The EID is taken with from the forwarded page variable, and it calls the information from the Employee table. Also this function works the rest of the admin and other member tables with the same logic. The result as follows;

Admin-Employee Management

This screen is serving searching employees, creating new employees and updating and deleting employees.

28

All these functions use the same table the employee table.

Let’s look at each function one by one.

Search Function

The search function works seek the employee name and the employee surname in the employee table. Search Function works through with the following query. Select *

From Employee

Where Name='"+search+"'OR Surname='"+search+"'

29

The search parameter is taken through user screen. The examples are as follows;

Because there is only one employee with the name “Tolga”, only one result will we return from this query.

On the other hand when we try to enter “Medeni” in the search box, it returns the following result set;

Create a New Employee

To create a new employee, the following textbox is used.

When the admin click the add button, it runs the following query;

30

INSERT INTO Employee

(Name,Surname,Gender,DateofBirth,Job_Title,EmployementDate)

Values('" + name + "','" + surname + "','" + gender + "','" +

dateofBirth + "','" + jobTitle + "','" +employmentDate+ "')

After a new employee added, the following message will be displayed;

Update an Employee

Before updating or deleting an employee we need to enter EID of that employee as follows;

The query to call employee information as follows; Select *

From Employee

Where EID='" + search + "'

Let’s change the Employee name ”Orcun” to “Emre”;

31

The update query as follows; Update Employee

Set Name='" + name + "', Surname='" + surname + "',Gender='" + gender +

"',DateOfBirth='" + dateofBirth + "',Job_Title='" + jobTitle +

"',EmployementDate='" + employmentDate + "' WHERE EID='" +

EID_Search.Text + "'

As you can see, we are taking the all the TextBox values from screen. Let’s Delete this Employee

Delete query as follows; Delete From Employee

WHERE EID='" + EID_Search.Text + "'

32

This query also works according to EID textbox value, for this case, it deletes EID=17 record.

Admin-Project Management

This screen made for satisfying the main project requirements of the system. Admin can search a project according to project name. Also the initialization of the project has been done through create a new project. Update and Delete part has been used for the purposes of updating project information or delete project information according to specified PID, project ID.

All these functions use the following table, Project Table;

Project

PID

Name

Contructor_Name

PStartDate

pDeadLine

Each of the specific functions as follows;

Search a Project

Search function runs when the admin enters a project name or a constructer name;

33

The following query is used to gather the project information; Select *

From Project

Where Name='" + search + "'OR Contructor_Name='" + search + "'

The result set displays on the screen as follows;

Add a Project

After entering the required text boxes, the query runs when the admin clicks the add button. The following query works as follows; INSERT INTO Project (Name,Contructor_Name,PStartDate,pDeadLine)

Values('" + name + "','" + Contructor_Name + "','"+ PStartDate + "','"

+PDeadLine+ "')

The result of this insert query displays the following result;

The changes in the table as follows;

Update and Delete

Before making any changes, first of all, we need to enter PID.

34

The PID calls the project information with the following query; Select *

From Project

Where PID='" + search + "'"

Let’s change the contractor name to “Huseyin Gumus” ;

The following query runs for the update operation; Update Project Set Name='" + name + "', Contructor_Name='" + c_name +

"',PStartDate='" + p_StartDate + "',pDeadLine='" + p_DeadLine +"'

WHERE PID='" + PID_Search.Text + "'

Let’s say, from now on, we canceled our contact, so we need to delete it from our DB.

The delete operation the following query was executed; Delete From Project

WHERE PID='" + PID_Search.Text + "'

After the final transaction, our table changed as follows;

35

Admin Project Employee Management

This screen’s purpose is to manage employees in a project. Admin can add, and delete employees in a project. The Project_Employee table is used for this purpose;

EmployeeEID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

ProjectPID

Name

Contructor_Name

PStartDate

pDeadLine

Project_Employee

PEID

PID

EID

As you can see, this table keeps PID and EID keys as foreign keys.

Viewing Connection between a Project and Employees

After entering a PID, the following query runs; Select EmployeeID EID, EmployeeName Name,EmployeeSurname Surname

From Project_Employee_View PEV

Where ProjectID='" + search + "' ORDER BY EmployeeID

36

As you can see, we are retrieving our information from the Project_Employee_View. Also to get the employee information in an order, we used Order By. This view is given as follows;

When we entering PID=’1’, the following results will be displayed;

In here actually we are retrieving employee information through with the previous query. On the other hand, we retrieve the Project Name with the following query; Select Distinct ProjectName

From Project_Employee_View

Where ProjectID='" + search + "'"

The “Distinct” command is used because there are more than one Project Name information could return from the Project_Employee_View.

37

Remove an Employee from the Project

Actually this is a very simple operation. We just give the EID and click the DeleteEmployee button.

The following query executes for this purpose; Delete From Project_Employee

WHERE EID='" + search + "' AND PID='"+P_Label.Text+"'

As you can see we are using the Project_Employee table connection to remove an employee from the project. After this operation, the table is updated as follows;

Add an Employee to the Project

Let’s re-add “Tunc Medeni” to the project. To do that we need to enter PID of this employee into TextBox;

“Click”ing EnterPID executes the following query, Select *

From Employee

Where EID='" + search + "'

38

When we enter AddEmployee button, the following result displayed as follows;

The following query is executed; INSERT INTO Project_Employee(PID,EID)

Values('" +P_Label.Text + "','" + E_Label.Text + "')

After this insertion, the table is updated as follows;

Admin-Role Management

Role management is used by admin to assign different role to the project employees. There are three predefined roles, project manager, engineer and draftsman. The main screen as follows;

To identify a project role, first of all we need to enter a PID;

39

When admin enters a PID, the predefined employees are listed on the screens. The screen is populated from another view, Project_ Role Employee_View.

The query that is used to populate the screen as follows; Select EID,N Name,S Surname,Role

From Project_Role_Employee_View

Where ProjectID='" + search + "' ORDER BY N

In here the project name is extracted with the following query

40

Select Distinct Project

From Project_Role_Employee_View

Where ProjectID='" + search + "'

Role Update

When admin clicks EditRoles button the screen changes as follows;

After viewing this screen, we can be able to update a project role of an employee with entering his/her EID and clicking update button. Let’s update role of Tunc Medeni from “Project Manager” to “Draftsman”;

The following query is executed; Update Role Set RoleName='" + role + "'

41

WHERE RID=(

Select RID FROM Role

Where EID='"+search+"' AND PID='"+Project_ID.Text+"')

As you can see, the update operation executed through a nested query and this nested query affects the role table;

RoleRID

EID

PID

RoleName

StartDate

EndDate

The result of this update in the Role table as follows;

As you can see, the RID 3 is updated to the RoleName Draftsman. In here PID=1 is the GAP and EID=2 is the Tunc Medeni

Define a New Role

To define a new role, first of all we need to specify PID and EID. When we enter PID as 1 the following result will be displayed as follows;

EnterPID button retrieves the record with the following query; Select *

42

From Project

Where PID='" + search + "'"

EnterEID works similarly, let’s enter EID as 15;

The query that retrieves the employee information works as follows; Select *

From Employee

Where EID='" + search + "'";

After selecting the employee’s role we can click “Add Role” button;

In here, we Insert two records, one in the Role table and the other one is on the Project_Employee table. INSERT INTO Role (EID,PID,RoleName)

Values('" + SearchEID.Text + "','" + Search.Text + "','" + Role + "');

INSERT INTO Project_Employee (PID,EID)

Values('" + Search.Text + "','" + SearchEID.Text + "')

After insertion the tables updated as follows;

Project_Employee Table Role Table

43

Also on the screen the following message is displayed;

Remove a Role

To remove a role, we need PID and EID. When we enter the values as PID=1 and EID=15, the role and the Project-Employee relation will be removed;

The queries as follows; Delete From Role

WHERE EID='" + EID + "' AND PID='" + PID + "';

Delete From Project_Employee

WHERE EID='" + EID + "' AND PID='" + PID + "'

As you can see, we are effecting two tables, the Role and Project_Employee .

Admin-User Accounts

This screen was build for managing the new members. In here, admin create a new user based on his/her EID and creates a new user name with his/her password.

44

The following table is used for this operation;

Log_In

UserName

Password

EID

As you can guess, we cannot add a EID which is not in the employee table. The following query executes after clicking “Create” button; INSERT INTO Log_In (UserName,Password,EID)

Values('" + TB_UserName.Text + "','" + new_pass + "''" + EID + "'

To execute this query, admin should enters same password on enter and retype part. If not the screen displays the following message;

After entering a record, the result is displayed as follows;

The records updated as follows;

45

Delete a User

To delete a user, we need to enter his EID. After this operation, just the user’s log-in credentials are removed, still employee is an employee.

The following query executed after clicking delete user button

Delete From Log_In

WHERE EID='" + EID + "'

Admin-Message and GuestBook

46

Sometimes organizations require controlling content of their pages against improper message or comments. This page helps admin to support this need. This page actually related with the two tables the Guestbook and the Message tables;

GuestBookGBID

[User]

Comment

MessageMID

[From]

[To]

Message_Title

Message

Date

[Read]

GuestBook Update and Delete

To update or delete a guestbook comment we need to enter GBID of the guestbook entry. In here we can change comment or user information. EnterGBID calls the record with the following query;

47

Select [User],Comment

From GuestBook

Where GBID='" + search + "'

Let’s change the information of GBID=3

When we click the update button the values update with this query Update GuestBook Set [User]='" + TB_GBUser.Text + "', Comment='" +

TB_Comment.Text + "'

WHERE GBID='" + TB_GBID.Text + "'

After update, when we refresh the page, we will see the change on the entry;

If we click the Delete button, this will remove the GBID=3 entry with executing this query; Delete From GuestBook

WHERE GBID='" + search + "'

The result will be as follows;

48

As you can see no more GBID=3 entry.

Message Delete and Update

Message delete and update works similarly; this part also on the same screen. To update or delete a message user requires to enter MID. The EnterMID button calls the required with the following select statement; Select [From],[To],Message_Title,Message,Date

From Message

Where MID='" + search + "'

In here after changing the values when we click update this query is executed; Update Message Set [From]='" + TB_From.Text + "', [To]='" + TB_To.Text +

"', Message_Title='" + TB_Title.Text + "', Message='" + TB_Message.Text

+ "', Date='" + TB_Date.Text + "'

WHERE MID='" + TB_MID.Text + "'

49

To delete a message, after entering MID, we need to click “Delete” and it calls the following query; Delete From Message

WHERE MID='" + search + "'

The result of deleting MID=9 as follows;

Admin-Web Page Management

This screen helps admin to manage the access right of the member’s project management screen and the defining the web pages and their links into database. This will give flexibility to extend this functionality for every single page of the system. On the other hand, because of the time limit of this project, we just included access right of the member’s project management screen. The following tables are the main tables;

Page *

PageID

Page

PageURL

PageRole *

PageRoleID

PageID

RID

50

As you can, before giving any access right, first off all we need to define Page. Also the rights are given according to roles of the employees. The page information filled with the following query; Select * From Page

The current rights fill with the following query; Select PageRole.RID,PageRole.PageRoleID,PageURL

From Role,Page,PageRole

Where Role.RoleName='Project Manager' AND PageRole.RID=Role.RID AND

PageRole.PageID=Page.PageID

To see which employee has the role “project manager” we also require the following query; Select R.RID,E.Name,E.Surname

From Employee E,Role R

Where R.RoleName='Project Manager' AND R.EID=E.EID

Page Information and Its Functions

51

To create a new page information we need to enter page title and the Page URL. After clicking “CreatePage” this will execute the following query; INSERT INTO Page (Page,PageURL)

Values('" + TB_PageName.Text + "','" + TB_URL.Text +"' )

To delete or update a page we just need to enter PageID The update button calls the following query; Update Page Set Page='" + TB_PageName0.Text + "', PageURL='" +

TB_URL0.Text + "'

WHERE PageID='" + TB_PageID.Text + "'

The delete button calls the following button; Delete From Page

WHERE PageID='" + search + "'

The sample examle as follows;

Page Rights and Its Functions

52

In our scenario only for the ProjectManagement.aspx, project manager’s right is defined. For this reason we only require project managers. The create connection button calls the following query; INSERT INTO PageRole (PageID,RID)

Values('" + TB_PID.Text + "','" + TB_EID.Text + "' )

The update button calls the following query; Update Page Set PageID='" + TB_PID.Text + "', RID='" + TB_EID.Text + "'

WHERE PageID='" + TB_PageID.Text + "'

The delete button calls the following query; Delete From PageRole

WHERE PageRoleID='" + PRID + "'

After that we will not see this relation;

Employee Screens

There are two basic users’ types under these screens, the project managers and non-project managers. Let’s start with the non-project manager screens.

53

Non-Project Manager Screens

Let’s enter the credentials of non-project manager

The opening page is the member site

In this screen, and the following screens (both for project and non-project manager members) the following query executes and until log out; SELECT Name,Surname

From Employee

Where EID='"+EID+"'

Also this EID value specific to the user and carry out between the pages. http://localhost:52599/CEIS/Member/Member.aspx?EID=2 As you can see there are four basic functions Tasks, ProjectMan, Message and User. In here when this non-project manager tries to enter this site he will face an error message;

54

This control achieved through with the following tables;

PageRole *

PageRoleID

PageID

RID

Role *RID

EID

PID

RoleName

StartDate

EndDate

First off all, we need RID, we can retrieve this information with using EID. In role table, with sending EID; SELECT RID

FROM Role

WHERE RID=(

Select MAX(RID)

FROM ROLE

WHERE EID='" + ManID + "')

In here, because we could receive more than one RID (because an employee could have more than one role) to use the highest RID value, we use MAX(RID) query. (In here for this scenario, we assume, an employee could be project manager of one project) After than with the returned RID, we try to take “project manager “role information with the following query; SELECT PageRole.PageRoleID

From Page, PageRole

55

WHERE PageRole.RID='" + Role_Man + "' And

Page.PageURL='~/Member/ProjectManagement.aspx' And

PageRole.PageID=Page.PageID

If there is an existing Role_Man RID in PageRole table and it has access right to this page, it will let to page. But in this condition, this is not the case. So this user cannot see the whole page.

Let’s look at the other functions;

Tasks

The task screen shows employee the given tasks by the project employee. In here this employee could create a sub version related with this project and assign it to the other employees. The following tables are used in this screen;

56

Employee

EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Role

RID

EID

PID

RoleName

StartDate

EndDate

Task

TID

TaskType

Creator

CreationDate

Condition

TaskName

Approved_Version

DueDate

TaskFlow

TFID

RID

VID

[From]

Comment

Date

Version

VID

Name

TID

PreVer

Creator

CreationDate

Lock

Locker

The task assigned to this employee retrieved with the following query; Select

V.TID,V.VID,R.RID,T.TaskName,T.TaskType,T.CreationDate,T.DueDate,V.Name

As Version

From Task T, Version V, TaskFlow TF, Role R,Employee E

Where R.EID='"+EID+"' And R.RID=TF.RID AND V.VID=TF.VID AND T.TID=V.TID

AND R.EID=E.EID

57

To assign this task to another employee, employee could click the ListProjectEmployees button. This will help him to see the employees in the same project.

The query executed in this operation as follows; Select E.Name,E.Surname,R.RID From Employee E,Project P, Role R

Where R.PID=P.PID AND E.EID=R.EID AND P.PID IN(

Select PID From Role R Where R.EID='"+EID+"')

The creation of this sub-version requires a couple of queries; INSERT INTO Version (Name,TID,Prever,Creator,CreationDate)

Values('" + TB_Ver.Text + "','" + TB_TID.Text + "','" + TB_VID.Text +

"','" + TB_RID_T.Text + "','" + DateTime.Now.ToShortDateString() + "');

The first insertion operation rights into the Version table. In here the name of the version, its original taskid, its previous version (if it is exists), the creator role id(in here this employee’s role ID specific to this project, and creationDate, the present date when it is created, is written to the table. Before putting this new version into the TaskFlow, we need the lasted VID at is created by the previous query. We retrieve this information as follows; Select VID

From Version

Where VID=(Select max(VID)from Version)

In here, the Max(VID) is the highest, the latest VID. (We used it as nested, it creates problem with the single query).The second Insert statement uses this VID as follows; INSERT INTO TaskFlow (RID,VID,[From],[Comment])

Values('" + TB_RID_T.Text + "','" + VID+ "','" + TB_RID_F.Text + "','" +

TB_Comment.Text + "')

58

In here, the one who creates this version inserted with the RID, the VID comes from the previously Select query and [From] is the RID of the receiver, the comment is the one added by our user to dictated wants and requirements given to the receiver. In here let’s our user send it back the first Task to the project manager Tolga

When our user click to the create version, this new version will be sent to the Tolga and he will this version in his screen;

Task Locking and Unlocking

Task locking and unlocking basically updates version in the version table. In the future, this would be used for controlling and coordinating delegated tests. The screen as follows;

In here the tasks are showing VID of the tasks that is delegated to the employee Tunc Medeni. When we look at the version table we can see the results as follows;

59

As you can see, the VID 11 and 16 seen as unlock. Lets lock the VID=11;

When the user click enter, it will update the table as follows;

The query does the job as follows; "Update Version Set Lock='True', Locker='" +Request.QueryString["EID"] +

"' Where VID='"+TB_VID_Lock.Text+"'

The unlock mechanism works similarly;

60

When the clicks the EnterVID on the unlock part it will update table as follows;

The query related with this function as follows;

Update Version Set Lock='False', Locker='" + Request.QueryString["EID"]

+ "' Where VID='" + TB_VID_Unlock.Text + "'";

Updating Employee Versions

In here updating version is related with who will retrieve the new version and with what comment. Version creator can change these values;

Before updating a version, first of all, the member user should enter TFID, task flow id of his version. When the user clicks the Enter_TFID button, the following query executes; Select RID,Comment

FROM TaskFlow

61

Where TFID='" + TextBox1.Text + "'

In TaskFlow, this version is given as follows;

When the member user changes the comments or receiver, it needed to click to UpdateVersion button. When he/she clicks the following query calls; Update TaskFlow Set

RID='"+TB_Receiver.Text+"',Comment='"+TB_Comment_U.Text+"'

Where TFID='" + TextBox1.Text + "'

Let’s change the Comment as follows;

When the member clicks the update button, it will update the TaskFlow table as follows;

Message

62

Message screen purpose is to create an interactive connection between the member users of the system. The tables responsible for this operation as follows;

Employee *EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Message *MID

[From]

[To]

Message_Title

Message

Date

[Read]

When the employee clicks the message button, the system will retrieve the messages with the following query; Select E.Name,E.Surname,M.Message_Title,M.Message

From Message M, Employee E

Where M.[To]='" + Request.QueryString["EID"] + "' AND E.EID=[From]

In here the EID of the employee send to the system. An employee also sends messages to the other users with EID of the users. The query of this operation as follows; INSERT INTO Message ([From],[To],Message_Title,Message,Date)

Values('" + From + "','" + To + "','" + Title + "','" + Message + "','"

+ DateTime.Now.ToShortDateString() + "' )

63

From is the EID of the sender, to is the EID of the receiver, title is message title, message is the body and date is the present date send the message; Let’s send a message to the Tolga;

When Tolga Log-In in to the system he will see the message;

User

User screen is the part that an employee can update his password. The screen as follows;

This screen is similar with the Admin’s screen. The only difference, in here an update query is working;

64

Update Log_In Set Password='" + new_pass + "'

WHERE EID='" + EID + "'

In here, if the users enter different passwords in enter new password and retype your new password he will receive the following message;

After changing a password, this screen will be displayed;

The table responsible for this operation is;

Log_InUserName

Password

EID

65

Project Manager Screens

Project managers also enter to the system through the same login screen. The only difference because they are already assigned to the project management page, (Page-Role Table) they can see the whole context;

Again, for defining a task the following tables are the main players;

Project

PID

Name

Contructor_Name

PStartDate

pDeadLine

ProjectTask

PTID

PID

TID

Task

TID

TaskType

Creator

CreationDate

Condition

TaskName

Approved_Version

DueDate

TaskFlow

TFID

RID

VID

[From]

Comment

Date

Version

VID

Name

TID

PreVer

Creator

CreationDate

Lock

Locker

Employee

EID

Name

Surname

Gender

DateOfBirth

Job_Title

EmployementDate

Role

RID

EID

PID

RoleName

StartDate

EndDate

66

To create a new task, again we require a sequence of queries; SELECT RID FROM Role

WHERE RID =(Select RID FROM Role where EID='" + ManID + "' AND PID='" +

PID_LBL.Text + "')

In this query, we are taking the RID of the manager. INSERT INTO Task (TaskType,Creator,CreationDate,TaskName,DueDate)

Values('" + TaskType + "','" + Role_Man + "','" +

DateTime.Now.ToShortDateString() + "','" + TaskName + "','" + dueDate+

"')

With this query, we are inserting the task table, the task information. After creating this task, we need to show the system, which project this task belongs to, so we need the TID(task id). Again the newest, the highest TID value will be our Task, the following query takes this value; Select TID

From Task

Where TID=(

Select max(TID)from Task)

Now we can create the Task Project relation with this query; INSERT INTO ProjectTask (PID,TID)

Values('" + PID + "','" + TID + "')

In this system, every new created task actual is the first version, so we need to add a record to the version table; INSERT INTO Version (TID,PreVer,Creator,CreationDate)

Values('" + TID + "',1,'" + Role_Man +

"','"+DateTime.Now.ToShortDateString()+"')

As you can see, because this one is the first version, we added PreVer as one. Similar to the creating version, we require this version’s VID

67

Select VID From Version

Where TID=(

SELECT MAX(VID) FROM Version)

Here the following query defines a task flow for this version; INSERT INTO TaskFlow (RID,VID,[From],Comment,Date)

Values('" + Role + "','" + VID + "','" + Role_Man + "','" + Comment +

"','" + DateTime.Now.ToShortDateString() + "')

Let’s create subversion for the employee Tunc

On the Tunc’s screen he will see the task as follows;

Listing Project According To Updates through Derived Attribute

Actually, we used derived attributes in many screens like creating and employee, which is taking the string attribute and convert it to the date-time attribute.

68

In this screen, again using a derived attribute, with a given date we can list the projects before and after list details.

If we would like to list to project before this date we need to select before, the result screen as follows;

The query that works behind as follows; SELECT PID,Name,Contructor_Name

FROM Project

WHERE pDeadLine<='" + d_date + "'"

In this query d_date is taken as string from the drop down lists. If we want to project defined after this date, we need to choose after;

The following query is responsible for this task; SELECT PID,Name,Contructor_Name

69

FROM Project

WHERE pDeadLine>='" + d_date + "'"

Task Update and Delete

Actual this screen is a part of Project Management screen. On the other hand, to manage the update and delete operation we require a separate screen

When the project manager enters PID he will see the task defined for the project.

The following query displays these tasks. Select TID,TaskName,TaskType

From Task

Where TID IN (

Select TID

70

FROM ProjectTask

Where PID='"+TB_PID.Text+"')

In here we are retrieving the project tasks. After than with entering TID, the project manager can update or delete the task. For “Update” ing the task the following queries are executed; Update Task Set TaskType='" + TaskType + "', TaskName='" + TaskName +

"',DueDate='" + DueDate + "' WHERE TID='" + TB_TID.Text + "'

The updating the task will effect only the main task. On the other hand, the delete is working differently. If a main task deletes, we need to delete all related versions and their flows. The following delete sequence can do this job for us; Delete From ProjectTask

WHERE TID='" + search + "';

Delete From TaskFlow

Where VID IN(Select VID From Version Where TID='" + search + "');

Delete From Version

Where TID='" + search + "';Delete From Task Where TID='" + search

+ "'

If we update the last Task’s due date, the task will also ease from the screen of employee Tunc;

On the Tunc’s screen this information will also updated;

71

Also if we delete this task on project manager’s screen, this task also will drop on the Tunc’s screen

72

Summary

Exception Handling

In CEIS, because of the time constraints are our main goal (creating a real functional system) we could not concentrate on achieving error or exception handling in every page. However we are using Exception Handling under login screen. On our screen, if the user enters wrong user name or password, on the screen we giving an error message;

Also a similar mechanism work under beneath on the Project Manager Screen. When non-admin user tries to enter the system, CEIS displays the following message;

Under .Net Framework, espacially after version 2.0 we can publish these types of messages without writng a detailed code. But in here, the permissions are return values controlled via SQL queries.

Cascade on Delete and Update and Referential Integrity of CEIS

In CEIS, on the design phase, most of the tables are created according to satisfy the referential integrity and cascade operation on Delete and Update. In CEIS we can do it in two ways, one with direct approach and the other one with the indirect approach.

Direct Approach

73

When we consider cascade operations we need to define it when we are creating the tables. Also with the SQL 2008 property it let us to define cascade relation on the ER diagram (SQL 2008’s ER diagram)

Let’s consider the figure below. In this ER diagram when we click the edge between Task and ProjectTask the following properties displayed as follows;

Also between the ProjectTask and Project edge;

74

If we use the create SQL script property of the SQL Server 2008 management tool the Project Table’s SQL printed as follows; USE [CEIS] GO /****** Object: Table [dbo].[ProjectTask] Script Date: 01/06/2010 16:28:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProjectTask]( [PTID] [int] IDENTITY(1,1) NOT NULL, [PID] [int] NULL, [TID] [int] NULL, CONSTRAINT [PK_DesignProject] PRIMARY KEY CLUSTERED ( [PTID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ProjectTask] WITH CHECK ADD CONSTRAINT [FK_ProjectTask_Project] FOREIGN KEY([PID]) REFERENCES [dbo].[Project] ([PID]) GO ALTER TABLE [dbo].[ProjectTask] CHECK CONSTRAINT [FK_ProjectTask_Project] GO ALTER TABLE [dbo].[ProjectTask] WITH CHECK ADD CONSTRAINT [FK_ProjectTask_Task] FOREIGN KEY([TID]) REFERENCES [dbo].[Task] ([TID])

75

ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[ProjectTask] CHECK CONSTRAINT [FK_ProjectTask_Task] GO

The yellow highlighted part shows us the cascade relationship. In our code, to satisfy this property, we used multi-delete or multi update sequence together.

In-Direct Approach

In this approach for delete and update operations we run sequence of queries together. For insert operation we can give this example as follows; INSERT INTO Task (TaskType,Creator,CreationDate,TaskName,DueDate)

Values('" + TaskType + "','" + Role_Man + "','" +

DateTime.Now.ToShortDateString() + "','" + TaskName + "','" + dueDate+

"')

INSERT INTO ProjectTask (PID,TID)

Values('" + PID + "','" + TID + "')

For delete operation we can define as follows; Delete From ProjectTask

WHERE TID='" + search + "';

Delete From TASK

Where TID IN(Select TID From Version Where TID='" + search + "');

This approach also work for non-multiuser systems. On the other hand if there is a multi-user condition that forces us interleaving, this would cause unwanted results.

Screen Based Insert, Delete and Update Operations

We can summarize the screens and their insert, delete, and update operations as follows;

Table Name Insert-Screen(s) Delete-Screen Update-Screen

1 Employee Admin-Employee Management

Admin-Employee Management

Admin-Employee Management

2 GuestBook GuestBook Admin-Message and GuestBook

Admin-Message and GuestBook

3 Log_In Admin-User Accounts Admin-UserAccounts Admin-UserAccounts 4 Message Member-Message Admin-Message and Admin-Message and

76

GuestBook GuestBook

5 Page Admin-Web Page Management

Admin-Web Page Management

Admin-Web Page Management

6 PageRole Admin-Web Page Management

Admin-Web Page Management

Admin-Web Page Management

7 Project Admin-Project Management Admin-Project Management

Admin-Project Management

8 Project_Employee

Admin-Project Employee Management,Admin-Employee Role Management

Admin-Project Employee Management

9 ProjectTask Member-Project Manager Member-UD_Task

10 Role Admin-Employee Role Management

Admin-Employee Role Management

Admin-Employee Role Management

11 Task Member-Project Manager Member-UD_Task Member-UD_Task

12 TaskFlow Member-Task,Project Manager Member-UD_Task Member-Task

13 Version Member-Task,Project Manager Member-UD_Task Member-Task

As you can see, we did not use update operations for the Project_Employee and Project_Task. These are relational tables and they keep the information of Project, Employee and Project, Task. We cannot change their uniqueness with an update operation. On the other hand, we can create new records if we want to use same employee in another project or use same task in another project.

Views

View Name Screen

1 Project_Employee_View Admin-Project Employee Management

2 Project_Role_Employee_View Admin-Employee Role Management

Actually we define how we call these views on the Screens. However one more thing that we need to require for these views, the create statements of these views. For Project_Employee_View; CREATE VIEW [dbo].[Project_Employee_View]

AS

SELECT PE.PEID, PE.PID AS ProjectID, P.Name AS ProjectName, PE.EID

AS EmployeeID, E.Name AS EmployeeName, E.Surname AS EmployeeSurname

FROM dbo.Project AS P INNER JOIN

dbo.Project_Employee AS PE ON P.PID = PE.PID INNER

JOIN

dbo.Employee AS E ON PE.EID = E.EID

77

For the Project_Role_Employee_View CREATE VIEW [dbo].[Project_Role_Employee_View]

AS

SELECT P.PID AS ProjectID, R.EID, P.Name AS Project, E.Name AS N,

E.Surname AS S, R.RoleName AS Role

FROM dbo.Project AS P INNER JOIN

dbo.Role AS R ON P.PID = R.PID INNER JOIN

dbo.Employee AS E ON R.EID = E.EID

Some Example Queries

Also there are some selected queries (with some complex, nested queries) that you may interest;

• From Admin-Employee Role Management Screen;

Update Role Set RoleName='" + role + "'

WHERE RID=(

Select RID FROM Role

Where EID='"+search+"' AND PID='"+Project_ID.Text+"')

• From Admin-Web Page Management Screen;

Select PageRole.RID,PageRole.PageRoleID,PageURL

From Role,Page,PageRole

Where Role.RoleName='Project Manager' AND PageRole.RID=Role.RID

AND PageRole.PageID=Page.PageID

and

Select R.RID,E.Name,E.Surname

From Employee E,Role R

Where R.RoleName='Project Manager' AND R.EID=E.EID

• From Member-Project Manager Screen;

SELECT RID

FROM Role

78

WHERE RID=(

Select MAX(RID)

FROM ROLE

WHERE EID='" + ManID + "')

And

SELECT PageRole.PageRoleID

From Page, PageRole

WHERE PageRole.RID='" + Role_Man + "' And

Page.PageURL='~/Member/ProjectManagement

• From Member-Task

Select

V.TID,V.VID,R.RID,T.TaskName,T.TaskType,T.CreationDate,T.DueDate,V

.Name As Version

From Task T, Version V, TaskFlow TF, Role R,Employee E

Where R.EID='"+EID+"' And R.RID=TF.RID AND V.VID=TF.VID AND

T.TID=V.TID AND R.EID=E.EID

And

Select E.Name,E.Surname,R.RID From Employee E,Project P, Role R

Where R.PID=P.PID AND E.EID=R.EID AND P.PID IN(

Select PID From Role R Where R.EID='"+EID+"')

• From Member-Message

Select E.Name,E.Surname,M.Message_Title,M.Message

From Message M, Employee E

Where M.[To]='" + Request.QueryString["EID"] + "' AND E.EID=[From]

One More Query

Also let me include two other queries that is not used under our screens. For these queries we will use the following populated tables;

79

The Project Table

The Task Table

And the Project_Task Table

80

• Find the task type, that is the same for all the the projects;

Select T.TaskType

From Task T

Where not Exists(select *

From Project P

Where not Exists(Select *

From ProjectTask PT

Where PT.TID=T.TID

AND PT.PID=P.PID))

Result:Engineering