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
