Database Design And Development Assignment 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.

This assignment is about database design and development. In thisassignment, we demonstrate an understanding of applying data modeling and dataanalysis techniques to the design and development if database solutions. Thisassignment has six tasks.

In task 1, we produce an Entity Relationship Diagram and describe theentities and their attributes of the data held by the Research Department.

In task 2, we produce the resulting tables that include the indicating ofthe primary and foreign keys.

In task 3, we draw the form design for the Research department and normalizethe tables to ensure that they are all in BCNF. And we describe the steps wehave taken in normalizing the tables.

In task 4, we use the Database Management System (DBMS) to set-up all of thenormalized tables that we did in task 3. We describe the table design view,data sheet view and the relationship diagram.

In task 5, we set-up and test the queries using Structured Query Language(SQL) and provide the SQL code for each query. We need to write the SQL codesare:

  • Displaythe name of all of the employees (both internal and external) currently workingon at least one project for the Research Department along with the projectsthey work on, and their roles in each project.
  • Displayall the projects that have received funding from a funding agency. The resultshould display the name of the project, the names of employees involved, thename of the funding agency, the funding contribution from the funding agency,the name of the project manager, the project total cost, and project end date.

In task 6, combine the output from task 1 to 5; we describe the reference ifneeded.

Acknowledgement

I have to thanks many people who help me from the first study stage toimplement stage of DDD Assignment. I got it from many people of helps andadvices.

Firstly, I would like to say, I very thank all my teachers and my parents. Myparents allowed to me to attend IADCS, if they do not allow, I cannot attendand I cannot meet such good teachers and cannot study such full of knowledge ofIADCS subject. Without my teachers' help and suggestion, my assignment will notcomplete successfully. Moreover, I really thank to U Thaung Tin (Principle andManaging Director of KMD Company Limited) and Daw Tyn Tyn Aye (Director of KMDComputer 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 thankabove all people.

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

Table of Contents

NoDescription

1Cover Page

2Confirmation andStatement

3IntroductionAcknowledgement

4Table ofcontents

5 Task1

5-1Entity RelationshipDiagram

5-2Entity and theirattribute

Analysis andAssumption

6 Task2

6-1Result Tables with Primary Key and Foreign Key

Analysis andAssumption

7 Task3

7-1FormDesign

7-2Normalization

Analysis andAssumption

8 Task4

8-1Table Design View & Data Sheet View

8-2Relationshipdiagram

Analysis andAssumption

9 Task5

Task5

10 Task6

Assumption

Reference

Task - 1

Entity Relationship Diagram

Entity and their attributes

The relationship for internal employee and internal employee detail

Their relationship is one to many relationships because one internalemployee has many internal employees detail.

The relationship for project and internal employee detail

Their relationship is one to many relationships because a project has manyinternal employees detail.

The relationship for project and department

Their relationship is one to many relationships because each department hasmany projects.

The relationship for project and external employee detail

Their relationship is one to many relationships because a project has manyexternal employee details.

The relationship for role and role detail

Their relationship is one to many relationships because one externalemployee has many external employee details.

The relationship for the project and agency

Their relationship is one to many relationships because each agency has manyprojects.

Analysis

We create the Entity Relationship Diagram that include with theirrelationships. The relationship are one to one relationship, one to manyrelationship and many to many relationship.

Assumption

An entity may be defined as a thing which an organization recognizes asbeing capable of an independent existence and which can be uniquely identifiedand it may be a physical object. And the entity is characterized by a number ofproperties or attributes.

The project entity includes the attributes of Project ID, Full Name,Duration, Start Date, End Date, Roles, Employee Times, Total Cost, Outcome,Internal Employee No, External Employee No, Agency and Department No.

The department entity includes the attributes of Department No andDepartment Name.

The internal employee entity includes the attributes of Internal EmployeeNo, Internal Employee Name, Age, Address, Nationality, Qualifications andResearch Team.

The Internal employee detail entity includes the attributes of Project ID,Internal Employee No, Post Name, Grade, Starting Date and Salary.

The external employee entity includes the attributes of External EmployeeNo, External Employee Name and address.

The external employee detail entity includes the attributes of Project ID,External Employee No, Company they work, how many hours and cost.

The agency entity includes the attributes of Fund Agency No, Agency Name,Contact Person and Contact Telephone.

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

Task - 2

Result Tables with Primary Key and Foreign Key

