UML Diagram Assignment

UML Diagram Assignment 2020

Part A:

 

ER Diagram:

UML Diagram Assignment

 

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:

UML Diagram Assignment

 

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:

UML Diagram Assignment

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:

UML Diagram Assignment

Command:

use abctech;

create table Experience(

StaffID varchar(25),

TeachingPeriod varchar(25),

CoursesTaught varchar(25),

OrganizationNameandAddress varchar(25),

CourseRole varchar(25)

);

Output:

UML Diagram Assignment

Command:

use abctech;

Create table EmployementExperience(

StartDate date,

FinishDate date,

WorkingStatus varchar(50),

PositionTitle varchar(50),

EmployerName varchar(50),

Duties varchar(50)

);

Output:

UML Diagram Assignment

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:

UML Diagram Assignment

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:

UML Diagram Assignment

Inserting values:

Personal Details:

use abctech;

insert into personaldetails values(‘521′,’Kishore’,’Nellutla’,’kish@gmail.com’,’9948390094′,’Mr’,’Aus’);

insert into personaldetails values(‘522′,’Nellutla’,’Anup’,’anup@gmail.com’,’9963790061′,’Mr’,’Aus’);

insert into personaldetails values(‘523′,’Geetha’,’Nellutla’,’geeth@gmail.com’,’9666970094′,’Ms’,’Aus’);

insert into personaldetails values(‘524′,’Laxmi’,’Nellutla’,’laxmi@gmail.com’,’9948233252′,’Mr’,’Aus’);

insert into personaldetails values(‘525′,’Naveen’,’Kankipati’,’naveen@gmail.com’,’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:

UML Diagram Assignment

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:

UML Diagram Assignment

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:

UML Diagram Assignment

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:

UML Diagram Assignment

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:

UML Diagram Assignment

Queries:

Code:

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

personaldetails.LastName, qualifications.levelID from personaldetails

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

Output:

UML Diagram Assignment

Code:

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

personaldetails.lastname,  approval.Notes from personaldetails, approval

where approval.Notes=’Terminate’;

 

Output:

UML Diagram Assignment

 

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.

 

 

 

 

 

 

 

Leave a Comment