2. : office management system using SQL.

Added on - 23 Sep 2019

  • 12

    Pages

  • 2748

    Words

  • 101

    Views

  • 0

    Downloads

Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 4 of 12 pages
1Running head: office management system using SQLOFFICE MANAGEMENT SYSTEM USING SQLTable of Content
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
3Running head: office management system using SQLIntroduction:DBMS is the system software by which a DBA can create and manage a database. One of themain reasons for using DBMS is having central control of both data and the database. Here, thelearner 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 willexecute this on oracle by software by creating, updating the table1. Data Requirements:Employee: Here learner took an entity of an employee of an organisation. The attributes underEmployee is taken as Ssn, Bdate, Fname, Mname, Lname, Address, Salary, Sex.Department: The department is taken as separate entity of company diagram. The attributesunder department are Name, location number,Project: Project is considered as the separate entity. The attributes taken under project is name,number locationDependent: The dependent is taken as separate entity. Here the attributes are taken on this isname, sex, birth_date, relationship.2. Transaction Requirements:Data insertion:Enter the details of employeeEnter the details of a departmentEnter the details of projectEnter 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
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 departmententity (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 toone relationship in working. The employee to supervisor holds the one to many relationshipsimilarly as department to project. The example of many to many relationships is employee andproject (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 isconsidered as the weak entity which is dependent on employee, which considered as Strongentity. The flaws of this E-R relationship diagram are there is no foreign key between thedepartment and the project. Therefore the referential integrity has been not met up.
desklib-logo
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document