Monday, October 12, 2009

sql server database design interview questions


Last updated: 10/5/2010 9:19 PM
category
topic
links
Database design interview question
most common approaches to design schema
#DB design approaches

  1. Gather the requirements from FRS(functional requirement specification) or the most common one is from the prototype of the application or from the existing legacy application database.
  2. create a conceptual model (use case) , <you can skip this if you have prototype or existing DB design in legacy>
  3. Create a logical model (ER Modeling) from this conceptual model( logical model contains only table,columns,relations) with out specifying data types
  4. apply the normalization rules to reduce the redundancy (duplicates) and avoid the anomalies( problems with deleting or updating the data with out primary key)
  5. convert the logical model to physical model (choose RDBMS)(data types, constraints, indexes etc...) from the logical model
  6.  create a prototype or from the existing prototype of your application and start design
    and
     test the model with the prototype.
Database design interview question
What are the advantages and disadvantages of Normalization
#is my database normalized

Advantages: reduce the redundancy(dupliate) ,avoid null values, avoid update anomalies(refer update anomalies)

Disadvantages: too many joins required to produce reports.
Database design interview question
Normalization or De-normalization. Which one to choose ?
#Tradeoff matrix

It depends. A tradeoff between no. of updates vs. reports will guide till what level the database should be normalized or de-normalized.
Database design interview question
What is your approach on normalizing the database to improve the performance of the database

Refer above questions answer.
Database design interview question
Have you experienced any problems or failures in database design?

·        Assigning too large data types, thinking that the length of the data would grow in future. E.g.:. Customer_first_name varchar (90) – in general this will not be 90, but if the designer doesn’t have any idea on the realistic values then this leads to occupying more pages for small data.
·        Not doing any normalization or improper normalization: when designing the database focusing on meeting the requirements, sometimes the designer might ignore to normalize the database and especially doing the tradeoff between No. of Insert/Update/Deletes vs. reports.
Tip: validate the designing through having a standard checklist helps to make sure that the design is up to the mark.

·        Don’t keep the audit data in the same table, look at the below table where the table has the audit data itself where you can’t maintain any constraints (fk, unique). Requires maintaining the integrity at the coding level where it leads to complex SQL statements instead of simple queries.


Name
Age
Status
Date
Robert
30
Updated
01-jan-1999 5 AM
Robert
31
Inserted
01-jan-1999 5 AM
Robert
31
Deleted
01-jan-1999 5 AM
o   Resolution: create a view with a SQL statement to produce only the active data and use these views in your front-end application. Rest of all the tables will be act as audit tables.
·        Don’t design the database by assumptions, all the assumptions must be clarified with all the stakeholders before finalizing the database design.
·        Freeze the scope of the project before finalizing the database design
·        I had seen many projects that the front-end developers are mostly complaining about the database that the design is not well and good and leads to the poor performance. This is in fact true and below is the most possible scenarios in most of the projects which makes the design not up to the mark for performance.
o   Not following any trade-off matrix before making any decisions on changing the database design during the every build – This is the most common problem as most of the project requires some changes and the designer/developer has to be very careful before making any change on the schema, he has to consider the impact of the change WRT time, cost, scope and has to think about how to mitigate as well.
Database design interview question
What is the difference between Primary Key and Unique Key
#heap table

A unique key can also be a primary key and can involve in joins.
Differences are: when you create a primary key by default it will create a clustered index whereas for the unique key by default it will create non clustered index.
A table will contains only one primary key in a table, but can contains more than one unique key.
Primary key won’t allow any null value, but unique key allows only one null.
Database design interview question
Can a foreign key allow null value?

Yes.(also refer cardinality)
Database design interview question
Mention few best practices to design a database

·        Select the right design tool to design the database, there are many tools available in the market and the most popular one is Erwin, but make sure that Erwin ( not sure about the current version)  itself has some problems in it and you must know about these before using the Erwin.
§  One of the most challenging task when working with Erwin is during making any changes , Erwin generates the scripts which contains the drop and recreate statements which has to be analyzed first before executing directly on the development database which might make the database inconsistent if the script fails to execute at the middle.
§  Creates a non-clustered index by default on the primary key, so make sure there won’t be any heap tables created by Erwin.
§  When you modify the table in Erwin which has any comments defined then the script generated by Erwin will not contains any of these comments.
§  Finally make sure its 100% compatible with the version of the SQL Server you are using.
·        Define the scope of your project and finalize the database design before moving to the development as any change in the schema leads to the major changes in the application development.
·        Conform the logical design with the requirements and get the sign-off
·        Tip: It’s possible to confirm the design of the database as per requirements through the prototype of the application.
·        follow a proper naming convention
·        try to define the data types as short as possible ( refer SQL Server Page 8 KB)
·        Don’t use the text data type unless its highly required as querying the data inside a text data type sometime requires to build a full text catalog
·       

