logo

Project-Based Learning in SQL

   

Added on  2019-09-23

12 Pages2748 Words208 ViewsType: 208
Databases
 | 
 | 
 | 
1Running head: office management system using SQLOFFICE MANAGEMENT SYSTEM USING SQLTable of Content
Project-Based Learning in 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
Project-Based Learning in 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
Project-Based Learning in 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.
Project-Based Learning in 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

SQL Server Management Studio | Report
|8
|925
|12

Relational Database Design for Aisle Perfect
|3
|931
|125

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