1

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).

My answer 

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

My teachers answer

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...

Community
  • 1
user1534664
  • 125
  • 4
  • Do you and your teacher differ about the subject matter itself, or about the database design that is supposed to reflect the subject matter? – Walter Mitty Oct 06 '14 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). – user1534664 Oct 06 '14 at 16:11

2 Answers2

0

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.

Your proposal is pushing subtle choices of Physical and External design into the Conceptual Schema inappropriately.

It appears to me that your teacher is having difficulty explaining that your proposal might be a valid physical database design, later, given certain assumptions, and accurately reflects the External views as currently implemented, but fails to meet the needs of a Conceptual Schema that maximizes normalization with minimal clutter.

A key observations is that whenever two tables (entities) have the same Primary Key, then even if the normalization is the same the clutter has been increased. There will always be many ways to clutter a clean (Conceptual Schema) design, but this clutter can always be eliminated from it. In a real world database design with hundreds or even thousands of tables minimizing clutter is an essential design attribute.

Pieter Geerkens
  • 1,970
  • 13
  • 21
  • I do not make the division between conceptual, logical and physical the same way you do. When I learned it the conceptual layer only consisted of entities, relationships, attributes, and values. Normalization was not done here. The logical layer consisted of tables and constraints, and this is where normalization was done (or not done). I do not know if the definitions have changed since I learned them, a long time ago. – Walter Mitty Oct 07 '14 at 10:08
  • What's relevant for the OP is how the teacher divides up the layers. – Walter Mitty Oct 07 '14 at 10:09
  • 1
    @WalterMitty: I was taught the 3-schema technique from the 1970's: http://en.wikipedia.org/wiki/Three_schema_approach – Pieter Geerkens Oct 07 '14 at 10:44
  • Thanks for the wikipedia reference. Yes, we learned it differently. The main issue here is what's of most use to the questioner and future visitors. – Walter Mitty Oct 09 '14 at 10:00
0

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.

Walter Mitty
  • 4,306
  • 20
  • 21