Is my database normalised enough till the 3rd form?

kpeeroo · #1 (**permalink**) 07-31-09, 11:24

Hi

I am currently designing a database where i need to register and keep a record of students at a college. I have done the design and normalized it but i want to know if i can make it more 'atomic' so that there is no repetition when i enter data.

There is the design of the db attached to this post in pdf. Please anyone can check it and let me know what you think, if it is correct by design or i should atomize it further. For eg, the address table, what if a person has more than 1 postal addresses, i would then have to repeat the other attributes as well right? so not good design? can anyone advise? thanks.

regds
kaleem

dportas · #2 (**permalink**) 07-31-09, 14:28

With only a list of attribute names there isn't much to go on. I think you are right to think about multiple addresses. How would you go about implementing that?

You also need to indicate the keys in your design.

kpeeroo · #3 (**permalink**) 07-31-09, 20:22

hi

thanks for your reply. I have modified the design and here is the 2nd version attached. I have broken the entities further down so that attributes are not repeated in my tables but only appear once. I have also indicated the keys in the 'related keys DB' where all the foreign keys are listed in my table.

Only thing I am worried is whether it will be a problem to query considering the number of joins? will that be a problem? if it is, what can be done? thanks

shammat · #4 (**permalink**) 08-01-09, 03:50

Your wording is not OK either. It should be Student table not DB. A database (DB) is the collection of tables (and other database objects)

I personally prefer a different layout when it comes to a textual description of a table, something like this

Code:

Table student

Column            | Type            | Modifiers | Description
------------------+-----------------+-----------+------------------------
 id               | integer         | not null  | Primary key 
 firstname        | varachar(50)    | not null  | Student's first name
 lastname         | varachar(50)    |           | Student's last name
 accademic_year   | integer         |           | The year when the student graduates

And you should also indicated foreign key relations between the table.

Things like this are normally done using an ER (entity relationship) diagram.

sco08y · #5 (**permalink**) 08-01-09, 07:26

Each table is supposed to have a clear meaning in the real world.

For example, the Student table might mean "There is a student whose id is "id", whose first name is "First name", whose surname is "Surname", who is addressed by "Title," who will graduate in "Academic year", who is a citizen of "Nationality" with a passport id of "IDNumber" and who was born on "Date of Birth."

It's long and tedious, but it makes it more clear what you're trying to say.

What would an entry in "Related Keys" actually mean?

kpeeroo · #6 (**permalink**) 08-01-09, 19:33

sorry about that, yea that should be termed 'table' instead of db. I wanted to show the keys in my 'related keys' table so that for example if a student has a number of postal addresses say, then if i indicate the foreign key in the student table, i would have to repeat attributes entries like 'firstname', 'surname', etc right? Isn't this kind of relation called the fourth normal form as explained here:

Page 4 - Database Normalization And Design Techniques

so the real world meaning of say the first record of 'related keys' table would then be:
A student has studentid of 'studentid' with postal address id 'postaladdressid' and residential address id of 'residentialaddressid' ....

which then becomes after a search is made for that 'studentid', 'postaladdressid', 'residentialaddressid', etc in their corresponding tables:

a student of student id 'studentid' with first name 'firstname' and surname 'surname' addressed by title 'title' .... has postal address of 'postalAddress' and postal address code of 'postaladdresscode' living at 'residentialaddressid', etc

Is that correct guys or am i missing something very crucial there? I mean in this way there is no duplication of data except the keys in the 'related keys' table where i try to link up everything. Suppose i have 2 postal addresses for a student, then if i put the postal address id as foreign in the student table, wouldnt it be a problem in the sense that i would then have 2 records of the same student but with 2 different postal address ids? that would mean duplicating attributes such as 'firstname', 'surname' etc right?

kpeeroo · #7 (**permalink**) 08-02-09, 18:56

hi guys,

I have modified the design not wanting to risk going into the 4th normal form. I've made the studentID foreign to all tables. I think the designed is atomized in this way with no duplication possible. Is it correct?

