Explain how aggregation and generalization help to minimize redundancy while designing ER Diagram.
Questions
Subject: All Subjects
Topic: All Topics
Consider the following relational database:
- Employee(empid, gender, name, dob, salary, address, deptno, start_date)
- Department(dpetno, name)
- Students(std_id, gender, name, dob, projectno, deptno)
- Project(projectno, name, location)
Write Relational algebra expression for the following:
- Find employee name and address who works in "xyz company".
- Delete all employees whose salary is greater than Rs. 1,00,000.
- Update department name to "SitaGroup" where student "Sita" is involved.
- Find the average salary of all employees.
- Insert student record named "Gopal Shrestha" for department "Ram Group".
Consider the following relational database:
- Employee(empid, gender, name, dob, salary, address, deptno, start_date)
- Department(dpetno, name)
- Students(std_id, gender, name, dob, projectno, deptno)
- Project(projectno, name, location)
Write SQL queries for the following based on the schema given:
- Display the name of employees working in 'BBM' Department.
- List details of employees earning salary above average salary.
- List details of all students along with the name of project they are involved in. Also display the name of the department.
- Decrease salary by 10% of all employees whose salary is more than 10,000.
- Insert (empid, name, dob, salary) to the table Employee.
Consider the following relational database:
- Patient(PID, Pname, Paddress, Pgender, disease)
- Doctor(DID, Dname, Daddress, Department, salary)
- Appointment(PID, DID, time)
Write SQL syntax for the following:
- Display name and address of patients who are suffering from Tuberculosis(TB).
- Count the number of doctors working in ENT department.
- Display name of doctor who is receiving maximum salary.
Consider the table given below:
eID | eName | Designation | Address | Age |
---|---|---|---|---|
1001 | Rabin | Marketing | Kathmandu | 27 |
1002 | Shekhar | Sales | Lalitpur | 32 |
1003 | Raman | Accounting | Lalitpur | 33 |
1006 | Rashmi | Sales | Kathmandu | 31 |
1015 | Sushmita | Marketing | Bhaktapur | 28 |
1027 | Shubham | Marketing | Bhaktapur | 29 |
Write the following commands for the given table.
- Create a table named 'Employees' for the following entries, using different constraints. Also use primary key in the table creation.
- Insert all the data in the employees table like given above.
- Select all the entries with designation 'Marketing'.
- Select all the entries with address 'Kathmandu'.
Consider the following relational database schema consisting of the four relation schemas.
- passenger (pid, pname, pgender, pcity)
- agency (aid, aname, acity)
- bus (bid, bdate, time, src, dest)
- booking (pid, aid, bid, bdate)
Answer the following question using relational algebra queries:
- Give the details of all buses from Dhangadhi to Kathmandu.
- Find the name of passengers who booked at least one bus.
- Find the bus number for the passenger with pid p04 for bus to butwal before 20/05/2021.
- Find the name of passenger who has not booked any bus.
- Find the details of all male passengers associated with Ugratara Agency.
Consider the relational database:
- Students (Roll, SName, SAddress, SContact, SFee)
- Teachers (TID, TName, TSalary, TAddress)
- Teaches (Roll, TID)
Write the SQL statement for the following:
- Find the record of the student who pay fee in the range of Rs. 1000 and Rs. 10000.
- Find the name of teacher consisting of at most 6 characters.
- List the name of all teachers in ascending order.
- Find the name of the highest paying teacher.
- Insert a new tuple in the relation teacher.
Consider the relational database:
- Students (Roll, SName, SAddress, SContact, SFee)
- Teachers (TID, TName, TSalary, TAddress)
- Teaches (Roll, TID)
Write the relational algebra for the following:
- Find the Roll of all students who are taught by teacher "Raman".
- Delete all students who are from Biratnagar.
- Increase the fee of students who are from Kathmandu by 2%.
- Find the name of teacher who lives in Kathmandu and get salary greater than Rs. 100000.
- Change the name of student to Sushila whose roll number is 10.
Consider the following database, where primary keys are underlined.
- Supplier(supplier_id, supplier_name, city)
- Supplies(supplier_id, part_id, quantity)
- Parts(part_id, part_name, color, weight)
Write relation algebra and SQL expressions for each of the following queries.
- Find the name of all suppliers located in the city "Kathmandu" that supplies part 'P01'.
- Find the name of all parts supplied by "RD Traders".
- Find the name of all parts that are supplied in quantity greater than 300.
- Find the number of parts supplied by "S02".
- Find the number of parts supplied by each supplier.
Consider the following schema of the relational database.
- Publisher(pid, name, location)
- Book(bid, title, author, page, price)
- Publish(bid, pid, publish_date)
Write Relational Algebra expressions for the following:
- Find title of book published by Addison_wisely.
- Update the author name to "MNP" of book "introduction database".
- Find the publish date of book "Database Management".
Normalize the following Student_Course table to 2NF.
S_ID | S_Name | C_ID | C_Name |
---|---|---|---|
1 | Subham | C01 | English |
1 | Subham | C02 | Economics |
2 | Supriya | C03 | Mathematics |
3 | Sawan | C01 | English |
Consider schema U = { A, B, C, D, E, F } and the set of functional dependencies:
Find closure of AB.