Database Design Assignment Solution – ABC Organization

Assumptions

Normalization is ensured by making sure each entity is in 3NF. The entities shown in the ER Diagram above are all in 3NF because;

• Normalization too 1NF was done by ensuring that all repeating groups are removed in all the relations.
• Normalization to 2NF was done by ensuring that each relation has only candidate key and no partial dependencies exist.
• Normalization to 3NF was done by ensuring that all relations have one candidate key that functionally determines all the other attributes in the relation and no transitive dependencies exist.

• Each staff can be administrator or a normal staff. An administrator is in charge of performing duties like approval of documents, approving disciplines and reviewing of approvals.
• Each staff specializes in or more areas thus a staff can have more than one qualification.
• Teaching staff can have one or more reaching experience records or one or more employment experience.
• Teaching experience is different from employment experience thus each has a separate table where data is stored. A staff can have one or more of each of both.

Db Creation script

CREATE SCHEMA IF NOT EXISTS `abc` DEFAULT CHARACTER SET latin1 ;

USE `abc` ;

— Table `abc`.`staff`

cREATE TABLE IF NOT EXISTS `abc`.`staff` (

  `staffNO` INT(11) NOT NULL AUTO_INCREMENT,

  `title` CHAR(5) NOT NULL,

  `fname` VARCHAR(50) NOT NULL,

  `lname` VARCHAR(50) NOT NULL,

  `address` VARCHAR(250) NOT NULL,

  `email` VARCHAR(100) NOT NULL,

  `phone` VARCHAR(25) NOT NULL,

  `admin` CHAR(1) NOT NULL,

  PRIMARY KEY (`staffNO`),

  INDEX `staff_index` (`staffNO` ASC))

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`approval`