sco08y · #8 (**permalink**) 08-08-09, 08:56

Quote:

Originally Posted by kpeeroo

I have modified the design not wanting to risk going into the 4th normal form.

Why would that be a problem?

Quote:

I've made the studentID foreign to all tables. I think the designed is atomized in this way with no duplication possible. Is it correct?

Can you generate some example data?

kpeeroo · #9 (**permalink**) 08-08-09, 11:24

I have not yet built the db in mysql. I wanted to design it properly first because it could be a large db we are dealing with. I have worked on it further and I think this is one of my final drafts.

Now I will start with the building of the db on sql. I would post out the example data for the sake of some examples. Any idea or suggestion on my design is welcome in the meantime.

sco08y · #10 (**permalink**) 08-08-09, 13:14

Quote:

I wanted to design it properly first because it could be a large db we are dealing with. I have worked on it further and I think this is one of my final drafts.

It's impossible to tell from what you've posted, but I suspect you're not designing it properly and you're not much further than when you first posted.

It looks like you're trying to make version 1 do everything and be perfect. I think you'd should try to get just a few tables working fully. Once that small portion can do add, update, delete and some useful queries, you'll have a better understanding of what the normalization thing is really about.

Quote:

Originally Posted by kpeeroo

I have not yet built the db in mysql.

You don't have to have the system running to write down a few rows of example data for each table.

You also don't have to have a working system to write out questions you're going to ask and translate those to actual SQL SELECT statements.

For instance, how would you answer "How many students living on-campus will be graduating this year?"

Really, a database is a question and answer machine. That's the way to really understand it and think through the problem fully, not this mystical puzzle of writing out table names and column names and trying to divine the most normalized form.

kpeeroo · #11 (**permalink**) 08-08-09, 19:01

great post scooby. that's what i was searching for, ie, suggestions on how to proceed. Yeps think u r right. i will drop in some example rows for you to check out as well so you can ask some questions to the db. but what ve been doing is creating the backbone, i know am bit maniaco on the topic

but most tutorials ve read seem to do that first, which i followed, like the one posted earlier. anywayz, am doing the tables in mysql now n i'll show what i come up to.

kpeeroo · #12 (**permalink**) 08-09-09, 00:25

ok i have put data in my tables Students, Academics and Courses. I have been able to query things like how many students were in academics year 2 in 2008? it gave me the correct results. I also questioned the db with how many students are doing the course BBA? correct results were obtained.

I am using visual developer 2005. I dont know how to post the tables with data and its database diagram. any idea how? but it does work..!

kpeeroo · #13 (**permalink**) 08-11-09, 21:23

can i have two foreign keys in a table A with different names but referring to the same primary key of another table B?

r937 · #14 (**permalink**) 08-12-09, 08:50

Quote:

Originally Posted by kpeeroo

can i have two foreign keys in a table A with different names but referring to the same primary key of another table B?

yes

teams
teamid
teamname

games
gameid
gamedate
hometeamid FK to teams
awayteamid FK to teams

kpeeroo · #15 (**permalink**) 08-12-09, 15:10

how would you do a join select statement so that we get both hometeamid and awayteamid? I am using Visual Web Developer but when the statement is created with both FKs in the join statement, no results are returned. I have to do two separate calls:

Code:

SELECT     DataTypes.name, Student.firstName, Types.name AS Expr1
FROM         DataTypes INNER JOIN
                      Types ON DataTypes.dataTypeID = Types.dataTypeID INNER JOIN
                      Student ON Types.typeID = Student.internetAccessID

This call does not work: returns no results

Code:

SELECT     DataTypes.name, Student.firstName, Types.name AS Expr1
FROM         DataTypes INNER JOIN
                      Types ON DataTypes.dataTypeID = Types.dataTypeID INNER JOIN
                      Student ON Types.typeID = Student.relationshipID AND Types.typeID = Student.internetAccessID

Internet Services

Web Development

Digital Marketing

Consumer Tech