Developing an Office Management System with SQL Database

This assignment details the creation of an office management system using SQL, covering various aspects of database design and implementation. The student begins by outlining data and transaction requirements, defining entities like employees, departments, and projects. An Entity-Relationship (E-R) diagram is constructed to visually represent the database schema. The assignment then progresses to database creation, including table definitions for employees, departments, projects, and dependents, followed by data insertion using SQL's INSERT statements. It explores data querying using SELECT statements with examples, and demonstrates join operations (inner and left outer joins) to combine data from multiple tables. The assignment covers table modification through UPDATE and DELETE statements, and also includes view generation to create customized data representations. Furthermore, it demonstrates the use of check constraints to enforce data integrity. The student discusses learning outcomes, including understanding E-R diagrams, cardinality ratios, basic SQL operations, data integrity, and join operations, and reflects on challenges faced, such as data redundancy, and how they were addressed through techniques like normalization. The assignment concludes with references to relevant database systems literature.

1
Running head: office management system using SQL
OFFICE MANAGEMENT SYSTEM USING SQL

2
Running head: office management system using SQL
Table of Contents
Introduction:....................................................................................................................................3
1. Data Requirements:.....................................................................................................................3
2. Transaction Requirements:..........................................................................................................3
3. Data queries:................................................................................................................................3
4. Entity Relationship Diagram (E-R diagram):..............................................................................4
5. Database creation:........................................................................................................................5
6. DATA Insertion:..........................................................................................................................5
7. Select Comment:..........................................................................................................................7
8. Join:..............................................................................................................................................7
9. Table Modification:.....................................................................................................................8
10. View Generation:.......................................................................................................................9
11. Check Constraints:...................................................................................................................10
12. Learning Outcome:..................................................................................................................10
12. Evaluation:...............................................................................................................................10
Conclusion:....................................................................................................................................11
References:....................................................................................................................................12

3
Running head: office management system using SQL
Introduction:
DBMS is the system software by which a DBA can create and manage a database. One of the
main reasons for using DBMS is having central control of both data and the database. Here, the
learner considers an office management model where the learner has taken employee,
department and project as entities. Learner will construct an E-R diagram of this model and will
execute this on oracle by software by creating, updating the table
1. Data Requirements:
Employee: Here learner took an entity of an employee of an organisation. The attributes under
Employee is taken as Ssn, Bdate, Fname, Mname, Lname, Address, Salary, Sex.
Department: The department is taken as separate entity of company diagram. The attributes
under department are Name, location number,
Project: Project is considered as the separate entity. The attributes taken under project is name,
number location
Dependent: The dependent is taken as separate entity. Here the attributes are taken on this is
name, sex, birth_date, relationship.
2. Transaction Requirements:
Data insertion:
Enter the details of employee
Enter the details of a department
Enter the details of project
Enter the details of dependent
Data Update and delete:
Update/delete the details of an employee.
Update/delete the details of a department
Update/delete the details of a project
Update/delete the details of a dependent
3. Data queries:
Examples of queries required by the Branch user views:
(a) List of total number of employees
(b) List of the total number of employees within a department
(c) List of total number of projects
(d) Identify the total number of projects under a department
(e) Identify the total number of employees on a particular project
(f) Identify the supervisor of a project
(g) Identify the supervisor of an employee

4
Running head: office management system using SQL
(h) Identify the employee details
(i) Identify the project details
(j) List of supervisors.
4. Entity Relationship Diagram (E-R diagram):
Fig 1: Entity Relationship Diagram
(Source: learner)
Here In this E/R diagram the Employee manages the department and works on the department
entity (Thalheim, 2013). The department also controls the Project. The supervision attribute of an
employee is related to another employee. Here the Employee and the department are the example
of many to one relationship in works. Similarly the department and the employee are in one to
one relationship in working. The employee to supervisor holds the one to many relationship
similarly as department to project. The example of many to many relationships is employee and
project (Foster, 2013).
Here ‘Ssn’ is the primary key of the employee table; Name is the primary key of the department,
Number. The primary keys of the project are ‘Name’ and ‘Number’. The Department is
considered as the weak entity which is dependent on employee, which considered as Strong
entity. The flaws of this E-R relationship diagram are there is no foreign key between the
department and the project. Therefore the referential integrity has been not met up.

