ASAP ~ How to make a query/normalise in writing (1 Viewer)

[astroman]

i came across this question in a past paper and need to know how to answer these in writing?

http://i.imgur.com/GWBZOP6.png

 

[astroman]

BUMP

 

[astroman]

.

 

[Katsumi]

All 3 can be found in the Davis textbook. I recall doing this question/something very similar last year leading up to my trial exams

For i you need to do SQL

SELECT*
FROM Hoist
WHERE Max_weight_capacity >1000KG AND Cost_of_hire <$1000 (you may have to drop the KG and $ sign)

For ii you literally draw out the screens. It's legit one of the easiest mark grabbers in this subject. One screen for searching. One screen for results. If you want to do this and scan them in i can check this one out for you, as it also crosses over with one of the uni subjects i am doing at the moment.

For iii you draw a schema of the normalized database. I would personally have the following tables

Customer - Customer_ID, Customer_Phone, Customer_Address
Stock - Stock_ID, Stock_Name, Stock_Type, Max_Weight_Capacity
Order - Hire_Date, Return_Date, Cost_Of_Hire, Customer_ID, Stock_ID

An alternative and less complex layout could be
Customer - Customer_ID, Customer_Phone, Customer_Address
Hoist- Hoist_ID, Hoist_Name, Max_Weight_Capacity
Lift - Lift_ID, Lift_Name, Max_Weight_Capacity
Order - Order_ID, Hire_Date, Return_Date, Cost_Of_Hire, Customer_ID, Hoist_ID, Lift_ID

Only difference between the 2 is that in the first, i applied another step of normalization that may or may not have been necessary to the question at hand. I noticed that, after creating the hoist and lift tables, that there was an element of data redundancy with Max_Weight_Capacity across both tables, in hand with the separate Hoist_Name and Lift_Name fields causing a little redundancy as well. So i decided to go ahead and merge them both into a central "stock table", adding an extra field called "Stock_Type" to specify if it was a lift or a hoist, subsequently reducing redundancy. You need to remember that the purpose of normalization is to minimize data redundancy.

From there you need to map out your primary, secondary and foreign keys. And your relationships (one to one, one to many, many to one).

For b rote the info from the davis textbook and apply accordingly. It's a content dump question
Keep in mind that while i quite extensively use my IPT knowledge in my university course, my skills, especially in practical database applications and additional stuff has declined since November. I also have a lot more stuff to remember now that i am in uni haha

Also in the IPT HSC exams feel free to use mind maps, bullet points, drawings and otherwise. They don't mark you down for them.

 

[astroman]

don't get part i

 

[BlueGas]

don't get part i

Just learnt this today so this might help. For part i you have to use SQL basically to filter out the information required from the question. The question is asking to find who can lift more than 1000KG and cost of hire is less than $1000 dollars. So Katsumi already mentioned what you need to do to filter it out.

I'll give another example, if there was a table with about 20 people and the states they live in Australia, the question is asking you to find the people who live in NSW, the answer would basically be this State = NSW, this basically filters the data and shows all the people that only live in NSW. For your questions, the answer Katsumi gave basically filters the data and only shows people who can lift more than 1000KG and cost of hire is less than $1000.

 

[astroman]

why is there only the * in select??

 

[astroman]

Is this SQL correct?

SELECT *

FROM Hoist

WHERE Max_Weight_Capacity>1000 AND Cost_of_Hire<1000 AND Return_Date>=28/03/13

ORDER BY Return_Date DESC

 

[Kittyrules]

Is this SQL correct?

SELECT *

FROM Hoist

WHERE Max_Weight_Capacity>1000 AND Cost_of_Hire<1000 AND Return_Date>=28/03/13

ORDER BY Return_Date DESC

umm I'm not quite sure why Katsumi has only put an asterisk in the select section. The select should include every single field name that should be chosen. The question asks for all the hoists and their 'relevant details'. I would include: Hoist, Maximum Weight Capacity, Lift, Cost of Hire
becuase the other details are not based solely on the hoist, but on other entities (i.e the customers)

so it would be:
SELECT Hoist, Maximum Weight Capacity, Lift, Cost of Hire

FROM Hoist

