Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

Interview Questions

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

Byvivekjohari

Jan 2, 2014 #Question of the Week

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. Asp.net, 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 Asp.net 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).

DMCA.com

Trending
Advantages of SQL Stored Procedure

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Interview Questions SQL Advanced SQL Indexes

Interview question – Is Clustered index on column with duplicate values possible?

Aug 13, 2015 vivekjohari
Interview Questions

Question of the Month (July)

Aug 3, 2015 vivekjohari
Interview Questions SQL Basic SQL Server

Script to find complete months between two given dates

Jul 10, 2015 vivekjohari
32 thought on “Question of the Week – 2/1/2014 – Question 1”

  1. 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.

    Reply

  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)

    Reply

  4. 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…

    Reply

  24. … [Trackback]

    […] There you will find 36875 additional Info on that Topic: techmixing.com/2014/01/question-of-week-212014-question-1.html […]

    Reply

  25. … [Trackback]

    […] Here you can find 18392 additional Info to that Topic: techmixing.com/2014/01/question-of-week-212014-question-1.html […]

    Reply

  27. … [Trackback]

    […] There you can find 2576 additional Info to that Topic: techmixing.com/2014/01/question-of-week-212014-question-1.html […]

    Reply

Leave a Reply

You missed

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

January 1, 2025 vivekjohari No Comments
SQL Server

Database Partitioning in SQL Server

December 31, 2024 vivekjohari No Comments
SQL Server

What are the important python libraries?

December 31, 2024 vivekjohari No Comments
SQL Server

Understanding Estimated and Actual Query Plans in SQL Server

December 31, 2024 vivekjohari No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading