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 the following queries. 1. Find the information of a client named ‘Peter Smith’. 2. Find all the airtime packages whose broadcasting frequency is more than 5. 3. 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. 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. 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. Find the distinct model that technical support employee ‘Peter’ specializes in. Show such model Numbers. 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
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 the following queries. 1. Find the information of a client named ‘Peter Smith’. 2. Find all the airtime packages whose broadcasting frequency is more than 5. 3. 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. 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. 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. Find the distinct model that technical support employee ‘Peter’ specializes in. Show such model Numbers. 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
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.
1. Find the information of a client named ‘Peter Smith’.
2. Find all the airtime packages whose broadcasting frequency is more than 5.
3. 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. 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. 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. Find the distinct model that technical support employee ‘Peter’ specializes in. Show
such model Numbers.
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.
1. Find the information of a client named ‘Peter Smith’.
2. Find all the airtime packages whose broadcasting frequency is more than 5.
3. 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. 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. 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. Find the distinct model that technical support employee ‘Peter’ specializes in. Show
such model Numbers.
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
Step 1: Relational Algebra (RA) statements to the following queries
Relational Algebra (RA) statements to the above given queries
- σ name='Peter Smith'(Client)
- σ frequency>5(AirtimePackage)
- π videoCode(σ siteCode='S345'(Broadcasts))
- π serialNo(σ type='restaurant'(Locates))
- π empId, name(σ modelNo='M456781'(Specializes ⋈ TechnicalSupport))
- π modelNo(σ name='Peter'(Specializes ⋈ TechnicalSupport))
- π videoCode, videoLength(σ siteCode=111(Broadcasts) - σ siteCode=112(Broadcasts) ⋈ Video)
- π empId, name(TechnicalSupport) ∪ π empId, name(Salesman) ∪ π empId, name(Administrator)
- π empId, name(σ modelNo='M01'(Locates ⋈ DigitalDisplay) ⋈ Specializes ⋈ TechnicalSupport)
- π empId, name(Salesman) - π empId, name(σ empId=Salesman.empId(Purchases))
Trending now
This is a popular solution!
Step by step
Solved in 2 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