2. : office management system using SQL.

Added on -2019-09-23

| 12 pages
| 2748 words
| 256 views

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 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
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.

Found this document preview useful?

You are reading a preview
Upload your documents to download
or
Become a Desklib member to get accesss

Students who viewed this