Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
When is it due in?
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Ian, Where can i sign up for you classes?  I owe you greatly!
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
quote:
Originally posted by Ian
When is it due in?
We got presentation week commencing 28th of march, so 2 weeks?
Applicaiton is due in in May.
[Edited on 21-03-2011 by daymoon]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
I would also be having a poke about and getting proper data.
http://www.google.co.uk/search?q=diphe+filetype%3Axls
Get your list of courses from there.
Institutions in here somewhere -
http://www.google.co.uk/search?q=leedsmet+ljmu+filetype%3Axls
Job titles -
http://www.google.co.uk/search?q=registrar+doctor+surgeon+filetype%3Axls
The crux of the other stuff is how many of each do people have.
If your employees have multiple things then they need to be listed in other tables.
But if the things they have are shared with other people, then it's a many-to-many meaning you have a list of the things and no employee id field in there, then you have a junction table with both the employee id and the thing id.
That way multiple people can have multiple things.
If you don't understand many to many then you need to, it's rather useful.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Oh i do understand many to many, It's just At uni it is always called a relationship, not a junction
Anyways i am going to munch it over tomorrow when I am at work doing another 10 hours shift, and report back tomorrow.
Thanks greatly to you guys, Contributions to the show truck will be on their way soon!
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Relationship is the single line between a table, junction is the middle of a many to many, which has two relationships, one going from the junction table to each of the one-ends of the relationship.
Like this, but without the primary key constraint on either of the fields in the UserRole table, which would prohibit actually using it like a many to many 
|
Tommy
Member
Registered: 24th Aug 00
Location: Essex, Colchester
User status: Offline
|
I woek in a hospital and work with the IT Analysts.
They have to use DBs to track patient pathways, so when they first arive to when they leave etc. This in turn can be pinpointed to departments and then to consultants. Just thought it could be something else you could use as this sort of thing is used in every hospital.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Slightly better but in this case I wouldn't have a primary key in the MovieCategory table, it's not needed to uniquely identify the row because the composite of MovieID and CategoryID would do that and it's not used in any further relationships I can think of.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
quote:
Originally posted by Tommy
They have to use DBs to track patient pathways, so when they first arive to when they leave etc. This in turn can be pinpointed to departments and then to consultants.
Yes except it more than doubles the size of the database. Which might be what they need. Not sure if they are having patient records though.
|
Tommy
Member
Registered: 24th Aug 00
Location: Essex, Colchester
User status: Offline
|
Yer true it would make it bigger but just wanted to give my input.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Right so could I just keep tables:
Employee
Salary and hourly pay(Would have same fields really apart from the salaray/Hourly pay? So maybe join them?)
And add :
position
address
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
quote:
Originally posted by Tommy
Yer true it would make it bigger but just wanted to give my input.
yes it will be part of the system, although it has been re-done today so not sure what it looks like now.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
First thing is to decide what you want to store.
I would go back to basics and start with a big list of everything you have you and take it from there.
Then when that is clear you can worry about all the relationships.
Currently I don't even know what you need to keep and what you don't.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
I would need:
Employee contact info
How much they earn
What they do/responsobilities
What qualifications/education they have(a ward may require some one with a minimum qualification in something or another)
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
That's a start, now break that down.
Doesn't matter if it's a proper structure yet, just throw some ideas about.
[Edited on 21-03-2011 by Ian]
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Break that down as list atributes i would have?
Now i seriously give up. work at 6am.
Thank you very much Sir!
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
How much they earn
- salary / hourly rate / bonuses?
What they do/responsibilities
- full job title, additional responsibility, temporary ability to cover?
What qualifications/education they have
- multiple quals, this is certainly a many-to-many, needs dates
Thing is we're not itemising cleaners, admin staff there but my first thoughts would be is that you don't need to.
If you have a list of job titles in the entire organisation and a list of quals that covers anyone, they don't need to be separate tables anyway. You just insert in to the junction table wherever you have an intersection.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Right. I tried to talk my way round this, but no one wants to listen.
When I asked my tutor about something else I got an answer, but When i asked for an explanation...
Unfortunatly the tables will stay like they are on our ERD. During logical/physical design some composition will be made, although that will cause repeat data.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Denormalisation is OK as per my last post on the previous page.
Are they not listening because you're not making the case very well or do they have better ideas?
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
"Too much work" is typical answer...
Although I made my point over something similar(Had one to one link between room/bed and patient) will change that to many to many and add a junction to solve that. Thing is, out tutor has same/similar tables for employee aswell 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Do also bear in mind there is not one single solution and different structures could offer you comparable query complexity/efficiency.
So you may never agree completely. The main thing to ensure is that your solution doesn't do anything poorly, and I would argue that redundant data is worse than difficult queries.
Have you got any aggregation in the queries? ie. GROUP BY in the SELECT
I use this loads and find it massively useful and elegant, but I can only generally do it because stuff is properly structured and the JOINS are all indexed right.
And the too much time thing is bollocks. You could probably start again and have it better than you currently do in a few hours if you were clear in which direction you were going in.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
At the moment we have no queries written down.
However something like:
Which patients are taking medicine "A" Would allow us to have a group by in query.
When we did a module on just databases/apex, Part of assesment was to come up with 3 queries that are both useful and complex.
SELECT c.client_name,
f.fraud_desc,
SUM(( t.hours_allocated - t.task_hours_worked ) * t.price_per_hour) AS
money_over_budget
FROM task t,
client c,
fraud_type f,
fraud_investigation_project p
WHERE c.client_id = t.client_id
AND p.project_fraud_ip_id = t.project_fraud_ip_id
AND f.fraud_type_id = p.fraud_type_id
GROUP BY c.client_name,
f.fraud_desc
HAVING SUM(( t.hours_allocated - t.task_hours_worked ) * t.price_per_hour) > '0'
ORDER BY money_over_budget DESC
SELECT c.client_name,
c.client_phone_number,
c.client_email,
c.contact_name,
t.total_spent
FROM (SELECT client_id,
SUM(hours_allocated * price_per_hour) AS total_spent
FROM task
GROUP BY client_id) t,
client c
WHERE t.client_id = c.client_id
AND rownum <= '3'
ORDER BY total_spent DESC
SELECT client_name,
client_phone_number,
client_email, contact_name
FROM client
WHERE client_id IN(SELECT DISTINCT(client_id)
FROM fraud_investigation_project
WHERE project_start_date
BETWEEN '01-MAY-10' AND '01-SEP-10'
AND fraud_type_id NOT IN (2,7,9))
ORDER BY client_name
What you think of those? Would you consider those queries any good? (Not for this particular assignment though)
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
They're about the level, yes.
Does depend on what functionality you need though, that is what will dictate which features of the SELECT you will need.
|