·         
Database design interview question
What is the difference between Primary Key and Candidate Key

Candidate key: A single column or set of columns that can uniquely identifies the row in a table. a candidate key can acts as a primary key and you can define only candidate key as a primary key.
Primary Key: A key or set of keys that can uniquely identifies the row in a table.
E.g.:  sometimes when you declare a surrogate key (column with identity property) as a primary key then the actual business primary key will acts as candidate key.
Database design interview question
What is cardinality

It’s a property to specify the number of instances of an entity that can be existed in each side of the relationship. E.g.:. One-One or null, One-Many or null
Database design interview question
What is Functional Dependency?

It’s a special relationship among columns in a table .A set of attributes X in table functionally dependent on set of attributes Y on the same table where the values in attributes Y  identifies the values X.
E.g.:. Employee Address, Employee phone functionally depends on Employee Name.
Database design interview question
What is Tuple

Row in a table
Database design interview question
What is a Schema?

Table, columns and the relations between the tables.
Note: the concept of schema in SQL Server 2005 & 2008 is different from the concept of schema in relational theory where the SQL Server 2005 & 2008 refers to the logical container of database objects.
Database design interview question
what is the difference between DBMS and RDBMS
# SQL Server and Codd Rules
Rather than differentiating between the DBMS and RDBMS in detail, the fact is all the leading database software available in the market are DBMS.
So what is DBMS: software to manage the database (collection of data) through insert/update/delete / retrieval of data through a systematic and structured way (SQL) and manages the hierarchical, network, relational, object oriented databases.
To define any DBMS as RDBMS it has to satisfy all the 12 rules defined by E.F Codd.
There is an assumption that if the system has any relational databases then it’s an RDBMS which is not really true. For e.g.: MS Access is not an RDBMS though the recent version maintains the relationships through primary and foreign keys.
Is SQL Server is DBMS or RDBMS
Yes, but not 100%. As per E.F Codd rule 0, to define any system as an RDBMS it has to satisfy all the 12 rules.
Most of the leading software in the market are violating the Rule-9: Logical Data Independence.
Database design interview question
can you create 2 tables with the same name
yes , in a different schemas
SQL server 2008 Database design interview question
Can you enforce the naming convention in SQL Server to have all the tables should follow as defined?
Yes, through policy based management.

Open SSMS àManagement àPolicy Management àFacets à right-click Table, and then click New Condition
à type Table starts with tbl_
* In the Facet box, confirm that Table is selected.
* In Expression area, in the Field box, select @Name, in the Operator box select LIKE, and in the Value type "tbl_%"
* Optionally, you can type a description of the condition, by clicking on the Description tab.
Database design interview question
what are the new data types in SQL Server 2005
Database design interview question
What are the new data types in SQL Server 2008/2012/2014
Database design interview question
what is sparse column and its pros and cons
As per BOL “columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values”
So, when the column value is NULL for any row in the table, the values require no storage, but when it has any value then it takes few more bytes than its actual size.

It’s better to go for this only when the column has lot many null values.

Refer BOL for the limitations and estimated space.
Database design interview question
How will you implement a tree structure (one employee reports to another who is an employee of the organization itself) in design?
What are the challenges in managing this type of tables?
refer the dev-tree struct-qries

