database.sql

-- =============================================
-- AutoGarage Formatief Database Setup Script
-- =============================================

-- Create database if it doesn't exist
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'AutoGarageFormatief')
BEGIN
    CREATE DATABASE [AutoGarageFormatief];
END
GO

USE [AutoGarageFormatief];
GO

-- Drop tables if they exist (for clean re-run)
IF OBJECT_ID('dbo.Vehicle', 'U') IS NOT NULL
    DROP TABLE [dbo].[Vehicle];
GO

IF OBJECT_ID('dbo.CarOwner', 'U') IS NOT NULL
    DROP TABLE [dbo].[CarOwner];
GO

-- Create CarOwner table
CREATE TABLE [dbo].[CarOwner](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    CONSTRAINT [PK_CarOwner] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

-- Create Vehicle table
CREATE TABLE [dbo].[Vehicle](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](50) NULL,
    [LicensePlate] [varchar](8) NOT NULL,
    [TowingWeight] [int] NULL,
    [CarOwnerId] [int] NOT NULL,
    CONSTRAINT [PK_Vehicle] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

-- Add foreign key constraint
ALTER TABLE [dbo].[Vehicle]
ADD CONSTRAINT [FK_Vehicle_CarOwner] FOREIGN KEY([CarOwnerId])
REFERENCES [dbo].[CarOwner] ([Id]);
GO

-- Insert sample data - CarOwners
SET IDENTITY_INSERT [dbo].[CarOwner] ON;
INSERT INTO [dbo].[CarOwner] ([Id], [Name]) VALUES (1, 'Rob');
INSERT INTO [dbo].[CarOwner] ([Id], [Name]) VALUES (2, 'Miel');
INSERT INTO [dbo].[CarOwner] ([Id], [Name]) VALUES (3, 'Bob');
INSERT INTO [dbo].[CarOwner] ([Id], [Name]) VALUES (4, 'Rick');
SET IDENTITY_INSERT [dbo].[CarOwner] OFF;
GO

-- Insert sample data - Vehicles
SET IDENTITY_INSERT [dbo].[Vehicle] ON;
INSERT INTO [dbo].[Vehicle] ([Id], [Description], [LicensePlate], [TowingWeight], [CarOwnerId]) 
VALUES (1, 'Opel Zafira', 'AA-00-BB', NULL, 2);
INSERT INTO [dbo].[Vehicle] ([Id], [Description], [LicensePlate], [TowingWeight], [CarOwnerId]) 
VALUES (2, 'Volkswagen Golf', 'ABC-12-2', NULL, 3);
INSERT INTO [dbo].[Vehicle] ([Id], [Description], [LicensePlate], [TowingWeight], [CarOwnerId]) 
VALUES (3, 'Vespa GTS', 'ML-BB-44', NULL, 1);
INSERT INTO [dbo].[Vehicle] ([Id], [Description], [LicensePlate], [TowingWeight], [CarOwnerId]) 
VALUES (4, 'Fiat 500', 'ABC-8-DE', NULL, 1);
INSERT INTO [dbo].[Vehicle] ([Id], [Description], [LicensePlate], [TowingWeight], [CarOwnerId]) 
VALUES (5, 'Volkswagen Transporter', 'VW-999-B', 2050, 1);
SET IDENTITY_INSERT [dbo].[Vehicle] OFF;
GO

PRINT 'Database AutoGarageFormatief successfully created and populated with sample data.';
GO