KMD Computer Centre (Yangon, Myanmar)
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.
Analysis and Assumption for All task
This assignment is about Research Department of a large Research and Development Company. It includes 6 Tasks. For the Task 1, we have to draw an Entity Relationship Model which describes the content and structure of the data held by Research Department. For the Task 2, we have to produce the resulting tables clearly indicating primary and foreign keys. Task 3 is about normalization we have to normalize the tables to ensure that all are in BCNF and we have to show the steps we have taken in normalizing the tables. In Task4, we have to use the Database Management System (DBMS) of our choice, set-up all of the above normalized tables, and populate them with well-designed test data (minimum 5 records per table). For me I choose Microsoft Access 2007 for Task 4. After structuring table, we have to provide printouts of all tables. In Task 5, It is about System Query Language (SQL). We have to setup and test all of the following queries using System Query Language (SQL). For this Task we have to provide printouts of SQL code for each query and the output produced when we run the query in the database we have developed. There are two bullets for us to do in this Task. For the First bullet, we have to display full name of all the employee( Both internal and External) currently working on at least one project for the Research Department along with the projects they work on, and their roles in each project. For the second bullet, we have to display all the projects that have received funding from a funding agency. The result should display the name of the project, the names of the employees involved, the names 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. For the Task 6, we have to explain any assumptions that we have made when analyzing, designing and implementing the above database, justify the approach we have taken and explain any alternative approaches we could have taken to any of the above tasks. We also have to discuss any changes we would make to improve our work. This assignment includes Scenario for all these above tasks. According to the Scenario, the Research Department is responsible for managing the research projects of the company. Research Department holds information on every research project carried out by the company. For every project the Research Department keeps the following information; full project name, duration, start date, end date, internal employees involved and their roles in the project (Roles are Project Manager, Principal Researcher, Developer), employees time (in hours), details of any external employees involved in the project (see more info below), total cost of the project, grant amount received from funding agencies, outcome (successful or not successful). For external employee, the research department keeps their name, address, company they work for, how many hours they have worked on the project, cost of employment. External employees can only fill the roles of Principal Researcher or Developer. For internal employees, Research Department keeps the following information full name, age, address, nationality, qualifications, research team, (end of contract date is also included if the person has left), Projects involved in, Current employment, Post Name, Grade, Employment Starting Date and current salary. The Research Department also keeps information for every funding agency, which has funded any of their projects; full agency name, address, contact person, contact telephone, project names they have funded along with the funded amount; full funding provided to date. Depending on the scenario, I got Agency Entity, Project Entity, External Employee Entity, and Internal Employee Entity. There are relationship between Agency and Project, Project and External Employee and Project and Internal Employee. The relationship between Agency and Project is one to many relationship. Between Project and Employee, many to many relationship is exist. So, use Allocate as Dummy Table. According to the Task2, we have to produce the resulting tables and indicate the primary key and foreign key. I got 4 resulting tables they are Agency table, Project table, Allocate table, Employee table. For Agency table, Agency ID is Primary Key. Agency table is used to keep the information of agency. For Project table, Project ID is Primary Key and Agency ID is Foreign Key. Project table is used to keep the information of Project. For external employee table, External Employee id is Primary Key. For Employee table, Employee ID is primary key. Employee table is used to keep the information of employee. For Allocate table, Project Id and Employee Id is Composit Primary Key. For Task3, I have to normalize the tables to ensure that they are in BCNF. So, I make normalization for Project From and Allocate form. For the Task 4, we have to use a Database Management System (DBMS) of our choice we have to set-up all of the normalize tables that we have made in Task 3. I used Microsoft Access 2007 and build Project Table, Agency Table, Employee Table, Allocate Table and. Project table includes 9 fields. They are Project Id, Agency Id, Project Name, Duration, Outcome, Start Date, End Date, Total Cost and Grant Amount. Project Id is used because if the project names are same we have to differentiate with Project Id. Agency Id is used because if the Agency names are same then we have to differentiate with Agency Id. In Project Name, we record the name of each of the project. In Duration, we record the duration of the each project. In Outcome, we record the outcome of the project (Successful or Unsuccessful). In Start date, we record the starting time of each Project by Day/Month/Year and in End date, we record the ending time of each project by Day/Month/Year. In Total Cost, we record the information about the cost we spent for each Project. In Grant Amount, we record the amount received from funding agencies for each of the Project. Agency Table includes Agency Id, Agency Name, Address, Contact Person, Contact Telephone, funded amount and funded date. Agency Id is used because if the Agency names are same then we have to differentiate with Agency Id. In Agency Name, we record the each of the agency name. In Address, we record the information of situation of the agency. In contact person, we record the information of the name of the person who is responsible for each of the agency. In contact telephone, we record the information of the responsible person ph no of each agency. In funded amount, we record the information about the amount that is allowed from each agency. In funded date, we record the information about the date that agency allowed for amount to used. In Allocate Table, We record the information of Project Id, Employee Id, Research Team, and Role. Project ID is used because if Project Names are same then we have to differentiate with Project ID. Employee Id is used because if Employee names are same then we have to differentiate with Employee Id.
In Research Team, we record the information about which research teams have which employees. In Role, we record the information about the role of the employee on the Project. Employee table includes Employee ID, External Employee Name, Employee Type, Age, Address, Nationality, Qualifications, Research Team, Company they work for, Work on project in hour, Cost of employment, Current Employment, Grade, Employment Starting Date and Current Salary. Employee Id is used because if Employee names are same then we have to differentiate with Employee Id. In Employee name, we record the name of each of the employee. In Employee Type, we record the information of employee that they are external or internal. In Age, we record the information about each employee age. In address, we record the information of where they lived. In Nationality, we record the information about internal employee nationality. In Qualification, we record the information of the employee qualification. In research team, we record the information of which team the employee involved in. In Company they work for, we record the information of each of the company they work for. In Work on project in hour, we record the information of the duration of employee involved on the project. In cost of employment, we record the information of the cost they got for their involvement. In current employment, we record the current employment of each of the employee. In grade, we record the information about the grade of each of the employee. In employment start date, we record the information about the date of employee start working on research department. In current salary, we record the information about each of the employee salary. Above things are that we do for the research department.
Database Design and Development Text Book From KMD