[nanook2k3]

I'm trying to design a 3NF logical schema for a relational database, but I'm a fairly new to this and need some help. I have the following as the entity relationship diagram:

DATA STRUCTURE: (FIELD SIZE)

Supplier = Supplier Identification Number (12)
+ Supplier Name (30)
+ Supplier Street Address (30)
+ Supplier City (15)
+ Supplier State (2)
+ Supplier Phone Number (10)
+ 1 {Payment method + (1)
Early Discount Period + ( 2 )
Early Discount Rate + (0.2)
Payment Deadline} 3 (2)
+ 1 {Material Number + (9)
Material Description + (30)
Unit Price + (5.2)
Quantity Discount Threshold + (3)
Quantity Discount Percentage } n (0.2)


This is a homework problem I've got for my Systems Design and Analysis class, but the book is not very good (no example problems or anything....and I've done plenty of searching around on the web but can't seem to figure out how to start this).....can anyone help me out? I'd really appreciate it!

Thanks

[certus]

Divide your problem into three tables Supplier, Payment and Material and normalize from there. Remember to use synthetic keys for the tables' primary keys.

[certus]

To take it a step further look at each column and ask yourself, "Will there be redundant rows in this column?" If the answer is "yes" then that column can probably be a lookup table.

[nanook2k3]

Thanks for the response....you got me started and I think I'm on the right track now. I've got three columns, supplier, payment and materials, and each one has several entities which can be described by their attribute (name), type (text, integer, etc), domain (further restrictions on type), and whether or not it's required. From there I create another table that describes the relationships between these entities. Does this sound reasonable? I'm a little fuzzy on normalization too....I know an entity is 1NF if there are no attributes that can have more than one value for a single instance of the entity, and an entity is in 2NF if it's already in 1NF and the values of all nonpirmary key attributes are dependent on the full primary key.....this is where I think i'm getting stuck though......from the list does it look like any normalization needs to occur?

thanks again for your help/suggestions

[certus]

No, your entities are Supplier and its attributes, Payment and its attributes and Material and its attributes. They are not columns.

Just for clear discussion:

Entities are person places or things we record data about.
Attributes are properties of an entity.

You have 3 Entities and their attributes.

Some of these attributes contain instances which repeat themselves. For example Payment method "cheque" can occur more than once and therefore can be normalized out by creating a Payment Method entity.

The question as presented does not provide adequate information to normalize the table. We do not know enough about the data. We do not know enough about the business rules. Our efforts are speculative.