-- For the demonstration

USE tempdb;

GO

-- Drop any objects left over from previous runs

IF OBJECT_ID(N'dbo.PetColours', N'V') IS NOT NULL

DROP VIEW dbo.PetColours;

IF OBJECT_ID(N'dbo.GetPetColourString', N'IF') IS NOT NULL

DROP FUNCTION dbo.GetPetColourString;

IF OBJECT_ID(N'dbo.PetColourMap', N'U') IS NOT NULL

DROP TABLE dbo.PetColourMap;

IF OBJECT_ID(N'dbo.Pet', N'U') IS NOT NULL

DROP TABLE dbo.Pet;

IF OBJECT_ID(N'dbo.Colour', N'U') IS NOT NULL

DROP TABLE dbo.Colour;

IF OBJECT_ID(N'dbo.Animal', N'U') IS NOT NULL

DROP TABLE dbo.Animal;

IF OBJECT_ID(N'dbo.Sound', N'U') IS NOT NULL

DROP TABLE dbo.Sound;

GO

-- Create tables

CREATE TABLE dbo.Sound

(

sound_id INTEGER NOT NULL PRIMARY KEY,

name NVARCHAR(50) NOT NULL,

);

CREATE TABLE dbo.Animal

(

animal_id INTEGER NOT NULL PRIMARY KEY,

name NVARCHAR(50) NOT NULL,

leg_count SMALLINT NOT NULL,

has_fur BIT NOT NULL,

sound_id INTEGER NOT NULL REFERENCES dbo.Sound,

);

CREATE TABLE dbo.Colour

(

colour_id INTEGER NOT NULL PRIMARY KEY,

name NVARCHAR(50) NOT NULL,

)

CREATE TABLE dbo.Pet

(

pet_id INTEGER IDENTITY PRIMARY KEY,

name NVARCHAR(50) NOT NULL,

animal_id INTEGER NOT NULL REFERENCES dbo.Animal,

);

CREATE TABLE dbo.PetColourMap

(

map_id INTEGER IDENTITY PRIMARY KEY,

pet_id INTEGER NOT NULL REFERENCES dbo.Pet,

colour_id INTEGER NOT NULL REFERENCES dbo.Colour,

)

GO

-- For foreign keys

CREATE NONCLUSTERED INDEX [IX dbo.Animal sound_id] ON dbo.Animal (sound_id);

CREATE NONCLUSTERED INDEX [IX dbo.Pet animal_id] ON dbo.Pet (animal_id);

CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.PetColourMap pet_id, colour_id] ON dbo.PetColourMap (pet_id, colour_id);

CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.PetColourMap colour_id, pet_id)] ON dbo.PetColourMap (colour_id, pet_id);

GO

-- Define sounds

INSERT dbo.Sound (sound_id, name) VALUES (0, N'silent');

INSERT dbo.Sound (sound_id, name) VALUES (1, N'ruff ruff');

INSERT dbo.Sound (sound_id, name) VALUES (2, N'hiss');

INSERT dbo.Sound (sound_id, name) VALUES (3, N'oink');

INSERT dbo.Sound (sound_id, name) VALUES (4, N'meow');

INSERT dbo.Sound (sound_id, name) VALUES (5, N'eek eek');

-- Define colours

INSERT dbo.Colour (colour_id, name) VALUES (0, N'black');

INSERT dbo.Colour (colour_id, name) VALUES (1, N'white');

INSERT dbo.Colour (colour_id, name) VALUES (2, N'brown');

INSERT dbo.Colour (colour_id, name) VALUES (3, N'green');

INSERT dbo.Colour (colour_id, name) VALUES (4, N'pink');

INSERT dbo.Colour (colour_id, name) VALUES (5, N'tabby');

INSERT dbo.Colour (colour_id, name) VALUES (6, N'dark brown');

INSERT dbo.Colour (colour_id, name) VALUES (7, N'lime green');

-- Define animals

INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)

VALUES (1, N'dog', 4, 1, 1);

INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)

VALUES (2, N'snake', 0, 0, 2);

INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)

VALUES (3, N'pig', 4, 0, 3);

INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)

VALUES (4, N'cat', 4, 1, 4);

INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)

VALUES (5, N'monkey', 4, 1, 5);

INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)

VALUES (6, N'iguana', 4, 0, 0);

-- Create pets

INSERT dbo.Pet (name, animal_id) VALUES (N'Spike', 1);

INSERT dbo.Pet (name, animal_id) VALUES (N'Rex', 1);

INSERT dbo.Pet (name, animal_id) VALUES (N'Slither', 2);

INSERT dbo.Pet (name, animal_id) VALUES (N'Wilbur', 3);

INSERT dbo.Pet (name, animal_id) VALUES (N'Fluffy', 4);

INSERT dbo.Pet (name, animal_id) VALUES (N'Hunter', 4);

INSERT dbo.Pet (name, animal_id) VALUES (N'Mr. Biggles', 5);

INSERT dbo.Pet (name, animal_id) VALUES (N'Godzilla', 6);

-- Add entries to the pet colour mapping table

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (1, 0);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (1, 2);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (2, 0);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (2, 1);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (3, 3);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (4, 4);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (5, 0);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (5, 1);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (6, 5);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (7, 6);

INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (8, 7);

GO

-- An in-line table-valued function to combine pet colours

-- into a delimited string with guaranteed order of colours

CREATE FUNCTION dbo.GetPetColourString (@PetID INTEGER)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT pet_colours =

STUFF(

Colours.xml_string.value('./text()[1]', 'NVARCHAR(100)')

, 1, 1, N'')

FROM (

SELECT N'/' + C.name

FROM dbo.PetColourMap CM

JOIN dbo.Colour C

ON C.colour_id = CM.colour_id

WHERE CM.pet_id = @PetID

ORDER BY

C.colour_id ASC

FOR XML PATH(''), TYPE

) Colours (xml_string);

GO

CREATE VIEW dbo.PetColours

AS

SELECT P.pet_id,

P.name,

PCS.pet_colours

FROM dbo.Pet P

CROSS

APPLY dbo.GetPetColourString (P.pet_id) PCS;

GO

-- Pet names and sounds made

SELECT pet_name = P.name,

sound_name = S.name

FROM dbo.Pet P

JOIN dbo.Animal A

ON A.animal_id = P.animal_id

JOIN dbo.Sound S

ON S.sound_id = A.sound_id;

-- Pets and colours

SELECT PC.pet_id,

pet_name = PC.name,

PC.pet_colours

FROM dbo.PetColours PC

WHERE pet_colours = N'black/white';

GO

-- Tidy up

DROP VIEW dbo.PetColours;

DROP FUNCTION dbo.GetPetColourString;

DROP TABLE dbo.PetColourMap;

DROP TABLE dbo.Pet;

DROP TABLE dbo.Colour;

DROP TABLE dbo.Animal;

DROP TABLE dbo.Sound;

-- End script

Ok, so I was bored :laugh: