Database management

(Question 1)(7 points).Assume the following relation EMP has the following rows: Name Office Dept Salary Smith 400 CS 45000 Jones 220 Economics 35000 Green 160 Economics 50000 Brown 420 CS 65000 Smith 500 Finance 60000 Use relational algebra to write down the operations for the following. a) Find the minimum salary; b) Find the average salary: c) Find the total salary for the Economics department; d) Select only those Employees in the Economics department; e) Select only those Employees whose name are not Jones; f) Select only those Employees with last name Smith whose department is Finance; g) Select only those Entployees who are either in the CS or in the Finance department. US or in the Finance department. (Question 2)(5 points), Create an Entity Relationship diagram that captures this information about the UPS system described below. Shipped items are the heart of the UPS product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the UPS system at a single retail center. Retail centers are characterized by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard UPS transportation events. These transportation events characterized by a unique scheduleNumber, a type, and a deliveryRoute. are (Ouestion 3) Th 11 (Question 3). The following tables are given: Student LName FName Studio Major Grades Studid Susan Courid Grade James A+ Smith Bond Smith Cecil 131313 Comp 007007 Math 555555 Comp 010101 Math Susan 007007 555555 007007 007007 John A C 302 C 302 C301 M214 C 201 C 201 A+ Course PName Courid Points B- 15 DB Sys C302 SofEng C301 DisMat M214 Pr&Sys C201 131313 555555 131313 007007 010101 Depr Comp Comp Math Comp 15 22 22 C 302 C 201 C 201 A Write the SQL queries for the following: a) (3 points). Retrieve the last and first names of Math students b)(3 points). Find all different grades. c)(3 points). Retrieve department names that have '2' as the second character in Courld. d) (3 points). Sort StudID and CourID in ascending and descending orders, respectively. e) (3 points). What is the total point value of the courses in the COMP dept? 1) (3 points). How many different first names do COMP majors have? (Question 4) (5 points). Let the first transaction T, transfers 5% from the balance X to the balance Y, the second transaction T2 transfers $350 of the balance from X to Y, the third transaction Tz transfer 8% + $300 of the balance from Y to the X. Write the transaction schedule for this problem. ( Oosti SVE (Question 5)(5 points). Build the horizontal and vertical fragmentations for the table Customer" given below. CUS_NUM CUS_NAME CUS_ADDRESS CUS_LIMIT CUS_BAL CUS_RATING O Sinex, Inc. 12 Main St. $3,500.00 $2,700.00 3 11 Martin Corp 321 Sunset Blvd $6,000.00 $1,200.00 1 12 Mynux Corp. 910 Eagle St. $4,000.00 $3,500 00 3 13 BTBC, Inc. Rue du Monde $3,000.00 $5,890.00 3 14 Victory, Inc. 123 Maple St. $1,200.00 $550.001