继续请教一个面试题目,高手帮忙啊,先谢了!
Scenario:
A   company   has   several   small   tables   in   their   database.   These   tables   consisted   of   two   columns   and   less   than   one   thousand   rows.  

Below   an   example   of   table   schema,
create   table   hairColor
(
hairColorID int   identity(1,1),
description varchar(16)   not   null

)
Go
insert   into   hairColor   values(‘Brown’)
insert   into   hairColor   values(‘Blonde’)


create   table   eyeColor
(
eyeColorID int   identity(1,1),
name varchar(10)   not   null

)
Go
insert   into   hairColor   values(‘Black’)
insert   into   hairColor   values(‘Grey’)

create   table   submitStatus
(
statusID int   identity(1,1),
description varchar(30)   not   null

)
Go

Question:
How   would   you   combine   these   tables   into   one   table   and   enforce   the   data   integrity?   Please   provide   the   table   schema   and   few   insert   statement   to   demonstration   how   to   fill   the   table.


------解决方案--------------------
create table T
(
id int identity(1,1),
description varchar(30) not null,
flag varchar(02) not null
)

insert into T(description,flag)
select 'Brown ', 'H ' union all
select 'Blonde ', 'H ' union all
select 'Black ', 'E ' union all
select 'Gray ', 'E ' union all
select 'xxx ', 'S '
.....
------解决方案--------------------
create table T
(
id int identity(1,1),
description varchar(30) not null,
eyeColorID int not null,
hairColorID int not null,
statusID int not null
)
------解决方案--------------------
create table hairColor
(
hairColorID int identity(1,1),
description varchar(16) not null

)
Go
insert into hairColor values(‘Brown’)
insert into hairColor values(‘Blonde’)


create table eyeColor
(
eyeColorID int identity(1,1),
name varchar(10) not null

)
Go
insert into hairColor values(‘Black’)
insert into hairColor values(‘Grey’)

create table submitStatus
(
statusID int identity(1,1),
description varchar(30) not null

)
Go

select id=identity(int,1,1),* into #t from
(
select description,1 as flag from hairColor
union all
select description,2 from eyeColor
union all
select description,3 from submitStatus


)a