KMD Company Limited

Published:

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

KMD Computer Centre (Yangon, Myanmar)

Introduction

I am student of international Advance Diploma. I have to thanks many people who help me from the first study stage to implement stage of DDD Assignment. I got it from many people of helps and advices.

Firstly, I would like to say, I very thank all my teachers and my parents. My parents allowed to me to attend IADCS, if they do not allow, I cannot attend and I cannot meet such good teachers and cannot study such full of knowledge of IADCS subject. Without my teachers' help and suggestion, my assignment will not complete successfully. Moreover, I really thank to U Thaung Tin (Principle and Managing Director of KMD Company Limited) and Daw Tyn Tyn Aye (Director of KMD Computer Center). In addition, I want to thank to U Myo Tun (Training Manager), Daw Moe Sandar Aung, Daw KHIN THU AUNG and Daw Phyo Zar Chi Aung. In addition, I truly thanks to teachers who help me in the Computer Lab Room. I really thank above all people.

You have been sub-contracted to design the database system supporting the Academic Human Resources (AHR) department for the “We Are The Best - WATB” college. The initial analysis of the college's AHR department has shown the following”

* The AHR department is responsible only for the academic posts.

* The AHR department keeps information for every academic who has ever been employed:

Ø Personal information: full name, age, address, nationality, qualifications (along with date the qualification was awarded and awarding institution/body);

Ø Work related information: current post name and grade, starting date, previous posts, dates of every post held, and current salary. End of Contact date is also included if the person has left.

Ø Information regarding the various roles that an academic might hold in the college and information on the additional payment (on the top of the salary) that these roles attract.

* The rules of the AHR department state that an academic might hold only one post per time but they might occupy more than one rule; For example a Senior Lecture (post) might be holding the roles of Head of Department (role) and Research Leader (role).

* The posts that can be fulfilled by an academic are: Lecture, Senior Lecture, Principal Lecture, Reader, and Professor.

* The roles that can be fulfilled by an academic are Research Leader, Programme Director, Course Leader, Head of Department, Associate Head of Department, Quality Leader, Learning and Teaching Leader, Recruitment Officer, and Examinations Officer.

* The AHR department holds information on who occupies these roles in each department of the college.

* The college has 5 departments. Computer Science, Arts, Business, Physics and Mathematics.

* Each department has a maximum of one Head of Department and a maximum of two Associate Heads of Department. An academic cannot occupy the roles of Head of Department and Associate Head of Department at the same time.

* Only academics that hold senior posts can occupy the roles of Head of Department and Associate Head of Department. Senior posts are: Principal Lecture, Reader, and Professor. The rest of the roles can be occupied by any academic irrespective of the post they hold.

* Academics might hold the same role more than one time. For example, the current Head of the Mathematics department, was also Head of Department from 2001 - 2002.

Task 1

Draw an Entity Relationship Data Model which describes the content and structures of the data held by the Academic Human Resources Department.

Task 2

Produce the resulting tables clearly indicating the primary and foreign keys.

Task 3

Normalize these tables to ensure that are all in BCNF (3NF).

Task 4

Using a Database Management System (DBMS) of your choice, set-up all the above normalized tables, and populate them with well-designed test data (minimum 5 records per table). Provide printouts of all tables.

Task 5

Set-up and test all of the following queries using Structured Query Language (SQL). Please provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed:

* Display the name of all the heads of the departments together with information on any other roles they might occupy and the current post they hold.

* Display the name of all the current Senior Lecturers along with the name of the department that they belong to and their final salary (the final salary should include the salary paid for the post plus any additional payments that an employee might receive for roles they might occupy).

* Display all the heads of the Mathematics department from 2000 to date. The result should return their name, current post and qualifications.

Task - 1

Academic Human Resources (Entity Relationship Data Model)

The Entity Relational data model include Role, post, Department, Qualification and Academic. One of Academic has many Departments, many posts, many Qualifications and many roles. One of Department has many Academics and many Roles. One of Role has many Academics and many Departments. But, One of Post has much Qualification. The AHR department is responsible only for academic posts. The rules of the AHR department state that an academic may hold only one post at any time but they may occupy more than one role. The AHR department holds information on Work related information. Each department has a maximum of one role and two roles. The rest of the roles can be occupied by any academic irrespective of the post they hold. Academic may hold the same role on more than once occasion.AHR department associated information hold Work related information Entity and Academic Entity .The work related information store post detail. Post entity includes current post no and post name. The work related information entity includes Department No, Department Name, Academic No, Academic Name, Post No, Post Name, Grade, current salary and additional payment. So, the Entity relationship data model mainly Entity is Work related information Entity.

