RELATIONAL ALGEBRA
Assume that "ABC media" has decided to have the relational schema for their database. 1. Video (videoCode, videoLength) 2. Model (modelNo, width, height, weight, depth, screenSize) 3. Site (siteCode, type, address, phone) 4. DigitalDisplay (serialNo, schedulerSystem, modelNo) Foreign key: modelNo references Model (modelNo) 5. Client (clientId, name, phone, address) 6. TechnicalSupport (empId, name, gender) 7. Administrator (empId, name, gender) 8. Salesman (empId, name, gender) 9. AirtimePackage (packageId, class, startDate, lastDate, frequency, videoCode) 10. AdmWorkHours (empId, day, hours) Foreign key: empId references Administrator (empId) 11. Broadcasts (videoCode, siteCode) Foreign key: videoCode references Video (videoCode) Foreign key: siteCode references Site (siteCode) 12. Administers (empId, siteCode) Foreign key: empId references Administrator (empId) Foreign key: siteCode references Site (siteCode) 13. Specializes (empId, modelNo) Foreign key: empId references TechnicalSupport (empId) Foreign key: modelNo references Model (modelNo) 14. Purchases (clientId, empId, packageId, commissionRate) Foreign key: clientId references Client (clientId) Foreign key: empId references Salesman (empId) Foreign key: packageId references AirtimePackage (packageId) 15. Locates (serialNo, siteCode) Foreign key: serialNo references DigitalDisplay (serialNo) Foreign key: siteCode references Site (siteCode) Task: Given the above database schema, write Relational Algebra (RA) statements to answer th following queries. 1. (10 points) Find the information of a client named 'Peter Smith'. 2. (10 points) Find all the airtime packages whose broadcasting frequency is more than 5. 3. (10 points) Find the distinct videos that are broadcasted in a site with site code ' S345.. Your relational algebra should only return the video code of such videos. 4. (10 points) Find all the digital displays that are located in a 'restaurant' (i.e., site type is 'restaurant'). For such digital displays, you should only return their serial nos. 5. (10 points) Find the information of all the technical supports who specialize a model with model no 'M456781'. You should return the empid and the name for such technical supports. 6. (10 points) Find the distinct model that technical support employee 'Peter' specializes in. Show such model Numbers. 7. (10 points) Find the videos that are broadcasted at site 111 , but not site 112 . Show the code and length of such videos. 8. (10 points) Find the names of all employees (including administrators, salesmen, and technical supports) working in this company. 9. (10 points) Assume that a digital display with model no ' M01locatedataspecificsitewith site code 111 has issues, find all the technical supports who specializes in repairing this digital display. Show the employee ids and names of such technical supports. 10. (10 points) Find all the salesmen who did not sell out any airtime package yet (i.e., no purchases from clients). You should return the emp id and the name of such salesmen
