Data Base Design and Development

Database Design & Development

Introduction

This Data Base Design and Development (DDD) assignment which contain in International Advanced Diploma in Computer Studies course. In this DDD assignment, the main purpose is to demonstrate the understanding of applying data modeling and data analysis techniques to the design and development of database solutions by learning the Database Design and Development course. I have to design the database system supporting the Research Company.The Research Department is responsible for managing the research project of the company. The Research Department holds the information on every research project carried out by the company. The Research Department keep the information such as full project name, duration, start date, end date, internal employee(s) involved and their roles in the project (Roles are: Project Manager, Principal Researcher, Developer), employee(s) time (in hours), details of any external employees involved in the project, total cost of project,, grant amount received from funding agencies, outcome(successful or unsuccessful). The Research Department also keeps the information for every internal employee who has ever been involved in a research project. This department also keeps the information for every funding agency, which has funded any of their project.

By the above scenario of the assignment, I have to make altogether 6 tasks in this assignment. Which are-

Task 1 is to draw an Entity Relationship Data Model (ERD) for Research Department database that describes the content and structure of the data of it according to above scenario.

Task 2 is for producing the resulting tables for the database especially indicating their Primary Key and Foreign Keys.

Task 3 is to normalize these above tables with the details steps I have taken in normalizing of these table.

Task 4 is to produce the database from above normalized tables with a Data Base Management System (DBMS) of my choice, and populate these tables with well-designed test data (minimum 5 records per table). After set up the database, it must provide the suitable screen shots of these tables.

Task 5 is to set up and test two queries from the database using Structured Query Language (SQL) and produce the result table for each query.

Task 6 is to make the analysis and assumptions that have made when analyzing, designing and implementing throughout this assignment. And also explain the approaches that could have taken to any of the above tasks. In Task 6, it should also contain any changes that would make to improve this assignment.

Acknowledgement

Firstly, I thank to my parents who take support to my education .I thanks very much and appreciate U TAUNG TIN (Managing Director of KMD Computer Center) and DAW TIN TIN AYE (Director of KMD Computer Center). They support and carefully teach a lot of student and give high performance of facilities for their students. So I have a lot of thanked our teachers. I got the ability and work experience cause of NCC Education is ordered making the assignment to us concerned with our lessons.

I really thank to gratitude to all the lectures form KMD Computer Center for their kindness and warmly sharing their idea and give the many precious advice. Their teaching, training, guidance and advising made me to have enthusiasm.

Daw WAH WAH and DAW KHIN THU AUNG ( Lecturer of KMD who responsible to teach us Database Design coruse), they taught me DDD coruse and also helped along for my assignment and support with full strength to complete in time.

I also very thank to U MYO TUN ( NCC Department in KMD Computer Centre) who support, guide, and advice and give us idea.

And I would like to thank all people who help me to develop my project completely. I want to say thank you to all my teachers, lecturers, classmates, senior and my friends from KMD Computer Center.

Entity Relationship Diagram

Funding Agency Details- is to store the information of funding agency which has funded any of their projects.

Funding Agency- is to store the information for every funding agency

Project-is to store the details of project data

External Employee Details-is to keep the details of external employee involved in the project

External Employee- is to store the related information of external employee

Internal Employee Details-is to store the details of internal employee who has ever been involved in a research project

Internal Employee-is to store the related information of internal employee

External Employee Role Details-is to store the role information of external employee

Internal Employee Role Details-is to store the different roles of internal employee

Role-is to store the many kinds of external and internal employee roles

OPTIONALITY AND DEGREE OF RELATIONSHIP

Optionality : One Project has more than one InternalEmployee and One Internal Employee has more than one Project.

Degree of relationship: Many to Many

Optionality : One Project has more than one External employee and One External Employee has more than one Project.

Degree of relationship: Many to Many

Optionality : One Internal Employee has more than one Internal Employee Role Details .

Degree of relationship: One to Many

Optionality : One Role has more than one Internal Employee Role Details.

Degree of relationship: One to Many

Optionality : One External Employee has more than one External Employee Role Details

Degree of relationship: One to Many

Optionality: One Role has more than one External Employee Role Detials

Degree of relationship:One to Many