Entity

Academic

Qualification

Role

Post

Work related information

Academic ID

Qualification No

Role No

Post No

Work related information No

Academic Name

Qualification Name

Role Name

Post Name

Academic ID

Age

Post No

NRC No

Department No

Address

Current Post Name

Nationality

Grade

Phone

Starting Date

pervious posts

Current salary

Additional salary

Task - 2

Table NAME - Academic

Attributes

Data Type

Size

Comments

Academic ID

Varchar

50

Store Academic ID

Academic Name

Varchar

50

Store name

NRC No

Varchar

50

Store NRC No

Age

Varchar

50

Store Age

Nationality

Varchar

50

Store nationality

Address

Varchar

50

Store Address

Phone

Varchar

50

Store phone

Primary Key = Academic ID

Foreign keys-empty

Academic/Role Detail

Attributes

Data Type

Size

Comments

No

Varchar

(50)

-

Academic ID

Varchar

(50)

Putout Academic table

Role No

Varchar

50

Put out RoleNo

Composite Primary keys- Academic ID, Role No

Table NAME - Department

Attributes

Data Type

Size

Comments

Department No

Varchar

50

Store Department No

Department Name

varchar

50

Store Department name

Primary key= Department No

Foreign key -empty

TableName-Department/Role Table

Attributes

Data Type

Size

Comments

No

Varchar

50

-

Department No

Varchar

50

Put out Department table

Role No

Varchar

50

Put out role table

Composite primary keys-Department No and Role No

Table NAME - Role form

Attributes

Data Type

Size

Comments

Role No

varchar

50

store Role No

Role Name

varchar

50

store Role Name

Primary key =Role no

Foreign keys -empty

Attributes

Data Type

Size

Comments

Post No

Varchar

50

Store Post No

Post Name

Varchar

50

Store Post Name

Primary key=Post No

Foreign key =empty

Table NAME - Qualification form

Attributes

Data Type

Size

Comments

Qualification No

Varchar

10

Store Qualification No

Qualification Name

Varchar

50

store Name

Primary key = Qualification No

Foreign key -empty

Table Name -Academic Qualification

Attributes

Data Type

Size

Comments

No

Varchar

50

-

Qualification No

Varchar

50

Put out Qualification No

Academic ID

Varchar

50

Put out Academic ID

Table Name- Role

Attributes

Data Type

Size

Comments

Role No

varchar

10

Store Role No

Role Name

Varchar

50

Store Name

Primary key - Role No

Foreign key - empty

Table Name- Work related information

Attributes

Date Type

Size

Comments

Work Related Information No

Varchar

50

StoreWork Related Information No

Academic ID

Varchar

50

Store Academic ID

Post No

Varchar

50

Store Post No

Department No

Varchar

50

Store Department No

Current Post Name

Varchar

50

Store Current Post Name

Grade

Varchar

50

Store Grade

Starting Date

Varchar

50

Store Starting Date

Pervious posts

Varchar

50

Store Pervious posts

Current salary

Int

-

Store Current salary

Additional salary

int

-

Store Additional salary

Primary key-work related information ID

Foreign key-empty

Task - 3

Academic Human Resource (AHR)

Academic Form

Academic ID : --------------------------

Academic Name : --------------------------

Personal

Age : --------------------------

Phone : --------------------------

NRC No : --------------------------

Nationality : --------------------------

Address : ----------------------------------------------------------------------

----------------------------------------------------------------------

No

Qualification No

Qualification Name

Gather Data

Academic ID

Academic Name

Age

Phone

NRC No

Nationality

Address

No

Qualification Id

Qualification Name

Primary Keys-Academic ID

1NF

Academic ID

Academic ID

Academic Name

Age

Phone

NRC No

Nationality

Address

Academic ID

No

Qualification ID

Qualification Name

2NF

Academic ID

Academic ID

Academic Name

Age

Phone

NRC No

Nationality

Address

Academic ID

Qualification ID

Data Model

Academic Human Resource (AHR)

Work Related Information Form

Work related information No : ----------------------------------

Post No : ----------------------------------

Post Name : ----------------------------------

Start Current Date : ----------------------------------

Previous Post Name : ----------------------------------

Current Salary : ---------------------------------

Grade : -----------------------------------

Additional payment : ----------------------------------

Academic ID : ---------------------------------

Academic Name : ---------------------------------

Department No : --------------------------------

Department Name : ------------------------------------

Gather Data

Work related information No

Post Name

Post No

Start Current Post

Previous Post Name

