What would the relational diagram look like in this situation?

Situation

Last week I had to do an assignment, and I did not agree with my teacher's ERD. She used a n-ary relationship while I used two binary relationships. I would like to know a true dba's (you) opinion on this.

The original assignment was to design an ERD, but since the ERD notation we use at school is very uncommon I'm going to ask what the relational diagram is supposed to look like. I think I can figure it out by myself once I know what the relational diagram is supposed to look like.

Assignment

There's a housing assocation company that wants a database.

When a customer registers, the customer has to fill in his details (name, dob, phonenumber), and at that moment he also has to fill in a second form. There, he has to specify what city's he wants to live in, and what types of houses he wants to live in (house, mansion, villa etc.). It's required the customer fills in at least one city and one house.

The second form also has some other fields that the customer must fill in, such as a maximum price and a remark (e.g. the house has to have a swimming pool).

``````CUSTOMER: customer# (PK), firstname, lastname, dob, phonenumber
FORM: customer# (PK), dateofregister, remark, maxprice
contains1: customer# (PK), city (PK)
contains2: customer# (PK), housetype (PK)
``````
• CUSTOMER 1..1 FORM
• FORM 1..n contains1
• FORM 1..n contains2

She merges both `CUSTOMER` and `FORM` into the same table since they have the same key `customer#`, and creates the table `CONTAINS: customer# (PK), city (PK), housetype (PK)`.

I don't agree with her, since I could specify `housetype` twice with the same `customer#`. Same thing goes for `city`. I also don't agree on the fact that she puts all the `FORM` attributes into the `CUSTOMER` table. It looks sloppy IMO, and I believe you would split it up in the 3rd normal form and you would end up with my result again...

• Do you and your teacher differ about the subject matter itself, or about the database design that is supposed to reflect the subject matter? Oct 6, 2014 at 12:20
• @WalterMitty the latter :) I spoke to her today and she said I shouldn't take normalization into consideration, since it's a different way of designing a database. I would like to know how a good relational model of this domain would look like (which pieter didnt provide). Oct 6, 2014 at 16:11

There is more than one school of thought regarding the relationship between conceptual data modeling and logical data modeling. These schools of thought often result in similar practices, but differences in terminology can be confusing, especially in a formal learning situation.

Conceptual data modeling, as I learned it, follows ER modeling as first popularized by Peter Chen. If you look at the Wikipedia article on Chen you'll see a para on ER modeling and conceptual modeling. If your teacher's framework agrees with this one, then perhaps her ER diagrams depict an ER model, and not a relational model. If that's true, then normalization questions ought to be deferred until after the entities and relationships are agreed on.

If, on the other hand, your teacher's framework is the same as that of Pieter's excellent response, then Pieter's response should guide you.

Either way, you need to keep clear on the differences between analyzing the subject matter and designing useful tables that reflect that analysis.

I am with your teacher here. I cannot see any difference in degree of Normalization between the two solutions, but you are embedding aspects of a particular implementation presentation (Two views of what is often referred to as the External or Logical Schema.) into the core data structures. When performing the initial database design (of the Conceptual Schema) it is preferred to hide as much as possible both of the external presentations and of the physical table design.

Once the Conceptual Schema has been designed (and fully normalized), then one would determine the best way to present the Logical views of that structure to external applications, and the optimal physical assignment of columns to tables (the Physical Schema) to maximize performance.