TBL_EMPLOYEES
ID
Int ( primary key , identity 1,1)
emp_id
char(9) , unique key
emp_name
varchar(30)
rep_to
int (foreign key references emp_id
designation
varchar(30)

when you enter the data it looks like the below :

ID
emp_id
emp_name
rep_to
designation
1
A1
bill gates
chairman
2
A2
steve
CEO
3
B1
craig
1
vice president
4
B2
Jeff raikes
2
president

Challenges: populating data from the text files is bit challenging.
If you try to insert the new data and update the existing data from the below text file into this table then you need to write at least one cursor.

emp_id
emp_name
reports
designation
M1
tuchen
marketing manager
M2
Jim alchen
M1
co-president
I2
David Campbel
I1
infra-manager
I1
Paul Flessner
1
sr.vice president

Database design interview question
what is the disadvantage of having a foreign key
There is no any disadvantage in particular and in fact it supports the referential integrity which is the major advantage.
But internally when there is a foreign key means there is a overhead on the SQL Server system :
Before delete a master record: the engine will look at the existence of the child record in foreign key table internally.
before insert or updating a primary key a child record : the engine will look into the master record in the primary key table
conclusion : so it’s always better to create a clustered index on the primary key and a non-clustered index on a foreign key
why there should be a clustered index on every primary key and non-clustered index on every foreign key
Refer above question.
give an example for the generalization
Generalization: when 2 or more entities shares the common attribute can be GENERALIZED into a higher level entity type.
E.g. Permanent (name) | Contractors (name)
Super Entity / Generalized entity is Employees (Login Name)







what is a surrogate key and how you will implement surrogate key in SQL Server
what are the advantages and disadvantage of having Identity Keys in SQL Server
basis on what you will define a identity key
Identity key (auto increment key) is an integer field with auto increment property. this can be act as a surrogate key when there is no primary key in the table or the length of the primary key is large.

Advantages of having a identity key is its auto increment, so we no need to enter a value in this field.
which NF the below table “customers” is in

ID(unique)
Name
Address
telephone
age
1
Albert
123, cb street,new jersey, usa
001999999
50

which NF the below table “customers” is in

ID(unique)
Name
street
apt
city
country
1
Albert
cb street
123
new jersey
USA

what is normalization define 1-NF, 2-NF,3NF
1NF – No Repetitive Groups , Each non-key attribute should functionally dependent on key attribute

2NF – (! Applies if it has composite Key) – It should be in 1NF and every non-key attribute should functionally dependent on the whole key (composite key , if exists) and NOT just part of the key

3NF (!Applies if it has computed columns or dependent columns on non-keys) – It should be 2NF, NO transitive dependency (No dependencies between non-key attributes)
what is a Heap Table and when will you make heap tables
A table without a clustered index is called a heap table
Though the table has non-clustered index , but with OUT a clustered index still considered as heap table
what is collation and how will you define a collation at column level
Collation means character set (language) . it could be USA English, zapan,etc..
give an example for 1-1 relationship
A table with large number of columns where some of the column groups has more nulls can be isolated to a different table one-one relationships to improve the performance on updates since it takes less space and more rows can be fit in single page belongs to the table
give an example for 1- many relationship
Database design interview question
Is your database is DBMS?
this is a very basic and a funny question J
Your database is not same as the database software (Oracle/SQL Server).
Its incorrect to say that my database is an DBMS
what is the difference between and when to choose what:

char
varchar
varchar
nvarchar
text
varchar(max)
complete create table syntax
performance tuning considerations
security considerations
Below are the most common mistakes :
Including sensitive information : passwords , SSN, DOB etc in the database and replacing the values with the binary – though a developer cant see the values by default , but by using a convert function he can get the results.

Its always recommended to use the encryption function with a key Eg:.openpgp function or through using sql server certificates or using a customized algorithm instead using binary datatype
Index – fill factor – pad index – page split


44 comments:

  1. Very Nice post, quiet informative.
    Database designing problems being the buzzwords these days, one of the most common designing mistakes is the “Poor Planning / Architecture” of the database or mart. Follow the link to know more…
    http://www.sqllion.com/2010/08/database-design-and-modeling-i/

    ReplyDelete
  2. Nice post... Sir

    SANTOSH

    ReplyDelete
  3. Very helpful post. Thank you.

    ReplyDelete
  4. I'm impressed, I must say. Seldom do I encounter a blog that's both equally educative and engaging,
    and let me tell you, you have hit the nail on the head.
    The problem is something too few folks are speaking intelligently about.
    Now i'm very happy I came across this during my hunt for something
    concerning this.

    ReplyDelete
  5. Hey there just wanted to give you a quick heads up.
    The words in your post seem to be running off
    the screen in Ie. I'm not sure if this is a format issue or something to do with internet browser compatibility but I thought I'd post to let you know.

    The layout look great though! Hope you get the issue
    resolved soon. Many thanks

    ReplyDelete
  6. Thank you for the auspicious writeup. It in fact was a amusement account it.
    Look advanced to far added agreeable from you!
    However, how can we communicate?

    ReplyDelete
  7. I would like to thank you for the efforts you've put in writing
    this website. I really hope to view the same high-grade content from you in the
    future as well. In truth, your creative writing abilities has encouraged me to get my own site now ;)

    ReplyDelete
  8. Hi there mates, nice paragraph and pleasant arguments commented at this place, I am actually enjoying by these.

    ReplyDelete
  9. I have been browsing online more than 2 hours today, yet I
    never found any interesting article like yours.

    It is pretty worth enough for me. In my opinion, if all web owners and bloggers made
    good content as you did, the net will be a lot more useful than ever before.

    ReplyDelete
  10. Once the roulette wheel is spun, the seller may be the one who will release the spinning
    ball. Despite this, amateurs and new on-line poker fans being quite knowledgeable about this advantage.
    The owners with this network devise that rule
    whose top most priority is always to give immense happiness to the clients.

    ReplyDelete
  11. But, 2010 is special for Delhi and a large number of sports persons, who
    will be likely to come here and engage in the games. Playing free addicting games or any game accessible on the internet can in fact provide
    enjoyment to the household. This is an open-source tool that allows that you modify variables inside your computer's memory.

    ReplyDelete
  12. The people entering these searches are usually people looking for chances to try out the said high bets roulette on the Internet, or
    people seeking to be aware of the procedure of playing such high
    roulette online. Prior to the beginning from the
    deal by the dealer, the gamers are required to lay down their bets about the Blackjack table.
    Who would believe that even slots can now be played using
    the internet.

    ReplyDelete
  13. Hello there, You've done an incredible job. I'll definitely
    digg it and individually recommend to my friends.
    I'm confident they'll be benefited from this website.

    ReplyDelete
  14. Regards for all your efforts that you have put in this.

    Very interesting info.

    ReplyDelete
  15. You may also add forum sections where people can leave their comments, questions,
    suggestions, and reactions. Much advancement has brought place ever since then, and from now on, anyone who wagers bets
    know that William Hill Casino is just one of Europe's leading online
    sports betting and also gaming sites, in addition to being you'll be able to rightly guess, their wide experience has earned for themselves a title for integrity and reliability.
    The following paragraph will explain much about some advices and
    methods for the ones from serious bettors.

    ReplyDelete
  16. It is widely accepted that inside bet can be
    a high stake - low chance bet while outside bet is often a low stake - high chance bet.

    Prior to the beginning of the deal by the dealer, the gamers have to lie down their bets about the Blackjack table.

    One reasons why the sport has captured a lot of people's interest is because, amidst its simplicity, it could elicit excitement and thrill from its
    players.

    ReplyDelete
  17. It is important to recover as much unprocessed trash, you'll be able to, you are able to input it in the project, you can use or even sold along with other players.
    Perhaps essentially the most potent spelling
    word games is always to challenge your children to write down their own folk stories, original
    poetry, or musicals using a tremendous number of their words.
    After they are grown, you will find the same amount of time for it to harvest them before
    linked with emotions . wither.

    ReplyDelete
  18. Thanks for one's marvelous posting! I actually enjoyed reading it, you happen to be a great
    author. I will make sure to bookmark your blog and may come back someday.
    I want to encourage yourself to continue your great job,
    have a nice evening!

    ReplyDelete
  19. Appreciate this post. Let me try it out.

    ReplyDelete
  20. Hi there, I discovered your blog by means of Google whilst looking for a similar matter, your web site got
    here up, it appears good. I have bookmarked it in my google bookmarks.

    Hello there, just become aware of your blog thru Google, and located that it's truly informative.
    I am gonna be careful for brussels. I will be grateful if you happen to proceed this in future.
    Lots of other people can be benefited from your writing.
    Cheers!

    ReplyDelete
  21. Even following your tournament was extended until 4AM Friday morning, the champion couldn't be decided.
    You should then look to begin with to look into the type of the
    rest of the players. The owners with this network devise that
    rule whose top most priority is usually to give immense happiness towards the clients.

    ReplyDelete
  22. Awesome site you have here but I was curious if you knew of any community forums that cover the same topics talked about in this article?
    I'd really love to be a part of community where I can get feed-back from other experienced individuals that share the
    same interest. If you have any suggestions, please let me know.
    Thanks a lot!

    ReplyDelete
  23. This post is worth everyone's attention. When can I find out more?

    ReplyDelete
  24. Cool blog! Is your theme custom made or did you download it from
    somewhere? A design like yours with a few simple adjustements would really
    make my blog stand out. Please let me know where you got
    your design. Bless you

    ReplyDelete
  25. J. et J. Soupré, Maisons du Pays basque, vol.

    ReplyDelete
  26. C'est un élément fondamental dans l'islam.

    ReplyDelete
  27. It's perfect time to make some plans for the future and it is
    timne to bbe happy. I have learn this publish and if I could I desire to counsel you
    some interesting things or advice. Maybe yoou could write subsequent articcles relating to this article.
    I want to learn more things about it!

    ReplyDelete
  28. I loved as much as you'll receive carried out right here.

    The sketch is tasteful, your authored material stylish.
    nonetheless, you command get got an edginess over that you wish be delivering the following.
    unwell unquestionably come further formerly again as exactly the same nearly a lot often inside case you shield this increase.

    ReplyDelete
  29. Good day! Do you know if they mame any plugins to protect against hackers?
    I'm kinda paranoid about losing everything I've worked hard on. Any suggestions?

    ReplyDelete
  30. This comment has been removed by a blog administrator.

    ReplyDelete
  31. Hello to every , as I am genuinely eager of reading this webpage's post
    to be updated regularly. It carries fastidious data.

    ReplyDelete
  32. Hey! Do you use Twitter? I'd like to follow you if that would be ok.
    I'm definitely enjoying your blog and look forward to new posts.

    ReplyDelete
  33. Téléréalité : Quand la réalité est un mensonge.

    ReplyDelete
  34. hey there and thank you for your information - I have certainly picked up something new from right here.
    I did however expertise several technical points using this
    site, as I experienced to reload the site lots of times previous to I could
    get it to load correctly. I had been wondering if
    your hosting is OK? Not that I am complaining, but slow loading instances times will sometimes affect your
    placement in google and can damage your high-quality score if advertising and marketing
    with Adwords. Anyway I am adding this RSS to my e-mail
    and could look out for a lot more of your respective exciting content.
    Ensure that you update this again very soon..

    ReplyDelete
  35. Great article! That is the type of information that
    are meant to be shared around the internet. Disgrace on the search engines for not
    positioning this submit higher! Come on over and consult with my web site .

    Thanks =)

    ReplyDelete
  36. Yesterday, while I was at work, my cousin stole my
    apple ipad and tested to see if it can survive a thirty foot drop, just
    so she can be a youtube sensation. My apple ipad is now
    broken and she has 83 views. I know this is entirely off topic but I had
    to share it with someone!

    ReplyDelete
  37. Marvelous, what a website it is! This web site gives useful
    data to us, keep it up.

    ReplyDelete
  38. Thanks for one's marvelous posting! I definitely enjoyed reading it,
    you are a great author.I will be sure to bookmark your blog and will come back in the future.
    I want to encourage yourself to continue your great writing, have a nice day!

    ReplyDelete
  39. Hello colleagues, its wonderful piece of writing regarding tutoringand fully
    defined, keep it up all the time.

    ReplyDelete
  40. I was suggested this website by my cousin. I am not sure whether this post is written by him as nobody else know such detailed about my problem.

    You are wonderful! Thanks!

    ReplyDelete
  41. I realoy like reading thgrough ann article that
    will make people think. Also, thanks for permitting me to comment!

    ReplyDelete
  42. Finest 37 Onlinne Casinos in Sweden 2019.

    ReplyDelete
  43. What's Going down i am new to this, I stumbled upon this I
    have discovered It positively useful and it has helped me out loads.
    I'm hoping to give a contribution & assist other users like its aided me.
    Great job.

    ReplyDelete
  44. I would like to thank you for the efforts you have put in penning this website.
    I'm hoping to view the same high-grade blog posts by you in the future as well.
    In fact, your creative writing abilities has encouraged me to get my very own website now ;
    )

    ReplyDelete

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate t...

Contributors