5
Running head: office management system using SQL
5. Database creation:
CREATE TABLE EMPLOYEE (
Ssn int NOT NULL PRIMARY KEY (7),
Bdate int(7),
Fname VARCHAR2 (20),
Minit VARCHAR2 (20),
Lname VARCHAR2 (20),
Address VARCHAR 2(20),
Salary int (7),
Sex VARCHAR2 (20),
);
CREATE TABLE DEPARTMENT (
Name VARCHAR2 (20) PRIMARY KEY,
Location VARCHAR2 (20),
Number int (20) PRIMARY KEY,
Employees VARCHAR2 (20) FOREIGN KEY REFERENCES EMPLOYEE (Ssn),
);
CREATE TABLE PROJECT (
Name VARCHAR2(20) primary key,
Location VARCHAR2(20),
Number int (20) primary key,
Employees VARCHAR2 (20) FOREIGN KEY REFERENCES EMPLOYEE (Ssn),
);
CREATE TABLE DEPENDENT (
Ssn int PRIMARY KEY REFERENCES Employee (Ssn),
Name VARCHAR2 (20)
Sex VARCHAR2 (20),
Birth_dateint(20),
Relationship VARCHAR2 (20),
);
6. DATA Insertion:
The data insertion is one of Data Manipulation Language (DML).
INSERT INTO EMPLOYEE
(Ssn, Bdate, Fname, Minit, Lname, Address, Salary, Sex)
VALUES
(0050, 05061980, ‘Mark’, Null, ‘Steverson’, ‘30 Benjin Street New Zeland’, ‘ 200’, ‘Male’);
INSERT INTO EMPLOYEE
(Ssn, Bdate, Fname, Minit, Lname, Address, Salary, Sex)

6
Running head: office management system using SQL
VALUES
(0051, 05071986, ‘Jammy’, Null, ‘Stark’, ‘,’Mount eden road’, ‘150’, ‘Male’);
INSERT INTO EMPLOYEE
(Ssn, Bdate, Fname, Minit, Lname, Address, Salary, Sex)
VALUES
(0051, 05071986, ‘Daniel’, Null, ‘Paul’, ‘173 Park Road’, ‘160’, ‘Male’);
INSERT INTO EMPLOYEE
(Ssn, Bdate, Fname, Minit, Lname, Address, Salary, Sex)
VALUES
(0060, 15021990, ‘Adam’, Null, ‘Smith’, ’15 Parth’, ‘360’, ‘Male’);
INSERT INTO EMPLOYEE
(Ssn, Bdate, Fname, Minit, Lname, Address, Salary, Sex)
VALUES
(0062, 17091989, ‘Sam’, Null, ‘Noel’, ‘173 Park Road’, ‘190’, ‘Male’);
INSERT INTO DEPARTMENT
(Name, Location, Number, Ssn)
VALUES
(‘PRODUCT MANEGER’, ‘CAMBRIDGE’, 001, 0050);
INSERT INTO DEPARTMENT
(Name, Location, Number, Ssn)
VALUES
(‘Java Devoloper’, ‘Parth’, 002, 0060);
INSERT INTO DEPARTMENT
(Name, Location, Number, Ssn)
VALUES
(‘HRD’, ‘CAMBRIDGE’, 004, 0055);
INSERT INTO DEPARTMENT
(Name, Location, Number, Ssn)
VALUES
(‘Dot NET’, ‘Wellington’, 007, 0062);
INSERT INTO DEPARTMENT
(Name, Location, Number, Ssn)
VALUES
(‘Dot NET’, ‘Wellington’, 007, 0061);
INSERT INTO PROJECT
(Name, Location, Number, Ssn)
VALUES
(‘NEWZELAND BANK’, ‘CAMBRIDGE’, 26, 0050);
INSERT INTO PROJECT
(Name, Location, Number, Ssn)
VALUES

