CREATE DATABASE K9Data; 

CREATE TABLE Users ( 
Username NVARCHAR(50) PRIMARY KEY,
 Email NVARCHAR(255) NOT NULL UNIQUE, 
 FullName NVARCHAR(100) NOT NULL,
 PasswordHash NVARCHAR(64) NOT NULL,
 IsActive BIT NOT NULL DEFAULT 1,
 SecurityQuestion NVARCHAR(255) NOT NULL,
 SecurityAnswer NVARCHAR(64) NOT NULL 
);

	CREATE TABLE SavedSessions ( 
 	SessionID INT IDENTITY(1,1) PRIMARY KEY, 
 	SessionDate DATE NOT NULL, 
 	UserName NVARCHAR(50) NOT NULL, 
 	SessionNumber INT NOT NULL, 
 	CapturedPackets INT NOT NULL,
CONSTRAINT FK_SavedSessions_Users FOREIGN KEY (UserName) 
 	REFERENCES Users(Username) 
 	ON DELETE CASCADE 		
ON UPDATE CASCADE,  		
CONSTRAINT UQ_SavedSessions UNIQUE (UserName, SessionNumber)
);


CREATE TABLE CapturedPackets ( 
 id INT PRIMARY KEY IDENTITY, 
session_id INT, timestamp 
DATETIME, source_ip 
VARCHAR(50), destination_ip 
VARCHAR(50), protocol 
VARCHAR(10), 
 packetlength INT, 
FOREIGN KEY (session_id) REFERENCES SavedSessions(SessionID) 
); 

ALTER TABLE CapturedPackets 
ADD packet_data TEXT; 
