SQL and Relational Algebra Queries Guide
Uploaded by
pradeepshettar50SQL and Relational Algebra Queries Guide
Uploaded by
pradeepshettar50Common questions
Powered by AIThe query is: SELECT e1.FName, e1.LName FROM Employee e1 JOIN Employee e2 ON e1.SuperSSN = e2.SSN JOIN Employee e3 ON e2.SuperSSN = e3.SSN WHERE e3.SSN = '888665555'. This uses two self-joins on the Employee table to traverse up the hierarchical structure, first linking employees to their supervisors, then those supervisors to their superiors with the specified SSN '888665555'.
Third Normal Form (3NF) ensures that every non-prime attribute is non-transitively dependent on the primary key, eliminating transitive dependencies. For example, if a table includes attributes like EmployeeID, DepartmentID, and DepartmentName, and where DepartmentName depends only on DepartmentID but not directly on EmployeeID, it violates 3NF. Converting it to 3NF requires separating the department info into a new table. This reduces redundancy and prevents anomalies during data operations.
The SQL query to achieve this is: SELECT DName, COUNT(SSN) FROM Employee e JOIN Department d ON e.DNo = d.DNumber GROUP BY DName HAVING AVG(Salary) > 30000. This groups the employees by department name, uses COUNT to get the number of employees, and filters those with an average salary greater than $30,000 using the HAVING clause.
The query to list names and addresses of all guests, sorted alphabetically by name from the Guest table, can be constructed using the SQL SELECT and ORDER BY clauses: SELECT name, address FROM Guest ORDER BY name ASC. This selects the name and address fields from the Guest table and orders the results alphabetically by the name field.
A SQL query to create this view is: CREATE VIEW ProjectSummary AS SELECT p.PName, d.DName, COUNT(w.ESSN) AS NumEmployees, SUM(w.Hours) AS TotalHours FROM Project p JOIN WorksOn w ON p.PNumber = w.PNo JOIN Department d ON p.DNum = d.DNumber GROUP BY p.PName, d.DName HAVING COUNT(w.ESSN) > 1. A view abstracts complex queries and provides a simplified, consistent interface for data retrieval, enhancing security and convenience.
The EMP_PROJ relation is not in Third Normal Form (3NF). A relation is in 3NF if it is in 2NF and all the attributes are functionally dependent only on the primary key. Given Ssn -> Ename and Pnumber -> {Pname, Plocation}, transitive dependency exists through non-prime attributes which violates 3NF. Normalizing further involves removing these transitive dependencies, forming separate relations for employee and project details.
The SQL statement to list all family rooms with a price below Rs. 400 would be: SELECT roomno, hotelno, type, price FROM Room WHERE type = 'family' AND price < 400. This selects all rooms from the Room table where the type is 'family' and the price is less than Rs. 400.
To retrieve all details of hotels located in Mysuru city, the SQL query is: SELECT * FROM Hotel WHERE city = 'Mysuru'. This selects all columns from the Hotel table where the city column matches 'Mysuru'.
A relation is in Second Normal Form (2NF) if it is in First Normal Form (1NF), and all non-key attributes are fully functional dependent on the primary key. In the EMP_PROJ relation with dependencies: Ssn, Pnumber → Hours, Ssn → Ename, Pnumber → Pname, Plocation, it is not in 2NF because Ename and {Pname, Plocation} depend on parts of the composite key (either Ssn or Pnumber). To normalize, create two new relations: EMP(Ssn, Ename) and PROJ(Pnumber, Pname, Plocation), maintaining Hours with Ssn and Pnumber in EMP_PROJ.
The relational algebra expression can be constructed by joining and selecting the necessary conditions: (DNo=5 and PName='Product X' and Hours>10) (Employee ▹◃ (WorksOn ▹◃ Project)) ▹◃ Department. This expression first joins the Employee, WorksOn, and Project tables to filter employees in department 5 who work more than 10 hours on 'Product X'.