Skip to main content

Interview question - Database schema design

This is a database schema design interview question I have attended recently. The interviewer asked me to design DB schema for the below data in an efficient and normalized way and write a query to retrieve " first name, last name, department and work phone numbers of  all employees who are managed by Frank Callea".

The data is given below in the diagram.



The schema that I have designed is given below. I have created 3 tables as below.

Employee Table

     EMPID FIRSTNAME                        LASTNAME                             DEPTID      MGRID
---------- -------------------------------- -------------------------------- ---------- ----------
         1 Trad                             Lin                                       1          2
         2 Frank                            Callea                                    2          0
         3 Fred                             Snyder                                    3          2
         4 Nancy                            Pilato                                    1          1
         5 Evan                             Petre                                     2          1
         6 Rich                             Benjamin                                  3          3
         7 Julie                            Kastel                                    4          8
         8 Julie                            Carson                                    4          0

Dept Table

    DEPTID DEPTNAME
---------- --------------------------------
         1 Turbine
         2 ExtroVert
         3 App

Phone Table

   PHONEID      EMPID T PHNUMBER
---------- ---------- - ------------
         1          1 W 312-555-2441
         2          1 C 773-555-1962
         3          1 P 888-555-9740
         4          2 W 312-555-2488
         5          2 C 312-555-2488
         6          2 P 888-555-8718
         7          3 W 312-555-8233
         9          3 P 888-555-2519
        10          4 W 312-555-3360
        11          4 C 708-555-2755
        13          5 W 312-555-8842
        15          5 P 888-555-1469
        16          6 W 312-555-4484
        17          7 W 312-555-4168
Please note the relationships between tables Empid is a foreign key in Phone table, each table has a unique key field introduced.

Now the query part, how to retrieve "first name, last name, department and work phone numbers of  all employees who are managed by Frank Callea".

The query which I have used is given below. If there is a better query for this problem please provide it as a comment.

select e.firstname,e.lastname,d.deptname,p.phnumber,e.mgrid from employee e, dept d, phone p where p.type='W' and e.deptid=d.deptid and e.empid=p.empid  and e.empid in ( select e1.empid from employee e1, employee e2 where  e2.firstname='Frank' and e2.lastname='Callea' and  e1.mgrid=e2.empid);

FIRSTNAME                        LASTNAME                         DEPTNAME                         PHNUMBER          MGRID
-------------------------------- -------------------------------- -------------------------------- ------------ ----------
Trad                             Lin                              Turbine                          312-555-2441          2
Fred                             Snyder                           App                              312-555-8233          2


Comments

  1. AnonymousDecember 28, 2015 at 9:28 AM

    select e.firstname,e.lastname,d.deptname,p.phnumber,e.mgrid from employee e, dept d, phone p where p.type='W' and e.deptid=d.deptid and e.empid=p.empid and e.mgrid in ( select empid from employee where firstname='Frank' and lastname='Callea');

    ReplyDelete

Post a Comment

Popular posts from this blog

How to create a minikube single node cluster for learning Kubernetes

In this post I will explain how to setup a minikube single node kubernetes cluster using AWS EC2 instance which would help anyone who is trying to learn kubernetes and also help them to gain practical knowledge in kubernetes by running kubernetes commands, creating kubernetes objects etc. Minikube is a single node kubernetes cluster which means a kubernetes cluster with only one node that is a single VM. Minikube is only used for learning purposes and it is not an alternative for a real kubernetes cluster and should not be used for development and production usage. In this example I have launched an AWS EC2 instance with below configuration where I will install minikube and related tools. AWS EC2 Instance Configuration AMI: Ubuntu Free tier eligible 64 bit Instance type : t2-large ( For me t2-small or t2-micro is giving performance issues due to less memory) Once the EC2 instance is up and running, login to the instance using below command on terminal. If you are using wi...
27 comments
Read more

log4j - How to write log to multiple log files using log4j.properties

In Java applications some times you may need to write your log messages to specific log files with its own specific log properties. If you are using log4j internally then first step that you need to do is to have a proper log4j.properties file. Below example shows 2 log4j appenders which write to 2 different log files, one is a debug log and another one is a reports log. Debug log file can have all log messages and reports log can have log messages specific to reporting on say splunk monitoring. # Root logger option log4j.rootLogger=ALL,STDOUT,debugLog log4j.logger.reportsLogger=INFO,reportsLog log4j.additivity.reportsLogger=false     log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout log4j.appender.STDOUT.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %C:%L - %m%n     # Direct log messages to a log file log4j.appender.debugLog=org.apache.log4j.RollingFileAppen...
25 comments
Read more

Create SNMP Client in JAVA Using SNMP4j

Please follow the below mentioned steps to write a very simple SNMP4J application using Java and eclipse IDE. First you need to download a copy of SNMP4J.jar. You can get it from http://www.snmp4j.org/html/download.html Then you need to decide how you are going to test your application. Check whether you have access to any real network devices like Cisco routers or Juniper routers or whether you are aware of any SNMP agent running on any of your servers.There is nothing to worry if you cannot find one , you can start SNMP agent on your desktop machine as well. Here I am giving an example of how you can use your Windows machine to test your application by starting the SNMP service on the machine. I am using a Windows XP machine and below are the steps needed to start SNMP agent on the machine. 1. Go to Start->Control Panel -> Add or Remove Programs -> Add or Remove Windows Components      Select or Check "Management and Monitoring Tools". ...
16 comments
Read more