This is database design to support the Knowledge Transfer department for the WATB Collage. KT department of the WATB collage is stored the consultancy data by the collage's academics. It keeps the information of the academic who has ever been involved in the consultancy projects. When companies are applied, department makes management for project. Not only academic and consultant, they also hire the external consultant for project. External consultants are hired by the contract. Hiring external consultants depend on the type of project.
Before making database design, we must consider the entity relationship of the department. Entity relationship diagram show the relation of the entity of the database. The facts that include in scenario are help to make ERD diagram. The company, consultant, academic and project data are mainly show to make database. It shown: the KT department holds information for every consultancy ever done by collage's academic. And then "The KT department keeps information for every academic who has ever been involved in consultancy project", "The KT department keeps information for every company and every consultant". So we need to consider only 4 entities for this database. These are academic, company, consultant and project.
Firstly, the relationship between the project and company will be considered. The companies join the department to make project. They have one to many relations because a company can order to make many projects of their functions. Then a project will be made for a company.
Then academic, external consultant and projects are next. The company ordered the project, the department can use academic or hire external consultant for create a project. A project make with many academic and external consultant. Academic and external consultant can make more than one project in this system. So they have many to many relationships with each other. In the ERD, all are many to many relationships except company and project relations. We drew ERD with logical way. ERD diagram in task-1 is logical ERD design for database.
In the task-2, we will create the data table and define keys of these tables. There have five tables in this task. These are company table, project table, academic table, external consultant and project detail table. Company ID, company name and other that related with company profile which are included in company table. The academic, external consultant tables are including their profiles. In the project table, project ID, project name, project type and other are included. Project detail table means the dummy or combination of other four tables. This table has links with other. Mostly we defined ID for primary key of table. Company table primary key is company ID. It haven't foreign key in table. The academic ID is a primary key of the academic table and external consultant table also define ID for primary key. These tables don't have foreign keys. In project table, project ID is primary key and company ID is situated for foreign key in table. The project and company table have direct link with each other. Project detail table include project detail ID as a primary key. It has many foreign keys and makes links with tables. These are company ID, Project ID, External Consultant ID, Academic ID. Keys make the links for all tables.
Normalization is also analysis of the data in the forms. Making normalize is procedure of the task-3. We drew the form that based on the data table for database.
We summarize the data of table and design the form of database design. The project, academic and external consultant are including in task-3. Normally they have five stages in normalization that are UNF, 1NF, 2NF, 3NF and optimization. Formally, we collect all of the data of form and group the repeating and normal. Then choose the key of the form. It must be unique key and define it to the primary key from normal or single group.1NF is defining of the key in repeating group. In 2NF, we separate group of data under the keys in repeating group. After 2NF stage, 3NF stages choose the key in single group, the foreign key. Some form doesn't need to make 3NF stage. They end in 2NF stages. In normalization, the final stage is optimization.
Firstly we create forms. The project, academic and external consultant forms are included in this system.