-- 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