COMPUTER SCIENCE 378: INTRODUCTION TO DATABASE MANAGEMENT SAMPLE EXAMINATION NEW EXAM ACTUAL EXAM QUESTIONS AND ANSWERS COMPLETE MATERIAL GUIDE 100% ATHABASCA UNIVERSITY

COMPUTER SCIENCE 378: INTRODUCTION TO DATABASE MANAGEMENT SAMPLE EXAMINATION NEW EXAM ACTUAL EXAM QUESTIONS AND ANSWERS COMPLETE MATERIAL GUIDE 100% ATHABASCA UNIVERSITY You will have 2 hoursto complete a similar closed-book examination. Question 1 A real estate company uses a database to store information about customers, property, andcontracts. The following relations are used in the database: Customer: Customer number (unique), name, mailing address, Balance, lawyer name, Discount. Contract: Customer number (unique), Agent number (unique), Property_ID (one per contract), Date ofcontract, Property addresses, Property value, Type, Number of rooms, Land size, Built size, Tax value) The following functional dependencies apply. customer_no name customer_no mail_address customer_no balance customer_no lawyer_name customer_no discount {customer_no, Agent_no} property_id {customer_no, Agent_no} date_contract property_id property_address property_id value property_id type property_id number_rooms property_id land_size This study source was downloaded by from CourseH on :09:09 GMT -05:00 balance lawyer_name discount property_id built_size property_id tax_value Transform these relations into 3NF (please use only the attributes described above, and do not add anynew attributes). Answer: Tables in 3NF will be as follows: • Normalization is the process of reducing duplication of data and ensurereferential integrity. • In third normal form there is no transitive dependency. • Here property Id transitively depends customer and contract, So avoid dependency we separate the tables into 3. Customer Property property_i property_addres valu typ number_room land_siz built_siz tax_valu d s e e s e e e Contract Customer_no name mail_address This study source was downloaded by from CourseH on :09:09 GMT -05:00 MAINTENANCE Create flight ( ) FLIGHTS Create maint ( ) Main_Id: Intg (PK) Plane_Id: Intg (FK) Tech_Id: Intg (FK) Main_type: string Main_date: date Customer_no Agent_no property_id Date_contarct Question 2 Draw a class diagram for the following situation. An airline company has a number of planes. The attributes of a plane include plane_id (unique), name, and vendor. The airline company serves many destinations. The attributes of a destination include destination_id (unique), name, and flight_date. Each plane flies to one or more destinations; or it may beused as back-up; or it may be under maintenance, and therefore will not fly to any destination. For each plane undergoing maintenance, the company records the name of the technician and the type of maintenance performed. A destination may be served by one or more planes. Each plane’s service charges vary by the number of destinations the plane serves. The airline company maintains records of the service charges for each plane when it flies to a certain destination. At the end of each year, the airline company applies a depreciation percentage to each plane. The airline company calculates each plane’s percent depreciation based on the amount of service charges and the total hours of flying for that plane. Plane_Id: Intg (FK) Destination_Id: Intg (FK)Flight_date: date Flight_hour: date Flight_catagory: string Service_charge: float PLANE Plane_Id: Intg (PK) Name: char Vendor: char Add new plane ( ) DESTINATION Destination_Id: Intg (PK) Name: char Flight_date: Date Add new destination ( ) TECHNICIAN Tech_Id: Intg (PK) Name: string Add tech ( ) DEPRECIATION Plane_Id: Intg (PK) Year: date Service_charge: string Hour: date Depreciation_amount: float Calculate dep ( ) This study source was downloaded by from CourseH on :09:09 GMT -05:00 Question 3 Consider the following three relations: Manager(name, address,specialization, salary)Employee(name, address, rank, salary) Project (proj_num, emp_name, manager_name, duration) Write the SQL queries corresponding to the following questions. a. Find the number of different employees who are assigned to projects. b. Find the managers’ average salary. c. List the name and number of projects supervised by each manager. d. Show the names and salaries of managers who supervise employees of rank “beginner" in ascendingorder of salary (use a subquery). e. Show the names and salaries of managers who supervise employees of rank “beginner" in descendingorder of salary (do not use a subquery). f. Show the names and salaries of supervisors who supervise more than 20 employees. Answer: A. SELECT COUNT(DISTINCT ) "No of Employees"FROM employee e,project p WHERE =_name; -- Avg will display only Average Salary of All Mangers B. SELECT AVG(salary)FROM manager; -- Wrote sub query to get count of Projects by Managers C. SELECT NAME,COUNT(*) FROM (SELECT ,_numFROM manager m,project p WHERE = er_name GROUP BY ,_num) a GROUP BY NAME; -- Wrote sub Query for Get Employee who are beginner and clubed into Managers in Project D. SELECT DISTINCT ,yFROM manager m,project p WHERE = er_name AND _name IN (SELECT NAME FROM employeeWHERE rank='beginner') GROUP BY ,_num; -- Direct Query for Get Employee who are beginner and clubed into Mangers in Project E. SELECT DISTINCT This study source was downloaded by from CourseH on :09:09 GMT -05:00 ,y FROM manager m,project p,employee WHERE = er_name AND _name =e.NAME AND ='beginner'GROUP BY ,_num; -- Wrote sub Query for Get Employee count and clubed in manager and checking for havingcount>20 Continues...