0 Download8 Pages / 1,989 Words
Add in library
Click this icon and make it bookmark in your library to refer it later.
GOT IT
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);
To View this & another 50000+ free samples. Please put your valid email id.
Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.
Upload
Unique Document
Document
Under Evaluation
Get Money
into Your Wallet
Total 8 pages, 1 USD Per Page
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). Relational Database Systems. Retrieved from https://myassignmenthelp.com/free-samples/ict701-relational-database-systems/level-before.html.
"Relational Database Systems." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/ict701-relational-database-systems/level-before.html.
My Assignment Help (2021) Relational Database Systems [Online]. Available from: https://myassignmenthelp.com/free-samples/ict701-relational-database-systems/level-before.html
[Accessed 18 April 2021].
My Assignment Help. 'Relational Database Systems' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/ict701-relational-database-systems/level-before.html> accessed 18 April 2021.
My Assignment Help. Relational Database Systems [Internet]. My Assignment Help. 2021 [cited 18 April 2021]. Available from: https://myassignmenthelp.com/free-samples/ict701-relational-database-systems/level-before.html.
For years, MyAssignmenthelp.com has been operating as one of the cheap assignment help providers in the USA. We are one of the best college paper writing services that keep service price minimal. We do not let the affordability of our service to hamper the standard of our work. We have separate teams of experts to provide report writing help . At MyAssigenmnthelp.com, we believe in earning credibility, so students can pay for assignments only after getting satisfied solutions. Tough assignments bother you? Trust us with your project. You will not regret paying us to write assignments for you.
Answer: The database given was normalized to achieve a better database. Normalization of the database involved following the following steps; Ensuring no entity has any repeating groups to normalize the relations to 1NF. Ensuring all entities achieved as a result of normalization to 1NF have no partial dependencies thus normalizing them to 2NF. This involves making sure that an entity has only one candidate key that the other keys depend o...
Read More
Answer: The database should be able to store the details of all the courses in the system and each course in the system should be identified with a unique identity that would be helpful in distinguishing the different entries in the tables. The database should be able to store the details of all the students each data should be identified by a unique identification key. The database should be able to store the details of the enro...
Read More
Answer: Part 1: Use Case Realisation for the ‘Record New Tour’ Use Case 1.1 Analysis Class Diagram: In the paradigm of software engineering, the class diagram is a part of UML that represents the static structure of an application. The class diagram, as the name suggests, represents the classes of the application. These classes has attributes and operations. The class diagram are mainly used for describing the element...
Read More
Answer: Assumption: - A APPLIED_STAFF has many QUALIFICATION_DETAIL and a QUALIFICATION_DETAIL is related to many APPLIED_STAFF. Here many too many relationships occur. So I create a new table that is STAFF_QUALIFICATION. Here a staff has one or many STAFF_QUALIFICATION and each STAFF_QUALIFICATION is related to one and only one APPLIED_STAFF. A qualification is related to one or many STAFF_QUALIFICATION and each STAFF_QUALIFICATION is ...
Read More
Answer: Introduction Racism and humiliation has been one of the biggest curses in the modern society. This has really become a widely spread problem in almost all the countries of the world. The children are not spared of it as well. The aim of the following essay will be develop an understanding of the impact of the racism and humiliation in the several schools of Canada. Canada has always been a multilingual country with great many people c...
Read More
Just share requirement and get customized Solution.
Orders
Overall Rating
Experts
Our writers make sure that all orders are submitted, prior to the deadline.
Using reliable plagiarism detection software, Turnitin.com.We only provide customized 100 percent original papers.
Feel free to contact our assignment writing services any time via phone, email or live chat. If you are unable to calculate word count online, ask our customer executives.
Our writers can provide you professional writing assistance on any subject at any level.
Our best price guarantee ensures that the features we offer cannot be matched by any of the competitors.
1758 Order Completed
99% Response Time
285 Order Completed
99% Response Time
647 Order Completed
98% Response Time
1265 Order Completed
97% Response Time
Get all your documents checked for plagiarism or duplicacy with us.
Get different kinds of essays typed in minutes with clicks.
Calculate your semester grades and cumulative GPa with our GPA Calculator.
Balance any chemical equation in minutes just by entering the formula.
Calculate the number of words and number of pages of all your academic documents.
Our Mission Client Satisfaction
I got a very high mark and I am satisfied with the service provided this time. Thank you!
Australia
It was great. The paper got the details and the information I needed. It was good effort.
Australia
It was good. The assignment provided good details and I got the information I needed.
Australia
GOOD! The tutor is professional, responsible and patient. The reply was timely...................
Australia
Say hello to our new app