7
Running head: office management system using SQL
(‘SS Solution’, ‘PARTH’, 27, 0061);
INSERT INTO PROJECT
(Name, Location, Number, Ssn)
VALUES
(‘NEWZELAND BANK’, ‘CAMBRIDGE’, 26, 0062);
INSERT INTO DEPENDENT
(Ssn, Name, Sex, Birth_date, Relationship)
VALUES
(0050, ‘Jon Nikkel’, ‘Male’, 03081980, ‘Team Lead’);
INSERT INTO DEPENDENT
(Ssn, Name, Sex,Birth_date, Relationship)
VALUES
(0051, ‘Daniel Paul, ‘Male’, 05071986, ‘Team Lead’);
7. Select Comment:
The select comment is used to select the data from database according to conditions.
Example 1:
SELECT Fname, Lname FROM EMPLOYEE;
Example 2:
SELECT Name FROM DEPARTMENT;
Example 3:
SELECT Name FROM PROJECT;
Example 4:
SELECT Number FROM DEPARTMENT WHERE Name=’HRD’;
Example 5:
SELECT TOP (Salary) FROM EMPLOYEE;
8. Join:
Joining is the clause which helps to combine the rows of the multiple tables. Here learner has
used the Inner join and left outer join to combine the tables.
Example 1:
SELECT EMPLOYEE.ADDRESS
FROM EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLYEE.Ssn = DEPARTMENT.Ssn
WHERE DEPARTMENT.NAME=’ENGINEER’;
Example 2:
SELECT EMPLOYEE.Fname
FROM EMPLOYEE
INNER JOIN PROJECT ON EMPLYEE.Ssn = PROJECT.Ssn
WHERE PROJECT.NAME=’NEWZELAND BANK’;

8
Running head: office management system using SQL
Example 3:
SELECT DEPENDENT.Name as Project Lead
FROM DEPENDENT
INNER JOIN PROJECT ON DEPENDENT.Ssn = PROJECT.Ssn
WHERE PROJECT.NAME=’NEWZELAND BANK’;
Example 4:
SELECT EMPLOYEE.Fname, DEPARTMENT.Name, PROJECT.Name
FROM ((EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLOYEE.Ssn = DEPARTMENT.Ssn)
INNER JOIN PROJECT ON EMPLOYEE.Ssn= PROJECT.Ssn)
WHERE EMPLOYEE.Ssn= ‘0050’;
Example 5:
COUNT * AS Number of Projects
FROM ((EMPLOYEE
RIGHT JOIN DEPARTMENT ON EMPLOYEE.Ssn = DEPARTMENT.Ssn)
RIGHT JOIN PROJECT ON EMPLOYEE.Ssn = PROJECT.Ssn)
WHERE DEPARTMENT.Name = ‘Java’;
9. Table Modification:
Table modification is update and deletion. This DML query helps to update and delete the
existing data of table.
Example 1:
UPDATE EMPLOYEE
SET Fname= ‘MARK’
WHERE Ssn= 0050;
Example 2:
UPDATE DEPARTMENT
SET Name = ‘JAVA DEVOLOPER’
WHERE Number= 001;
Example 3:
UPDATE PROJECT
SET Name= ‘SD SOLUTION’
WHERE Number = 26;
Example 4:
UPDATE PROJECT
SET Name= ‘DBA’, Location = ‘PERTH’
WHERE Number = 002;
Example: 5
DETETE FROM EMPLOYEE
WHERE Ssn = ‘0081’;

9
Running head: office management system using SQL
10. View Generation:
Example 1:
CREATE VIEW EMPLOYEE VIEW AS
SELECT EMPLOYEE.Fname, EMPLOYEE.Address, DEPARTMENT.Name, PROJECT.Name
FROM ((EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLOYEE.Ssn= DEPARTMENT.Ssn)
INNER JOIN PROJECT ON EMPLOYEE.Ssn= PROJECT.Ssn)
WHERE EMPLOYEE.Ssn= 0050;
Example 2:
CREATE VIEW HR_VIEW AS
SELECT EMPLOYEE.Fname, EMPLOYEE.Address, EMPLOYEE.Salary,
DEPARTMENT.Name, PROJECT.Name
FROM ((EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLOYEE.Ssn = DEPARTMENT.Ssn)
INNER JOIN PROJECT ON EMPLOYEE.Ssn = PROJECT.Ssn)
WHERE EMPLOYEE.Ssn = 0050;
Now, learner has updated a tuple on EMPLOYEE, DEPATMENT, PROJECT and the view
remains same
The view will update if and only if conditions are matched mentioned below:
The SELECT statement might not contain DISTINGUE.
The SELECT statement might not contain SUMMARY clause.
The SELECT statement might not contain SET function.
The SELECT statement might not contain ORDER BY clause.
The ‘WHERE’ clause should not contain sub-queries (Ordonez, Cabrera & Gurram, 2017)
Now Lerner has run the code
UPDATE HR_VIEW AS
SET DEPARTMENT.LOCATION= ‘PARTH’
WHERE EMPLOYEE.Ssn = 0050;
It do not make an effect because the view includes multiple tables.
So learner has overcome this problem by using the code
CREATE VIEW HR VIEW AS
SELECT EMPLOYEE.Fname, EMPLOYEE.Address, EMPLOYEE.Salary,
DEPARTMENT.Name, PROJECT.Name
FROM ((EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLOYEE.Ssn= DEPARTMENT.Ssn)
INNER JOIN PROJECT ON EMPLOYEE.Ssn = PROJECT.Ssn)
WHERE EMPLOYEE.Ssn = 0050
AND
DEPARTMENT.Location = ‘PARTH’;

