Database Design Q.1. In a police station many people lodge First Information Reports. These FIR’s are given to the investigation team headed by an officer. There are many junior officers under this officer. These officers in turn report to an area officer. Draw an ER-Diagram for the above scenario. Q.2. Draw ER-Diagrams for the tables given below in Q.3. Q.3. Create the tables as given below: TABLE NAME - BRANCH FIELD NAMENULL?DATA TYPE BRANCH CODENOT NULLCHAR(4) ADD1VARCHAR2(20) ADD2VARCHAR2(20) CITYVARCHAR2(25) TELEPHONENUMBER(8) TABLE NAME – AIRBUS FIELD NAMENULL?DATA TYPE AIRBUSNONOT NULLCHAR(5) FIRST_CAPNUMBER(3) BUS_CAPNUMBER(3) ECO_CAPNUMBER(3) FIRST_WL_CAPNUMBER(3) BUS_WL_CAPNUMBER(3) ECO_WL_CAPNUMBER(3) TABLE NAME – SERVICE FIELD NAMENULL?DATA TYPE SS_CODENOT NULLCHAR(2) SS_DESCNOT NULLVARCHAR2(25) SS_FARENUMBER(5) TABLE NAME – FARE FIELD NAMENULL?DATA TYPE ROUTE_CODENOT NULLCHAR(7) ROUTE_DESCNOT NULLVARCHAR2(25) ORIGINNOT NULLVARCHAR2(15) DESTINATIONNOT NULLVARCHAR2(15) FIRST_FARENUMBER(5) BUS_FARENUMBER(5) ECO_FARENUMBER(5) TABLE NAME – FLIGHT_SCH FIELD NAMENULL?DATA TYPE FLIGHTNONOT NULLCHAR(4) AIRBUSNOCHAR(5) ROUTE_CODECHAR(7) DEPRT_TIMECHAR(5) JOURNEY_HRSCHAR(5) FLIGHT_DAY1NUMBER(1) FLIGHT_DAY2NUMBER(1) TABLE NAME – FLIGHT FIELD NAMENULL?DATA TYPE FLIGHTNONOT NULLCHAR(4) FLIGHT_DATENOT NULLDATE FIRST_SEATS_BKNUMBER(3) BUS_SEATS_BKNUMBER(3) ECO_SEATS_BKNUMBER(3) TABLE NAME – CONTROL FIELD NAMENULL?DATA TYPE AIR_TAXNUMBER(4) EXCESS_BAG_CHGNUMBER(3) FIRST_BG_LIMITNUMBER(3) BUS_ BG_LIMITNUMBER(3) ECO_ BG_LIMITNUMBER(3) CANC_DEDUC_12NUMBER(2) CANC_DEDUC_6NUMBER(2) CANC_DEDUC_3NUMBER(2) TABLE NAME – CANCELLATION FIELD NAMENULL?DATA TYPE PNRNOT NULLNUMBER(4) FLIGHTNOCHAR(4) FLIGHT_DATEDATE CLASSCHAR(1) RESERV_DATEDATE PASS_NAMEVARCHAR2(20) PASS_ADD1VARCHAR2(20) PASS_ADD2VARCHAR2(20) PASS_ADD3VARCHAR2(20) PASSPORT_NONUMBER(8) SS_CODECHAR(2) CREDIT_CARD_NONUMBER(8) CANCEL_DATEDATE TOTAL_FARENUMBER(8,2) BRANCH_CODECHAR(4) TABLE NAME – RESERVATION FIELD NAMENULL?DATA TYPE PNRNOT NULLNUMBER(4) FLIGHTNOCHAR(4) FLIGHT_DATEDATE CLASSCHAR(1) RESERV_DATEDATE PASS_NAMEVARCHAR2(20) PASS_ADD1VARCHAR2(20) PASS_ADD2VARCHAR2(20) PASS_ADD3VARCHAR2(20) PASSPORT_NONUMBER(8) SS_CODECHAR(2) CREDIT_CARD_NONUMBER(8) PASS_STATUSCHAR(1) TOTAL_FARENUMBER(8,2) BRANCH_CODECHAR(4) TABLE NAME – EMPLOYEE FIELD NAMENULL?DATA TYPE EMPNONOT NULLNUMBER(5) EMP_NAMEVARCHAR2(30) DESIGNATIONCHAR(10) DATE_OF_JOINDATE SALARYNUMBER(9,2) DEPTNONUMBER(2) TABLE NAME – DEPARTMENT FIELD NAMENULL?DATA TYPE DEPTNONOT NULLNUMBER(2) DEPT_NAMEVARCHAR2(30) DEPT_LOCVARCHAR2(20) TABLE NAME – ALLOWANCE FIELD NAMENULL?DATA TYPE DESIGNATIONNOT NULLCHAR(10) SP_ALLOWANCENUMBER(8,2) CONVEYANCENUMBER(8,2) Data Manipulation Language Q.4. Insert the following data in their respective tables. BRANCH
AIRBUS
SERVICE
FARE
FLIGHT_SCH
FLIGHT
CONTROL
EMPLOYEE
DEPARTMENT
ALLOWANCE
CANCELLATION
RESERVATION
Data Retrieval Q.5. List the employees belonging to department 20. Q.6. List the employees who are earning more than 1200 but less than 4000. Q.7. List the employees who have joined after 1st Jan 84 in the order of the joining date. Q.8. List the employees who are either in Officer or Manager position. Q.9. List the employees who are located at New York. Q.10. List the employees who are in the Sales department. Q.11. List the departments that do not have any employees. Q.12. List the employees who are earning more than Robert. Q.13. Find out how many employees are there in the organization. Q.14. Find out how many employees are working in Sales department. Q.15. Find out the total salaries paid to the employees. Q.16. What is the average salary paid to the employees? Q.17. What is the minimum salary paid in department 30? Q.18. Display names & grades of employees based on their designation. DesignationGrade ManagerA OfficerB AnalystC ClerkD Q.19. Display employee names & date of join, Joining date should be displayed in the following format: 26, January Nineteen Ninety Eight. Q.20.Find out how long an employee has worked in the terms of number of: - Days - Months - Years Q.21. Display the total salaries department wise. Q.22. Display the maximum salaries in each department along with the name of the department. Q.23. Display the total salary (Salary+Sp_Allowance+Conveyance) of each employee in the order of total salary. Q.24. List the number of employees along with their department numbers in each department. Q.25. List the department wise total salary. Q.26. List the number of employees in each designation in the descending order. Q.27.List the total salary, maximum & minimum along with the average salary of each employee designation wise. Q.28.List the total salary, maximum & minimum along with the average salary of each employee designation wise for department 30. Q.29.List the total salary, maximum & minimum along with the average salary of each employee designation wise for department 30 & display only those rows that have their average salary greater than 1000. Q.30. List the total salary of the employees for each designation department wise. Q.31. List the employee details such as his employee number, name, date_of_join, basic salary & designation for department=’Marketing’. Q.32. List the employee details such as his employee number, name, date_of_join, basic salary & designation for the employees working in the location ‘America’. Q.33. List the departments where there are no employees functioning. Q.34. Create a view called temp that consists of the employee number, name, date_of_joining from the employee table, department number, department name & location from the department table. Q.35.Use predefined exceptions. Raise & handle storage_error exception when PL/SQL runs out of memory, raise & handle no_data_found exception when a select statement returns no rows, and raise & handle divide by zero exception if a no. is divided by zero. Q.36.Use Cursor to retrieve reservation details based on the class that is passed as a parameter. Q.37.Calculate the fare depending on the class and the special service availed using cursor. Q.38.Create a procedure, which receives branch code as a parameter and calculates the total collection made due to reservation and lost due to cancellation. It also counts the total no. of reservation and cancellations. Q.39. Create a function, which receives day no. of the week as a parameter and return the day in character format. Q.40.Create a package named ‘airline’ which will pack the procedures and functions that we have created. Illustrates objects such as a record type and variable can be declared and used in the package. Q.41.Create a trigger which will display total fare amount for the passenger, when a reservation is made. Q.42.Accept the ‘AirbusNo’ and display its waiting capacity for the different classes. Q.43.Accept the ‘AirbusNo’ and display the flights scheduled for that airbus. Q.44.Accept flight day in characters and display the flights scheduled on the day. For example,if the user enters ‘TUE’ then display all the flights scheduled on Tuesday. Q.45.Write a procedure that accepts ‘FlightNo’ and ‘Date’. Display the total seats booked for the different classes. Q.46.Write a procedure that receives FlightNo and Flight_Date as a parameter. Display the passengers booked on that flight. |