Oracle SQL Assignment (coursework) – Homework is the development of software for recording the projects

Oracle SQL Assignment (coursework)

Scenario

Consider that you are the team of founders of a new IT company. Your homework is the development of software for recording the projects and the progress of each project that the company will undertake.
The company will be engaged in undertaking software projects. The aim is to fully record all the details of these projects and to systematically monitor them for the entire period from the initial discussions to the analysis, implementation and final delivery of the project.
Homework is the implementation of a DataBase where this information will be stored. Starting from the initial finding of a project, it is necessary to record the details of the potential customer, the type of the business and to categorize the type of project to be taken over.
Then, all the contact information (who spoke to whom and on what matter, which documents were exchanged, if there are deadlines at each stage, who is responsible for the company, etc.) will be stored in the Base so that there can be follow up and monitoring in Communication. Of course, it is also necessary to define who will be responsible for the communication, that is to say, that the company’s employees should have an exclusive understanding with the customer.
The next step is to take or reject a project (with similar reasoning and of course dates). In case of a takeover, the project timetable (projects – sub-projects – work with calculated and spent man-hours) will be kept in the Base, who has undertaken to implement them, who are the project managers in each project and of course, there will be a graduation performance of each employee.
At the same time, financial data will be stored on the project (and any subdivision) and financial earnings of employees. The goal is to prove at the end of the project if the compared project was profitable or not.
Based on all of the above, you will need to design and implement a system that, among other things, must meet the following requirements:
1) Have appropriate tables so that all data can be stored

2) Record the salary of staff and which can not be less than 800 € and not more than 5000 €
3) For each employee, his / her specialty and his / her occupation must be recorded
4) An employee will not be allowed to take part in more than 2 projects in parallel.
5) Customer and employee information in the email field should check whether or not you include the @
6) For projects, the expiration date can not be before the start date (the same applies to each subdivision of the project)
7) The Ids in each array must be generated automatically (auto increment) using sequence and trigger mechanisms

Deliverables
Your company should:
1) Design the system (production of ER diagrams and a list of all tables and columns) & Documentation
2) Implement it (SQL – Oracle Code / commands) Use of triggers & sequence
3) Input the data (demo data) and create lists of records for each table
4) Cost it
5) Draw SQL queries that will
i. Returns a list of employee details (Employees are considered AND owners !!!)
ii. Returns a list of projects sorted by date of withdrawal
iii. Returns a list of projects – subprojects – jobs sorted by the start date of each phase
iv. Returns the number of projects per status (active, discarded, completed, etc.)
v. Returns list of employees per project phase and duration of each phase (project code required)
vi. Returns the man-hours for each project by analysis per employee
vii. Returns financial details of the project: budget, the final cost of work in man-hours X man-hours cost, employee’s work rate in the project, the final amount corresponding to his salary the engagement with this work