CREATE TABLE IF NOT EXISTS `abc`.`approval` (

  `approvalNO` INT(11) NOT NULL AUTO_INCREMENT,

  `staffNO` INT(11) NOT NULL,

  `approver` INT(11) NOT NULL,

  `level` VARCHAR(50) NOT NULL,

  `location` VARCHAR(100) NOT NULL,

  `approval_Date` DATE NULL DEFAULT NULL,

  `review_Date` DATE NULL DEFAULT NULL,

  `notes` TEXT NOT NULL,

  PRIMARY KEY (`approvalNO`),

  INDEX `staffID` (`staffNO` ASC, `approver` ASC),

  INDEX `approval_index` (`staffNO` ASC),

  INDEX `ondex_on_approval` (`staffNO` ASC),

  INDEX `approver` (`approver` ASC),

  CONSTRAINT `approval_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `approval_ibfk_2`

    FOREIGN KEY (`approver`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 10

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`teaching_experience`

CREATE TABLE IF NOT EXISTS `abc`.`teaching_experience` (

  `teID` INT(11) NOT NULL AUTO_INCREMENT,

  `staffNO` INT(11) NOT NULL,

  `field` VARCHAR(50) NOT NULL,

  `organisation` VARCHAR(100) NOT NULL,

  `role` VARCHAR(50) NOT NULL,

  `period` VARCHAR(25) NOT NULL,

  PRIMARY KEY (`teID`),

  INDEX `staffID` (`staffNO` ASC),

  CONSTRAINT `teaching_experience_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET =

 Table `abc`.`course`

CREATE TABLE IF NOT EXISTS `abc`.`course` (

  `CourseCode` INT(11) NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(100) NOT NULL,

  `teID` INT(11) NOT NULL,

  PRIMARY KEY (`CourseCode`),

  INDEX `teID` (`teID` ASC),

  CONSTRAINT `course_ibfk_1`

    FOREIGN KEY (`teID`)

    REFERENCES `abc`.`teaching_experience` (`teID`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`discipline`

CREATE TABLE IF NOT EXISTS `abc`.`discipline` (

  `disciplineNO` INT(11) NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(50) NOT NULL,

  `approvalNO` INT(11) NOT NULL,

  PRIMARY KEY (`disciplineNO`),

  INDEX `approvalNO` (`approvalNO` ASC),

  CONSTRAINT `discipline_ibfk_1`

    FOREIGN KEY (`approvalNO`)

    REFERENCES `abc`.`approval` (`approvalNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

– Table `abc`.`documents`

CREATE TABLE IF NOT EXISTS `abc`.`documents` (

  `docNO` INT(11) NOT NULL AUTO_INCREMENT,

  `type` VARCHAR(50) NOT NULL,

  `staffNO` INT(11) NOT NULL,

  `signee` INT(11) NOT NULL,

  PRIMARY KEY (`docNO`),

  INDEX `staffID` (`staffNO` ASC, `signee` ASC),

  INDEX `signed_By` (`signee` ASC),

ER Diagram in Crows Foot notation

  CONSTRAINT `documents_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`employment_experience`

ATE TABLE IF NOT EXISTS `abc`.`employment_experience` (

  `employmentID` INT(11) NOT NULL,

  `staffNO` INT(11) NOT NULL,

  `period` VARCHAR(50) NOT NULL,

  `FTE` VARCHAR(50) NOT NULL,

  `employer_Name` VARCHAR(50) NOT NULL,

  `position` VARCHAR(50) NOT NULL,

  `duties` VARCHAR(50) NOT NULL,

  PRIMARY KEY (`employmentID`),

  INDEX `staffID` (`staffNO` ASC),

  CONSTRAINT `employment_experience_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`notes`

CREATE TABLE IF NOT EXISTS `abc`.`notes` (

  `noteNO` INT(11) NOT NULL AUTO_INCREMENT,

  `staffNO` INT(11) NOT NULL,

  `details` VARCHAR(250) NOT NULL,

  PRIMARY KEY (`noteNO`),

  INDEX `staffID` (`staffNO` ASC),

  CONSTRAINT `notes_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`publication`

CREATE TABLE IF NOT EXISTS `abc`.`publication` (

  `pubNO` INT(11) NOT NULL AUTO_INCREMENT,

  `year` INT(4) NOT NULL,

  `title` VARCHAR(50) NOT NULL,

  `journal_Vol` VARCHAR(50) NOT NULL,

  `type` VARCHAR(50) NOT NULL,

  `peer_Ref_Rev` CHAR(1) NOT NULL,

  `classifcation` VARCHAR(50) NOT NULL,

  `staffNO` INT(11) NOT NULL,

  PRIMARY KEY (`pubNO`),

  INDEX `staffID` (`staffNO` ASC),

  CONSTRAINT `publication_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1

— Table `abc`.`qualification`

CREATE TABLE IF NOT EXISTS `abc`.`qualification` (

  `qID` INT(11) NOT NULL AUTO_INCREMENT,

  `level` VARCHAR(25) NOT NULL,

  `name` VARCHAR(50) NOT NULL,

  `subject` VARCHAR(50) NOT NULL,

  `institution` VARCHAR(100) NOT NULL,

  `year_Awarded` INT(11) NOT NULL,

  `staffNO` INT(11) NOT NULL,

  PRIMARY KEY (`qID`),

  INDEX `staffID` (`staffNO` ASC),

  CONSTRAINT `qualification_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

— Table `abc`.`review`

CREATE TABLE IF NOT EXISTS `abc`.`review` (

  `reviewNO` INT(11) NOT NULL AUTO_INCREMENT,

  `reviewer` INT(11) NOT NULL,

  `reviewDate` DATE NOT NULL,

  `outcome` VARCHAR(250) NOT NULL,

  `next_Review_Date` DATE NULL DEFAULT NULL,

  `staffNO` INT(11) NOT NULL,

  PRIMARY KEY (`reviewNO`),

  INDEX `conductedBY` (`reviewer` ASC, `staffNO` ASC),

  INDEX `staffNO` (`staffNO` ASC),

  CONSTRAINT `review_ibfk_1`

    FOREIGN KEY (`staffNO`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `review_ibfk_2`

    FOREIGN KEY (`reviewer`)

    REFERENCES `abc`.`staff` (`staffNO`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = latin1;

— insert statments

INSERT INTO `staff` (`staffNO`, `title`, `fname`, `lname`, `address`, `email`, `phone`, `admin`) VALUES

(1, ‘Mr’, ‘Smith’, ‘Roy’, ‘1 Main Street Brisbane, QLD 4000’, ‘[email protected]‘, ‘04100233232’, ‘N’),

(2, ‘Mr’, ‘Walker’, ‘Lenny’, ‘5 Main Street Brisbane, QLD 4000’, ‘[email protected]‘, ‘089787873’, ‘Y’),

(3, ‘Miss’, ‘Julia’, ‘Rena’, ‘1 Main Street Brisbane, QLD 4000’, ‘[email protected]‘, ‘78676767’, ‘N’),

Normalization to 3rd Normal Form

(4, ‘Mrs’, ‘Mercy’, ‘Hendrick’, ‘5 Main Street Brisbane, QLD 400’, ‘[email protected]‘, ‘0677867878’, ‘N’),

(5, ‘Mr’, ‘chris’, ‘Johnson’, ‘5 Main street Brisbance, QLD 4000’, ‘[email protected]‘, ‘0878787866’, ‘N’);

INSERT INTO `teaching_experience` (`teID`, `staffNO`, `field`, `organisation`, `role`, `period`) VALUES

(1, 1, ‘MIS’, ‘USQ’, ‘Tutor’, ‘June 2016- June 2017’),

(2, 1, ‘MIS’, ‘USQ’, ‘Lecturer’, ‘July 2017 – July 2018’),

(3, 2, ‘ICT’, ‘Victoria Universtity’, ‘Lecturer’, ‘July 2013- May 2016’),

(4, 3, ‘ICT’, ‘USQ’, ‘Lecturer’, ‘December 2012- May 2014’),

(5, 5, ‘ICT’, ‘Victoria University’, ‘Tutor’, ‘May 2018  – Present’);

INSERT INTO `review` (`reviewNO`, `reviewer`, `reviewDate`, `outcome`, `next_Review_Date`, `staffNO`) VALUES

(1, 1, ‘2018-09-20’, ‘Continue’, NULL, 1),

(2, 3, ‘2018-09-20’, ‘Continue’, NULL, 3),

(3, 2, ‘2018-05-18’, ‘Continue’, NULL, 3),

(4, 2, ‘2018-05-09’, ‘Warning’, NULL, 4),

(5, 2, ‘2018-05-14’, ‘Continue’, NULL, 5);

INSERT INTO `qualification` (`qID`, `level`, `name`, `subject`, `institution`, `year_Awarded`, `staffNO`) VALUES

(1, ‘8’, ‘Bachelors computer science’, ‘Neural networks’, ‘University of the sunshine coast’, 2014, 1),

(2, ‘7’, ‘Masters Computer science’, ‘Data statistics and anallysis’, ‘University of Tasmania’, 2017, 1),

(3, ‘8’, ‘Bachelors IT’, ‘Machine learning’, ‘University of the sunshine coast’, 2014, 3),

(4, ‘7’, ‘Masters articial intelligence’, ‘python programming’, ‘University of the sunshine coast’, 2015, 4),

(5, ‘8’, ‘Masters computer security’, ‘Ethical hacking’, ‘University of tasmania’, 2011, 5);

INSERT INTO `publication` (`pubNO`, `year`, `title`, `journal_Vol`, `type`, `peer_Ref_Rev`, `classifcation`, `staffNO`) VALUES

(1, 2015, ‘Machine learning in Locomotive industry’, ‘Machine learning vol 1’, ‘Book chapter’, ‘Y’, ‘scholarship’, 1),

(2, 2012, ‘Neural networks in machine learning’, ‘Neural networks vol 1’, ‘Journal article’, ‘N’, ‘research’, 1),

(3, 2012, ‘How facebook revolunized social media’, ‘Understanding social media vol1’, ‘journal article’, ‘Y’, ‘research’, 2),

(4, 2017, ‘THe growth of autonomous vehicles’, ‘Machine learning in autonomous vehicle vol 2’, ‘Jounal article’, ‘Y’, ‘scholarship’, 3),

(5, 2017, ‘THe trend of ariticial intelligence’, ‘Artifical intelligence vol2’, ‘Journal article’, ‘N’, ‘Scholarship’, 4);

INSERT INTO `notes` (`noteNO`, `staffNO`, `details`) VALUES

(1, 1, ‘Local university member’),

(2, 1, ‘Local College board member’),

(3, 2, ‘Local community college board member’),

(4, 3, ‘Local communicty college board member’),

(5, 4, ‘Local highschool board member’);

INSERT INTO `employment_experience` (`employmentID`, `staffNO`, `period`, `FTE`, `employer_Name`, `position`, `duties`) VALUES

(1, 2, ‘january 2014- January 2018’, ‘Part time’, ‘Micrososft’, ‘Network admin’, ‘Network management’),

(2, 3, ‘March 2016 – April 2017’, ‘Full time’, ‘Google’, ‘Data analyst’, ‘Data analysis (complex)’),

(3, 3, ‘May 2016- June 2018 ‘, ‘full time’, ‘Facebook’, ‘developer’, ‘Web developer’),

(4, 4, ‘June 2016- June 2018’, ‘part time’, ‘Twitter’, ‘developer’, ‘Python developer’),

(5, 5, ‘January 2018- present’, ‘full time’, ‘Oracle’, ‘developer’, ‘Java developer’);

INSERT INTO `documents` (`docNO`, `type`, `staffNO`, `signee`) VALUES

(1, ‘Curriculum Vitae’, 1, 2),

(2, ‘Lynda certificate’, 3, 2),

(3, ‘Udemy certificate’, 4, 2),

(4, ‘Oracle java certifcation’, 5, 2),

(5, ‘Oracle DBA proffessional certificate’, 1, 2);

INSERT INTO `discipline` (`disciplineNO`, `name`, `approvalNO`) VALUES

(1, ‘Information science’, 1),

(2, ‘Data analysis’, 2),

(3, ‘Computer networks’, 3),

(4, ‘Neural networks’, 1),

(5, ‘Machine learning’, 3);

INSERT INTO `course` (`CourseCode`, `name`, `teID`) VALUES

(1, ‘Comptuer science’, 1),

(2, ‘Informaton technology’, 2),

(3, ‘Information science’, 3),

(4, ‘computer engineering’, 1),

(5, ‘Telecommunication engineering’, 3)

INSERT INTO `approval` (`approvalNO`, `staffNO`, `approver`, `level`, `location`, `approval_Date`, `review_Date`, `notes`) VALUES

(1, 1, 2, ‘9’, ‘QSU’, ‘2018-09-13’, ‘2019-01-25’, ‘verified’),

(2, 2, 3, ’10’, ‘QSU’, ‘2018-04-11’, ‘2019-06-14’, ‘verified’),

(3, 3, 2, ’10’, ‘QSU’, ‘2018-01-16’, ‘2018-06-23’, ‘verified’),

(4, 4, 2, ‘7’, ‘QSU’, ‘2017-12-27’, NULL, ‘penging review’),

(6, 4, 2, ‘9’, ‘QSU’, ‘2018-05-08’, NULL, ‘pending review’),

(9, 4, 2, ‘8’, ‘QSU’, NULL, NULL, ‘pending review’);

— Queries

–query 1

SELECT

    a.names, a.name, a.level

FROM

    (SELECT

        qID, CONCAT(fname, ‘ ‘, lname) AS names, name, level

    FROM

        staff

    INNER JOIN qualification ON staff.staffNO = qualification.staffNO) a

        INNER JOIN

    (SELECT

        qID, MAX(level)

    FROM

        staff

    INNER JOIN qualification ON staff.staffNO = qualification.staffNO

    GROUP BY staff.staffNO) b ON b.qID = a.qID

— query 2

SELECT

    CONCAT(fname, ‘ ‘, lname), notes

FROM

    staff

        INNER JOIN

    approval ON approval.staffNO = staff.staffNO

WHERE

    approval.approval_date IS NULL;

– query 3

SELECT

    CONCAT(fname, ‘ ‘, lname) AS names

FROM

    staff

WHERE

    staffNO NOT IN (SELECT

            staffNO

        FROM

            teaching_experience);

–query 4

SELECT

    qID, CONCAT(fname, ‘ ‘, lname) AS names, name, level

FROM

    staff

        INNER JOIN

    qualification ON staff.staffNO = qualification.staffNO

GROUP BY level;

— trigger

CREATE TRIGGER `set_review_date`

AFTER INSERT ON `approval`

FOR EACH ROW 

    UPDATE `approval` SET `review_date` = date_add(new.approval_date,interval 1 year) WHERE `approvalNO` = NEW.approvalNO;

END;

DELIMITER ;

–indexes

create index index_staff on staff (staffNO);

create index index_approval on approval (staffNO);