Current Salary

Academic Name

Post Name

Start current Post

Previous post name

Current Salary

Grade

Additional payment

Department No

Department Name

Academic ID

Academic Name

2NF

Work related information No

Start current Post

Previous post name

Current Salary

Grade

Additional payment

Post No

Post Name

Department No

Department Name

Academic ID

Academic Name

3NF

Work related information No

Start current Post

Previous post name

Current Salary

Grade

Additional payment

Academic Human Resource (AHR)

Role Form

Role No : -------------------------------

Role Name : -------------------------------

No

Department No

Department Name

Academic ID

Academic name

Gather Data

Role No

Role Name

No

Department No

Department Name

Academic ID

Academic Name

Primary key- Role No

1NF

Role No

Role Name

Role No

No

Department No

Department Name

Academic ID

Academic Name

Role No

Department No

Role No

Academic ID

Department No

Department Name

Academic ID

Academic Name

Normalization

Role

Role No

Role Name

Role Department Detail

Role No

Department No

Role Academic Detail

Role No

Academic ID

Department

Department No

Department Name

Academic

Academic ID

Academic Name

Data Model

Task - 4

Academic Table

Academic/Role Table

Academic /Qualification Detail Table

Department Table

Department/Role

Post

Qualification

Qualification/Academic Table

Role

Work related information Table

Academic

Academic/Role

Department

Department/Role

Post

Qualification

Qualification/Academic

Role

Work related information

Relationship diagram

Task - 5

(a) Display the name of all Heads of department together with other roles they occupy and the current post they hold

SQL Statement: SELECT a.AcademicID, a.AcademicName, R.RoleNo, R.RoleName, W.CurrentPostName

FROM Academic AS a, [Role] AS R, WorkRelatedInformation AS W, AcademicRole AS ED

WHERE ED.AcademicID=a.AcademicID And ED.RoleNo=R.RoleNo And a.AcademicID=W.AcademicID And R.RoleName='Head Of Department'

(b)Display the name of all the current Senior lecturers along with the name of the department they belong to and their full salary (the full salary should include the salary paid for the post plus any additional payments that an employee might receive for roles they occupy).

SQL Statement:SELECT a.AcademicName,P.PostName,D.DepartmentName, W.CurrentSalary, W.AdditionalSalary, (W.CurrentSalary + W.AdditionalSalary) AS [Total Salary]

FROM Academic AS a, Post AS P, Department AS D, WorkRelatedInformation AS W

WHERE W.AcademicID=a.AcademicID And W.PostNo=P.PostNo And W.DepartmentNo=D.DepartmentNo and W.CurrentPostName='Senoir Lecturer'

(b) Display all the heads of the mathematics department from 2000 to the present date .The result should return their name ,current post and qualification

SQL statement:SELECT a.AcademicID, a.AcademicName, W.CurrentPostName, QD.QualificationNo, Q.QualificationName, D.DepartmentNo, D.DepartmentName, W.perviousposts, W.StartingDate ,R.RoleName

FROM Academic AS a, WorkRelatedInformation AS W, QualificationAcademic AS QD, Qualification AS Q, Department AS D,AcademicRole as ard,[Role] as r

WHERE a.AcademicID=W.AcademicID

And QD.AcademicID=W.AcademicID

And Q.QualificationNo=QD.QualificationNo

And D.DepartmentNo=W.DepartmentNo

and D.DepartmentName='Mathematics'

and a.AcademicID=ard.AcademicID

and r.RoleName='Head of Department'

And W.StartingDate>='#1.1.2000#'

Task - 6

This assignment has objective is to demonstrate how much understanding of data modeling, created one of data model ,analysis techniques to the design and development of database solutions. Data modeling means defining and analyzing the data requirements which needs to provide the business process of an organization. The assignment will build the data base system for design support the one of administrator.

Task-1

The Entity Relational data model include Role, post, Department, Qualification and Academic. One of Academic has many Departments, many posts, many Qualifications and many roles. One of Department has many Academics and many Roles. One of Role has many Academics and many Departments. But, One of Post has much Qualification. The AHR department is responsible only for academic posts. The rules of the AHR department state that an academic may hold only one post at any time but they may occupy more than one role. The AHR department holds information on Work related information. Each department has a maximum of one role and two roles. The rest of the roles can be occupied by any academic irrespective of the post they hold. Academic may hold the same role on more than once occasion.AHR department associated information hold Work related information Entity and Academic Entity .The work related information store post detail. Post entity includes current post no and post name. The work related information entity include Department No, Department Name, Academic No, Academic Name, Post No, Post Name ,Grade ,current salary and additional payment. So, the Entity relationship data model mainly Entity is Work related information Entity.

