Quick Normalization Question

[Floatzel98]

Hey guys, I've been having a tiny bit of trouble with normalizing databases as sometimes i get a bit confused when getting it into 2NF and 3NF. For the question below, i was tripping up on whether when splitting them up into tables we should either have a Members table and Boat table, as Name, adress, state, member type and gender would all go into it (After 1NF of course). That only leaves the boat and the boat storage fees in boat. And Total Fees are a caluculated field.

My friend said we should have it as Member and Membertype as the fees only depend on the member type and you could argue that boat storage fees depends on whether you have a boat or not. Nothing is dependent on boat name and can be left out.

I understand in questions like this maybe you have to reach out into 3 different tables, but i don't know if one of us could be right with just the 2 tables.

Also when designing ERD's our teacher says that it will always fit into 3 tables. Example; We would be given data that we could split into Products, Customers and then link them with a purchases table in between. Would VCAA ask questions on the exam that would always fall into that form or it just depends on what is given and what you can do with it?

Also, i have some other questions from my textbook that i need some help answering properly:

1) What types of information do people need that could be gained from websites? Give examples.

2)List needs that website operators have that would be satisfied by supplying data from their websites.

3)Describe techniques that a website operator could use to acquire data using a website.

4)Describe responsibilities that an organisation may have to protect the legal rights of data providers. What are two techniques for protecting the rights of these people.

I have done these questions but there are no answers in my textbook so i would like to see what other people would answer to them.

Thanks!

[MJRomeo81]

Hey

I can help with the normalisation part of your post.

Before continuing, I strongly recommend checking out a post I wrote on database normalisation: Database Normalization

It covers all of the key concepts with examples. Knowing the difference between 2NF, 3NF, etc. is just a case of understanding the different types of dependencies - functional, partial, and transitive.

Also when designing ERD's our teacher says that it will always fit into 3 tables.

This isn't true at all. You could have an ERD that has 5 or so entities with a bunch of relationships, or you could have PLAYER <plays-in> TEAM (which is a 1-M relationship hence you will only have two tables in this example).

Example; We would be given data that we could split into Products, Customers and then link them with a purchases table in between. Would VCAA ask questions on the exam that would always fall into that form or it just depends on what is given and what you can do with it?

Depends entirely on what is asked by VCAA. I mean VCAA could ask a question where you need to have 2, 3, 4, N number of tables. So I wouldn't be trying to fit every possible Q into some magical form where 3 tables works for everything. Always keep an open mind when working with database tables



As for the case study you have shared:


A few notes before I share my solution:

* We need to create some PKs because MemberName and BoatName aren't candidate keys. A candidate key is a field/attribute that can be used as a primary key, i.e. it is unique. We cannot guarantee that generic names are going to be unique.

* TotalFeesDue is a calculated field and doesn't belong in the database schema. It can be computed by a simple query when doing the reporting.

* Don't forget to ensure each field has atomic values to satisfy 1NF. i.e. break up the MemberName field and Address_suburb fields.


Can a member own more than one boat? Let's assume they can't. But if they could, you would have a M-M relationship and things would have to change.


Functional dependencies:

MemberID -> MemberName, Address, suburb, State, Gender, memType

BoatID -> BoatName, BStorageFees

memType -> fees (if you don't have a memType table, you have a transitive dependency, hence you violate third normal form). This design avoids redundancy. What if this yacht club had 5000 members? We would have redundancy all over the place. What if we had to update the values to reflect a change in the business rule? This puts an unnecessary load on the database query engine.


Now to my solution.

Assuming a Member can only own one Boat.

Primary keys are underlined. Foreign keys are in italics.
 


MEMBER: (MemberID, FirstName, LastName, Address, Suburb, State, memType, boatID)

BOAT: (BoatID, BoatName, BStorageFees)

MEMTYPE: (MemType, Fees)


My homework question for you:

How would you structure the tables if one member could own more than one boat?

[Floatzel98]

Hey, sorry i never replied when you posted this, i totally forgot. I read through it all at the time though and it really helped. Thanks!

I still need help with these questions though:

List needs that website operators have that would be satisfied by supplying data from their websites.

Describe responsibilities that an organisation may have to protect the legal rights of data providers. What are two techniques for protecting the rights of these people.

My friend and i have been trying to answering them but haven't really found good answers yet. For the first one, all we have is that they supply their contact information in case they ever need feedback or they get asked questions.

For the second one, we thought that they have the responsibilities to protect the privacy of users and they can do this with things like....? I don't really know how though. Would be requiring passwords or validations count as good enough answers?

Thanks for your help!