RelationModel: kreiranje.sql

CREATE TABLE AuditlyUser (
Id INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
EmailAddress VARCHAR(255) NOT NULL,
UserDomainName VARCHAR(255)
);

CREATE TABLE Product (
DomainName VARCHAR(255) PRIMARY KEY
);

CREATE TABLE ProductIndustry (
ProductDomainName VARCHAR(255),
Industry VARCHAR(255) NOT NULL,
PRIMARY KEY (ProductDomainName),
FOREIGN KEY (ProductDomainName) REFERENCES Product(DomainName)
);

CREATE TABLE Reviewer (
Id INT PRIMARY KEY,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
Position VARCHAR(255),
CompanyName VARCHAR(255),
EmailAddress VARCHAR(255),
LinkedInUserProfileUrl VARCHAR(255)
);

CREATE TABLE Message (
Id INT PRIMARY KEY,
UserId INT,
MessageType VARCHAR(255) CHECK (MessageType IN ('email', 'linkedin')),
EmailSubject VARCHAR(255),
EmailText TEXT,
LinkedInText TEXT,
FOREIGN KEY (UserId) REFERENCES User(Id),
CHECK ((MessageType = 'email' AND EmailSubject IS NOT NULL AND EmailText IS NOT NULL) OR (MessageType = 'linkedin' AND LinkedInText IS NOT NULL))
);

CREATE TABLE Review (
Id INT PRIMARY KEY,
ReviewerId INT,
ProductId INT,
ReviewSite VARCHAR(255) NOT NULL,
Sentiment VARCHAR(255),
ReviewerScore NULL,
ReviewText TEXT,
DateOfReview DATE NOT NULL,
FOREIGN KEY (ReviewerId) REFERENCES Reviewer(Id),
FOREIGN KEY (ProductId) REFERENCES Product(DomainName)
CHECK (Sentiment = 'positive' OR Sentiment = 'negative' OR Sentiment = 'neutral')
CHECK(Sentiment NOT NULL OR ReviewerScore NOT NULL)
CHECK (ReviewerScore >= 0);
);

CREATE TABLE ReviewerSocialMediaLinks (
SocialMediaLink VARCHAR(255) PRIMARY KEY,
ReviewerId INT,
SocialMediaType VARCHAR(255),
FOREIGN KEY (ReviewerId) REFERENCES Reviewer(Id)
);