Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE Species ( > SpeciesCode CHAR(1) NOT NULL, > Name CHAR(30) NOT NULL, > -- > CONSTRAINT Species_PK PRIMARY KEY (SpeciesCode), > CONSTRAINT Species_AK UNIQUE (Name) > ); > > CREATE TABLE Animal ( > AnimalId INT NOT NULL IDENTITY (1,1), > SpeciesCode CHAR(1) NOT NULL, > CreatedDateTime DATETIME NOT NULL, > -- > CONSTRAINT Animal_PK PRIMARY KEY (AnimalId), > CONSTRAINT Animal_to_Species_FK FOREIGN KEY (SpeciesCode) > REFERENCES Species (SpeciesCode) > ); > > CREATE TABLE Fox ( > FoxId INT NOT NULL, > BusinessId VARCHAR(50) NOT NULL, > BodyLength INT NOT NULL, > FurColour VARCHAR NOT NULL, > -- > CONSTRAINT Fox_PK PRIMARY KEY (FoxId), > CONSTRAINT Fox_AK UNIQUE (BusinessId), > CONSTRAINT Fox_to_Animal_FK FOREIGN KEY (FoxId) > REFERENCES Animal (AnimalId) > ); > > CREATE TABLE Elephant ( > ElephantId INT NOT NULL, > BusinessId VARCHAR(50) NOT NULL, > Weight INT NOT NULL, > TuskLength INT NOT NULL, > -- > CONSTRAINT Elephant_PK PRIMARY KEY (ElephantId), > CONSTRAINT Elephant_AK UNIQUE (BusinessId), > CONSTRAINT Elephant_to_Animal_FK FOREIGN KEY (ElephantId) > REFERENCES Animal (AnimalId) > ); > > CREATE TABLE Item ( > ItemSeq INT NOT NULL IDENTITY (1,1), > AnimalId INT NOT NULL, > Description VARCHAR(500) NOT NULL, > CreatedDateTime DATETIME NOT NULL, > -- > CONSTRAINT Item_PK PRIMARY KEY (ItemSeq), > CONSTRAINT Item_to_Animal_FK FOREIGN KEY (AnimalId) > REFERENCES Animal (AnimalId) > ); > GO > > <pre> > ✓ > </pre> <!-- --> > INSERT INTO Species > (SpeciesCode, Name) > VALUES > ('F', 'Fox'), > ('E', 'Elephant'); > GO > > <pre> 2 rows affected > </pre> <!-- --> > SELECT SpeciesCode, > Name > FROM Species; > GO > > <pre> > SpeciesCode | Name > :---------- | :----------------------------- > E | Elephant > F | Fox > </pre> <!-- --> > CREATE VIEW FullFox AS > SELECT A.AnimalId, > F.BusinessId, > F.BodyLength, > F.FurColour, > A.CreatedDateTime > FROM Fox F > JOIN Animal A > ON F.FoxId = A.AnimalId; > GO > > <pre> > ✓ > </pre> <!-- --> > CREATE VIEW FullElephant AS > SELECT A.AnimalId, > E.BusinessId, > E.Weight, > E.TuskLength, > A.CreatedDateTime > FROM Elephant E > JOIN Animal A > ON E.ElephantId = A.AnimalId; > GO > > <pre> > ✓ > </pre> <!-- --> > CREATE VIEW ItemWithAnimal AS > SELECT I.ItemSeq, > A.AnimalId, > A.SpeciesCode, > I.Description, > I.CreatedDateTime AS ItemCreatedDateTime, > A.CreatedDateTime AS AnimalCreatedDateTime > FROM Item I > JOIN Animal A > ON I.AnimalId = A.AnimalId; > GO > > <pre> > ✓ > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bd20b53bc5285ac212e283f7644619ad)*
back to fiddle