ICT701 The Outcome of the Normalization Process
A new training organization called ABC TechTraining is opening soon and they have approached you to help design their new database. They have just completed the refurbishment of the premises and are now looking at hiring staff to teach the many IT courses they have. They anticipate hiring a large number of staff within the first few months, around 200 – 300 staff annually. Due to the size and importance, they have asked you to look at developing just the staff database at this stage. They are aware they will need to expand the database to incorporate course and student information but are not looking at doing this now.
List of documents filed for each staff member and who sighted them and when. For example, Resume, Certificates, Diplomas, Degrees etc.
• Any additional notes to be recorded and who recorded them.
• Reviews. Each year staff will be reviewed. The review date is one year from the approval date. The review will be conducted by the administration manager. All reviews will consist of an outcome which will determine if the staff member will be allowed to continue teaching with ABC TechTraining. Outcomes consist of:Continue, Warning, Terminate. Notes are to be recorded with each review. The date of the next review is then noted.
Answer:
Normalization is done through three steps;
- 1NF- step involves identifying any repeating groups and removing to normalize the table to 1NF.
- 2NF- This step involves removing any partial dependency in all relations.
- 3NF- This step involves removing any transitive dependency in all the relations. Although there are other levels of normalization, 3NF is the recommended level before implementing the entities as tables to form a database.
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;
- A staff can have teaching experience in one or more courses.
- A staff can have additional information for example being a member of the Australian computer society.
- A staff can have one or more documents and every document is reviewed by another staff.
- A staff can either be administrator or not an administrator.
- A staff who is an administrator approves documents containing other information about a staff
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);
Buy ICT701 The Outcome of the Normalization Process Answers Online
Talk to our expert to get the help with ICT701 The Outcome of the Normalization Process Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
Get Online Support for ICT701 The Outcome of the Normalization Process Assignment Help Online
Resources
- 24 x 7 Availability.
- Trained and Certified Experts.
- Deadline Guaranteed.
- Plagiarism Free.
- Privacy Guaranteed.
- Free download.
- Online help for all project.
- Homework Help Services
Resources
- 24 x 7 Availability.
- Trained and Certified Experts.
- Deadline Guaranteed.
- Plagiarism Free.
- Privacy Guaranteed.
- Free download.
- Online help for all project.
- Homework Help Services