WHERE Max_Weight_Capacity>1000 AND Cost_of_Hire<1000 AND Return_Date>=28/03/13

ORDER BY Return_Date DESC

and nice adding of the return by date descending btw. not to confuse you further, but also it depends on what the HSC handbook says, but sometimes in SQL, they not only write the name of the fields, but also their table names. This is used when the data is taken from multiple tables(not the case here), just to show where each is from and make it easier. an example would be: SELECT hoist.hoist, maximum weight capacity.hoist, lift.hoist, cost of hire.hoist

hope that helps

 

[Katsumi]

umm I'm not quite sure why Katsumi has only put an asterisk in the select section. The select should include every single field name that should be chosen. The question asks for all the hoists and their 'relevant details'. I would include: Hoist, Maximum Weight Capacity, Lift, Cost of Hire
becuase the other details are not based solely on the hoist, but on other entities (i.e the customers)

so it would be:
SELECT Hoist, Maximum Weight Capacity, Lift, Cost of Hire

FROM Hoist

WHERE Max_Weight_Capacity>1000 AND Cost_of_Hire<1000 AND Return_Date>=28/03/13

ORDER BY Return_Date DESC

and nice adding of the return by date descending btw. not to confuse you further, but also it depends on what the HSC handbook says, but sometimes in SQL, they not only write the name of the fields, but also their table names. This is used when the data is taken from multiple tables(not the case here), just to show where each is from and make it easier. an example would be: SELECT hoist.hoist, maximum weight capacity.hoist, lift.hoist, cost of hire.hoist

hope that helps

It has been quite a while haha, i sort of misread the question and assumed it wanted me to include *. So it was a bit of a mess up on my part, remember that i did note to take that advice on i with a grain of salt due to my being a little rusty in terms of practical database experience.

Thanks for clearing that up for me

Just to note, i was always told by my IPT teacher that i shouldn't add anything outside of what the question specifically asks for SQL. SO i didn't think ORDER_BY was necessary

 

[astroman]

had my test, was good, i had done a few questions exactly the same in past papers and smashed em. But i think i spend too much time on them, i wrote a page and a half for quite a few 4 and 5 markers and so didn't have too much time to the end to write the same amount for other 4 and 5 markers. I also made a silly mistake, there was a scenario given where it clearly gave reasons towards an agile development approach but i was stressing in the last 5 mins to get it done and straight up wrote about the traditional method, i realised around when there was 1 minute left and just realised, . i hope i can still get some marks there doe.

 

[BlueGas]

had my test, was good, i had done a few questions exactly the same in past papers and smashed em. But i think i spend too much time on them, i wrote a page and a half for quite a few 4 and 5 markers and so didn't have too much time to the end to write the same amount for other 4 and 5 markers. I also made a silly mistake, there was a scenario given where it clearly gave reasons towards an agile development approach but i was stressing in the last 5 mins to get it done and straight up wrote about the traditional method, i realised around when there was 1 minute left and just realised, . i hope i can still get some marks there doe.

If you don't mind, do you have any tips on what to study or what I should be studying?

 

[Katsumi]

had my test, was good, i had done a few questions exactly the same in past papers and smashed em. But i think i spend too much time on them, i wrote a page and a half for quite a few 4 and 5 markers and so didn't have too much time to the end to write the same amount for other 4 and 5 markers. I also made a silly mistake, there was a scenario given where it clearly gave reasons towards an agile development approach but i was stressing in the last 5 mins to get it done and straight up wrote about the traditional method, i realised around when there was 1 minute left and just realised, . i hope i can still get some marks there doe.

With IPT, whilst there is usually a "right" approach/answer you typically still get marks for providing the correct/logical reasoning towards your suggested method. So if you correctly and logically wrote about the traditional method with specific reference to your scenario, i wouldn't be too worried about loosing too many marks.

Good luck for results day, need to keep that #1 spot haha

 

[Katsumi]

If you don't mind, do you have any tips on what to study or what I should be studying?

Past exam questions/How to apply your knowledge to real life scenario's if you haven't done much on it yet.

 

[astroman]

If you don't mind, do you have any tips on what to study or what I should be studying?

well it depends on what topics you've done but the general thing is to do your past papers and revise the crap out of your notes.