Project Table

Field Name

Field Type

Field Size

Key

Project ID

Full Name

Duration

Start Date

End Date

Roles

Employee times

Total Cost

Outcome

Internal Employee No

External Employee No

Agency No

Department No

Number

Test

Number

Date

Date

Test

Number

Number

Number

Number

Number

Number

Number

Long Integer

50

Integer

10

10

50

Integer

Integer

Integer

Long Integer

Long Integer

Long Integer

Long Integer

Primary key

-

-

-

-

-

-

-

-

Foreign key

Foreign key

Foreign key

Foreign key

Internal Employee Table

Field Name

Field Type

Field Size

Key

Internal Employee No

Internal Employee Name

Age

Address

Nationality

Qualification

Research Team

Number

Test

Number

Test

Test

Test

Test

Long Integer

50

Long Integer

50

50

50

50

Primary key

-

-

-

-

-

-

External Employee Table

Field Name

Field Type

Field Size

Key

External Employee No

External Employee Name

Address

Number

Test

Test

Long Integer

50

50

Primary key

-

-

Department Table

Field Name

Field Type

Field Size

Key

Department No

Department Name

Number

Test

Long Integer

50

Primary key

-

Internal Employee Detail Table

Field Name

Field Type

Field Size

Key

Project ID

Internal Employee No

Post Name

Grade

Starting Date

Salary

Number

Number

Test

Date

Number

Long Integer

Long Integer

50

10

Long Integer

Primary key

Composite primary key

-

-

-

-

External Employee Detail Table

Field Name

Field Type

Field Size

Key

Project ID

External Employee No

Company they work for

How many hours

Cost

Number

Number

Test

Number

Test

Long Integer

Long Integer

50

Long Internal

50

Primary key

Composite primary key

-

-

-

-

Agency Table

Field Name

Field Type

Field Size

Key

Agency No

Agency Name

Address

Contact Person

Contact Telephone

Number

Test

Test

Test

Number

Long Integer

50

50

50

Long Integer

Primary key

-

-

-

-

Analysis

We produce the primary key and foreign key for the tables.

Assumption

The primary key identifies one or more columns of a table whose values areused to uniquely each of the rows in a table. Primary key is chosen from one ofthe candidate keys that have been selected as the identifier for an entitytype. The primary has two properties: must be not null and must be unique. Forexample, Project ID is the primary key because a Project has one ID and theirID can not be the same. The foreign key is a column or group of columns of sometable and the means of interconnecting the data stored in a series of disparatetables. The foreign key draws its value from the same domain as the primary keyof some related table in the database. They are attributes used to crossreference tuples using the tuples' primary key values. The example for theforeign key is Internal Employee No. Internal Employee No is the primary key inone table and the foreign key in other table.

Task - 3

Form Design

Project Registration Form

Project Registration Form

Project ID:……………InternalEmployeeNo :……………

Full Name:……………InternalEmployee Name: ……………

Duration:……………ExternalEmployee No : ……………

Start Date :……………ExternalEmployee Name : ……………

End Date: …………… Agency No : ……………

Roles :…………… Agency Name : ……………

Employee times: ……………DepartmentNo: ……………

Total Cost : …………… DepartmentName: ……………

Outcome: ……………

Internal Employee Detail Form

Internal Employee Detail Form

Project ID :……………

Full Name : ……………

Internal Employee No

Internal Employee Name

Post Name

Grade

Starting Date

Salary

External Employee Detail Form

External Employee No

External Employee Name

Company they work for

How many hours

cost

External Employee Detail Form

Project ID: ……………

Full Name: ……………

Normalization

Project Registration Form

Academic Registration Form

Project ID:…………… Internal EmployeeNo : ……………

Full Name :…………… Internal EmployeeName: ……………

Duration: …………… External EmployeeNo : ……………

Start Date :…………… External EmployeeName : ……………

End Date :……………AgencyNo : ……………

Roles: ……………AgencyName : ……………

Employee Times:…………… DepartmentNo: ……………

Total cost :……………DepartmentName: ……………

Outcome: ……………

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

1. Gather data

Project ID

Full Name

Duration

Start Date

End Date

Roles

Employee Times

Total Cost

Outcome

Internal Employee No

Internal Employee Name

External Employee No

External Employee Name

Agency No

Agency Name

Department No

DepartmentName

2. Choose a key

Project ID

