As a student of international advanced diploma in computer studies(NCC education), we need to do the Database Design and Development Assignment. This database assignment will allow you to understand how to apply data modeling and data analysis ways to the design and development of database solutions.
According to this assignment scenario, we will analysis and design the database system about Research Department of a large Research and Development Company. This Research Department is responsible for can manage the research projects of this company. As I understand, this research department keeps information on every research projects which is carried out by this company. The research department keeps the following information concern with the project. These field names are full project name, duration, start date, end date, internal employee involved and their roles in the project(roles are project manager, principl researcher, developer), employee time, details of any external employees involved in the project, total project cost, grand amount received from funding agencies and project outcome which describes successful or unsuccessful.
They also keeps the information concern with internal employees and external employees who involved in their project. For external employee, this research department keeps the following information: their name, address, company they work for, how many hours they have worked on the project, cost of employment. And this research department also have an restrict rule. That is external employee can only be the two roles, principal researcher or developer among three roles.
And for internal employee they keep the following information. They separate information into two parts. They are personal information and project related information.
In the personal information, it contains full name, age, address, nationality, qualifications, research team, (end of contract date also included if the person is left);
And in the project related information, they keeps the internal employee involved in which projects, current employment post name, grade, employment starting date, and current salary.
But internal employee can fill all roles for every projects(principal researcher, developer and project manager). Both types of employee may employ the various roles at the same time.
The assignment intended to produce the entity relation diagram which describes the content and structure of the data of the Research department. And then produce the entities with attributes and the key of primary and foreign. Then normalize the form design to table and set up the resulting table by using the Data Base Management System (DBMS) and enter the test data to the relevant tables. Then produce the query which is given in task of assignment by using SQL statement. Then retrieve the required records from the relevant table.
Analysis and Assumption for All tasks
Task 1 is to draw the Entity Relationship Diagram of Research department which explains content and structure of the data held by Research Department of the large research and Development Company. According to the project scenario, we got twelve entities for research department. They are valid project, valid project/project, research, employment, employment/internal employee, internal employee, internal employee assign, project assign, external employee assign, external employee, role, and project. Then we produce the logical diagram with relationships. And then, we have produced the physical entity relationship Diagram by analyzing the logical diagram. We also consider how those entities relate. We can know how to separate the table and how to relate the resulting entities by doing this task. We can get more understanding how to draw a entity relationship diagram.
Task 2 is to produce the resulting tables from entity relationship Diagram and specify which is the primary keys and foreign keys. Then, we produce the entities with possible attributes by the scenario. If you want, you can consider the some additional attributes for your tables. We can know how to carry the attributes and how to assign the key of primary and foreign.
In task 3, we normalize the four form design to tables. And we also need to describe the steps of normalization for these tables. In this task, we will separate by considering the attributes or fields not to duplicate. We can know how to do normalization for table.
Task 4 is to build the result of normalized tables as the database design and set up the possible well-designed test data at minimum 5 records for each table. And choose the data base management system (DBMS) to build the table. For this task, we choose the Microsoft Access 2003 for building the result table. And then we set up the result tables by using the access 2003. And then we have entered the possible test data more than 5 records in the corresponding table. By making this task, we have got how to build a database system and how to record the data in the relevant table.
Task 5 is to create the queries using the Structured Query Language (SQL). In this task, we have created three queries according with the relevant table. First, we produce the query which displays the name of all of Heads of Department together with other roles which they employ and the current post that they hold. The Second, we produce the query which display the name of all the current Senior Lecturers with the department name that they employ and their full salary which include the sum of the salary and the total additional payment of all that they will receive for role. The third, we produce the query which display the name all of Heads of Mathematics Department from 2000 to the present and current post and qualification. By making this task, we have known how to create query better and how to retrieve the data from the relevant table.
By making this assignment, we have got many experiences about the database system and how to analysis, design and implement. And then we have improved our knowledge of the database system.