Optionality: One Project has more than one Funding Agency

Degree of relationship:One to Many

Optionality: One Funding Agency has more than one Funding Agency Details

Degree of relationship: One to Many

Primary Key and Foreign Key of each table

NO.

Entity Name

Attributes

Type

Length

Remark

1

Funding Agency

Funding ID

Funding Name

Funding Address

Contact Person

Contact Telephone

Int(10)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

10 characters

50 characters

50 characters

50 characters

50 characters

PrimaryKey

2

Funding Agency Details

Funding Agency ID

Project ID

Funding Amount

Funding Date

Int(10)

Varchar(50)

Varchar(50)

Varchar(50)

10 characters

50 characters

50 characters

50 characters

PrimaryKey

3

Project

Project ID

Project Name

Project Duration

Start Date

End Date

Project Cost

Funding Grant Amount

Outcome

Int(10)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

10 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

PrimaryKey

4

Internal Employee Details

Internal Employee ID

Project ID

Int(10)

Varchar(50)

10 characters

50 characters

PrimaryKey ForeignKey

5

Internal Employee

Internal Employee ID

Internal Employee Name

Internal Employee Age

Internal Employee Add

Internal Employee Nationality

Internal Employee Post

Internal Employee Grade

Internal Employee Salary

Research Team

Start Date

Contract Date

Int(10)

Varchar(50) Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

10 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

PrimaryKey

6

External Employee Detail

External Employee ID

Project ID

External Employee Hours

External Employee Cost

Company

Int(10)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

10 characters

50 characters

50 characters

50 characters

50 characters

PrimaryKey

7

External Employee

External Employee ID

External Employee Name

External Employee Add

ProjectID

Int(10)

Varchar(50)

Varchar(50)

10 characters

50 characters

50 characters

PrimaryKey

ForeignKey

8

Internal Employee Role Details

Role ID

Internal Employee Role ID

Int(10)

Varchar(50)

10 characters

50 characters

PrimaryKey ForeignKey

9

External Employee Role Details

Role ID

External Employe Role ID

Int(10)

Varchar(50)

10 characters

50 characters

PrimaryKey ForeignKey

10

Role

Role ID

Role Name

Int(10)

Varchar(50)

10 characters

50 characters

PrimaryKey

 

Normalization

Normalization of Project Funding Information

UNF 1NF

Project ID Project ID

Project Name Project Name

Project Duration Project Duration

Start Date Start Date

End Date End Date

Project Cost Project Cost

Outcome Outcome

Funding Agency ID

Funding Agency Name Project ID

Funding Agency Address Funding Agency ID

Fund Amount Funding Agency Name

Funding Agency Address

Choose a Key Fund Amount

Project ID

2NF 3NF

Project ID Project ID

Project Name Project Name

Project Duration Project Duration

Start Date Start Date

End Date End Date

Project Cost Project Cost

Project ID Project ID

Funding Agency ID Funding Agency ID

Fund Amount Fund Amount

Funding Agency ID Funding Agency ID

Funding Agency Name Funding Agency Name

Funding Agency Address Funding Agency Address

Optimization Data Model

PROJECT

Project ID

Project Name

Project Duration

Start Date

End Date

Project Cost

Outcome

PROJECT

Project ID

Funding Agency ID

Fund Amount

FUNDINGD AGENCY

Funding Agency ID

Funding Agency Name

Funding Agency Address

Normalization of Internal Employee Information

UNF 1NF

Project ID Project ID

Project Name Project Name

Internal Employee ID ProjectID

Internal Employee Name Internal Employee ID

Internal Employee Age Internal Employee Name

Internal Employee Address Internal Employee Age

Internal Employee Nationality Internal Employee Address

Internal Employee Qualification Internal Employee Nationality

Internal Employee Grade Internal Employee Qualification

Internal Employee Salary Internal Employee Grade

Start Date Internal Employee Salary

Contract Date Start Date

Contract Date

Choose a Key

Project ID

2NF 3NF

Project ID Project ID

Project Name Project Name

Project ID Project ID

Internal Employee ID Internal Employee ID

Start Date Start Date

Contract Date Contract Date

Internal Employee ID Internal Employee ID

