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.
Chapter2: Database Design Fundamentals
Section: Chapter Questions
Problem 3TD
Related questions
Question
Assume that “ABC media” has decided to have the relational schema for their
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.
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
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
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.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr