Formulate two triggers for this database
I. Consider a BUSINESS_REVIEW database in which new businesses submit their business
proposals to a bank for funding consideration. Reviews by bank analysts are recorded for use in
the selection process. The database system caters primarily to the bank analysts who record
answers to evaluation questions for each proposal they review and make recommendations
regarding whether to accept or reject the funding request. The data requirements are
summarized as follows:
o Submitters of proposals are uniquely identified by e–mail id. First and last names are
recorded.
o Each proposal is assigned a unique identifier by the system and is described by a title,
summary, and the name of the (electronic) file containing the proposal.
o A proposal may have multiple authors, but one of the authors is designated as the
contact author.
o Business analysts who review the proposals are uniquely identified by e–mail address.
Each reviewer’s first name, last name, phone number, branch information, and topics of
expertise are also recorded.
o Each proposal is assigned between two and four analyst. A analyst rates each proposal
assigned to him or her on a scale of 1 to 10 in four categories: knowledge of the
business proposed, chance of success, location of the business, and relevance to the
bank. Finally, each reviewer provides an overall recommendation regarding each
proposal.
o Each analysis contains two types of written comments: one to be seen by the bank loan
authorities only and the other as feedback to the proposer.
Design a complete entity–relationship diagram for the BUSINESS_REVIEW database and
translate the entity –relationship design to a relational database structure
Formulate the following queries in SQL:
• List the names of all employees who have a dependent with the same first name as
themselves.
• Find information about employees named ‘Smith’
• Find the department name, manager name, and manager salary for every department
• Find employee name, supervisor name, and employee salary for each employee who works
in the ‘Research’ department
• Find the project name, controlling department name, number of employees, and total hours
worked per week on the project for each project
• Find the project name, controlling department name, number of employees, and total hours
worked per week on the project for each project with more than one employee working on
it
• Retrieve the names of all employees in department 5 who work more than 10 hours per
week on the ProductX project.
• Find the names of all employees who are directly supervised by ‘Franklin Wong’.
• For each department whose average employee salary is more than $30,000, retrieve the
department name and the number of employees working for that department.
• Retrieve the names of employees who make at least $10,000 more than the employee who
is paid the least in the company
• Formulate two triggers for this database.Explain what the trigger does, and show the
corresponding SQL statement
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.