Internal Employee Name Internal Employee Name

Internal Employee Age Internal Employee Age

Internal Employee Address Internal Employee Address

Internal Employee Nationality Internal Employee Nationality

Internal Employee Qualification Internal Employee Qualification

Internal Employee Grade Internal Employee Grade

Internal Salary Internal Employee Salary

Optionality

PROJECT

Project ID

Project Name

PROJECT/INTERNAL EMPLOYEE

Project ID

Internal Employee ID

Start Date

Contract Date

INTERNAL EMPLOYEE

Internal Employee ID

Internal Employee Name

Internal Employee Age

Internal Employee Address

Internal Employee Nationality

Internal Employee Qualification

Internal Employee Grade

Internal Salary

Normalization of External Employee Information

UNF 1NF

Project ID Project ID

Project Name External Employee Name

External Employee ID Project ID

External Employee Name External Employee ID

External Employee Address External Employee Name

Company Exterrnal Employee Address

External Employee Hours Company Name

External Employee Cost External Employee Hours

External Employee Cost

2NF 3NF

Project ID Project ID

Project Name Project Name

Project ID Project ID

External Employee ID External Employee ID

Company Name Company Name

Employee Hours Employee Hours

Employee Cost Employee Cost

External Employee ID External Employee ID

External Employee Name External Employee Name

External Employee Address External Employee Address

2NF

PROJECT

Project ID

Project Name

PROJECT/EXTERNAL EMPLOYEE

Project ID

External Employee ID

Company Name

Employee Hours

Employee Cost

EXTERNAL EMPLOYEE

External Employee ID

External Employee Name

External Employee Address

 

File Design

* Table Name -Project Information

* File Type -Master

* Primary Key -Project ID

* Foreign Key -None

* File Organization -Sequential

Field Name

Data Type

Length

Project ID

Project Name

Project Duration

Start Date

End Date

Project Cost

Funding Grant Amount

Outcome

Int(4)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

4 chatacters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

* Table Name -Funding Agency Detail Information

* File Type -Master

* Primary Key -Funding Agency ID

* Foreign Key -None

* File Organization -Sequential

Field Name

Data Type

Length

Funding Agency ID

Funding Agency Name

Funding Agency Address

Contact Person

Contact Telephone

Project ID

Funding Amount

Funding Date

Int(4)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

4 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

* Table Name - Internal Employee Detail Information

* File Type - Master

* Primary Key - Internal Employee ID

* Foreign Key - None

* File Organization - Sequential

*

Field Name

Data Type

Length

Internal Employee ID

Internal Employee Name

Internal Employee Age

Internal Employee Address

Internal Employee Nationality

Internal Employee Qualification

Internal Employee Post

Internal Employee Grade

Internal Employee Salary

Research Team

Start Date

Contract Date

Int(4)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

4 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

50 characters

* Table Name - External Employee Detail Information

* File type - Master

* Primary Key - External Employee ID

* Foreign Key - None

* File Organization - Sequential

Field Name

Data type

Length

External Employee ID

External Employee Name

External Employee Address

Company Name

Employee Hours

Employee Cost

Int(4)

Varchar(50)

Varchar(50)

Varchar(50)

Varchar(50)

4 charcters

50 characters

50 characters

50 characters

50 characters

* Table Name - Role Information

* File Type - Master

* Primary Key - Role ID

* Foreign Key - None

* File Organization - Sequential

Field Name

Data Type

Length

Role ID

Role Name

Int (4)

Varchar(50)

4 characters

50 characters

* Table Name - Internal Employee Role Information

* File Type - Master

* Primary Key - Internal Employee ID

* Foreign Key - None

* File Organization - Sequential

Field Name

Data Type

Length

Internal Employee ID

Role ID

Int(4)

Varchar(50)

4 characters

50 characters

Set up a database Design View

Project Information

Funding Agency Information

Funding Agency Details

Internal Employee Information

Internal Employee Details

External Employee Information

External Employee Details

Internal Employee Role Details

External Employee Role Details

Role

Datasheet View of Project Information

Datasheet View of Funding Agency Information

Datasheet View of Funding Agency Details

Datasheet View of Internal Employee Information

Data Sheet View of Internal Employee Details

