Need some serious help please

[Mei0r]

Hi guys, First off I really dislike coming to forums for help as it makes me feel incapable of learning and I really hate that lol but at the moment I have no choice.

I am learning database in college and the teacher has gone over and over and over normalisation with us and creating ERD's and I just cant get my head around.

Basically this is all the information I have and the first thing I need to do is a flat file, also known as normalisation?

Here is my brief.

[FONT=&quot]A consulting company wants to develop a database system to computerise its operations management. Systems analysts have gathered the requirements for the database, and have noted the following business rules:[/FONT]

[FONT=&quot]The company has many clients.[/FONT]
[FONT=&quot]The company employs many consultants, and assigns them to projects at clients’ sites.[/FONT]
[FONT=&quot]A client assigns consultants to one or several projects in this client company.[/FONT]
[FONT=&quot]The consultants in the company are assigned work on projects at clients’ that may be with several clients concurrently. Each assignment is characterised by an assignment start date, a number of hours to work, and an hourly rate.[/FONT]
[FONT=&quot]Each project, at each client company may require the service of several consultants.[/FONT]
[FONT=&quot]Some consultants are not assigned to any projects, because for instance they receive training or take charge of administrative duties[/FONT]
[FONT=&quot]Each consultant has a (single) primary job classification. This job classification determines the hourly billing rate[/FONT]
[FONT=&quot]Several consultants may have the same job classification. For instance, the company employs Database designers, and Project Managers.[/FONT]

[FONT=&quot]In addition, systems analysts have decided to represent the data with following attributes:[/FONT]

[FONT=&quot]Each job classification is coded by a unique job code, and a description[/FONT]
[FONT=&quot]Each consultant has a first name, a last name, a middle name, a hire date, a date of birth, and a unique identification number[/FONT]
[FONT=&quot]Each client has a company name, and a unique client identification number[/FONT]
[FONT=&quot]Each project has a company name, and a project code that is unique at the client’s company (but it may not be unique for the consultants’ company, since client companies choose their project codes independently from one another)



Now to show you im not here purely for you guys to give me the answer I would just like you guys to help me through and see if i going on theright track.

My NF0 consists of the following.

Client ID (PK)
Client Address 1
Client Address 2
Client First Name
Client Second Name
Client DOB
Client Contact Number

Consultant ID (PK)
Consultant First Name
Consultant Second Name
Consultant DOB
Consultant Hire Date
Consultant Job Description
Consultant ROP

Project ID (PK)
Assignment ID (PK)
Start Date
End Date
Number of Hours Worked

Now this is where I get stuck, I need to break these down into NF1 and NF2 but I dont know what would go into these tables and why..... Please if anyone can help me I would be so greatful this assignment is due in soon and im really starting to panic.

Also I understand primary keys but just not foreign keys.....
[/FONT]

 

[Rabbie]

This link may help you learn about Normalization. Also read the article on it in Wikipedia.

A flat file is rarely if ever normalised so try to understand what normalisation really is.

Remember you are trying to build a Relational Database so think about the relationsships between your entities.

 

[Mei0r]

Yes its the relationships part is what I find confusing. We have to break down that flat file into NF0, NF1 and NF2, stuff that will be reapeated ect. Ill check out your link.

 

[tra072011]

Hi,

Thanks very much for this comment. It help me to think about my ideals.

Tks again and pls keep posting.

 

[gemma-the-husky]

informally -

you are trying to analyse every little piece of data (datum) you have, into connected logical entities, so that the final structure provides a minimal representation of the data - effectively eliminating duplicated data, and eliminating data that could be derived by considering other elements of your data.

the way to achieve this is generally to idenitfy repeating groups of data. If a table contains multiple fields that need to be taken together, and the collection of multiple fields includes duplicates when taken together, in principle that group could and should be extracted into a different table.

the data is fully normalised when all such groups are eliminated.

although there are some technical methods of achieving this, it is as much an art as a science, and experienced developers will quickly derive normalised dsata in most cases

one empirical problem is poor system analysis, though. In practice it is very hard to be sure that a client say, has fully documented their systems, and the inadequate analysis often contains information that would affect the data design.

a system may handle 99% of stuff in one way - the problem is generally representing the 1% of unusual transactions.