0

I am trying to design a database that acts as a sort of portfolio for my university work. The database needs to contain details about the course I am currently on, grades of the modules I have completed and contain examples of work I have completed in these modules. The database must be in 3NF.

The database is only going to feature information about one person so the use of student_ID or something doesn't seem logical to me.

My original design was something a long the lines of:

  • [Course]
  • Course_Title(PK),
  • Start_Date,

  • End_Date,

  • [Module]

  • Module_Title(PK)
  • Module_Grade

  • Course_Title(FK)

  • [Assignment]

  • Assignment_Title(PK)
  • Assignment_Grade
  • Assignment_File
  • Module_Title(FK)

I am aware that using this design would mean the table would feature repeating data and thus not be in 3NF. Any pointers as to what possible attributes I could introduce or how I should model this would be greatly appreciated.

gzgillespie
  • 9
  • 3
  • 1
    *'I am aware that using this design would mean the table would feature repeating data and thus not be in 3NF'*, where is the repeating data? AFAIC, I don't see any repeating data from your design. – rexcfnghk Oct 23 '15 at 01:16
  • I mistakenly had the Module_Title attribute in the Assignment entity named "Module_Name". (I have now edited this). Would Module_Title and Course_Title not be repeating? Each module has many assignments and each course many modules. – gzgillespie Oct 23 '15 at 01:40
  • Repeating data in foreign key columns are fine because you are representing one-to-many relationships. Imagine Course `Foo` having Modules `A` and `B`, you will need to represent the fact *both modules `A` and `B` belong to Course `Foo`* by repeating the foreign key to rows of modules `A` and `B` – rexcfnghk Oct 23 '15 at 01:44
  • Thank you for clearing this up. Would this design qualify as being in 3NF then? – gzgillespie Oct 23 '15 at 01:50
  • Yes. I suggest your read the [answer to a similar question](http://stackoverflow.com/questions/723998/what-are-1nf-2nf-and-3nf-in-database-design) for further clarification – rexcfnghk Oct 23 '15 at 06:29
  • I look at this and wonder why you are using titles to join, not keys? If the course title changes you need to change it in a couple of spots. – Nick.Mc Oct 24 '15 at 07:59
  • @Nick.McDermaid 1. One joins because it reflects the properties of the data wanted; constraints (PKs, CKs, superkeys, FKs included) are irrelevant. 2. The titles *are* (primary) keys and FKs. Did you mean, (surrogate) id( key)s? 3. Probably because they aren't relevant to learning about normalization. In fact they *impede* learning because poeple think they have something to do with normalization. And it's a lot easier to following examples containing only known application words rather than typical alphanumeric ids. – philipxy Oct 30 '15 at 07:17
  • given that the question talks about _databases_ and _tables_ and and _student_id_ rather than _logical models_ and _entities_ it's a fair assumption that it's a physical model. Also the assertion that it contains repeating data... or maybe we're speaking a different language. Not sure but I'm always willing to learn – Nick.Mc Oct 30 '15 at 10:07
  • @Nick.McDermaid To whom and re what is your last comment? *What about* "the assertion that it contains repeating data"? PS As non-poster of this post use @ to notify someone other than the poster. (As poster, use @ to notify someone when more than one have commented.) – philipxy Oct 31 '15 at 04:39
  • "Not feature repeating data" (whatever "repeating data" means) is not a definition or requirement of 3NF, so to "feature repeating data" does not imply not 3NF. Multiple occurrences of (subtuple) values per column(s) or table does not imply normalization is needed. Normalization doesn't introduce attributes. (See my answer.) – philipxy Nov 10 '15 at 10:27

1 Answers1

2

Normalization requires knowing all functional dependencies of your base relations... and other stuff like knowing how to normalize. (Viz definitions & procedures.) You don't mention any of that. So you need to read the references you were given. There are many .ppt & .pdf files online, eg from utexas.edu & stanford.edu, and even textbooks online.

You also mention some irrelevant things, so you seem to have some misconceptions. Repeating a subtuple value in a base relation happens all the time and does not imply redundancy. Redundancy is about the same fact being asserted by more than one (present or absent) tuple. Normalization does not involve adding new attributes. It just distributes old ones into new base relations.

Knowing whether this is in 3NF gets you nothing but, perhaps, marks. You need to learn why a table or database is or isn't in a given normal form and how to get it to one. Starting from your question, ie from nothing, that "why & how" is tantamount to an introductory textbook's presentation of normalization.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83