Datasheet View of External Employee Information

Datasheet View of Internal Employee Details

Datasheet View of Internal employee Role Details

Datasheet View of External Employee Role Details

Datasheet View of Role Information

 

SELECT IE.IntEmpID, IEI.IntEmpName, EE.ExtEmpID, EEI.ExtEmpName, PI.ProjectName, R.RoleName

FROM dbo.ExternalEmployeeDetails AS EE INNER JOIN

dbo.ExternalEmployeeInformation AS EEI ON EE.ExtEmpID = EEI.ExtEmpID INNER JOIN

dbo.InternalEmployeeDetails AS IE INNER JOIN

dbo.InternalEmployeeInformation AS IEI ON IE.IntEmpID = IEI.IntEmpID INNER JOIN

dbo.ProjectInformation AS PI ON IE.ProjectID = PI.ProjectID INNER JOIN

dbo.InternalEmployeeRole AS IER INNER JOIN

dbo.Role AS R ON IER.RoleID = R.RoleID ON IEI.IntEmpID = IER.IntEmpID ON EE.ProjectID = PI.ProjectID

SELECT PI.ProjectName, IEI.IntEmpName, PI.ProjectName AS Expr1, FI.FunAgName, F.FundAmount, PI.ProjectCost, PI.[End Date]

FROM dbo.FundingAgencyInformation AS FI INNER JOIN

dbo.FundingAgencyDetails AS F ON FI.FunAgID = F.FunAgID INNER JOIN

dbo.ProjectInformation AS PI ON F.ProjectID = PI.ProjectID INNER JOIN

dbo.InternalEmployeeInformation AS IEI INNER JOIN

dbo.InternalEmployeeDetails AS IE ON IEI.IntEmpID = IE.IntEmpID ON PI.ProjectID = IE.ProjectID

In my Database Design and Development assignment, I have to make are altogether 6 tasks. In Task 1, I have to draw an Entity Relationship Data Model (ERD) for Research Department Database. Research Department Database that describes the content and structure of the data of this department. According to the assignment scenario, I created ten database tables which are

Each table or entity has following fields as for Relation,

* In the Project Information table, Project ID, Project Name, Project Duration, Start Date, End Date, Project Cost, Outcome.

This table is to store the data concerned with information of Project.

* In the Funding Agency table, Funding Agency ID, Funding Agency Name, Funding Agency Address, Fund Amount, Fund Date.

This table is to store the Funding Agency data that has ever been involved in a Research Project.

* In the Funding Agency Details table, Funding Agency ID , Funding Agency Name.

This table is to store the Funding Agency Details data that has ever been involved in one of their research projects.

* In the Internal Employee Table, Internal Employee ID, Internal Employee Name, Internal Employee Age, Internal Employee address, Internal Employee Nationality, Internal Employee Qualification, Internal Employee Grade, Internal Employee Salary, Start Date and Contract Date are included.

* In the Internal Employee Details Table, Internal Employee ID and Project ID are included.

* In the External Employee Table, External Employee ID, External Employee Name, External Employee Address, Company Name.

* In the External Employee Details Table, External Employee ID, Project ID, Employee Hours, Employee Cost.

* In the Role Table, Role ID and Role Name are included.

* In the Internal Employee Role Details, Role ID and Internal Employee ID are included.

* In the External Employee Role Details, Role ID and External Employee ID are included.

In Task 2, I have to provide the table that indicates the primary key and foreign keys of the above entities together with the attributes of these entities.

* For the Project Information table, Project ID should be primary key

* For the Funding Agency table, Funding Agency ID should be primary key

* For the Funding Agency Details table, Funding Agency ID should be Primary key and Project ID is foreign key in this table.

* For the Internal Employee table, Internal Employee ID should be primary key.

* For the Internal Employee Details table, Internal Employee ID should be Primary Key and Project ID is Foreign Key.

* For the External Employee table, External Employee ID is Primary Key.

* For the External Employee Details table, External Employee is Primary Key and Project ID is Foreign Key in this table.

* For the Role Table, Role ID is Primary Key .

* For the Internal Employee Role Details, Role ID is Primary Key and Internal Employee ID is Foreign Key in this table.

