Database Symbols

Posted on 2011-02-25
Medium Priority
Last Modified: 2012-06-27
How do I solve this problem?

6.16) Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 3.6.

a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.

The relational operators noted refer to sigma, pi, Intersect, U, and Join.

Figure 5.5 wasn't in the book; Figure 6.5 looked relevant.

The related figures are:
Question by:JCW2
LVL 33

Expert Comment

ID: 34984943
We are not allowed to do your homework for you.

Author Comment

ID: 34985025
No you're not.

I'm looking to understand my problem better (That's what I tried to say with my first sentence).

Author Comment

ID: 34985053
(There are other possible operators)
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 15

Expert Comment

ID: 34985255
can you be more detailed in what you're actually looking for help with here? i can't understand what type of question you're really asking
LVL 37

Accepted Solution

ste5an earned 1000 total points
ID: 34986885

This kind of exercises must be precise otherwise they are worthless. Question a) is not solvable, if your given data is complete.

This is quite trival: You don't have the information about workhours per week. You have a PROJECT table which has no reference to weeks and you have the WORKS_ON table, which has a reference to the PROJECT table, but also not reference to weeks. While you can make an educated guess that the hours in WORKS_ON sum up to a weekly work shift, it is not mentioned anywhere. Thus you cannot solve it. You can guess it, but this not the question, as database modeling and data retrieval is about correct answers, not guessed ones.

--> stefan <--
LVL 20

Assisted Solution

virmaior earned 1000 total points
ID: 34987354
The operators are from set theory in mathematics.  The U means "Union" and in SQL is written by UNION (or UNION ALL) placed between two queries.  This means include all data from both.  (UNION ALL will include duplicates if the entries come from the separate halves).


The upside U means intersect and includes only data common to both sets.  In this case A intersect B and B intersect A might yield different data.  In SQL you express this by doing a JOIN and a WHERE

(A intersect B)
SELECT * FROM a LEFT JOIN b ON b.field = a.field WHERE b.field IS NOT NULL

(b intersect a)
SELECT * FROM b LEFT JOIN a ON a.field = b.field WHERE a.field IS NOT NULL

JOIN is a way of relating tables in SQL where you can combine them on a common field.  So if you had an order table with an id for the customer, you could retrieve all the orders for a customer and the customer's information in a single query.

I am not sure what Sigma and Pi mean in a SQL context.

Author Closing Comment

ID: 34988572
Tank you for your help.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
A method of moving multiple mailboxes (in bulk) to another database in an Exchange 2010/2013/2016 environment...
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question