CREATE TABLE Company ( CompanyId INT NOT NULL, Name TEXT NOT NULL, FoundingDate DATE NOT NULL, Etcetera TEXT NOT NULL, CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- CONSTRAINT Company_PK PRIMARY KEY (CompanyId), CONSTRAINT Company_AK UNIQUE (Name) ); CREATE TABLE Person ( PersonId INT NOT NULL, FirstName TEXT NOT NULL, LastName TEXT NOT NULL, GenderCode TEXT NOT NULL, BirthDate DATE NOT NULL, BirthPlace TEXT NOT NULL, Address TEXT NOT NULL, PhoneNumber TEXT NOT NULL, CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- CONSTRAINT Person_PK PRIMARY KEY (PersonId), CONSTRAINT Person_AK UNIQUE ( FirstName, LastName, GenderCode, BirthDate, BirthPlace ) ); CREATE TABLE CompanyOwner ( CompanyId INT NOT NULL, OwnerId INT NOT NULL, StockShareQuantity INT NOT NULL, Etcetera TEXT NOT NULL, CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- CONSTRAINT CompanyOwner_PK PRIMARY KEY (CompanyId, OwnerId), CONSTRAINT CompanyOwnerToCompany_FK FOREIGN KEY (CompanyId) REFERENCES Company (CompanyId), CONSTRAINT CompanyOwnerToPerson_FK FOREIGN KEY (OwnerId) REFERENCES Person (PersonId), CONSTRAINT StockShareQtyIsValid_CK CHECK (StockShareQuantity >= 0) ); CREATE VIEW CompanyAndOwner AS SELECT C.CompanyId, C.Name AS CompanyName, P.PersonId, P.FirstName, P.LastName, P.BirthDate, P.BirthPlace, P.GenderCode, P.Address, P.PhoneNumber, CO.StockShareQuantity, P.CreatedDateTime FROM Person P JOIN CompanyOwner CO ON CO.OwnerId = P.PersonId JOIN Company C ON C.CompanyId = CO.CompanyId;

INSERT INTO Company (CompanyId, Name, FoundingDate, Etcetera) VALUES (1748, 'Database Modeling Inc.', '1985-06-30', 'Foo'), (1750, 'Application Programming Co.', '1987-10-14', 'Bar'); INSERT INTO Person (PersonId, FirstName, LastName, BirthDate, BirthPlace, GenderCode, Address, PhoneNumber) VALUES (1, 'Edgar', 'Codd', '1923-08-19', 'Fortuneswell, UK', 'M', 'IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA', '01-800-17-50-17-50'), (2, 'Alan', 'Turing', '1912-06-23', 'Maida Vale, UK','M', 'National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England', '01-800-17-48-17-48'), (3, 'Grace', 'Hopper', '1906-12-09', 'New York City, USA', 'F', 'Navy’s Office of Information Systems Planning, USA.', '01-800-17-50-17-50'), (4, 'Diego', 'Velázquez', '1599-06-06', 'Seville, Spain', 'M', 'Palacio Real, Madrid, Spain', '01-800-17-50-17-50'), (5, 'Michelangelo', 'Buonarroti', '1475-03-06', 'Caprese, Italy', 'M', 'Sistine Chapel, Vatican City State', '01-800-17-50-17-50'); INSERT INTO CompanyOwner (CompanyId, OwnerId, StockShareQuantity, Etcetera) VALUES (1748, 1, 2500, 'V'), (1750, 2, 8000, 'W'), (1750, 3, 3580, 'X'), (1748, 4, 12899, 'Y'), (1750, 5, 12899, 'Z');

SELECT * FROM CompanyAndOwner WHERE CompanyId = 1750;

SELECT CompanyName, FirstName AS OwnerFirstName, LastName AS OwnerLastName, StockShareQuantity FROM CompanyAndOwner WHERE CompanyId = 1750;

SELECT * FROM CompanyAndOwner WHERE CompanyId = 1748;

SELECT CompanyName, FirstName AS OwnerFirstName, LastName AS OwnerLastName, StockShareQuantity FROM CompanyAndOwner WHERE CompanyId = 1748;

SELECT COUNT(OwnerId) AS OwnedCompaniesQuantity FROM CompanyOwner WHERE OwnerId = 1;