Final Term Project: Data Dimensions Service Desk Inventory Database

This project details the design and implementation of a service desk inventory database for Data Dimensions and More, aiming to create a scalable, user-friendly system. The project includes requirement gathering, ER diagram creation, translation into a relational schema, data dictionary development, table creation, and SQL query implementation. The team faced challenges that led to a refined approach, focusing on a structured methodology. The database design includes tables for employees, departments, offices, and issues, with detailed attributes and constraints. The project also addresses limitations, such as the need for more detailed information and enhanced security features. Future improvements include adding more tables, implementing data mining techniques, and incorporating role-based access controls. The project successfully applies database concepts and provides a practical solution for managing inventory and resources, offering a good presentation of the current scenario, though improvements are always possible.

final term project: week 7
Student name:

Contents
Account of who did what individual work: detail each team-members efforts throughout the project
...............................................................................................................................................................2
Problems encountered..........................................................................................................................2
How your approach to the project changed over time, and why it changed.........................................2
Project limitations or issues...................................................................................................................3
Future work / improvements planned for future releases....................................................................3
Conclusion.............................................................................................................................................3
References.............................................................................................................................................4
The course material, video lectures and lecture notes..........................................................................4
Final project...........................................................................................................................................4
INTRODUCTION.....................................................................................................................................4
QUERY ROADMAP.................................................................................................................................5
ER-DIAGRAM.........................................................................................................................................6
TRANSLATION METHOD........................................................................................................................6
RELATIONAL SCHEMA............................................................................................................................7
DATA DICTIONARY.................................................................................................................................8
TABLE CREATION...................................................................................................................................9
RECORD INSERTION.............................................................................................................................12
SQL QUERIES........................................................................................................................................16
1 | P a g e

Account of who did what individual work: detail each team-members efforts
throughout the project
<Member 1 name>: completed the designing of the scenario, case study and created the ER-
diagram. Carried out a thorough study and analysis to create the structure which meets all
possible requirements.
< Member 2 name>: studied the ER-diagram and then created the schema and data dictionary
for further completion of the project.
< Member 3 name>: created the database using the schema and data dictionary. Then
populated the tables with records and queried them as per the query roadmap.
Problems encountered
Since we all followed the lecture notes and video lectures, we did not face any problem in
completing the project. The topics covered in the lectures were very helpful in completing the
course final term project.
How your approach to the project changed over time, and why it changed
Our approach was to simply design the scenario into the ER diagrams and then into the
tables. But with time we realized that this was not the right way to proceed. So we tried to
breakdown the process into following steps:
1. Requirement gathering and analysis
2. Logical diagram of the scenario
3. Verification of the diagram
2 | P a g e

4. Translation of the diagram into a schema
5. Creation of data dictionary for the schema
6. Creation of tables with appropriate attributes.
7. Populating tables with relevant data
8. Querying the database to get useful results.
That’s how we proceeded in the process and then towards a successful completion of the
process.
Project limitations or issues
The limitations of the project are that:
1. There are lesser tables and contain lesser detailed information.
2. The database is less secure as there is no role based use.
3. No use of username and passwords.
4. The database is not present on a secure server and is prone to damage.
5. There is scope to add new and detailed attributes which has not been utilized.
Future work / improvements planned for future releases
In future, we would like to add more tables to the database where we can even store all the
detailed information about the items, their stock, employees, partners, promotions etc. we can
even use the process of data mining to extract useful patterns that can be used in the process
of business improvements and business growth. Moreover, we can add the security feature
and role based access to make the data more secure and guard against theft, damage or
unrequired edits or additions. We can even create view so that the actual data is never
affected.
Conclusion
3 | P a g e