3. 1NF

Academic ID

Full Name

Duration

Start date

End Date

Roles

Employee Time

Total Cost

Outcome

Internal Employee No

Internal Employee Name

External Employee No

External Employee Name

Agency No

Agency Name

Department No

Department Name

4. 2NF

Project ID

Full Name

Duration

Start Date

End Date

Roles

Employee Time

Total Cost

Outcome

Internal Employee No

Internal Employee Name

External Employee No

External Employee Name

Agency No

Agency Name

Department No

Department Name

5. 3NF

Project ID

Full Name

Duration

Start Date

End Date

Roles

Employee Times

Total Cost

Outcome

  • Internal Employee No
  • External Employee No
  • Agency No
  • Department No

Internal Employee No

Internal Employee Name

External Employee No

External Employee Name

Agency No

Agency Name

Department No

Department Name

6. Optimization

[Project]

Project ID

Full Name

Duration

Start Date

End Date

Roles

Employee Times

  • Internal Employee No
  • External Employee No
  • Agency No
  • Department No

[Internal Employee]

Internal Employee No

Internal Employee Name

[External Employee]

External Employee No

External Employee Name

[Agency]

Agency No

Agency Name

[Department]

Department No

Department Name

7. Data model

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

Internal Employee Detail Form

Post Detail Form

Project ID :……………

Full Name : ……………

Internal Employee No

Internal Employee Name

Post Name

Grade

Starting Date

Salary

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

1. Gather data

Project ID

Full Name

Internal Employee No

Internal Employee Name

Post Name

Starting Date

Salary

2. Choose a key

Project ID

3. 1NF

Project ID Project ID

FullName InternalEmployee No

Internal EmployName

PostName

Starting Date

Salary

4. 2NF

Project ID Project ID

FullName InternalEmployeeNo

Post Name

Starting Date

InternalEmployee No

Internal Employee Name

Salary

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

5. 3NF

Project ID Project ID

FullName InternalEmployeeNo

Post Name

Starting Date

InternalEmployee No

Internal Employee Name

Salary

6. Optimization

[Project][Internal Employee Detail]

Project ID Project ID

FullName InternalEmployeeNo

Internal Employee Name

Starting Date

7. Data model

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

[Internal Employee]

InternalEmployee No

Salary

Database Design & Development AssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

External Employee Detail Form

External Employee No

External Employee Name

Company they work for

How many hours

cost

External Employee Detail Form

Project ID: ……………

Full Name: ……………

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

1. Gather data

Project ID

Full Name

External Employee No

External Employee Name

Company they work for

How many hours

Cost

2. Choose a key

Project ID

3. 1NF

Project ID Project ID

FullName External Employee No

External Employee Name

Company they work for

How many hours

Cost

4. 2NF

Project ID Project ID

FullName External Employee No

Company they work for

How many hours

ExternalEmployee No

External Employee Name

Cost

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

5. 3NF

Project ID Project ID

FullName External EmployeeNo

Company they work for

How many hours

External Employee No

External Employee Name

Cost

6. Optimization

[Project][External Employee Detail]

Project ID Project ID

FullName External EmployeeNo

Company they work for

How many hours

7. Data model

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

[External Employee]

ExternalEmployee No

External Employee Name

Cost

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

Analysis

Normalize the tables to ensure that they are all in BCNF.

Assumption

Normalization is the process of identifying the logical associations betweendata items and designing a database which will present such associations butwithout suffering from the file maintenance anomalies. It is the process of nonloss decomposition. It is a bottom up technique for database designparticularly suited to relational database design. It eliminates the repeatinggroups and non functional dependencies to produce the logical tables which canbe mapped to relations. Normalization has seven steps. They are gather data,choose a key, first normal form (1NF), second normal form (2NF), third normalform (3NF), optimization and data model.

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

Database Design & DevelopmentAssignmentPage-37

KMD Computer Centre(Yangon, Myanmar)

Task - 4

Table Design View and Data Sheet View

Project Table

Project ID

Full Name

Duration

Start Date

End Date

Roles

Employee time

Total Cost

Outcome

Internal Employee No

External Employee No

Agency No

Department No

1

2

3

4

5

A

B

C

D

E

30hours

40hours

40hours

35hours

40hours

1/2/05

2/4/03

8/6/07

6/9/04

7/7/01

1/3/05

2/5/03

8/7/07

6/10/04

