For each relation, identify all functional dependencies that hold on the fields of that table. For each table, specify if that table is in BCNF, 3NF or neither. If a table is not in BCNF, then attempt to find a BCNF decomposition that is both lossless-join and dependencypreserving. If this is not possible, then a lossless-join, dependency-preserving 3NF decomposition is acceptable. Clearly specify the resulting relational schema.

From these Relational schema/Tables Character(cname, alias, gender, pname) JediSith(cname, level, lightsaberuse) Droid(cname, dtype, mainskill (derived , is based off dtype)) *** PoliticalUnit(uname, utype, startingyear, finalyear) (final, and starting cant be same) Planet(pname, x, y, z) Battle(bname, byear, pname, uname) Vehicle(vname, vtype, hyperspeedcapability(derived, based from vtype)) *** Weapon(wtype, wcolour) HoldsPosition(cname, uname) Mentorship(aname, mname, from, to, uname) , (from and to cant be the same year) VUsedIn(vname, bname) UsesV(cname, vname) UsesW(cname, wtype, wcolour)

CREATE TABLE Character( cname CHAR(30), alias CHAR(30), gender CHAR, pname CHAR(30), PRIMARY KEY (cname), UNIQUE (alias), FOREIGN KEY (pname) REFERENCES Planet ON DELETE NO ACTION ON UPDATE CASCADE)

CREATE TABLE JediSith( cname CHAR(30), level CHAR(30), lightsaberuse BIT (1), PRIMARY KEY (cname), FOREIGN KEY (cname) REFERENCES Character ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE Droid( cname CHAR(30), dtype CHAR(30), mainskill CHAR(30), PRIMARY KEY (cname), FOREIGN KEY (cname) REFERENCES Character ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE PoliticalUnit( uname char(30), utype CHAR(30), startingyear INTEGER, finalyear INTEGER, PRIMARY KEY(uname)) CREATE TABLE Planet( pname CHAR(30), x REAL, y REAL, z REAL, PRIMARY KEY(pname), UNIQUE(x, y, z)) (x, y, z are candidate key) CREATE TABLE Battle( bname CHAR(30), byear INTEGER, pname CHAR(30) NOT NULL, uname CHAR(30) NOT NULL, PRIMARY KEY(bname), FOREIGN KEY(pname) REFERENCES Planet ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (uname) REFERENCES PoliticalUnit ON DELETE NO ACTION ON UPDATE CASCADE) (For total participation, both pname and uname must be NOT NULL) CREATE TABLE Vehicle( vname CHAR(30), vtype CHAR(30), hyperspacecapability BIT (1), PRIMARY KEY(vname)) CREATE TABLE Weapon( wtype CHAR(30), wcolour CHAR(30), PRIMARY KEY(wtype, wcolour)) CREATE TABLE HoldsPosition( cname CHAR(30), uname CHAR(30), PRIMARY KEY(cname, uname), FOREIGN KEY(cname) REFERENCES Character ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(uname) REFERENCES PoliticalUnit ON DELETE NO ACTION ON UPDATE NO ACTION) CREATE TABLE Mentorship( aname CHAR(30), mname CHAR(30), from INTEGER, to INTEGER, uname CHAR(30), PRIMARY KEY(aname, mname), FOREIGN KEY(aname) REFERENCES Character(cname) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY(mname) REFERENCES Character(cname) ON DELETE NO ACTION ON UPDATE NO ACTION)) (uname can be null) CREATE TABLE VUsedIn( vname CHAR(30), bname CHAR(30), PRIMARY KEY(vname, bname), FOREIGN KEY(vname) REFERENCES Vehicle ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(bname) REFERENCES Battle ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE UsesV( cname CHAR(30), vname CHAR(30), PRIMARY KEY(cname, vname)) FOREIGN KEY(cname) REFERENCES Character ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(bname) REFERENCES Vehicle ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE UsesW( cname CHAR(30), wtype CHAR(30), wcolour CHAR(30), PRIMARY KEY(cname, wtype, wcolour), FOREIGN KEY(cname) REFERENCES Character ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(wtype, wcolour) REFERENCES Weapon ON DELETE CASCADE ON UPDATE CASCADE)