Question 1: Defining the Database

Banks:
Primary key = (BankName, City)
Justification: There can be banks with the same name in different cities so both attributes are needed to uniquely identify an entry

Foreign Key = NA
Justification: Does not reference any other tables

CREATE TABLE Banks (BankName CHAR(25) NOT NULL, City CHAR(25) NOT NULL, NoAccounts INT, Security CHAR CONSTRAINT scr CHECK (Security IN (‘weak’,’good’, ‘very good’, ‘excellent’)), PRIMARY KEY (BankName, City));

Robberies:
Primary key = (BankName, City, Date)
Justification: The same bank can be robbed multiple times so the Date attribute is needed to uniquely identify an entry

Foreign Key = (BankName, City)
Justification: Robberies references Banks so this foreign key is needed to uniquely identify the bank at which the robbery took place

CREATE TABLE Robberies (BankName CHAR(25) NOT NULL, City CHAR(25) NOT NULL, Date DATE NOT NULL CONSTRAINT rdRange CHECK (Date < ‘2017-10-01’), Amount INT CONSTRAINT raRange CHECK (Amount >= 0), PRIMARY KEY (BankName, City, Date), FOREIGN KEY (BankName, City) REFERENCES Banks ON DELETE NO ACTION ON UPDATE CASCADE);

Foreign Key actions justification:

(BankName, City): ON DELETE NO ACTION because if a Bank is deleted then the data of the Robberies would be deleted, and we want to prevent this
ON UPDATE CASCADE because if the details of a Bank are updated then they should be updated in Robberies also

Plans:
Primary key = (BankName, City, PlannedDate)
Justification: The same bank can be robbed multiple times so the PlannedDate attribute is needed to uniquely identify an entry

Foreign Key = (BankName, City)
Justification: Plans references Banks so this foreign key is needed to uniquely identify the bank at which the robbery will take place

CREATE TABLE Plans (BankName CHAR(25) NOT NULL, City CHAR(25) NOT NULL, PlannedDate DATE NOT NULL CONSTRAINT ppdRange CHECK (Date >= ‘2017-10-01’), NoRobbers INT, PRIMARY KEY (BankName, City, PlannedDate), FOREIGN KEY (BankName, City) REFERENCES Banks ON DELETE CASCADE ON UPDATE CASCADE);

Foreign Key actions justification:

(BankName, City): ON DELETE CASCADE because if a Bank is deleted then a Plan can no longer take place at the bank
ON UPDATE CASCADE because if the details of a Bank are updated then they should be updated in Plans also



Robbers:
Primary key = (RobberID)
Justification: A Robber can be uniquely identified with a RobberID, whereas using the Nickname attribute as the primary key might not work as Robbers can have the same Nickname

Foreign Key = NA
Justification: Does not reference any other tables

CREATE TABLE Robbers (RobberID INT NOT NULL CONSTRAINT rIdRange CHECK (RobberID >= 0 AND RobberID <= 1000), Nickname CHAR(25) NOT NULL, Age INT NOT NULL CONSTRAINT rAgeRange CHECK (Age >= 0 AND Age <=120), NoYears INT CONSTRAINT rNoYearsRange CHECK (NoYears <= Age), PRIMARY KEY (RobberID));

Skills:
Primary key = (SkillID)
Justification: A Skill can be uniquely identified with a SkillD

Foreign Key = NA
Justification: Does not reference any other tables

CREATE TABLE Skills (SkillID INT NOT NULL CONSTRAINT SIdRange CHECK (SkillID >= 0 AND SkillID <= 100), Description CHAR(25) NOT NULL, PRIMARY KEY (SkillID));


HasSkills:
Primary key = (RobberID, SkillID)
Justification: A Robber can be uniquely identified with a RobberID and a Skill can be uniquely identified with a SkillID so a Robbers Skills can be uniquely identified with a primary key combining these two attributes

Foreign Keys = (RobberID), (SkillID)
Justification: HasSkills references Robbers and Skills, so RobbersID and SkillsID are needed as foreign keys

CREATE TABLE HasSkills (SkillID INT NOT NULL, RobberID INT NOT NULL, Preference INT CONSTRAINT hspRange CHECK (Preference IN (1,2,3)), Grade CHAR(2) CONSTRAINT hsgi CHECK (Grade IN (‘A’,’A+’,‘B’,’B+’,’C’,’C+’,NULL)), PRIMARY KEY (SkillID, RobberID), FOREIGN KEY (SkillID) REFERENCES Skills ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (RobberID) REFERENCES Robbers ON DELETE CASCADE ON UPDATE CASCADE);

Foreign Key actions justification:

(RobberID): ON DELETE CASCADE because if a Robber is removed from Robbers then they shouldn’t be in HasSkills anymore as it is strictly for the Skills of Robbers
ON UPDATE CASCADE because if the RobberID is updated in Robbers then it should also be updated in HasSkills

(SkillsID): ON DELETE CASCADE because if a Skill is removed from Skills then it shouldn’t be in HasSkills anymore
ON UPDATE CASCADE because if the SkillID is updated in Skills then it should also be updated in HasSkills

HasAccounts:
Primary key = (BankName, City, RobberID)
Justification: An account is setup between a Robber and a Bank, so to uniquely identify an account the primary key is a combination of the primary keys of

Foreign Keys = (BankName, City), (RobberID)

CREATE TABLE HasAccounts (BankName CHAR(25) NOT NULL, City CHAR(25) NOT NULL, RobberID INT NOT NULL, PRIMARY KEY (BankName, City, RobberID), FOREIGN KEY (BankName, City) REFERENCES Banks ON DELETE CASCADE ON UPDATE CASCADE), FOREIGN KEY (RobberID) REFERENCES Robbers ON DELETE CASCADE ON UPDATE CASCADE);

Foreign Key actions justification:

(BankName, City): ON DELETE CASCADE because if a Bank is deleted then a Robber can no longer have an account with that Bank
ON UPDATE CASCADE because if the details of a Bank are updated then they should be updated in HasAccounts also

(RobberID): ON DELETE CASCADE because if a Robber is removed from Robbers then they shouldn’t be in HasAccounts anymore as it is strictly for Robbers with bank accounts
ON UPDATE CASCADE because if the RobberID is updated in Robbers then it should also be updated in HasAccounts

Accomplices:
Primary key = (BankName, City, RobberID, RobberyDate)
Foreign Keys = (BankName, City, RobberyDate), (RobberID)

CREATE TABLE Accomplices (RobberID INT NOT NULL, BankName CHAR(25) NOT NULL, City CHAR(25) NOT NULL, RobberyDate DATE NOT NULL CONSTRAINT adRange CHECK (RobberyDate < '2017-10-01'), Share INT CONSTRAINT asRange CHECK (Share >= 0), PRIMARY KEY (RobberID, BankName, City, RobberyDate), FOREIGN KEY (RobberID) REFERENCES Robbers ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (BankName, City, RobberyDate) REFERENCES Robberies (BankName, City, Date) ON DELETE CASCADE ON UPDATE CASCADE);

Foreign Key actions justification:

(BankName, City, RobberyDate): ON DELETE CASCADE because if a robbery is removed from Robberies it should also be removed from Accomplices
ON UPDATE CASCADE  because if the details of a robbery in Robberies are updated then it should also be updated in Accomplices

(RobberID): ON DELETE CASCADE because if a Robber is removed from Robbers then they shouldn’t be an accomplice anymore
ON UPDATE CASCADE because if the RobberID is updated in Robbers then it should also be updated in Accomplices