Normalization is done through three steps;
For the proposed database each of the entities making up the database was normalized by following the three normalization steps. The outcome of the normalization process is parent tables which do not depend on other tables and child tables which depend or reference the parent tables. There are junction tables which link two tables. All the entities shown in the entity relationship diagram shown in the figure are in 3NF.
assumptions
The following assumptions were made in addition to the business rules identified from the case study;
SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
CREATE DATABASE IF NOT EXISTS `abc` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `abc`;
CREATE TABLE IF NOT EXISTS `approval` (
`approvalID` int(11) NOT NULL,
`staffID` int(11) NOT NULL,
`approvedBy` int(11) NOT NULL,
`level` varchar(50) NOT NULL,
`location` varchar(100) NOT NULL,
`approvalDate` date NOT NULL,
`reviewDate` date DEFAULT NULL,
`notes` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
INSERT INTO `approval` (`approvalID`, `staffID`, `approvedBy`, `level`, `location`, `approvalDate`, `reviewDate`, `notes`) VALUES
(1, 1, 2, ‘8’, ‘QSU’, ‘2018-09-18’, ‘2019-01-19’, ‘Approved with no issue’),
(2, 2, 3, ‘9’, ‘Victoria university’, ‘2018-04-08’, ‘2018-09-14’, ‘Approved with no issues’),
(3, 3, 2, ’10’, ‘NSW university’, ‘2018-03-08’, ‘2018-04-23’, ‘Approved with no issues’),
(4, 4, 2, ‘7’, ‘NSW university’, ‘2018-03-20’, NULL, ‘waiting confimation’),
(6, 4, 2, ‘9’, ‘QSU’, ‘0000-00-00’, NULL, ‘Pending- waitinng for confirmation’),
(9, 4, 2, ‘8’, ‘NSW University’, ‘2018-09-19’, NULL, ‘everything processed’);
CREATE TABLE IF NOT EXISTS `approved_disciplines` (
`approvalID` int(11) NOT NULL,
`disciplineID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `approved_disciplines` (`approvalID`, `disciplineID`) VALUES
CREATE TABLE IF NOT EXISTS `course` (
`courseID` int(11) NOT NULL,
`name` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `course` (`courseID`, `name`) VALUES
(1, ‘IT’),
(2, ‘Computer Science’),
(3, ‘Computer technology’),
(4, ‘Information Systems’),
(5, ‘Information analysis’);
CREATE TABLE IF NOT EXISTS `discipline` (
`disciplineID` int(11) NOT NULL,
`name` varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `discipline` (`disciplineID`, `name`) VALUES
(1, ‘Computer science’),
(2, ‘Information technology’),
(3, ‘Data anaysis’),
(4, ‘Computer engineering’),
(5, ‘Computer technology’);
CREATE TABLE IF NOT EXISTS `documents` (
`docID` int(11) NOT NULL,
`type` varchar(50) NOT NULL,
`staffID` int(11) NOT NULL,
`signedBy` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `documents` (`docID`, `type`, `staffID`, `signedBy`) VALUES
(1, ‘Certificate in Oracle DBA’, 1, 2),
(2, ‘Diploma’, 3, 2),
(3, ‘Degree’, 4, 2),
(4, ‘Certificate’, 5, 2),
(5, ‘CV’, 1, 2);
CREATE TABLE IF NOT EXISTS `employment_experience` (
`recordID` int(11) NOT NULL,
`staffID` int(11) NOT NULL,
`period` varchar(50) NOT NULL,
`FTE` varchar(50) NOT NULL,
`employerName` varchar(50) NOT NULL,
`position` varchar(50) NOT NULL,
`duties` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employment_experience` (`recordID`, `staffID`, `period`, `FTE`, `employerName`, `position`, `duties`) VALUES
(1, 2, ‘july 2016 – present’, ‘Full-time’, ‘Google’, ‘Assistant Iformation analyst’, ‘analysing information’),
(2, 3, ‘July 2015 – March 2018’, ‘Part- time’, ‘Microsoft’, ‘Lead developer’, ‘Developing systems’),
(3, 3, ‘May 2018 to present’, ‘part time’, ‘IBM’, ‘QA’, ‘quaity assurance of systems’),
(4, 4, ‘January 2012 – May -2016’, ‘Full-time’, ‘Oracle’, ‘Database administrator’, ‘Managing the database’),
(5, 5, ‘January 2017- present’, ‘Full-time’, ‘Tesla’, ‘Head of IT’, ‘coordiinating the IT department’);
CREATE TABLE IF NOT EXISTS `experience_courses` (
`courseID` int(11) NOT NULL,
`recordID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `experience_courses` (`courseID`, `recordID`) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);
CREATE TABLE IF NOT EXISTS `otherinfo` (
`recordID` int(11) NOT NULL,
`staffID` int(11) NOT NULL,
`details` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `otherinfo` (`recordID`, `staffID`, `details`) VALUES
(1, 1, ‘meber of Australian Computer Society’),
(2, 1, ‘Local schoool board member’),
(3, 2, ‘USQ board member’),
(4, 3, ‘Victoria University board member’),
(5, 4, ‘local shool board member’);
CREATE TABLE IF NOT EXISTS `publication` (
`publicationID` int(11) NOT NULL,
`year` int(4) NOT NULL,
`title` varchar(50) NOT NULL,
`journalVol` varchar(50) NOT NULL,
`type` varchar(50) NOT NULL,
`peerRefRev` char(1) NOT NULL,
`classifcation` varchar(50) NOT NULL,
`staffID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `publication` (`publicationID`, `year`, `title`, `journalVol`, `type`, `peerRefRev`, `classifcation`, `staffID`) VALUES
(1, 2015, ‘The role of ICT in university teaching’, ‘Journal of information technology Vol 12’, ‘Journla article’, ‘Y’, ‘Research’, 1),
(2, 2012, ‘ICT in schools’, ‘Education Systems’, ‘Book chapter’, ‘N’, ‘Scholarship’, 1),
(3, 2012, ‘Current trend in game development’, ‘Game development vll 2’, ‘Jornal article’, ‘Y’, ‘research’, 2),
(4, 2017, ‘Next level of search engine optimasation’, ‘Search Engines vol 4’, ‘Jounal article’, ‘Y’, ‘Scholarship’, 3),
(5, 2017, ‘Computer networks in remote areas’, ‘Computer networks vol 3’, ‘Book chapter’, ‘N’, ‘Scholarship’, 4);
CREATE TABLE IF NOT EXISTS `qualification` (
`qualfifcationID` int(11) NOT NULL,
`level` varchar(25) NOT NULL,
`name` varchar(50) NOT NULL,
`subject` varchar(50) NOT NULL,
`institution` varchar(100) NOT NULL,
`yearAwarded` int(11) NOT NULL,
`staffID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `qualification` (`qualfifcationID`, `level`, `name`, `subject`, `institution`, `yearAwarded`, `staffID`) VALUES
(1, ‘8’, ‘Masters of IT’, ‘database programming’, ‘University of the sunshine coast’, 2014, 1),
(2, ‘7’, ‘Bachelor Honours’, ‘ICT’, ‘University of Tasmania’, 2017, 1),
(3, ‘8’, ‘Masters of Game design’, ‘Simulation’, ‘New South Wales University’, 2013, 3),
(4, ‘9’, ‘Masters Data science’, ‘data analysis’, ‘Queens university’, 2014, 4),
(5, ‘7 ‘, ‘Bachelors Computer science’, ‘Networking’, ‘Victoria universsity’, 2014, 5);
CREATE TABLE IF NOT EXISTS `review` (
`reviewID` int(11) NOT NULL,
`conductedBY` int(11) NOT NULL,
`reviewDate` date NOT NULL,
`outcome` varchar(250) NOT NULL,
`nextReviewDate` date DEFAULT NULL,
`staffID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `review` (`reviewID`, `conductedBY`, `reviewDate`, `outcome`, `nextReviewDate`, `staffID`) VALUES
(1, 1, ‘2018-09-04’, ‘Good teaching skills’, NULL, 1),
(2, 3, ‘2018-09-04’, ‘Could improve in teaching skills’, NULL, 3),
(3, 2, ‘2018-05-07’, ‘Good student involvement’, NULL, 3),
(4, 2, ‘2018-05-07’, ‘Better delivery of the content’, NULL, 4),
(5, 2, ‘2018-05-02’, ‘Very good teaching skills’, NULL, 5);
CREATE TABLE IF NOT EXISTS `staff` (
`staffID` int(11) NOT NULL,
`title` char(5) NOT NULL,
`firstName` varchar(50) NOT NULL,
`lastName` varchar(50) NOT NULL,
`address` varchar(250) NOT NULL,
`email` varchar(100) NOT NULL,
`phone` varchar(25) NOT NULL,
`isAdmin` char(1) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `staff` (`staffID`, `title`, `firstName`, `lastName`, `address`, `email`, `phone`, `isAdmin`) VALUES
(1, ‘Mr’, ‘Jonathan’, ‘Smith’, ‘1 Main Street Brisbane, QLD 4000’, ‘[email protected]‘, ‘04100233232’, ‘N’),
(2, ‘Miss’, ‘Cersei’, ‘Lannister’, ‘1 Main Street kings landing, NSW 4000’, ‘[email protected]‘, ‘04343434223’, ‘Y’),
(3, ‘Mr’, ‘Jon’, ‘Snpw’, ‘3 Main Street Brisbane, QLD 4000’, ‘[email protected]‘, ‘043433434’, ‘N’),
(4, ‘Mrs’, ‘Brianne’, ‘Tarth’, ‘4 Main Street Brisbane, QLD 4000’, ‘[email protected]‘, ‘0423332003232’, ‘N’),
(5, ‘Miss’, ‘Arya’, ‘Stark’, ‘3 Main Street Melbourne, NSW 5000’, ‘[email protected]‘, ‘0432300232003’, ‘N’);
CREATE TABLE IF NOT EXISTS `teaching_experience` (
`recordID` int(11) NOT NULL,
`staffID` int(11) NOT NULL,
`field` varchar(50) NOT NULL,
`organisation` varchar(100) NOT NULL,
`role` varchar(50) NOT NULL,
`period` varchar(25) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `teaching_experience` (`recordID`, `staffID`, `field`, `organisation`, `role`, `period`) VALUES
(1, 1, ‘ICT’, ‘Victoria Univeristy’, ‘Lecturer’, ‘July 2017 – present’),
(2, 1, ‘MIS’, ‘USQ’, ‘Tutor’, ‘Feb 2016 – July 2017’),
(3, 2, ‘ICT’, ‘Queens University’, ‘Lecturer’, ‘April 2016 – May 2018’),
(4, 3, ‘MIS’, ‘New South Wales University’, ‘Tutor’, ‘November 2017 to present’),
(5, 5, ‘ICT’, ‘USQ’, ‘Lecturer’, ‘January 2016 – present’);
ALTER TABLE `approval`
ADD PRIMARY KEY (`approvalID`), ADD KEY `staffID` (`staffID`,`approvedBy`), ADD KEY `approvedBy` (`approvedBy`), ADD KEY `approval_index` (`staffID`);
ALTER TABLE `approved_disciplines`
ADD PRIMARY KEY (`approvalID`,`disciplineID`), ADD KEY `disciplineID` (`disciplineID`);
ALTER TABLE `course`
ADD PRIMARY KEY (`courseID`);
ALTER TABLE `discipline`
ADD PRIMARY KEY (`disciplineID`);
ALTER TABLE `documents`
ADD PRIMARY KEY (`docID`), ADD KEY `staffID` (`staffID`,`signedBy`), ADD KEY `signedBy` (`signedBy`);
ALTER TABLE `employment_experience`
ADD PRIMARY KEY (`recordID`), ADD KEY `staffID` (`staffID`);
ALTER TABLE `experience_courses`
ADD PRIMARY KEY (`courseID`,`recordID`), ADD KEY `recordID` (`recordID`);
ALTER TABLE `otherinfo`
ADD PRIMARY KEY (`recordID`), ADD KEY `staffID` (`staffID`);
ALTER TABLE `publication`
ADD PRIMARY KEY (`publicationID`), ADD KEY `staffID` (`staffID`);
ALTER TABLE `qualification`
ADD PRIMARY KEY (`qualfifcationID`), ADD KEY `staffID` (`staffID`);
ALTER TABLE `review`
ADD PRIMARY KEY (`reviewID`), ADD KEY `conductedBY` (`conductedBY`,`staffID`), ADD KEY `staffID` (`staffID`);
ALTER TABLE `staff`
ADD PRIMARY KEY (`staffID`), ADD KEY `staff_index` (`staffID`);
ALTER TABLE `teaching_experience`
ADD PRIMARY KEY (`recordID`), ADD KEY `staffID` (`staffID`);
ALTER TABLE `approval`
MODIFY `approvalID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
ALTER TABLE `course`
MODIFY `courseID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `discipline`
MODIFY `disciplineID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `documents`
MODIFY `docID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `otherinfo`
MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `publication`
MODIFY `publicationID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `qualification`
MODIFY `qualfifcationID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `review`
MODIFY `reviewID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `staff`
MODIFY `staffID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `teaching_experience`
MODIFY `recordID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
ALTER TABLE `approval`
ADD CONSTRAINT `approval_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE,
ADD CONSTRAINT `approval_ibfk_2` FOREIGN KEY (`approvedBy`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
ALTER TABLE `approved_disciplines`
ADD CONSTRAINT `approved_disciplines_ibfk_1` FOREIGN KEY (`approvalID`) REFERENCES `approval` (`approvalID`) ON UPDATE CASCADE,
ADD CONSTRAINT `approved_disciplines_ibfk_2` FOREIGN KEY (`disciplineID`) REFERENCES `discipline` (`disciplineID`) ON UPDATE CASCADE;
ALTER TABLE `documents`
ADD CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`signedBy`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE,
ADD CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
ALTER TABLE `employment_experience`
ADD CONSTRAINT `employment_experience_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
ALTER TABLE `experience_courses`
ADD CONSTRAINT `experience_courses_ibfk_1` FOREIGN KEY (`recordID`) REFERENCES `teaching_experience` (`recordID`) ON UPDATE CASCADE,
ADD CONSTRAINT `experience_courses_ibfk_2` FOREIGN KEY (`courseID`) REFERENCES `course` (`courseID`) ON UPDATE CASCADE;
ALTER TABLE `otherinfo`
ADD CONSTRAINT `otherinfo_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
ALTER TABLE `publication`
ADD CONSTRAINT `publication_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE
ALTER TABLE `qualification`
ADD CONSTRAINT `qualification_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
ALTER TABLE `review`
ADD CONSTRAINT `review_ibfk_1` FOREIGN KEY (`conductedBY`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE,
ADD CONSTRAINT `review_ibfk_2` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
ALTER TABLE `teaching_experience`
ADD CONSTRAINT `teaching_experience_ibfk_1` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staffID`) ON UPDATE CASCADE;
— Queries
–query 1
select a.names,a.name,a.level from
(select qualfifcationID, concat(firstname, ‘ ‘, lastname) as names,name, level from staff inner join qualification on staff.staffID=qualification.staffID) a
inner join (select qualfifcationID,max(level) from staff inner join qualification on staff.staffID=qualification.staffID group by staff.staffID) b on b.qualfifcationID=a.qualfifcationID;
— query 2
select concat(firstname, ‘ ‘, lastname),notes from staff inner join approval on approval.staffID=staff.staffID where approval.approvaldate is null;
— query 3
select concat(firstname, ‘ ‘, lastname) from staff where staffID not in (select staffID from teaching_experience);
–query 4
select qualfifcationID, concat(firstname, ‘ ‘, lastname) as names,name, level from staff inner join qualification on staff.staffID=qualification.staffID group by level
— trigger
DELIMITER $$
CREATE TRIGGER `approval_trigger` BEFORE INSERT ON `approval`
FOR EACH ROW BEGIN
SET NEW.reviewdate = date_add(NEW.approvaldate,interval 1 year);
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