10
Running head: office management system using SQL
11. Check Constraints:
Check constrains is a key word by which we can limit the value of any attribute according to the
criteria. The code is given below
CREATE TABLE EMPLOYEE NAME (
Ssn int NOT NULL PRIMARY KEY (7),
Bdate int(7),
Fname VARCHAR2 (20),
Minit VARCHAR2 (20),
Lname VARCHAR2 (20),
Address VARCHAR2 (20),
Salary int(7) CHECK ( Salary>=100),
Sex VARCHAR2 (20),
);
Now Lerner has inserted data into this table
INSERT INTO EMPLOYEE
(Ssn, Bdate, Fname, Minit, Lname, Address, Salary, Sex)
VALUES
(0050, 05061980, ‘Smith’, Null, ‘Paul’, ‘30 Benjin Street New Zealand’, ‘90’, ‘Male’);
This sql insertion has not run because of violation in salary
12. Learning Outcome:
In this assignment learner has learnt the E-R diagram and after this Cardinality ration such as one
to one and many to one (Stonebraker, Deng & Brodie, 2016). Then learner has learnt to create
database and the basic insert, update, and delete operations. Learner has also gathered knowledge
of the integrity constrains. Learner has learnt the basic select operation. Learner has learnt to
collect data from multiple tables by joining operations. The knowledge of view operations also
gathered and updates the view, the limitations of view operations.
12. Evaluation:
The initial stage of the project such as E-R diagram the table creation and the basic operations
insert, update, delete goes smoothly. Then the complex part of the project is relating the
databases and collecting the data through multiple tables. The first problem of this project is data
redundancy. However, learner has reduced by decomposing the EMPLOYEE and DEPENDENT
table. First in the select operation learner has used the union operation, which gave data
redundancy but in order to reduce the redundancy learner has used the joining operation. Learner
has faced problem in the view operations then learner studied and indentified the problem.
In future when learner might work in the industries, learner has to deal with big data. There is a
high possibility of data redundancy. Keeping this in mind learner would rather work hard on the

11
Running head: office management system using SQL
normalisations and table decomposition. Learner might definitely use the most preferable joining
operations to reduce the search time.
Conclusion:
In this project, the learner has learned about the entity, entity relationship diagrams, the basic sql
syntaxes. Then the learning is implemented in the real project of office management system.
From this project the learner learns to apply select, count and the join operations. The learner
also faced some critical problems of data redundancy, which helped the learner to lean the
normalisation and effective table decomposition. The learner also applied view definition
language to create spate view to separate users.

12
Running head: office management system using SQL
References:
Bryla, B., & Loney, K. (2013). Oracle Database 12c The Complete Reference. New York:
McGraw-Hill Osborne Media.
Foster, E. C., & Godbole, S. V. (2014). Overview of Oracle. In Database Systems (pp. 301-309).
Apress.
Ordonez, C., Cabrera, W., & Gurram, A. (2017). Comparing columnar, row and array DBMSs to
process recursive queries on graphs. Information Systems, 63(2), 66-79.
Stonebraker, M., Deng, D., & Brodie, M. L. (2016, December). Database decay and how to
avoid it. In Big Data (Big Data), 2016 IEEE International Conference on (pp. 7-16). IEEE.
Thalheim, B. (2013). Entity-relationship modeling: foundations of database technology. New
York: Springer.