The term project was a great opportunity to learn about all the concepts and implement tem
while designing the database. All the parts of the project had a major role to play and even
helped in making it easier to implement the scenario. We incorporated all the steps and the
end product was a reliable one and up to the mark with the requirements. Although there is
always a scope for improvement yet the present project is good presentation of the current
scenario.
References
The course material, video lectures and lecture notes.
Final project
INTRODUCTION
The purpose of our initiative at Data Dimensions and More is to design, build, configure,
deploy and maintain a Service Desk Inventory database that is scalable, interactive, intuitive,
and user friendly.
The objective, goal and purpose is to streamline and deliver a usable, interactive and straight
forward inventory database that is effective and efficient for medium-sized workplace that
can scale as the company grows. The database system in question, address the relative need
of inventory, functionality, cost, resource driven parameters that meet the needs of the clients
and administrators as well.
The users are the clients of the company who utilize the inventory and resources applicable
for their daily production. Certain database, software, hardware entities and their attributes
are needed or need to be secured for each client. User must access the database by IT request
and submission. In turn the database is queried as to what is requested, its quantity, capability
and availability of the item in question. The technical level of the users is average at best with
some power-users in within the company. Because of this, our database is tailored towards
them and its functionality of usage.
The system will capture information via the entities in the database. The database will have
entities structured given clarity to its scope and relative for the user involvement. The entities
will be based off of the resources within the database, the components and each category.
4 | P a g e

In this environment, the organization in question is relatively a mid-sized business with 6 – 8
departments, 80 employees, and 1 satellite office. Each department has a manager and a
director except for the satellite office. The hardware consists of Dell Raid 5 and VMWare
servers (Application,
Database, Print and Exchange), 2 Routers and 2 Wireless Access Points, 80 Dell workstations
utilizing Windows 7 with immediate options for Windows 10 upgrade, 8 Xerox WorkCentre
full-service printers. Existing software is Windows 2012 Server, Windows 7 OS, Office
2013, Adobe, Java and proprietary internal applications. Company policy is consistent with a
standard internet, download and BYOD agreement. At present, organizational issues are non-
existent due to the relatively compact size of the company itself. The analysis of the current
database revealed that it was outdated, expensive to upgrade and currently has major glitches
with Java and the proprietary applications conducive for the success of the company and its
standard(s). The database specialists and managers will be in charge with entering, tabulating,
running queries/reports and local maintenance.
Data Dimensions and More propose to design and build a database system fully integrated
with legacy applications that are currently in usage. The problem domain of connecting the
user to the resource in minimal time with 100% output guarantees no downtime, profit loss or
company ineffectiveness going forward. The cost per license is 20 – 25% better than the
current market share who advertise and use similar database software absent of our
software’s ability for legacy integration. With 80 users at $150 per license the total cost is
$12,000.00. Data Dimensions and More offer a 10% discount for all new subscribers, orders
over $10,000.00 and a 2 year limited warranty. This incentive brings the overall total to
$10,800.00 which is $4200.00 less than the nearest competitor. We at Data Dimensions and
More value our clients and are focused driven to provide the best quality products, service,
support all the while passing the cost incentives to our clients and customers.
Team Charter: Our 3 member team functions as in 3 areas: Design, Building/Formatting and
Creation. Each member will exhibit and add to the project directly.
QUERY ROADMAP
Question 1
Director needs the name of all employees who are head of department along with office and
department details for a meeting.
Question 2
Manager needs to how many hardware issues were there till date.
Question 3
Director wants to update salary of all the employee by 10% who are head of departments.
Question 4
5 | P a g e

Manager needs to know the number of open issues and their registered dates.
Question 5
Director needs to know the number of employees (including heads) in each department of an
office so as to decide the number of employees that have to be hired in future.
ER-DIAGRAM
TRANSLATION METHOD
The translation method used on ERD was ‘mapped translation method’. In this method, a
table is created for each entity. Although the entities may be weak or strong but a table is
needed to define and declare the attributes of the entities. Then, the primary keys are added to
each table to identify each record and as shown in the ER diagram. Further we, map the
6 | P a g e

