#536 Consider the following scenario you are developing

Consider the following scenario you are developing - Computer Science

ChemistryExplain daily providing Q&A content “#536 Consider the following scenario you are developing" in Computer science, Ba computer science, Berkeley computer science, Computer science associate degree jobs

Get the Free Online Chemistry Q&A Questions And Answers with explain. To crack any examinations and Interview tests these Chemistry Questions And Answers are very useful. Here we have uploaded the Free Online Chemistry Questions. Here we are also given the all chemistry topic.

 ChemistryExplain team has covered all Topics related to inorganic, organic, physical chemistry, and others So, Prepare these Chemistry Questions and Answers with Explanation Pdf.

For More Chegg Questions

• Inorganic Chemistry
• Organic Chemistry
• Physical Chemistry

Join Our Telegram Channel for Covers All Update by ChemistryExplain:- Click Now

Free Chegg Question

Consider the following scenario. You are developing a fault management system. The database maintains the following information: Fault (fault_id,fault_type, fault_date) FaultType(fault_type, fault_description) FaultManagement(fault_id,technician_id, assigned_date) Technician(technician_id, name, age) Write relational algebra expression and SQL statements for the following:

i. Find all the faults with fault description

ii. Find the description of all the faults that occurred during Jan 2020-Dec 2020

iii. Find the name of the faults assigned to Aslam

iv. Group all the faults based on type, find their count and the average age of the technician managing it

v. Find all the faults that are not assigned to any technician

vi. Find the technician who has not been assigned any fault

vii. Find all the faults that end with ‘dead’

viii. Find the oldest technician

ix. Find the youngest technician

x. Find the second oldest technician

xi. Find the type of fault which has not occurred so far

xii. Find the number of faults assigned to each technician

xiii. Find the number of faults that occurred in every month of the year 2020

xiv. Find all the technicians who have not been assigned more than two faults

xv. Find all the faults, their type, assigned date, the technician details. Sort the faults based on the assigned date

//-----------------------------------------------------------------------------//
Only required an answer to question no.2

Question-2 marks [06] Read the above scenario, which describes the data requirements of the fault management system.

(a) Identify the main entity types in the given scenario.

(b) Identify the main relationship types between the entity types described in (a) and represent each relationship as an ER diagram.

(c) Determine the multiplicity constraints for each relationship described in (b).

(d) Determine candidate and primary key attributes for each (strong) entity type.

(e) Using your answers (a) to (d) attempt to represent the data requirements of the fault management system as a single ER diagram.

(f) Implement the above schema in one of the RDBMSs (in form of tables) you currently using.

Note: I just required an answer of question no.2 only. The reason of copying question no.1 is because the scenario is in question no.1.

Free Chegg AnswerFor More Chemistry Notes and Helpful Content Subscribe Our YouTube Chanel - Chemistry Explain  

Free Chegg Answer

SOLUTION IN BOTH SQL AND RELATIONAL ALGEBRA:

As you have posted multiple questions, I'm going to answer only first four sub part of the question first as per Chegg qna policy

Answering more than one question will result in the revocation of an expert's account.

It has also been informed to students to post a single question at a time.

Post the next question separately,and love to answer.

IN SQL:

1).SELECT fault_type,fault_description FROM Fault;

2).SELECT FaultType.fault_description FROM Fault INNER JOIN FaultType ON Fault.fault_id=FaultType.fault_id WHERE YEAR(Fault.fault_date)=2020;

3).SELECT Fault.fault_type FROM Fault INNER JOIN FaultManagement ON Fault.fault_id=FaultManagement.Fault_id INNER JOIN Technician ON FaultManagement.technician_id=Technician.technician_id WHERE Technician.name="Aslam";

4).SELECT COUNT(Fault.Fault_id),AVG(Technician.age) FROM Fault INNER JOIN FaultManagement ON Fault.fault_id=FaultManagement.Fault_id INNER JOIN Technician ON FaultManagement.technician_id=Technician.technician_id GROUP BY Fault.Fault_type;

IN RELATIONAL ALGEBRA:

1) πfault_type,fault_description(Fault);

2) πFaultType.fault_description(σYEAR(Fault.fault_date)=2020(Fault X FaultType));

3) πFault.fault_type(σTechnician.name="Aslam"( Fault X FaultManagement X Technician))

4) πCOUNT(Fault.fault_id),AVG(Technician.age) ((σTechnician.name="x"( Fault X FaultManagement X Technician));