#573 Read the above scenario, which describes the data

Read the above scenario, which describes the data - Computer Science

ChemistryExplain daily providing Q&A content “#573 Read the above scenario, which describes the data" 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

Read the above scenario, which describes the data requirements of fault management system. 

(a) Identify the main entity types in 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 relationships 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 systemas a single ER diagram.

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

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 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 ends with dead

viii. Find the oldest technician

ix. Find the youngest technician

x. Find the second oldest technician

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

xii. Find the number of faults assigned to each technician

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

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

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

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

Free Chegg Answer

Write relational algebra expression and SQL statements for the following:

1).SELECT fault_type,fault_description FROM Fault;

πfault_type,fault_description(Fault);

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

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

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";

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

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;

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

Explaination for SQL:

SELECT statement is used for output of that column, FROM is used for selecting the tables from which we want output,INNER JOIN is used for join operation on tables with specified columns using ON.WHERE is used to apply condition for output.COUNT() ,AVG() are aggregate functions in SQL where COUNT() is used to count the tuples of result and AVG() is used to find average of given column values.GROUP BY statement groups the result on specified column.Here,group By is used on Fault_type.

Explaination for Relational Algebra:

π is used to output the specified columns.σ is used for conditions . X is used for join operation in relational algebra.AVG() and COUNT() are aggregate functions and work as they work in mathematics.