• Posted on October 6, 2020
• Unique Submission Team

UML Diagram Assignment 2020

Part A:

 

ER Diagram:

 

Figure: ER Diagram

 

Normalization:

All the tables are in #3rd Normalization as per 1st Normal Form, 2nd Normal Form, and Third Normal Form standards.

 Assumptions:

1. Approval of the teaching should be based on the level approved, discipline, adminstaff, location, approvaldate, reviewdate, and notes.
2. Publications should be based on the year, title, Journalvolume, publication type, Peer-Reffered, and so on.

Part B:

Database Creation:

Command:

create database ABCTech;

Output:

 

Table Creation:

1. PersonalDetails:

Command:

use abctech;

create table PersonalDetails(

StaffID varchar(25) Primary Key,

FirstName varchar(25),

LastName varchar(25),

Email varchar(50),

Phone varchar(25),

Title varchar(25),

Address varchar(25)

);

 

Output:

Code:

use abctech;

create table Qualifications(

levelID varchar(25),

StaffId varchar(25),

QName varchar(25),

QSubject varchar(25),

Institution varchar(25),

YearAwarded varchar(25)

);

Output:

Command:

use abctech;

create table Experience(

StaffID varchar(25),

TeachingPeriod varchar(25),

CoursesTaught varchar(25),

OrganizationNameandAddress varchar(25),

CourseRole varchar(25)

);

Output:

Command:

use abctech;

Create table EmployementExperience(

StartDate date,

FinishDate date,

WorkingStatus varchar(50),

PositionTitle varchar(50),

EmployerName varchar(50),

Duties varchar(50)

);

Output:

Code:

use abctech;

create table publications(

pyear year,

title varchar(25),

JournalVolume varchar(25),

PublicationType varchar(30),

Peerreferred varchar(30),

ResearchClassification varchar(50),

disciplinePublication varchar(50)

);

Output:

Code:

use abctech;

create table approval(

LevelApproved varchar(50),

Discipline varchar(50),

AdminStaff varchar(50),

Location varchar(50),

ApprovalDate varchar(50),

ReviewDate varchar(50),

Notes varchar(50)

);

 

Output:

Inserting values:

Personal Details:

use abctech;

insert into personaldetails values(‘521′,’Kishore’,’Nellutla’,’[email protected]’,’9948390094′,’Mr’,’Aus’);

insert into personaldetails values(‘522′,’Nellutla’,’Anup’,’[email protected]’,’9963790061′,’Mr’,’Aus’);

insert into personaldetails values(‘523′,’Geetha’,’Nellutla’,’[email protected]’,’9666970094′,’Ms’,’Aus’);

insert into personaldetails values(‘524′,’Laxmi’,’Nellutla’,’[email protected]’,’9948233252′,’Mr’,’Aus’);

insert into personaldetails values(‘525′,’Naveen’,’Kankipati’,’[email protected]’,’61452225852′,’Mr’,’Aus’);

 

 

Approval:

insert into approval values(‘Level7′,’Level8′,’AD1′,’Brisbane’,’2018/09/30′,’2018/09/30′,’Continue’);

insert into approval values(‘Level8′,’Level9′,’AD2′,’Sydney’,’2018/09/30′,’2018/09/30′,’Warning’);

insert into approval values(‘Level9′,’Level10′,’AD3′,’Brisbane’,’2018/09/30′,’2018/09/30′,’Terminate’);

insert into approval values(‘Level10′,’Level10′,’AD4′,’Sydney’,’2018/09/30′,’2018/09/30′,’Terminate’);

Output:

Code:

insert into employementexperience values(‘2018/09/30′,’2018/10/10′,’ABC’,’Lecturer’,’QWER’,’AJU’);

insert into employementexperience values(‘2018/09/30′,’2018/10/10′,’ABC’,’Lecturer’,’ASDF’,’ALO’);

insert into employementexperience values(‘2018/09/30′,’2018/10/10′,’ABC’,’Lecturer’,’AHGFJ’,’QokQUJ’);

