Archive for January, 2020

Database Final Project Report

Table of Content

• Problem Description
  • Contribution
• Database Design
  • Entity Relationship Diagram
  • Relations
  • Normalization
• Sample Queries
• User Interface
• Link

Problem Description

The use of paper work in managing this system can be challenging. Human errors and the lack of integrity can occur. Not to mention the large amount of time consumed. Designing this system could alleviate the inefficiency that occasionally arose when using manual method. It provides uncomplicated methods in handling the employees that still maintains its privacy.

The purpose of this database is to manage and see the details of employees in a company. This database will contain employee details such as department, which branch do they work in, etc. Hence this database will make it easier to keep track of employees and makes managing employees more efficient.

Contributions

Brenda Spears:

In this project, I was assigned to create and design the user interface using Java Swing. I also contributed in designing and making further changes in the ERD. Adding to that, I also helped in creating the database.

Kotrakona Harinatha Sreeya Reddy:

My contribution to this project was to design and make the functions that will be used in the database. For example, addAdmin, checkadmin, deleteDepartment and updateEmployee are few of those functions. I also worked to optimise these functions to suit the database. I also helped contribute to making the queries that are used in the database.

Vicky Vanessa:

My contribution in this project are to design the initial ERD (Entity Relationship Diagram) for the project and then help with further changes in the ERD as discussed as well as making the database. Moreover, I provided the query that are used in the functions of the codes. I also helped with and provide further improvement for some of the code used for the function and user interfaces. Also, help with the initial design of the user interface and help to code it.

Database Design

Entity Relationship Diagram

Primary Key **

Field –

Foreign key #

Relations

admin(username, password)

branch(branch_id, branch_address, branch_city)

• PRIMARY KEY (branch_id)

department(department_id,  department_name, branch_id)

• PRIMARY KEY (department_id)
• FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE

job(job_id, job_name, min_salary, department_id)

• PRIMARY KEY (job_id)
• FOREIGN KEY (department_id) REFERENCES department(department_id) ON DELETE CASCADE

history(employee_id, hire_date, end_date, job_id)

• PRIMARY KEY (employee_id)
• FOREIGN KEY (job_id) REFERENCES job(job_id)

employee(employee_id, first_name, last_name, email, phone_number, job_id, salary)

• PRIMARY KEY (employee_id)
• FOREIGN KEY (employee_id) REFERENCES history(employee_id)
• FOREIGN KEY (job_id) REFERENCES job(job_id) ON DELETE CASCADE

Normalizations

Sample Queries

Query for showing all the employees.

SELECT employee.employee_id, employee.first_name, employee.last_name, employee.email, employee.phone_number, job.job_name, employee.salary, department.department_name, branch.branch_address, branch.branch_city FROM employee 

       INNER JOIN department ON department.department_id = employee.department_id 

       INNER JOIN branch ON department.branch_id = branch.branch_id 

       INNER JOIN job ON job.job_id = employee.job_id 

       INNER JOIN history ON employee.employee_id = history.employee_id 

       WHERE history.end_date is null 

       ORDER BY employee.employee_id DESC

Query for deleting branch

DELETE FROM branch WHERE branch_id = ?

Query for inserting into department

INSERT INTO department(department_name, branch_id) 

       VALUES(?,?)

Query for updating when an employee want to quit

UPDATE history set end_date = CURDATE() WHERE employee_id = ?

Query for adding department

INSERT INTO department(department_name, branch_id) 

       VALUES(?,?)

User Interface

Links

Report:

https://docs.google.com/document/d/1-S_TlQn2kCfX9hhcduOBIC4WOlSPnsxTR1pSBHe86H8/edit?usp=sharing

Github:

https://github.com/vynsss/Final_Project_Database

Video:

https://youtu.be/qI07j6FnNEI

MarkLogic – NoSQL

Group Members:

Kotrakona Harinatha Sreeya Reddy – 2201816165

Rachel Hera – 2201841671

Isabella Virgin – 2201808144

Mark Logic is a database designed for NoSQL speed and scale, without sacrificing the enterprise features required to run mission-critical, operational applications. It also has a search engine.

MarkLogic was first named Cerisent and was founded in 2001 by Christopher Lindblad who was the Chief Architect of the Ultraseek search engine at Infoseek, Paul Pedersen, a professor of computer science and Frank R. Caufield,  Founder of Darwin Ventures

MarkLogic combines fuses database internals, a search engine, all with application services layered over the top. MarkLogic uses an “Ask Anything” Universal Index that indexes data as soon as it is loaded so you can immediately begin asking questions of your data. It is a Document Data Model. Furthermore, data is stored as compressed binary trees and is processed as XML using XQuery

Database Project Contribution

My contribution to this project was to design and make the functions that will be used in the database. For example, addAdmim, checkadmin , deleteDepartment and updateEmployee are few of those functions. I also worked to optimise these functions to suit the database. I also helped contribute to making the queries that are used in the database.