Database Design Development

Database Development and Design

Task 1

Draw the Entity Relationship Diagram

Relationship for all the entity

For the Department and Employee relationship. Each department's have many employees. Each employee must have one department. The Department can store the department information and the employee entities store the employee work in which department.

For the employee entities and work detail entities relationships. Each department's have many employees. An employee have many work detail in these table. Each work detail must have one employee.

For the Post_assign and Employee relationship. Each employee has many post_assign. An employee has many post assign. Each post assign must have one employee.

For the Post and post_assign relationship. Each post's have many post_assign. Each post assign must have one post. Because that table to assign the employee to assign these

For the Employee and role_assign relationship. Each employee's have many role_assign. A employee have many role assign. Each role assign must have one employee.

A role have many role assign. Each role assign must have one role.

Post and employee has many to many relationships. So this relationship to decompose , these relationship to decompose appear the post assign table into our database.

Employee and role has many to many relationships. So this relationship to decompose , these relationship to decompose appear the role assign table into our database

Task 2

Department

Dept No (Primary Key)

Dept Name

Description

Location

Contact No

E-mail

Post

Post No (Primary Key)

Post Name

Grade

Salary

Post assign

Emp No (Primary Key)

Post No (Primary Key)

Starting date

Employee

Emp No (Primary Key)

Full Name

Age

Address

Nationality

Qualification

Dept No (Foreign Key)

Role

Role No (Primary Key)

Role Name

Role payment

Role assign

Emp No (Primary Key)

Role No (Primary Key)

Starting date

Work detail

Dept No (Primary Key)

Emp No (Primary Key)

Current post

Grade

Starting date

Previous post

Current role

Current salary

Role payment

Dept No

Dept Name

Location

Contact No

E-mail

Description

Current role

Previous post

Post Name

Grade

Salary

Role Name

Additional role payment

Emp No

Full name

Age

Address

Nationality

Qualification

Current post

Starting date

Current salary

Post No

Role No

TASK-3

NORMALIZATION

Un-normalization form

First Normal form

Dept No (PK)

Dept Name

Description

Location

Contact No

E-mail

Emp No

Full Name

Age

Nationality

Address

Qualification

Dept No (PK)

Emp No

Current post, current role

Grade

Starting date

Previous post

Current salary

Post name

Grade

Salary

Role name

Role payment

Second Normal Form

Dept No (PK)

Dept Name

Description

Location

Contact No

E-mail

Emp No

Full Name

Age

Nationality

Address

Qualification

Emp No (PK)

Post No (PK)

Starting date

Emp No (PK)

Role No (PK)

Starting date

Dept No (PK)

Emp No (PK)

Current post

Grade

Starting date

Previous post

Current role

Current salary

Role payment

Post No (PK)

Post Name

Grade

Salary

Role No (PK)

Role Name

Role payment

Third Normal Form

Department

Dept No (PK)

Dept Name

Description

Location

Contact No

E-mail

Post assign

Emp No (PK)

Post No (PK)

Starting date

Post

Post No (PK)

Post Name

Grade

Salary

Role assign

Emp No (PK)

Role No (PK)

Starting date

Employee

Emp No (PK)

Full Name

Age

Address

Nationality

Qualification

Dept No (FK)

Role

Role No (PK)

Role Name

Role payment

Work detail

Dept No (PK)

Emp No (PK)

Current post

Grade

Starting date

Previous post

Current role

Current salary

Role payment

TASK-5

SELECT EMPLOYEE.FullName,[ROLE].RoleName FROM EMPLOYEE,[ROLE],[ROLE-ASSIGN]

WHERE EMPLOYEE.EmpNo=[ROLE-ASSIGN].EmpNo

AND [ROLE].RoleNo=[ROLE-ASSIGN].RoleNo

AND RoleName='Head of Department'

SELECT dbo.Employee.FullName, dbo.Post.Salary

FROM dbo.[Post-assign] INNER JOIN

dbo.Employee ON dbo.[Post-assign].EmpNo = dbo.Employee.EmpNo INNER JOIN

dbo.[Role-assign] ON dbo.[Post-assign].EmpNo = dbo.[Role-assign].EmpNo

INNER JOIN dbo.Role ON dbo.[Role-assign].RoleNo = dbo.Role.RoleNo INNER JOIN

dbo.Post ON dbo.[Post-assign].PostNo = dbo.Post.PNO

WHERE (dbo.Post.PName = 'Senior Lecturer')

SELECT dbo.Employee.FullName, dbo.[Work-detail].[Current-post], dbo.Employee.Qualification, dbo.[Post-assign].[Starting-date]

FROM dbo.[Post-assign] INNER JOIN

dbo.Employee ON dbo.[Post-assign].EmpNo = dbo.Employee.EmpNo INNER JOIN

dbo.Post ON dbo.[Post-assign].PostNo = dbo.Post.PNO INNER JOIN

dbo.[Work-detail] ON dbo.[Post-assign].EmpNo = dbo.[Work-detail].EmpNo INNER JOIN

dbo.Department ON dbo.[Work-detail].DeptNo = dbo.Department.DeptNo

WHERE (dbo.Department.DeptName = 'Mathematics') AND (dbo.[Post-assign].[Starting-date] >= CONVERT(DATETIME, '2000-01-01 00:00:00', 102)) AND

(dbo.[Post-assign].[Starting-date] <= CONVERT(DATETIME, '2010-01-06 00:00:00', 102))

Task 6

Report

I have designed the database system supporting the Knowledge Transfer (KT) department for the We Are The Best (WATB) College. The KT department is responsible for managing consultancy projects for the academics of the WATB College. Drawing the entity relationship,

There are eleven entities (Tables) in this system. They are

1. Employee

2. Post

3. Department

4. Role

5. Post-Assign

6. Role-Assign

7. WorkDetail

In Task 1, we have to draw the Entity Relationship Data Model. ERD is the top-down approach to data analysis. E-R data model has three basic constructs. They are entities, relationships and attributes. Entity is a thing about which an organization holds information. Relationship is some association between entities. Attributes are the values in the entity.

In Task2, we have to define the primary and foreign keys from the resulting tables. Primary key is an attribute which uniquely identifies a single row within the relation. A foreign key is an attribute which is the primary key of one relation but appears as an attribute in another relation.

In Task3, we have to normalize these data to ensure that are all in Binary Coded Normal Form (BCNF). In this task, we have to do unnormalized form first normal form, second normal form and third normal form. Normalization is the process of producing a schema not subject to file maintenance anomalies. Normalization is the bottom-up approach. Unnormalized form is the list of attributes. Converting to first normal form is to remove the repeating group of data. A relation is in second normal form if and only if it is in first normal form and every non-key attribute is fully functionally dependent on the primary key. A relation is in third normal form of and only if it is in second normal form and every non-key attribute is non-transitively dependent on the primary key.

In Task4, we create the database management system and fill in the test data to clarify the inserted data is valid or not.

In Task5, we develop the SQL query code to acquire the relevant data what the system demands. After SQL codes have written and the output are examined, we produce the printouts of database tables in our system.

 

Please be aware that the free essay that you were just reading was not written by us. This essay, and all of the others available to view on the website, were provided to us by students in exchange for services that we offer. This relationship helps our students to get an even better deal while also contributing to the biggest free essay resource in the UK!