insert into employementexperience values(‘2018/09/30′,’2018/10/10′,’ABC’,’Lecturer’,’KIRJ’,’AJQ’);

Output:

Code:

insert into experience values(‘521′,’20days’,’ALL’,’ABCD’,’PLM’);

insert into experience values(‘522′,’20days’,’ALL’,’QWER’,’OKJN’);

insert into experience values(‘523′,’20days’,’ALL’,’AFGT’,’POU’);

insert into experience values(‘524′,’20days’,’ALL’,’JGUTR’,’IUYT’);

insert into experience values(‘525′,’20days’,’ALL’,’GJRK’,’ORE’);

Output:

Code:

insert into qualifications values(‘BD’,’521′,’Q1′,’QS1′,’QI1′,’2018′);

insert into qualifications values(‘BH’,’522′,’Q2′,’Qs2′,’QI2′,’2018′);

insert into qualifications values(‘M’,’523′,’Q3′,’QS3′,’QI3′,’2018′);

insert into qualifications values(‘Phd’,’524′,’Q4′,’QS4′,’QI4′,’2018′);

insert into qualifications values(‘BDH’,’525′,’Q5′,’QS6′,’QI5′,’2018′);

Output:

Code:

Create table publications(

year year,

title  varchar(25),

JournalVolume varchar(25),

PublicationType varchar(25),

Peerreferred varchar(25),

ResearchClassification varchar(25),

disciplinePublication varchar(25)

);

insert into publications values(‘2018′,’Database’,’8′,’Journal’,’Research’,’Scholarship’,’Scholarship’);

insert into publications values(‘2018′,’Database’,’9′,’conference proceedings’,’Research’,’Scholarship’,’Scholarship’);

insert into publications values(‘2018′,’Database’,’10’,’books’,’Research’,’Scholarship’,’Scholarship’);

insert into publications values(‘2018′,’Database’,’11’,’book chapters’,’Research’,’Scholarship’,’Scholarship’);

insert into publications values(‘2018′,’Database’,’12’,’book chapters’,’Research’,’Scholarship’,’Scholarship’);

Output:

Queries:

Code:

select personaldetails.staffid, personaldetails.title, personaldetails.FirstName,

personaldetails.LastName, qualifications.levelID from personaldetails

inner join qualifications ON personaldetails.staffid = qualifications.staffid;

Output:

Code:

select distinct personaldetails.staffid , personaldetails.title, personaldetails.firstname,

personaldetails.lastname,  approval.Notes from personaldetails, approval

where approval.Notes=’Terminate’;

 

Output:

 

Code:

SELECT * FROM abctech.employementexperience where StartDate is null and FinishDate is null;

Output:

 

 

Code:

SELECT count(staffid) as TotalNumberofStaff FROM abctech.personaldetails;

Output:

 

5.

Code:

CREATE EVENT reset

ON SCHEDULE

EVERY 1 HOUR

DO

update T1

set state=1

where time < date_sub(now(),interval 24 hour)

and (state=0 or state=2) ;

 

Output:

 

Code:

CREATE UNIQUE INDEX index_name

ON approval (levelapproved, discipline, adminstaff, location,

approvaldate, reviewdate, notes);

 

Output:

 

Code:

CREATE UNIQUE INDEX index_name

ON personaldetails (StaffID, FirstName, LastName, Email, Phone, Title, Address);

 

Output:

 

 

References:

Date, C. J. (2003). An Introduction to Database Systems (8th ed.). Pearson. ISBN 978-0321197849.

Wagner, Michael (2010), SQL/XML:2006 – Evaluierung der Standardkonformität ausgewählter Datenbanksysteme, Diplomica Verlag, ISBN 978-3836696098

Connolly, Thomas M.; Begg, Carolyn E. (2014). Database Systems – A Practical Approach to Design Implementation and Management (6th ed.). Pearson. ISBN 978-1292061184.