Little (Employee - Shift) SQL Database help

Question

Hi Guys,

Im creating a little database that has employee, emp_shift, shift, tables now im suppose to be able to calculate at the end of the month which employee has done the most number of shifts.

Ive created the SQL creation, insert statements for the tables, and a little diagram to explain what im trying to acomplish, im a beginner and this is a homework ive been trying to do for the last 4 days.

Diagram: http://latinunit.net/emp_shift.jpg SQL: http://latinunit.net/emp_shift.txt

can you please guys check it, deadline is 2 days and this is just a part of the whole database

Answer 1

You could start by telling us whoch RDBMS you are using, as some of the finer details might be different between RDMSs.

You need to create a link between tables (Called JOINS, Read this) and then perform a count of the requested data.

After you have read some of these, show us what you have done, and we can help you where you are having trouble.

Answer 2

That is a reasonable start. Will you have more tables? If not, it will be hard to identify how to pay people -- for example, it seems that you might want a "pay-period" table. Then you could find the start and end dates and be able to count the shifts within that period.

But if all you need to do is exactly what you said, that is a fair start.

(I am assuming you have other columns in mind, such as employee name, but that would be obvious).

Answer 3

also, it would be better practive to use a single numeric as the primary key instead of 'A', 'B', 'C' etc.