CREATE TABLE [CATEGORIE] (
    [CATEGORIE_ID] INT PRIMARY KEY,
    [CODE] INT,
    [NAAM] VARCHAR(80),
    [OMSCHRIJVING] NVARCHAR(MAX)
);

CREATE TABLE [LOCATIE] (
    [LOCATIE_ID] INT PRIMARY KEY,
    [ADRESAANDUIDING] VARCHAR(255),
    [LOCATIECODE] VARCHAR(255),
    [LOCATIEPUNT] VARCHAR(255)
);

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

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

CREATE TABLE [AANVRAAGOFMELDING] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY,
    [AFGEHANDELD] BIT,
    [CATEGORIE] VARCHAR(200),
    [CATEGORIECODE] VARCHAR(80),
    [DATUMAANMAAK] DATE,
    [DATUMAFHANDELING] DATE,
    [DATUMBEGINSTATUS] DATE,
    [DATUMEINDESTATUS] DATE,
    [HOOFDCATEGORIE] VARCHAR(80),
    [HOOFDCATEGORIECODE] VARCHAR(80),
    [IDENTIFICATIE] VARCHAR(255),
    [KANAAL] VARCHAR(80),
    [ONDERWERP] VARCHAR(200),
    [ONDERWERPCODE] VARCHAR(80),
    [SOORT] VARCHAR(80),
    [STATUS] VARCHAR(80),
    [STATUSCODE] VARCHAR(80),
    [STATUSVOLGORDE] VARCHAR(80),
    [SUBCATEGORIE] VARCHAR(200),
    [SUBCATEGORIECODE] VARCHAR(80)
);

CREATE TABLE [MELDING] (
    [AANVRAAGOFMELDING_ID] INT PRIMARY KEY,
    [24UURS] BIT,
    [BETREFT_CONTAINERTYPE_ID] INT,
    [BETREFT_FRACTIE_ID] INT,
    [BETREFT_LOCATIE_ID] INT,
    [DATUMTIJD] DATETIME2,
    [HOOFDCATEGORIE_CATEGORIE_ID] INT,
    [ILLEGAAL] BIT,
    [MELDINGNUMMER] VARCHAR(255),
    [OMSCHRIJVING] NVARCHAR(MAX),
    [SUBCATEGORIE_CATEGORIE_ID] INT
);

CREATE TABLE [KP_MLDNG_ANVRGFMLDNG] (
    [KP_MLDNG_ANVRGFMLDNG_ID] INT PRIMARY KEY,
    [MELDING_ID] INT,
    [AANVRAAGOFMELDING_ID] INT
);

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_GEN_AANVRAAGOFMELDING FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [AANVRAAGOFMELDING] ([AANVRAAGOFMELDING_ID]);
-- Generalization to AANVRAAGOFMELDING

ALTER TABLE [KP_MLDNG_ANVRGFMLDNG] ADD CONSTRAINT FK_KP_MLDNG_ANVRGFMLDNG_1 FOREIGN KEY ([MELDING_ID]) REFERENCES [MELDING] ([AANVRAAGOFMELDING_ID]);
-- MELDING (many-to-many junction, Connector_ID: 819)

ALTER TABLE [KP_MLDNG_ANVRGFMLDNG] ADD CONSTRAINT FK_KP_MLDNG_ANVRGFMLDNG_2 FOREIGN KEY ([AANVRAAGOFMELDING_ID]) REFERENCES [AANVRAAGOFMELDING] ([AANVRAAGOFMELDING_ID]);
-- AANVRAAGOFMELDING (many-to-many junction, Connector_ID: 819)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_BETREFT FOREIGN KEY ([BETREFT_LOCATIE_ID]) REFERENCES [LOCATIE] ([LOCATIE_ID]);
-- BETREFT (0..* to 1, Connector_ID: 821)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_BETREFT_2 FOREIGN KEY ([BETREFT_FRACTIE_ID]) REFERENCES [FRACTIE] ([FRACTIE_ID]);
-- BETREFT (0..* to 0..1, Connector_ID: 827)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_BETREFT_3 FOREIGN KEY ([BETREFT_CONTAINERTYPE_ID]) REFERENCES [CONTAINERTYPE] ([CONTAINERTYPE_ID]);
-- BETREFT (0..* to 0..1, Connector_ID: 828)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_SUBCATEGORIE FOREIGN KEY ([SUBCATEGORIE_CATEGORIE_ID]) REFERENCES [CATEGORIE] ([CATEGORIE_ID]);
-- SUBCATEGORIE (0..* to 1, Connector_ID: 835)

ALTER TABLE [MELDING] ADD CONSTRAINT FK_MELDING_HOOFDCATEGORIE FOREIGN KEY ([HOOFDCATEGORIE_CATEGORIE_ID]) REFERENCES [CATEGORIE] ([CATEGORIE_ID]);
-- HOOFDCATEGORIE (0..* to 1, Connector_ID: 836)