MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    -- 
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username)
);

CREATE TABLE Category (
    CategoryCode     CHAR(1)  NOT NULL,
    Name             CHAR(30) NOT NULL,
    -- 
    CONSTRAINT Category_PK PRIMARY KEY (CategoryCode),
    CONSTRAINT Category_AK UNIQUE      (Name)
);

CREATE TABLE Item (
    ItemId           INT      NOT NULL,
    OwnerId          INT      NOT NULL,
    CategoryCode     CHAR(1)  NOT NULL,
    Foo              CHAR(30) NOT NULL,
    Bar              CHAR(40) NOT NULL,
    Baz              CHAR(55) NOT NULL,  
    CreatedDateTime  DATETIME NOT NULL,
    -- 
    CONSTRAINT Item_PK             PRIMARY KEY (ItemId),
    CONSTRAINT Item_to_Category_FK FOREIGN KEY (CategoryCode)
        REFERENCES Category    (CategoryCode),
    CONSTRAINT Item_to_User_FK     FOREIGN KEY (OwnerId)
        REFERENCES UserProfile (UserId)  
);

CREATE TABLE Car (
    CarId INT          NOT NULL,
    Qux   DATE         NOT NULL,
    Corge DECIMAL(5,2) NOT NULL,   
    -- 
    CONSTRAINT Car_PK         PRIMARY KEY (CarId),
    CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidQux_CK    CHECK       (Qux >= '1990-01-01')        
);

CREATE TABLE Boat (
    BoatId INT      NOT NULL,
    Grault SMALLINT NOT NULL,
    Garply DATETIME NOT NULL,   
    Plugh  CHAR(63) NOT NULL,    
    -- 
    CONSTRAINT Boat_PK         PRIMARY KEY (BoatId),
    CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidGrault_CK  CHECK       (Grault <= 10000)
);

CREATE TABLE Plane (
    PlaneId INT      NOT NULL,
    Xyzzy   BIGINT   NOT NULL,
    Thud    TEXT     NOT NULL,  
    Wibble  CHAR(20) NOT NULL, 
    Flob    BIT(1)   NOT NULL,   
    -- 
    CONSTRAINT Plane_PK         PRIMARY KEY (PlaneId),
    CONSTRAINT Plane_to_Item_PK FOREIGN KEY (PlaneId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidXyzzy_CK    CHECK       (Xyzzy <= 3258594758) 
);
INSERT INTO UserProfile 
  (UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
  (1, 'Edgar', 'Codd', '1923-08-19', 'M', 'ted.codd', CURDATE()),
  (2, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M', 'michelangelo', CURDATE()),
  (3, 'Diego', 'Velázquez', '1599-06-06', 'M', 'd.velazquez', CURDATE());

INSERT INTO Category 
  (CategoryCode, Name)
VALUES
  ('C', 'Car'), ('B', 'Boat'), ('P', 'Plane');

INSERT INTO Item
  (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
  (1, 1, 'C', 'Motorway', 'Tire', 'Chauffeur', CURDATE());
    
INSERT INTO Car
  (CarId, Qux, Corge)
VALUES
  (1, '1999-06-11',  999.99);

INSERT INTO Item
  (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
  (2, 2, 'B', 'Ocean', 'Anchor', 'Sailor', CURDATE());

INSERT INTO Boat
  (BoatId, Grault, Garply, Plugh)
VALUES
  (2, 10000, '2016-03-09 07:32:04.000', 'So far so good.');

INSERT INTO Item
  (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
  (3, 3, 'P', 'Sky', 'Wing', 'Aviator', CURDATE());
    
INSERT INTO Plane
  (PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
  (3, 3258594758, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris. ', 'Here we go!', TRUE);

Records: 3  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


CREATE VIEW CarAndOwner AS
    SELECT C.CarId,
           I.Foo,
           I.Bar,
           I.Baz,
           C.Qux,
           C.Corge,           
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Car C
          ON C.CarId = I.ItemId
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;
CREATE VIEW FullItemAndOwner AS
    SELECT I.ItemId,
           I.Foo,
           I.Bar,
           I.Baz,
           IC.Name     AS Category,
           C.Qux,
           C.Corge,
           B.Grault,
           B.Garply,
           B.Plugh,
           P.Xyzzy,
           P.Thud,
           P.Wibble,
           P.Flob,
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Category IC
          ON I.CategoryCode = IC.CategoryCode
   LEFT JOIN Car C
          ON C.CarId = I.ItemId
   LEFT JOIN Boat B
          ON B.BoatId = I.ItemId
   LEFT JOIN Plane P
          ON P.PlaneId = I.ItemId               
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;
UPDATE CarAndOwner
   SET Corge = 333.15
 WHERE CarId = 1;

Rows matched: 1  Changed: 1  Warnings: 0


SELECT * FROM CarAndOwner;
SELECT * FROM FullItemAndOwner;


CarId
Foo
Bar
Baz
Qux
Corge
OwnerFirstName
OwnerLastName


1
Motorway
Tire
Chauffeur
1999-06-11
333.15
Edgar
Codd




ItemId
Foo
Bar
Baz
Category
Qux
Corge
Grault
Garply
Plugh
Xyzzy
Thud
Wibble
Flob
OwnerFirstName
OwnerLastName


1
Motorway
Tire
Chauffeur
Car
1999-06-11
333.15
null
null
null
null
null
null
null
Edgar
Codd

2
Ocean
Anchor
Sailor
Boat
null
null
10000
2016-03-09 07:32:04
So far so good.
null
null
null
null
Michelangelo
Buonarroti

3
Sky
Wing
Aviator
Plane
null
null
null
null
null
3258594758
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris.
Here we go!
1
Diego
Velázquez

ItemId	Foo	Bar	Baz	Category	Qux	Corge	Grault	Garply	Plugh	Xyzzy	Thud	Wibble	Flob	OwnerFirstName	OwnerLastName
1	Motorway	Tire	Chauffeur	Car	1999-06-11	333.15	null	null	null	null	null	null	null	Edgar	Codd
2	Ocean	Anchor	Sailor	Boat	null	null	10000	2016-03-09 07:32:04	So far so good.	null	null	null	null	Michelangelo	Buonarroti
3	Sky	Wing	Aviator	Plane	null	null	null	null	null	3258594758	Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris.	Here we go!	1	Diego	Velázquez