CREATE TABLE [OMZETGROEP] (
    [OMZETGROEP_ID] INT PRIMARY KEY,
    [NAAM] VARCHAR(80),
    [OMSCHRIJVING] NVARCHAR(MAX)
);

CREATE TABLE [LEVERANCIER] (
    [LEVERANCIER_ID] INT PRIMARY KEY,
    [NAAM] VARCHAR(200),
    [NUMMER] VARCHAR(255)
);

CREATE TABLE [PRODUCTGROEP] (
    [PRODUCTGROEP_ID] INT PRIMARY KEY,
    [NAAM] VARCHAR(80),
    [OMSCHRIJVING] NVARCHAR(MAX)
);

CREATE TABLE [PRODUCT] (
    [PRODUCT_ID] INT PRIMARY KEY,
    [CODEMUSEUMJAARKAART] VARCHAR(255),
    [DATUMEINDEGELDIGHEID] DATE,
    [DATUMSTART] DATE,
    [ENTREEKAART] INT,
    [LEVERANCIER_LEVERANCIER_ID] INT,
    [OMSCHRIJVING] VARCHAR(80),
    [PRIJS] DECIMAL(10,2)
);

CREATE TABLE [KP_PRDCT_PRDCTGRP] (
    [KP_PRDCT_PRDCTGRP_ID] INT PRIMARY KEY,
    [PRODUCT_ID] INT,
    [PRODUCTGROEP_ID] INT
);

CREATE TABLE [BALIEVERKOOP] (
    [BALIEVERKOOP_ID] INT PRIMARY KEY,
    [AANTAL] INT,
    [BETREFT_PRODUCT_ID] INT,
    [KANAAL] VARCHAR(80),
    [VERKOOPTIJD] DATETIME2
);

CREATE TABLE [ENTREEKAART] (
    [PRODUCT_ID] INT PRIMARY KEY,
    [RONDLEIDING] BIT
);

CREATE TABLE [KP_PRDCT_OMZTGRP] (
    [KP_PRDCT_OMZTGRP_ID] INT PRIMARY KEY,
    [PRODUCT_ID] INT,
    [OMZETGROEP_ID] INT
);

CREATE TABLE [WINKELVOORRAADITEM] (
    [WINKELVOORRAADITEM_ID] INT PRIMARY KEY,
    [AANTAL] INT,
    [AANTALINBESTELLING] INT,
    [BETREFT_PRODUCT_ID] INT,
    [DATUMLEVERINGBESTELLING] DATE,
    [LOCATIE] VARCHAR(80)
);

CREATE TABLE [BALIEVERKOOP_ENTREEKAART] (
    [BALIEVERKOOP_ID] INT PRIMARY KEY,
    [DATUMEINDEGELDIGHEID] DATE,
    [DATUMSTART] DATE,
    [GEBRUIKTOP] VARCHAR(255),
    [RONDLEIDING] BIT
);

CREATE TABLE [KP_ENTRKRT_PRDCT] (
    [KP_ENTRKRT_PRDCT_ID] INT PRIMARY KEY,
    [ENTREEKAART_ID] INT,
    [PRODUCT_ID] INT
);

CREATE TABLE [KP_BLVRKP_ENTRKRT_BLVRKP] (
    [KP_BLVRKP_ENTRKRT_BLVRKP_ID] INT PRIMARY KEY,
    [BALIEVERKOOP_ENTREEKAART_ID] INT,
    [BALIEVERKOOP_ID] INT
);

ALTER TABLE [BALIEVERKOOP_ENTREEKAART] ADD CONSTRAINT FK_BALIEVERKOOP_ENTREEKAART_GEN_BALIEVERKOOP FOREIGN KEY ([BALIEVERKOOP_ID]) REFERENCES [BALIEVERKOOP] ([BALIEVERKOOP_ID]);
-- Generalization to BALIEVERKOOP

ALTER TABLE [ENTREEKAART] ADD CONSTRAINT FK_ENTREEKAART_GEN_PRODUCT FOREIGN KEY ([PRODUCT_ID]) REFERENCES [PRODUCT] ([PRODUCT_ID]);
-- Generalization to PRODUCT

ALTER TABLE [KP_PRDCT_PRDCTGRP] ADD CONSTRAINT FK_KP_PRDCT_PRDCTGRP_1 FOREIGN KEY ([PRODUCT_ID]) REFERENCES [PRODUCT] ([PRODUCT_ID]);
-- PRODUCT (many-to-many junction, Connector_ID: 1112)

ALTER TABLE [KP_PRDCT_PRDCTGRP] ADD CONSTRAINT FK_KP_PRDCT_PRDCTGRP_2 FOREIGN KEY ([PRODUCTGROEP_ID]) REFERENCES [PRODUCTGROEP] ([PRODUCTGROEP_ID]);
-- PRODUCTGROEP (many-to-many junction, Connector_ID: 1112)

ALTER TABLE [KP_PRDCT_OMZTGRP] ADD CONSTRAINT FK_KP_PRDCT_OMZTGRP_1 FOREIGN KEY ([PRODUCT_ID]) REFERENCES [PRODUCT] ([PRODUCT_ID]);
-- PRODUCT (many-to-many junction, Connector_ID: 1114)

ALTER TABLE [KP_PRDCT_OMZTGRP] ADD CONSTRAINT FK_KP_PRDCT_OMZTGRP_2 FOREIGN KEY ([OMZETGROEP_ID]) REFERENCES [OMZETGROEP] ([OMZETGROEP_ID]);
-- OMZETGROEP (many-to-many junction, Connector_ID: 1114)

ALTER TABLE [KP_ENTRKRT_PRDCT] ADD CONSTRAINT FK_KP_ENTRKRT_PRDCT_1 FOREIGN KEY ([ENTREEKAART_ID]) REFERENCES [ENTREEKAART] ([PRODUCT_ID]);
-- ENTREEKAART (many-to-many junction, Connector_ID: 1123)

ALTER TABLE [KP_ENTRKRT_PRDCT] ADD CONSTRAINT FK_KP_ENTRKRT_PRDCT_2 FOREIGN KEY ([PRODUCT_ID]) REFERENCES [PRODUCT] ([PRODUCT_ID]);
-- PRODUCT (many-to-many junction, Connector_ID: 1123)

ALTER TABLE [KP_BLVRKP_ENTRKRT_BLVRKP] ADD CONSTRAINT FK_KP_BLVRKP_ENTRKRT_BLVRKP_1 FOREIGN KEY ([BALIEVERKOOP_ENTREEKAART_ID]) REFERENCES [BALIEVERKOOP_ENTREEKAART] ([BALIEVERKOOP_ID]);
-- BALIEVERKOOP_ENTREEKAART (many-to-many junction, Connector_ID: 1132)

ALTER TABLE [KP_BLVRKP_ENTRKRT_BLVRKP] ADD CONSTRAINT FK_KP_BLVRKP_ENTRKRT_BLVRKP_2 FOREIGN KEY ([BALIEVERKOOP_ID]) REFERENCES [BALIEVERKOOP] ([BALIEVERKOOP_ID]);
-- BALIEVERKOOP (many-to-many junction, Connector_ID: 1132)

ALTER TABLE [WINKELVOORRAADITEM] ADD CONSTRAINT FK_WINKELVOORRAADITEM_BETREFT FOREIGN KEY ([BETREFT_PRODUCT_ID]) REFERENCES [PRODUCT] ([PRODUCT_ID]);
-- BETREFT (0..1 to 1, Connector_ID: 1110)

ALTER TABLE [PRODUCT] ADD CONSTRAINT FK_PRODUCT_LEVERANCIER FOREIGN KEY ([LEVERANCIER_LEVERANCIER_ID]) REFERENCES [LEVERANCIER] ([LEVERANCIER_ID]);
-- LEVERANCIER (0..* to 0..1, Connector_ID: 1111)

ALTER TABLE [BALIEVERKOOP] ADD CONSTRAINT FK_BALIEVERKOOP_BETREFT FOREIGN KEY ([BETREFT_PRODUCT_ID]) REFERENCES [PRODUCT] ([PRODUCT_ID]);
-- BETREFT (0..* to 1, Connector_ID: 1133)