June 29, 2017 at 9:05 pm
Comments posted to this topic are about the item A Database Design Test
June 30, 2017 at 1:02 am
When I was interviewing for a report writer/analyst I put together a test that included a question where people had to look at an invoice, list all the fields and then normalise this to 3rd normal form and lastly to draw an ER diagram.
I found the majority of people who completed the test got it wrong or left it blank.
The successful applicant answered it correctly and scored 80% overall on the test and interviewed well too.
As the work in my dept focuses on query writing and some degree of creating databases, I think being able to normalise a database and draw an ER diagram shows candidates could come up with a good database design and would write logical and efficient queries with less tenancy towards a long-winded approach or queries that produce the wrong answer.
Robin
June 30, 2017 at 1:57 am
I haven't been asked many "design it" questions over the years. I hadn't thought about that until now.
Steve says "...There seem to be so few ER diagrams in the real world,..." and he's right (again, I hadn't really noticed until now). This is strange, and I don't know why. I wouldn't dream of writing anything more than a trivial query without sketching a diagram. It would probably be only a few boxes joined by lines with "crow's feet" and it might only ever exist in my notebook but it would be there. In fact, as I write I have the ERD of the database, and a related Object Model for a proposed small application pinned up beside my screen.
I find constructing the diagram (from the tables) or sketching the diagram (for designing something new) helps me to understand "the whole" and how things connect together. The diagram helps me to understand the implicit limitations of what I'm working with.
Given what I've just said, it does seem a little odd that I'm not asked more about ERDs (in interviews or otherwise) and that the world isn't awash with them. Maybe it is just me and the places I've been.
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
June 30, 2017 at 2:28 am
I have only once been given a database design question during an written test; Following a short description of the database requirement there was a diagram of five boxes, four of which listed the various attributes and one which was empty. the objective was to identify all Primary Keys, all Foreign Keys, to add the attributes to the empty box after recognising the need for it to resolve a many to many relationship between to other attributes and then to add all the relationship lines, indicating their relationship (one-to-one, one-to-many).
Of all the candidates they had I was told that not all had tackled that particular question and of those that did I was the only one that managed it 100%, sadly overall I did not do the best in the test.
Very surprised this is not tested more often as it is essential to understanding the whole of a database IMHO.
...
June 30, 2017 at 2:30 am
The normalisation then ERD then simple querying test is something we use as a norm in our interviews for DB bods including DBA's. We give them a simple form of data and ask how you'd normalise it (whatever NF they want, but usually 3NF), then we ask them to draw up a quick ERD based upon their conclusion and also to add indexes, then given the ERD we ask them how you'd retrieve and/or aggregate various records (left join, inner join, sum etc). All fairly simple. This is a too and fro conversation and we will certainly add explanations, discuss why and give promptings if necessary. This gives a us set of discussion points but we do take care to be fairly easy-going so it becomes more of a conversation. We don't expect the world from the interviewees but we do expect some basic understanding. As you can probably guess.. most interviewees flounder hopelessly, but then we know it is hard to think on your feet and interviews tend to be stressful so we do make allowances for this.
June 30, 2017 at 3:17 am
Asking for a quick diagram in interviews was standard when I used to run them. We'd ask how they'd structure the data seeing if they added in relationships etc and then ask them to produce simple queries on their set up. Pretty easy but you'd be amazed how many people can't do this!
June 30, 2017 at 3:27 am
Slightly different take on a similar theme...
We recently interviewed for a couple of positions that would involve developers feeding our data warehouse. As well as the usual prescribed HR questions (Some of which we didn't really hold in high regard as they were relatively meaningless for the jobs we needed to fill) I created two tests that asked basically the same questions.
I created an 'Interview' DB, filled out some dummy data (Only a small number of records, with no 'gotchas' - we weren't trying to make people fall over in an already stressful situation) and asked a number of questions whereby they were given the exact fields required and any summarising that should occur. I provided a DB diagram so they could see how everything linked together if it wasn't immediately apparent.
The other way that I did it was to print out the data contained within the tables (Albeit with lookup ID's replaced with the actual values etc.) and asked the same questions, but asked to show how they would link the tables together and what the expected outcomes would be.
We were more interested in finding out if people's heads worked 'the right way' when it came to navigating a predefined structure than testing their T-SQL coding skills as this can be taught relatively easily as long as the thinking is right.
Happily both candidates came in and hit the ground running after some initial training, so it seems to have worked in our case!
June 30, 2017 at 4:27 am
Hmm, no I've never had or given such a test, sounds like a fair plan though.
Last interview I had they asked me details of access modifiers in C# (protected internal etc). Recalling the exact details of such things, whilst not exactly trivial, is just not stuff I keep in my brain at all. It's easy to find the right one when you need it which is quite good enough for me! Probably means I wouldn't have liked the job anyway.
June 30, 2017 at 4:42 am
I've been asked to provide a high level architectural design for a system.
I've also been set DB scenarios as interview questions but haven't been asked explicitly for a DB design in ages. I have provided a DB design to illustrate my answers though.
I've been presented with solutions and been asked to critique them. This is an extremely dangerous interview question because the interviewer may be presenting you with their pride and joy as a show off to their colleagues on the interview panel. If you proceed to prove that their baby is the most hideous creation ever to disgrace Christendom then you are on to a loser. Mind you, perhaps you are best off discovering this at interview stage anyway.
June 30, 2017 at 4:48 am
No, I've never been asked to design an ER diagram during an interview. Like you, questions on designs, normalisation etc.
The most interesting test was to write a Sudoku solution in T-SQL. I was given code to generate a board and the starting numbers but then I had to code the solution - definitely the most fun interview problem to solve!
June 30, 2017 at 7:26 am
Yes I have been asked to produce an ERD in a job interview. They did not like the way I did it and I did not get an offer, but I did not like them either. The hiring manager was wearing filthy flip-flops and kept talking about all the long hours he worked. We would not have gotten along.
My favorite interview, I walked in, and the hiring manager yelled at me, here is a file, sort it and find the dupes! I sat down at his workstation, imported the file into a table, then queried it joined and grouped on itself. I told him that every record in the file was duped exactly once. He calmed down, sat down in the interviewee chair, and for the next 30 minutes we talked about his company and his products. I got that job and I quickly found out that at the time of my interview, he was in the middle of a huge production support problem. The file was from a client site and the team was trying to get the system back up and running. I learned from this to try to cut a hiring manager a little bit of slack. You don't know what they are going through.
June 30, 2017 at 7:31 am
Several years ago I took an interview test for a C# developer position. The question was simple enough. They provided some basic properties of an object and wanted us to write the code for the POCO. Then they expanded this and wanted us to create a double linked list "without using any native objects". Well this of course set off my spider sense that this was not a job I was interested in, even though they are a major player in their market and listed as one of the best places to work in KC. Seriously??? A double linked list with objects in C#??? This seems like a very strange thing, especially when .NET has a linked list class that does all of this for you. Having never used a double linked list since my C++ classes in college I was shocked. I mean, who is going to create pointers and force unsafe code in the real world? I scribbled some chicken scratch on the paper and then decided that I would rather comment about why this test was so absurd and that doing this in code is an exercise in poor design. I wrote about two pages of text by hand explaining that using a queue would be a much better approach and that creating a double linked list manually is an exercise in futility. If that is the functionality you need why not use the native objects designed to handle this stuff and all the bugs are already worked out. I am sure the intention was to evaluate my knowledge conceptually about software development but the test was woefully inadequate.
After about a week I received a call from them and they wanted to make me an offer. I was intrigued after that crazy test. I walked out of the discussion after just a few minutes because the position was on the help desk. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2017 at 7:33 am
I haven't been asked to make an er diagram, but I was asked to write a stored proc.
June 30, 2017 at 8:18 am
My first job out of college I was asked to either explain or create an ERD, I think the point was mostly to demonstrate that I had some basic idea of what tables and columns and relationships were. Never been asked to do that since and I generally don't bother with anything beyond high level diagrams now.
June 30, 2017 at 8:24 am
I am an architect for a very large, well known Fortune 120 company.
I would love to have a diagram for every database that I am responsible for (or work with), but taking the time to build an ERD is seen as a waste of time and money by management.
/RANT (sorry, but to use a millennial term 'I have been triggered')
I am just now being put on a project with a team of developers who have been allowed to run wild. I am tasked with getting their DB to run 'right'.
The lead developer is one of those types who believes that he has to use the newest technology and must use a 'buzzword' at least every 4th word in every sentence. Bane of my existence.
This guy built a database based on the party model. He read about the 'Party Model' on some agile blog and just HAD to use it as it is the next big thing.
Many examples of the tables lacking PK, FK's and normal form. No way to link a entity to a person. All tables built to make his application development easier to code instead of making the data reliable.
When I pointed this out, he said that all this would be taken care of 'in code'.
Indexing? Nope, don't need it.
Also found that the developers were reusing SQL code. Not a problem unless, as is the case here, the SQL was not modified to pull the data required in a targeted way.
They would pull every single record into application code only to filter the data on a date range!!!!!
They want to store JSON formatted data in the DB (2012), for ease of use for them. Meaning that the JSON data has to be broken up on the SQL side when trying to query a specific piece of the data.
Many of their queries were written in such a way as to force a full table scan.
Triangular joins. Need I say more.
I was able to review the DB before it made it out of DEV.
Wrote up a report for this guys boss expressing my dissatisfaction, listing the issues and with a recommendation that the DB be re-architected before any further development occurs.
Manager ignored me and allowed development. Code is now in PROD and is having all sorts of issues, failures and slowdowns. Oh, and was months behind schedule.
I have been tasked with fixing this nightmare DB.
/END RANT
IMHO, developers have no business building DB databases and I say this as a former developer. I also feel this way about developers writing SQL in general.
I can't tell you the number of times that I have had to put my foot down to stop a developer trying to mangle the data in a DB simply to make his/her life easier on the application side.
I can't tell you the number of times that I have had to 'consult' on terribly designed DB's (all designed by a 'full stack' developer).
I would love to have a DB design test in the interview process, but the heads of development would never let this fly.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply