ssume that “ABC media” has decided to have the relational schema for their database. Schema: 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 the following queries.. 7. Find the videos that are broadcasted at site 111, but not site 112. Show the code and length of such videos. 8. Find the names of all employees (including administrators, salesmen, and technical supports) working in this company. 9. Assume that a digital display with model no ‘M01’ located at a specific site with 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. 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.

ssume that “ABC media” has decided to have the relational schema for their database. Schema: 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 the following queries.. 7. Find the videos that are broadcasted at site 111, but not site 112. Show the code and length of such videos. 8. Find the names of all employees (including administrators, salesmen, and technical supports) working in this company. 9. Assume that a digital display with model no ‘M01’ located at a specific site with 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. 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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Assume that “ABC media” has decided to have the relational schema for their database.

Schema:
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 the
following queries..
7. Find the videos that are broadcasted at site 111, but not site 112. Show the code
and length of such videos.
8. Find the names of all employees (including administrators, salesmen, and technical
supports) working in this company.
9. Assume that a digital display with model no ‘M01’ located at a specific site with 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. 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.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.