. The Data Model build needed Entity Attributes and Relationship.

* Entity- Entity means the “item” about which we seek information.

* Attributes - Unlike entity it means the data we collect about entities.

* Relationship-that provide the structure needed to draw information from multiple entities.

The Academic human Department include all of field is Entity. They are Academic, Department, Role, Qualification and Post. One of entity has relation data show attributes. The detail relationship is described in entity relationship diagram. But the data (attributes) will not include in entity relationship diagram.

Task-2

The task produces the resulting tables clearly indicating the primary and foreign keys. The resulting table include primary keys is a candidate key to uniquely identify each row in a table and foreign keys is other data table primary keys. A unique key or primary key comprises a single column or set of columns. So, Creating the data table important primary key and foreign keys .Sometimes they needed composite key. One data can not represent the whole data at that one or more data have to combine to represent the whole group of data and it is called composite key. They have code standard for primary keys and foreign keys.

Code Standard

Academic

Name: Academic ID

Code: AID

File type and size: Number (10)

Coding Type: Significant

Prefix

Suffix

Value Width

Generate Format

Restart Type

A

None

7

XXXXXXX

-

Qualification

Name: Qualification No

Code: QNo

File type and size: Number (10)

Coding Type: Significant

Prefix

Suffix

Value Width

Generate Format

Restart Type

Q

None

7

XXXXXXX

-

Role

Name: Role No

Code: RNo

File type and size: Number (10)

Coding Type: Significant

Prefix

Suffix

Value Width

Generate Format

Restart Type

R

None

7

XXXXXXXXX

-

Post

Name: Post No

Code: PNo

File type and size: Number (10)

Coding Type: Significant

Prefix

Suffix

Value Width

Generate Format

Restart Type

P

None

7

XXXXXXXXX

-

Department

Name: Department No

Code: DNo

File type and size: Number (10)

Coding Type: Significant

Prefix

Suffix

Value Width

Generate Format

Restart Type

D

None

7

XXXXXXXXX

-

Work related information

Name: Work related information No

Code: WRI

File type and size: Number (10)

Coding Type: Significant

Prefix

Suffix

Value Width

Generate Format

Restart Type

WRI

None

7

XXXXXXXXX

-

Task-3

Firstly Analysis Task-1 in order to drawing the form Design for task-3.Task-3 is Normalization for this form. Normalization has seven stages. They are

1. Gather Data (to collect all of data from the form design)

2. Primary key(Select the primary key from the gather stages)

3. 1 normal Form (to collect single and repetitive form the gather stages)

4. 2 normal Form (to analysis repetitive group from the gather stages)

5. 3 normal form(to analysis single group form the gather stages)

6. Normal form (to build the group form the 3 normal form stages)

7. Data Model(to draw relation data design)

Task-4

This stage build data base by using the data table. The data base construct firstly consider the data base software. They are SQL server, mySQL, Oracle, Microsoft access and etc. Among then for this task we chose SQL database because it is open source and easy to use. After constructing table in database make relation between primary key and foreign from table to table in database. And then fill suitable test data as the assignment instruction. I used this data base software form my project. So, this data base familiar to me.

Task-5

In task 5, the assignment lets us to write the three SQL statements showing the names of all head of department together with information on any other roles they might occupy and the current post they hold, display the name of all the current Senior Lecturers along with the name of the department that they belong to and their final salary (the final salary should include the salary paid for the post plus any additional payments that an employee might receive for roles they might occupy) and finally Display all the heads of the Mathematics department from 2000 to date. The result should return their name, current post and qualifications. This stage is set-up and tests the following queries using Structured Query Language (SQL).The language use control data base because this language is a specialized programming language for sending queries to databases .The S.QL code for each query and produced when you run the query in the database you have developed:

(a) Display the name of all the Heads of department together with other roles they occupy and the current post they hold

(b) Display the name of all the current senior lecturers along with the name of the department they belong to and their full salary.

(c) Display all the Head of the Mathematics department from 2000 to the present data .The result should return their name, current post and qualifications.

By using SQL statement the instruction in database can update and delete and also insert data to database. When retrieve data from database select command from SQL language is used.

Task 6

This stage is writing the report about how to consider each task and do each task with which view. All of task analyzing, designing implementing assumes show this stage. Therefore, we could have taken alternative approaches to any of the above tasks. This stages include discuss any changes and improve above the task.

Reference

Book References

Database Design and Development
1-403-91601-2
NCC Education Limited
1/6/2010

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.