relationship between entities into the tables using foreign keys. These foreign keys act as a
bridge between the entities that have transformed into the table. Along with this we also add
all the attributes that we had defined in the ERD into the table as columns. This entire process
is called mapped translation because one by one each part is mapped and then translated into
another form for use in the database.
Its advantages are:
1. The multi-values attributes in the ERD can be transformed into atomic attributes in
the relational table.
2. Translation is easy rather than directly converting to the SQL.
3. Redundancy present in the ER diagram can be removed easily via it.
4. Constraints can be enforced after translation, which is not possible in the ER diagram
5. The actual physical storage requirements are reduced by applying this method.
6. Multiple tables can be easily joined without any hassles
Its disadvantages are:
1. Requires cost and expertise
2. Increased number of attributes while making the ERD atomic.
RELATIONAL SCHEMA
The schema designed from the above ER-diagram is as follows:
A. EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO*)
B. DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO*, OFFICE_ID*)
C. OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE)
D. ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO*)
7 | P a g e

DATA DICTIONARY
TABLE NAME: EMPLOYEE
TABLE DESCRIPTION: It will contain data about the employees.
ATTRIBUTE
NAME
DESCRIPTION DATA
TYPE
SIZE CONSTRAINTS
EMP_NO Unique no. allotted
to each employee
Integer Auto_increment PRIMARY KEY
FNAME First name of the
employee
Text 20
LNAME Last name of the
employee
Text 20
SALARY Salary of the
employee
Decimal (10,2) Not null
PHONE Phone number of
the employee
Numeric 10 Unique
DEP_NO Department number
in which the
employee works.
Varchar 4 Not null, foreign key
department(dep_no)
TABLE NAME: department
TABLE DESCRIPTION: It will hold the data about all the departments.
ATTRIBUTE
NAME
DESCRIPTION DATA TYPE SIZE CONSTRAINTS
DEP_NO Unique department
number allotted to
each department
Varchar 4 PRIMARY KEY
DEPT_NAME Name of each
department
Text 30 Not null
EMP_NO Employee id of
employee who
handles the
department
Integer Not null, foreign key
employee(emp_no)
OFFICE_ID ID of the office
which has this
department.
Varchar 4 Primary key, foreign
key office(office_ID)
TABLE NAME: office
TABLE DESCRIPTION: It will contain information about all the offices.
8 | P a g e

ATTRIBUTE
NAME
DESCRIPTION DATA TYPE SIZE CONSTRAINTS
OFFICE_ID Unique id allotted to
each office.
Varchar 4 PRIMARY KEY
OFFICE_NAME Name of each office Text 30
ADDRESS Address of each
office.
Text 50 Not null
PHONE Phone number of
each office
Numeric 10 Unique
TABLE NAME:
TABLE DESCRIPTION:
ATTRIBUTE
NAME
DESCRIPTION DATA
TYPE
SIZE CONSTRAINTS
ISSUE_ID Unique id allotted to
each issue.
Integer Auto_increment PRIMARY KEY
ISSUE_DATE Begin date of the
issue
Date Format (‘DD-
MM-YYYY’
OR ‘YYYY-
MM-DD’)
Not null
STATUS Current status of the
issue
Text 50 Values should be IN
(‘open’, ‘closed’,
‘pending’)
DETAILS Details related to
the issue
Text 100 Not null
REMARKS Ay remarks about
the issue
Text 100
EMP_NO Employee id of the
employee who
handles the issue.
Integer Not null, foreign key
employee(emp_no)
TABLE CREATION
CREATE TABLE OFFICE
(
OFFICE_ID VARCHAR(4) PRIMARY KEY,
OFFICE_NAME VARCHAR(30) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
PHONE NUMBER(10,0)
);
9 | P a g e

CREATE SEQUENCE seq_emp
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
CREATE TABLE EMPLOYEE
(
EMP_NO INT,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
SALARY NUMBER(10,2),
PHONE NUMBER(10,0),
DEP_NO VARCHAR(4),
PRIMARY KEY (EMP_NO)
);
10 | P a g e

CREATE TABLE DEPARTMENT
(
DEP_NO varchar(4) primary key,
DEPT_NAME varchar(30),
EMP_NO int,
OFFICE_ID varchar(4),
FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO),
FOREIGN KEY(OFFICE_ID) REFERENCES OFFICE(OFFICE_ID));
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK1 FOREIGN KEY(DEP_NO)
REFERENCES DEPARTMENT(DEP_NO);
CREATE SEQUENCE SEQ_ISUE
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
11 | P a g e

