How do you implement one-to-one,
one-to-many and many-to-many relationships while designing
tables?
One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data
into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction
table with the keys from both the tables forming the composite
primary key of the junction table.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on
which they are defined. But by default primary key creates a
clustered index on the column, where are unique creates a
nonclustered index by default. Another major difference is that,
primary key doesn't allow NULLs, but unique key allows one NULL
only.
What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server
datatypes by providing a descriptive name, and format to the
database. Take for example, in your database, there is a column
called Flight_Num which appears in many tables. In all these
tables it should be varchar(8). In this case you could create a
user defined datatype called Flight_num_type of varchar(8) and
use it across all your tables.
What is bit datatype and what's the information that can be
stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0
(true or false). Untill SQL Server 6.5 bit datatype could hold
either a 1 or 0 and there was no support for NULL. But from SQL
Server 7.0 onwards, bit datatype can represent a third state,
which is NULL.
Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table
uniquely. Generally a candidate key becomes the primary key of
the table. If the table has more than one candidate key, one of
them will become the primary key, and the rest are called
alternate keys.
A key formed by combining at least two or more columns is called
composite key.
What are defaults? Is there a column to which a default can't be
bound?
A default is a value that will be used by a column, if no value
is supplied to that column while inserting data. IDENTITY
columns and timestamp columns can't have defaults bound to them.
|
