|
|
CREATE TABLE PartyType (
PartyTypeCode CHAR(1) NOT NULL,
Name CHAR(30) NOT NULL,
CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);
CREATE TABLE Party (
PartyId INT NOT NULL,
PartyTypeCode CHAR(1) NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT Party_PK PRIMARY KEY (PartyId),
CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
REFERENCES PartyType (PartyTypeCode)
);
CREATE TABLE Person (
PersonId INT NOT NULL,
FirstName CHAR(30) NOT NULL,
LastName CHAR(30) NOT NULL,
GenderCode CHAR(3) NOT NULL,
BirthDate DATE NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT Person_PK PRIMARY KEY (PersonId),
CONSTRAINT Person_AK UNIQUE (
FirstName,
LastName,
GenderCode,
BirthDate
),
CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
REFERENCES Party (PartyId)
);
CREATE TABLE Organization (
OrganizationId INT NOT NULL,
Name CHAR(30) NOT NULL,
FoundingDate DATE NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT Organization_PK PRIMARY KEY (OrganizationId),
CONSTRAINT Organization_AK UNIQUE (Name),
CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
REFERENCES Party (PartyId)
);
CREATE TABLE UserProfile (
UserId INT NOT NULL,
UserName CHAR(30) NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT UserProfile_PK PRIMARY KEY (UserId),
CONSTRAINT UserProfile_AK UNIQUE (Username),
CONSTRAINT UserProfileToPerson_FK FOREIGN KEY (UserId)
REFERENCES Person (PersonId)
);
CREATE TABLE Account (
AccountNumber INT NOT NULL,
OwnerPartyId INT NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT Account_PK PRIMARY KEY (AccountNumber),
CONSTRAINT AccountToParty_FK FOREIGN KEY (OwnerPartyId)
REFERENCES Party (PartyId)
);
CREATE TABLE Transfer (
TransferorAccountNumber INT NOT NULL,
TransfereeAccountNumber INT NOT NULL,
TransferDateTime TIMESTAMP NOT NULL,
Amount INT NOT NULL,
Etcetera CHAR(30) NOT NULL,
CONSTRAINT Transfer_PK PRIMARY KEY (TransferorAccountNumber, TransfereeAccountNumber, TransferDateTime),
CONSTRAINT TransferToTransferor_FK FOREIGN KEY (TransferorAccountNumber)
REFERENCES Account (AccountNumber),
CONSTRAINT TransferToTransferee_FK FOREIGN KEY (TransfereeAccountNumber)
REFERENCES Account (AccountNumber),
CONSTRAINT AccountsAreDistinct_CK CHECK (TransferorAccountNumber <> TransfereeAccountNumber),
CONSTRAINT AmountIsValid_CK CHECK (Amount > 0)
);
|
