Question of the Week – 2/1/2014 – Question 1

Question :-

Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can belong to one designation at a time.
 It means at a time an employee can either be Software engineer or team lead or project manager but not software engineer as well team lead at a same time. 

Also the projects can work on different technologies like .Net, SQL Server, Oracle, HTML5 etc. Every project has a start data and end date. A project can work can include many technologies like .net, SQL Server , Java script, HTML5 etc. Most of the time every project work include a front end server technology like JAVA., uses Java script, HTML, CSS as client side technologies and SQL Server, oracle as back end database server. So In this ways a project work can include many technologies as in above example,  suppose a project can use as server end front end technology, java script, HTML, CSS, AJAX as client side technology and SQL server as back end database server.

What should be the design the database schema (tables, their foreign keys, primary keys etc).

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions and tagged . Bookmark the permalink.

32 Responses to Question of the Week – 2/1/2014 – Question 1

  1. Anonymous says:

    I think this question raises more questions than answers. I could interpret "an employee can belong to one role at a time" a couple different ways, at a time across the company, or at a time on a given project. There is a duplicated line about projects with many technologies, and it would seem this is supposed to be employees and proejcts, as it would be helpful to know what the employees are knowledgeable in.

  2. admin says:

    Hi Thanks for your valuable comment. I have update the question statement to further explain the Database design requirement.

  3. Following will be database schema:
    Employees(pk_employee_id, employee_name, fk_designation_id)
    Designation(pk_designation_id, desgination_name)
    Technology(pk_technology_id, technology_name)
    Project(pk_project_id, project_name, start_date, end_date)
    Allocation(serial_number, fk_employee_id, fk_project_id)
    ProjectDeatils(fk_project_id, fk_technology_id)
    EmployeesDetails(fk_employee_id, fk_designation_id, start_date, end_date)

  4. admin says:

    Thanks Nidhi,
    Good Answer. All the master table and the child tables are very well designed. One more child table can make the solution even more better employee_technology table which store the technology known by each employee
    i.e Employee_technology( emptechid , employeeid, technologyid)

    Thanks for your valuable comments…

  5. Pingback:

  6. Pingback: เว็บคาสิโน

  7. Pingback: รับทำ seo รายเดือน

  8. Pingback: 우리카지노

  9. Pingback: what is nitro strength?

  10. Pingback: my company

  11. Pingback: frete de carros

  12. Pingback: สมัครUFABET

  13. Pingback: abc diario

  14. Pingback: sen bir oğlansın

  15. Pingback: - a-thể thao

  16. Pingback: life is better with locs shirt

  17. Pingback: vao w88

  18. Pingback:

  19. Pingback: Agartha Market

  20. Pingback: The Majestic Garden

  21. Pingback: momo fishing

  22. Pingback: i99bet

  23. Pingback:

  24. Pingback: idrpoker

  25. Pingback: indoqq

  26. Pingback: tulsa vs east carolina live

  27. Pingback: indo qq

  28. Pingback:

  29. Pingback: Para Elly

  30. Pingback: ウォーターサーバー

  31. Pingback: เช่าชุดแต่งงาน

  32. Pingback: fun88

Leave a Reply