CREATE TABLE ISSUES
(
ISSUE_ID INT PRIMARY KEY,
ISSUE_DATE DATE,
STATUS VARCHAR(50),
DETAILS VARCHAR(100),
REMARKS VARCHAR(100),
EMP_NO INT,
FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO),
CONSTRAINT CHK1 CHECK (STATUS IN ('OPEN', 'CLOSED', 'PENDING') )
);
RECORD INSERTION
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES
('O1' , 'HEAD OFFICE' , '21 HILL VIEW ROAD, LONG ISLAND, USA', 9999999983);
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES
('O2' , 'SATELLITE OFFICE 1' , '222 PARK STREET, NYC, USA', 9999998983);
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES
('O3' , 'SATELLITE OFFICE 2' , '55/455 DENISE ROAD, LA, USA', 9999997983);
SELECT * FROM OFFICE;
12 | P a g e

INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'JOHN', 'LAMES', 8900.90, 9981234567,
'D1');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'DIA', 'STEVEN', 99900.90, 777789876,
'D1');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'DAVE', 'LANISTER', 7900.90, 771178876,
'D2');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'DAVID', 'STONES', 199900.90, 722778876,
'D2');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'JAMES', 'JOHNSON', 69000.90, 73778876,
'D3');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'JAMIE', 'BELE', 8900.90, 977778876, 'D3');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'JULIE', 'STEPHEN', 89900.90, 177778876,
'D4');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE,
DEP_NO) VALUES (SEQ_EMP.NEXTVAL, 'MIA', 'DRAKES', 8900.90, 577778876, 'D4');
13 | P a g e

INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID)
VALUES ('D1', 'SALES', 10, 'O1');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID)
VALUES ('D2', 'ADMIN', 12, 'O2');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID)
VALUES ('D3', 'TECHNICAL', 13, 'O3');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID)
VALUES ('D4', 'SUPPORT', 15, 'O3');
SELECT * FROM DEPARTMENT;
14 | P a g e

INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS,
EMP_NO) VALUES (1, '13-OCT-16', 'OPEN', 'SOFTWARE FAILURE', '', 10);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS,
EMP_NO) VALUES (2, '12-OCT-16', 'OPEN', 'HARDWARE FAILURE', '', 12);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS,
EMP_NO) VALUES (3, '10-OCT-16', 'PENDING', 'HARDWARE REPAIR', '', 14);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS,
EMP_NO) VALUES (4, '09-OCT-16', 'CLOSED', 'BACKUP ISSUES', '', 12);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS,
EMP_NO) VALUES (5, '08-OCT-16', 'CLOSED', 'SOFTWARE UPDATE', '', 14);
SELECT * FROM ISSUES;
15 | P a g e

SQL QUERIES
Question 1: Director needs the name of all employees who are head of department along with
office and department details for a meeting.
Sql: select e.fname as head, d.dept_name as department_name, o.office_id as offices from
employee e, department d, office o where e.emp_no = d.emp_no and o.office_id =
d.office_id;
16 | P a g e

Question 2: Manager needs to how many hardware issues were there till date.
Sql: select count(issue_id) AS NO_OF_HARDWARE_ISSUES from issues where details
like '%HARDWARE%';
Question 3: Director wants to update salary of all the employee by 10% who are head of
departments.
SQL: UPDATE EMPLOYEE SET SALARY = SALARY + (0.1 * SALARY) WHERE
EMP_NO IN (SELECT EMP_NO FROM DEPARTMENT);
Question 4: Manager needs to know the number of open issues and their registered dates.
SQL: SELECT ISSUE_ID, ISSUE_DATE FROM ISSUES WHERE STATUS = 'OPEN';
Question 5: Director needs to know the number of employees (including heads) in each
department of an office so as to decide the number of employees that have to be hired in
future.
17 | P a g e

SQL: SELECT COUNT(e.EMP_NO) as NO_OF_EMPLOYEES, D.DEPT_NAME,
O.OFFICE_NAME from employee e, department d, office o where e.dep_no = d.dep_no and
o.office_id = d.office_id GROUP BY D.DEPT_NAME, O.OFFICE_NAME;
18 | P a g e