help please!

[rawkas]

hi guys!

[Fou-Lu]

Originally Posted by rawkas

hi guys, any chance with help on the following question? would be much appreciated!

MySQL is a relational database management system. The data is structured as a set of tables linked by relations. Assume the following database has been setup for you.


Properties
propId

postCode
1

CB1 4PY
2

CB2 9GH
3

CB23 4DG

Persons
perId

propId

firstName

lastName
1

1

Fred

Bloggs
2

2

Arthur

Brown
3

1

Mary

Bloggs
4

4

Nigel

Wilson

3.3.1 Write the SQL to list postcodes and names of people who live there.
3.3.2 Write the SQL to list postcodes and people who live there (where applicable) for all properties regardless of whether the property is inhabited.
3.3.3 Write the SQL to list people and postcodes (where applicable) for all people regardless of whether they live in a property that exists.
3.3.4 Given values for postCode, firstName, lastName write the SQL statements to create suitable entries for each table. HINT you may need to use Last_INSERT_ID() - assume propId and perId columns have been defined as AUTO_INCREMENT.





thanks alot!

This hasn't a thing to do with PHP. Moving to MySQL forum. In the future please choose a better title for your threads.

This is clearly homework. Please post your current SQL attempts and the problem you are currently seeing with it.

[beaverking1989]

Okay, I feel the need to chip in and help my associate present this question more clearly.

This is how the question is supposed to look.


We really do appreciate any help you can provide as we're both stumped. Thanks for your time.

[Fou-Lu]

And that's fine, but where is the current SQL in use and the problem you are having with it?
You can't just present us with a homework assignment and expect that we should do it for you. That would be called cheating; however we can help you fix existing code (or in this case queries) by walking through the problem and guiding you to the proper solution.

[Old Pedant]

And on top of that, if the database is PROPERLY DESIGNED and uses correct RELATIONAL INTEGRITY, then task 3.3.4 is impossible. For that matter, the data as presented there is impossible. So somebody is teaching you bad database design.

Specifically, propid in the PERSONS table should be a foreign key to propid in the PROPERTIES table. And so the propid of 4 in the PERSONS table would be impossible in a correctly designed and implemented database.