logo

Office Management System using SQL

12 Pages2748 Words208 Views
   

Added on  2019-09-23

About This Document

This article provides a comprehensive guide on creating an office management system using SQL. It covers data requirements, transaction requirements, data queries, entity relationship diagram, database creation, data insertion, select comment, join, table modification, view generation, and check constraints. The article also includes the learning outcomes and evaluation of the project.

Office Management System using SQL

   Added on 2019-09-23

ShareRelated Documents
1Running head: office management system using SQLOFFICE MANAGEMENT SYSTEM USING SQLTable of Content
Office Management System using SQL_1
2Running head: office management system using SQLIntroduction:....................................................................................................................................31. Data Requirements:.....................................................................................................................32. Transaction Requirements:..........................................................................................................33. Data queries:................................................................................................................................34. Entity Relationship Diagram (E-R diagram):..............................................................................45. Database creation:........................................................................................................................56. DATA Insertion:..........................................................................................................................57. Select Comment:..........................................................................................................................78. Join:..............................................................................................................................................79. Table Modification:.....................................................................................................................810. View Generation:.......................................................................................................................911. Check Constraints:...................................................................................................................1012. Learning Outcome:..................................................................................................................1012. Evaluation:...............................................................................................................................10Conclusion:....................................................................................................................................11References:....................................................................................................................................12
Office Management System using SQL_2
3Running head: office management system using SQLIntroduction: 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 departmentEnter the details of project Enter the details of dependentData Update and delete:Update/delete the details of an employee.Update/delete the details of a departmentUpdate/delete the details of a projectUpdate/delete the details of a dependent3. 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
Office Management System using SQL_3
4Running 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 anemployee is related to another employee. Here the Employee and the department are the exampleof 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.
Office Management System using SQL_4

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Database Systems for Toulis Construction: Diagrams, SQL Queries, Security Measures and Future Improvements
|23
|2197
|298

Research Report on Entity Relationship Diagram Assignment PDF
|6
|1056
|191

Entity Relationship Diagram for Database INTRODUCTION 3 TASKS
|20
|1532
|227

Components of DBMS - Doc
|6
|730
|205

Entity Relationship Model : Assignment
|39
|3339
|277

Database And Business Intelligence Assignment 2022
|12
|1317
|52