I've been given a homework at university, which says I should make a database with 7 tables [see picture below]. The scheme I made is good, except some normalization is needed, as my tutor told me. (Side note: I have already done normalization with CONTINENTS and COUNTRIES tables)
After I have contacted with my tutor, he told me I'm missing two normalization in this schema and there is a database modelling mistake. However, this is all I know about my mistakes.
My thoughts about my possible mistakes:
In CUSTOMERS table COUNTRY_NAME should be a FK, referencing COUNTRY table's COUNTRY_NAME.
(In this case the CONTINENT_ID is not necessary in my opinion)
In ORDERS table the TOTAL_AMOUNT is redundant because of ITEM table's QUANTITY. Is it a problem, and if so, how can I get rid of it?
There are lots of 'CITY' columns with VARCHAR2 type. This can be a problem when 1) the user misspell them in other tables or 2) VARCHAR2 is taking up too much storage. Should I make a ZIPCODES table with zipcode and city columns and use it instead?
Repeating records: In WORKERS table there is a column called EMAIL. In my opinion a worker can have multiple e-mail addresses, so in this case: Can this cause me trouble, i.e. causing repeating records?
- CUSTOMER/WORKER table and DIVISION/WORKER table have a N:M (?, not so sure) relationship. So is it necessary to make another table between them?
I would be really thankful if somebody can find the missing normalizations and answer my questions.
Responses to clarification requests
About the modelling mistake: I don't have much information about business rules. It's because of this lesson is about normalization, so this is kind of a cherry on the cake. Not a big problem if I won't find it.
About the normalization: I'm pretty sure, that I should decompose the current structure up as mentioned via comments. The question is, what I'm missing here. I'm starting to go blind because of staring too much at them.