Question
Industrial Database is the database of an industrial organization that produces at the project level. Some requirements related to this database are as follows. Each
Industrial Database is the database of an industrial organization that produces at the project level. Some requirements related to this database are as follows.
Each project is carried out in a department. More than one project can be done in a department. Each project has a manager. It is not necessary for the project manager to be a person working in the department where the project is carried out.
Each employee works in a department. Each department has a manager. It is not necessary for the department manager to be a person working in that department.
An employee can be the manager of multiple departments or projects.
The materials used in the institution are the inputs that form the projects and the materials consumed in the projects. These materials can be produced in the institution, purchased ready-made from companies, or some can be produced and some can be purchased in the institution.
Many materials can be consumed in a project. A material can also be consumed in multiple projects.
Many materials can be produced in a department. A material can also be produced in zero, one, or multiple departments.
A material can be purchased from multiple companies. It is also possible to purchase multiple materials from a company.
The QUANTITY attributes in the PRODUCTION, CONSUMPTION and PURCHASE entity sets respectively show the total production, consumption and purchase amounts for the year.
Answer the following questions.
(1) Provide the Entity-Relationship Diagram (ER Diagram) and the Relational Schema (Relational Schema).
(2) [Using Relational algebra] "Find the personnel identification numbers and names of the people who are managers of departments other than the ones they work in".
(3) [Using SQL] "Find the project numbers and names of the projects whose materials consumed are all materials produced in their own department (not consuming any materials not produced in that department)".
(4) [Using (TRC)]. "Find the employee identification number and name of the person who earns a higher salary than the manager of the department they are working in".
(5) [Using Relational algebra] "Find the name and quantity of all materials consumed in project number 6".
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started