JNTU
JNTU B.Tech III DATABASE MANAGEMENT SYSTEMS Previous Question Paper (March - 2021 - R16)
Answer any five questions. All questions carry equal marks.
Main
Construct an ER diagram for a Airport Management System. Make suitable Assumptions with justifications.
Answer
An Airport Management System requires tracking entities such as Airports, Flights, Aircraft, Passengers, and Pilots. The following ER diagram design assumes a centralized database for a regional aviation authority.
Assumptions:
- Each flight is uniquely identified by a flight number and date.
- An aircraft is assigned to a specific flight for a given journey.
- A pilot can fly multiple types of aircraft, and an aircraft can be flown by multiple pilots over time.
- Passengers book tickets for specific flights.
Entities and Attributes:
- Airport: Airport_Code (PK), Name, City, Country.
- Flight: Flight_ID (PK), Departure_Time, Arrival_Time, Source_Airport, Destination_Airport.
- Aircraft: Aircraft_ID (PK), Model, Capacity.
- Passenger: Passenger_ID (PK), Name, Passport_Number.
- Pilot: Pilot_ID (PK), Name, License_Number.
Relationships:
- Operates: A many-to-many relationship between Pilot and Aircraft.
- Scheduled: A one-to-many relationship where one Airport has many departing/arriving Flights.
- Assigned: A one-to-many relationship where one Aircraft is assigned to many Flights.
- Books: A many-to-many relationship between Passenger and Flight, resulting in a Ticket entity containing Seat_Number and Booking_Date.
The ER diagram structure is defined by these entities linked by the relationships above, ensuring data integrity through primary keys and foreign key constraints between the Flight, Aircraft, and Airport entities.
What is a view? Discuss its role in Database systems.
Answer
A view is a virtual table that does not store data physically but is derived from one or more base tables through a stored query. It acts as a window into the database, presenting data in a specific format to the user.
The role of views in database systems includes:
- Data Security: Views restrict access to sensitive columns or rows by exposing only the necessary data to specific users, effectively hiding underlying table structures.
- Simplification: Complex queries involving multiple joins or aggregations can be encapsulated in a view, allowing users to query the view as a simple table.
- Data Independence: If the underlying table schema changes, the view definition can be updated to maintain the same interface, preventing the need to rewrite application-level queries.
- Customization: Different users can see the same data in different formats or perspectives tailored to their specific requirements.
What is an unsafe query? Give an example and explain why it is important to disallow such queries?
Answer
Sign in to view the complete answer paper.
Explain about Nested queries and correlated nested queries in the SQL.
Answer
Sign in to view the complete answer paper.
What are aggregate operators in SQL? Explain them with suitable examples.
Answer
Sign in to view the complete answer paper.
Explain in detail about Relational Algebra, Domain Relational Calculus with suitable examples.
Answer
Sign in to view the complete answer paper.
Define 5th NF. With an example illustrate how an unnormalised table is converted into 5th NF.
Answer
Sign in to view the complete answer paper.
What is Functional Dependency? Explain 3 NF and BCNF.
Answer
Sign in to view the complete answer paper.
Explain lossless join decomposition.
Answer
Sign in to view the complete answer paper.
Discuss Buffer Management.
Answer
Sign in to view the complete answer paper.
Explain basic timestamp mechanism for concurrency control.
Answer
Sign in to view the complete answer paper.
Discuss how multi level indexes are constructed using B- trees and B+ trees.
Answer
Sign in to view the complete answer paper.
Explain Linear Hashing method of indexing.
Answer
Sign in to view the complete answer paper.