* For the External Employee Role Details, Role ID is Primary Key and External Employee is Foreign Key in this table.

In Task 3, I have to create the form design for the database. I created altogether three form designs in this task. These three form designs are -

* Project Funding Information

* Internal Employee Selecting Information

* External Employee Selecting Information

The Project funding Information form is to use when any project make registration at the Research department. The Project Funding Information form is to use when any company make registration for later making project registration. The Internal Employee Information form is to when selecting the internal Employee for the project that which Employee contains in which project. The External Employee Information form is to use when selecting the External Employee for the project that which Employee contains in which project

After creating form designs, I have to make data normalization. In the normalizations, I have to make the following steps in sequence. These steps of normalization are Gathering Data (UNF), choosing a key, converting to first normal form (1NF), converting to second normal form (2NF), converting to third normal form (3NF) and optimization. After making the normalizations of the form, the result optimizations are as follow.

The Project Funding Information form contains Project ID, Project Name, Project Duration, Start Date, End Date, Project Cost, Outcome, Funding Agency ID, Funding Agency Name, Funding Agency Address, Fund Amount, Fund Date. The Internal Employee Selecting Information Form contains Project ID, Project Name, Internal Employee ID, Internal Employee Name, Internal Employee Age, Internal Employee Address, Internal Employee Nationality Internal Employee Qualification, Internal Employee Grade, Internal Salary, Start Date, Contract Date. The External Employee Information Form contains Project ID, Project Name, External Employee ID, External Employee Name, External Employee Address, Company Name, Employee Hours, Employee Cost.

In Task 4, I have to setting up the database design. To set up the database, I can choose the Data Base Management System (DBMS) as I like. In this assignment, I choose Microsoft SQL server 2005 to set up the database. In the database, I have to set up altogether 5 tables such as Project Information, Funding Agency Details, Funding Agency, Internal Employee Details, Internal Employee, External Employee Details, External Employee, Role, Internal Employee Role Details, External Employee Role Details. After setting up the tables, I have to make relationship for these 10 tables.

In Task 5, I have to set up and test 2 queries using Structured Query Language (SQL). This is the SQL statement for 2 queries

In 1st Query

SELECT IE.IntEmpID, IEI.IntEmpName, EE.ExtEmpID, EEI.ExtEmpName, PI.ProjectName, R.RoleName

FROM dbo.ExternalEmployeeDetails AS EE INNER JOIN

dbo.ExternalEmployeeInformation AS EEI ON EE.ExtEmpID = EEI.ExtEmpID INNER JOIN

dbo.InternalEmployeeDetails AS IE INNER JOIN

dbo.InternalEmployeeInformation AS IEI ON IE.IntEmpID = IEI.IntEmpID INNER JOIN

dbo.ProjectInformation AS PI ON IE.ProjectID = PI.ProjectID INNER JOIN

dbo.InternalEmployeeRole AS IER INNER JOIN

dbo.Role AS R ON IER.RoleID = R.RoleID ON IEI.IntEmpID = IER.IntEmpID ON EE.ProjectID = PI.ProjectID

In 2nd Query

SELECT PI.ProjectName, IEI.IntEmpName, PI.ProjectName AS Expr1, FI.FunAgName, F.FundAmount, PI.ProjectCost, PI.[End Date]

FROM dbo.FundingAgencyInformation AS FI INNER JOIN

dbo.FundingAgencyDetails AS F ON FI.FunAgID = F.FunAgID INNER JOIN

dbo.ProjectInformation AS PI ON F.ProjectID = PI.ProjectID INNER JOIN

dbo.InternalEmployeeInformation AS IEI INNER JOIN

dbo.InternalEmployeeDetails AS IE ON IEI.IntEmpID = IE.IntEmpID ON PI.ProjectID = IE.ProjectID

This is all the analyis and assumptions for this assignment. I have made when I analysing, designing and implementing. . If I have to make the assignment like this again, I think I can make more efficient assignment more than now with this experience.

References

Book Title: Database Design and Development

Publisher: NCC Education

Edition: Third Edition

ISBN: 1-403-91601-2

Author: Paul Beynon-Davies

Database Design & Development Assignment Page-11

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!