Normalization is ensured by making sure each entity is in 3NF. The entities shown in the ER Diagram above are all in 3NF because;
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),
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’),
(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);
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download