8/8/01

Manager

Manager

Manager

Manager

Manager

5hours

6hours

5hours

6hours

5hours

5000

6000

4500

4000

5500

6000

7000

8000

5000

6000

1

2

5

8

4

8

2

3

4

7

7

6

5

1

9

9

6

3

7

4

Internal Employee Table

Internal Employee No

Internal Employee Name

Age

Address

Nationality

Qulification

Research Team

1

2

3

4

5

Mg Mg

Aye Aye

Hla Hla

Tun Tun

Mya Mya

20

23

25

21

22

Yangon

Yangon

Yangon

Yangon

Yangon

Myanmar

Myanmar

Myanmar

Myanmar

Myanmar

BCSc (Computing)

BCSc (Computing)

BCSc (Computing)

BCSc (Computing)

BCSc (Computing)

3

4

6

8

9

External Employee Table

External Employee No

External Employee Name

Address

1

2

3

4

5

Hla Hla

Aye Aye

Mg Mg

Mya Mya

Kyaw Kyaw

Myanmar

Myanmar

Myanmar

Myanmar

Myanmar

Department Table

Department No

Department Name

1

2

3

4

5

Computer Science

Arts

Business

Physics

Mathematics

Internal Employee Detail Table

Project ID

Internal Employee No

Post Name

Grade

Starting Date

Salary

1

2

3

4

5

1

5

3

2

4

Senior Lecturer

Lecturer

Senior Lecturer

Lecturer

Senior Lecturer

A

B

A

B

A

1/2/2010

2/5/2009

5/7/2009

4/1/2010

12/10/2009

10000

8000

9000

7000

9000

External Employee Detail Table

Project ID

External Employee No

Company they work

How many hours

Cost

1

2

3

4

5

1

2

3

4

5

ABE

Sony

Sonic

Cherry

BCE

5 hours

6hours

6hours

6hours

6hours

200

300

200

200

150

Agency Table

Agency No

Agency Name

Address

Contact Person

Contact Telephone

1

2

3

4

5

BB

CC

DD

EE

FF

Myanmar

Myanmar

Myanmar

Myanmar

Myanmar

Mg Mg

Kyaw Kyaw

Hla Hla

Aye Aye

U Mg

223158

745551

756456

124857

587496

Analysis

Create the tables using Database Management System software and fill therecords in the table and make relationship diagram.

Assumption

The relational database is made up of tables with a sub language andintegrity rules using the primary and foreign keys. The relation is the onlyone data structure in the relational data model. And it is a table which obeysa certain restricted set of rule.

Task - 5

SQL Statement

  • Display the full name of all the employees (bothinternal and external) currently working on at least one project for theResearch Department along with the projects they work on, and their roles ineach project.
  • Display all the projects that have receivedfunding from a funding agency. The result should display the name of theproject, the names of employees involved, the name of the funding agency, thefunding contribution from the funding agency, the name of the project manager,the project total cost, and project end date.

Analysis

Using SQL for data manipulation and write the SQL codes for each query andthe output produced when it run in the database.

Assumption

Data manipulation has four aspects: input data into a relation, remove datafrom a relation, amend data in a relation and retrieve data from a relation. Aset of SQL functions that can operate over aggregations of data. Aggregatefunctions are COUNT, SUM, AVG, MIN and MAX. To undertake the aggregate work, weuse the GROUP BY clause.

Task - 6

Assumption

We do the above tasks by referencing the assignment scenario. The scenarioincludes:

We have been sub-contracted to design the database system supportingthe Research Department of a large Research and Development Company. An initialanalysis of the Research Department has shown the following:

  • The Research department is responsible only forthe project.
  • For every project the Research Department keepsthe following: full name project name, duration, start date, end date, internalemployees involved and their roles in the project, employees time, details ofany employees involved in the project, total cost of project, grant amountreceived from funding agencies, outcome(successful or not successful).
  • The roles that can be filled by externalemployees are: project manager, principal researcher, developer.
  • The Research Department also keeps the followinginformation for every internal employee who has ever been involved in aresearch project:

Personalinformation: full, name, age, address, nationality, qualification,research team, (end of contract date is also included if the person hasleft);

Project relatedinformation: projects involved in, current employment post name, grade,employment starting date, and current salary.

References

Book reference - Database Design and Development (NCC Education)

Database Design & DevelopmentAssignmentPage-37

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.