Home » Other » Training & Certification » 1D0-541: CIW Database Design Specialist
1D0-541: CIW Database Design Specialist [message #643528] Fri, 09 October 2015 14:59 Go to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Several years ago, I responded to a thread in this forum asking about the utility of the CIW Database Design Specialist certification and its associated exam, 1D0-541. I provided my thoughts from some limited research and preparation I had done for the exam at the time (mid 2012). I had a study guide started for 1D0-541 and I thought that I might complete the guide and take the exam shortly. As it turns out... measured in terms of the age of the universe, it was just an eyeblink. Measured in human terms, three-plus years might not qualify for the adverb 'shortly'.

That aside. I took the exam this morning and the study guide is completed. OK -- its almost completed. I still have to add in my post-exam updates for a handful of questions that highlighted areas I need to elaborate on. I should publish the guide this weekend.

I can now provide a more informed opinion about the exam. I won't go into huge detail as I intend to write a couple of articles about it -- one about 1D0-541 in isolation and a second that compares it with Oracle's new Database Foundations exam (1Z0-006) that has a similar set of topics.

The Good: I like the topics covered by the exam -- especially for database newbies. Not too long ago I had an assistant working for me as a (very) junior developer. His grasp of design theory, ERDs, etc. was practically nonexistent (despite having just graduated with an MIS degree that should have covered it). Had he understood the concepts covered by this exam, he probably would not currently be my ex-assistant. Beyond the section on relational algebra, which I could have done without, most of what is covered by this exam is reasonably practical. I like the topics covered more than I do the ones on 1Z0-006. 1Z0-006 has quite a few ivory-tower 'who-gives-a-flip' topics that have no use in the real world. I like the study guide I wrote for 1D0-541 better than the one I wrote for 1Z0-006 because I think it offers a better Cliff's Notes overview of the database design process.

The Bad: The test is too simple. Before taking the 1Z0-006 exam in July, I was afraid that it was going to be a walk in the park. As it turns out, the Oracle exam designers were able to write reasonably challenging questions even though the topics were fairly basic. By contrast, way too many of the 1D0-541 questions were simply 'gimmies'. The test was only 50 questions, with a 90 minute time limit, no exhibits, and a good quarter of the test had a single-sentence question with four 1-word answers. The means test takers have all the time in the world to spend.


So what do I recommend? Anyone who has read many of my forum posts or my articles knows I seldom recommend any specific course of action beyond do your own research and do what makes sense for you. In my opinion, vast majority of the information covered by the 1D0-541 topics is very useful if you are a database developer. I've been one for 17 years. If I didn't know this stuff -- I would be a very bad developer. Whether the test would be a good choice for any given individual (other than my ex-assistant), I cannot say.



--moderator update: corrected typo in title, to make sure it is searchable (which is necessary - a useful article, thankyou)

[Updated on: Sat, 10 October 2015 01:39] by Moderator

Report message to a moderator

Re: 1D0-541: CIW Database Design Specialist [message #645307 is a reply to message #643528] Wed, 02 December 2015 10:46 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Would you publish this certification on your LinkedIn profile and put it in your CV?

I passed Oracle Database SQL Expert exam. I think this exam too easy for me, although I still consider me as a junior developer.

[Updated on: Wed, 02 December 2015 10:47]

Report message to a moderator

Re: 1D0-541: CIW Database Design Specialist [message #645309 is a reply to message #645307] Wed, 02 December 2015 13:31 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Quote:
Would you publish this certification on your LinkedIn profile and put it in your CV?


It is on my LinkedIn profile. Mind you, it's towards the bottom of my certifications, but that's because I group them by vendor and Oracle is on top, and I have a lot of Oracle certifications.

That said -- I don't use certifications to try to impress employers or recruiters. I would not point to this (or any) certification and state "I am a great developer because I hold this shiny credential." I generally do not bring up my certifications with potential employers at all. Any time potential employers have asked about my credentials in interviews, I do not try to treat them as an ace card that demonstrate I have certain skills, but as an indicator that I have continually worked throughout my career to improve my skills outside the office.

If you study for any given certification exam and don't learn something new -- you're doing it wrong. Don't pursue the 1D0-541 exam if your goal is to convince someone you are a good database developer. Do it if your goal is to become a better database developer and the topics on that exam are ones you think would be useful to learn more about. The SQL on the CIW exam is a joke -- but the topics on database design are not bad -- and are not covered at all on 1Z0-047.

[Updated on: Wed, 02 December 2015 13:34]

Report message to a moderator

Re: 1D0-541: CIW Database Design Specialist [message #651072 is a reply to message #645309] Tue, 10 May 2016 15:01 Go to previous messageGo to next message
rambutan
Messages: 2
Registered: July 2011
Junior Member
This forum presumably isn't the place to comment on an exam study guide so I'm posting this in the spirit of a discussion about 1D0-541 generally.

Reference "Study Guide for 1D0-541: CIW Database Design Specialist" (by Matthew Morris, starter of this thread), it seems that the given explanations of normal forms are highly flawed [1].

Page 88:

Second rule of normalization: "If a table has a compound primary key and one or more fields in a table depend on only part of the primary key..."

That's not sufficient. 2NF requires that there are no partial dependencies on any candidate key, not just the primary key. Obviously that is required whether or not the primary key happens to be compound/composite.

Third rule of normalization: "If one or more fields do not depend at all on the primary key... (or any part of it), move them to a separate table... "

This is also incorrect because 3NF, like 2NF, is concerned equally with all keys, not just the primary key. 3NF requires that *non-key* attributes must be *non-transitively* dependent on *every* candidate key.

Is this just an error in the study guide or is the guide echoing inaccuracies that exist in official course material? If the course/exam is at fault then the exam candidate who has learnt from other sources ought to take note. I have not taken the exam myself.

[1] See E.F.Codd, "Further Normalization of the Data Base Relational Model" in Rustin (1972): "A relation R is in second normal form if it is in first normal form and every non-prime attribute of R is fully dependent on each candidate key of R. A relation R is in third normal form if it is in second normal form and every non-prime attribute of R is non-transitively dependent on each candidate key of R."
All my other database design books concur with Codd's definition.
Re: 1D0-541: CIW Database Design Specialist [message #651073 is a reply to message #651072] Tue, 10 May 2016 17:50 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Quote:
Is this just an error in the study guide or is the guide echoing inaccuracies that exist in official course material


Before starting on the issue of database normalization, I want to cover the above question. This is not a binary solution set where it is a given that both your sources and conclusions are 100% correct, so the only possible two options are where an error exists.

One of the biggest challenges I had in creating the study guide for the CIW Database Design Specialist certification is that in the past twenty years working with Oracle, I have designed dozens of databases. This makes me highly experienced in (and biased towards) the practical aspects of database design. Much of this exam is about the origins of relational database design (i.e. relational algebra) and textbook logical database design (which is seldom implemented fully in the real world). While I was researching the more theoretical aspects of the book, I found a significant number of points that were stated differently depending on what interpretation the authors used. In addition, a number of authors and sites mixed and matched elements and terminology from relational algebra, logical database design, and physical database design. The three are not synonymous and it's not kosher to pull elements from one and force-fit them into another.

The quote from your post: "A relation R is in second normal form if it is in first normal form and every non-prime attribute of R is fully dependent on each candidate key of R..." is specifically using elements and wording of relational algebra. Relational algebra is the basis of relational databases and relational database design. However, if you were to design a new Oracle database tomorrow (or any other RDBMS for that matter), you would make zero use of relational algebra. It simply has no place in the design process.

The reason I make this distinction is because after reading your post, I went back and checked through the normalization definitions from various sources on database design I'd used in researching my study guide. I then pulled up another half-dozen sources on database design that were not ones I'd used for my book and checked them. In every case that I found, the normalization rules referred to primary keys rather than candidate keys.

Now -- I am not arguing about the legitimacy of your quote from Codd. It sounds very much like others I came across in my research. In my opinion, however, the quote is specifically referring to relational algebra rather than relational database design. A database is generally normalized either late in the logical design process or early in the physical design process (and sometimes both if the switch to physical identifies new normalization issues). If normalization is performed during the logical process, it is after a UID has already been selected for the entity from the available candidate keys. That UID will become the primary key as soon as the switch to the physical model is made. If it is done during the physical design process... candidate keys are no longer relevant because a primary key has already been selected. In either case, it makes more sense from a database design perspective to refer to the normalization rules in respect to a primary key rather than a candidate key. Databases are normalized to their primary key.

You are certainly welcome to disagree. However, I do not believe there is a problem with the wording in the study guide as-is and I have no plans to change it.
Re: 1D0-541: CIW Database Design Specialist [message #651119 is a reply to message #651073] Wed, 11 May 2016 12:03 Go to previous messageGo to next message
rambutan
Messages: 2
Registered: July 2011
Junior Member
Thanks for taking the time to respond Matthew.

As a database professional (not an academic) I'm certainly interested in solving very practical issues of how to design and build good databases. That is Codd's stated aim too. The point of normalization is to help people create, analyse and validate database designs and solve practical database problems.

Database tables often require more than one key and you have an example of that in your book on Page 142. The table is called airports and its keys are apt_id and apt_abbr. Where a table has, or may have, several keys it would be very unwise to ignore all but one key in the normalization process. Of course you are expected to designate one key as primary - that is clear - but if you don't identify and treat the alternate keys as of equal significance with the primary key then the results of your normalization process may be defective and you could introduce exactly the kinds of problem that normalization is supposed to avoid. Attempting to rewrite the definitions of 2NF and 3NF using a primary-key-only approach could even be self-contradictory. It could lead to a situation where the same table design (whether it happened to be a good one or a faulty one) would be both normalized and not normalized depending on which of its keys was deemed to be "primary".

Irrespective of that, I think professional courses and exams ought to get scientific and technical facts right. You seem to have shown through your own researches that there is a lot of unreliable information about. The following books all agree on the correct definitions of Normal Forms (and in my opinion are some of the best books of their kind). Codd's paper alone has been cited directly in literally thousands of books and papers over three decades, so there isn't really any excuse for doubt about what the correct meaning of normalization is.

Database Systems, 4th Edition (Connolly, Begg) [page 411 in particular]
Database Systems: The Complete Book (Garcia-Molina, Ullman, Widom)
Database System Concepts, 6th Edition (Silberschatz, Korth)
Designing Effective Database Systems (Riordan)
Physical Database Design (Teorey et al)

Regards
Re: 1D0-541: CIW Database Design Specialist [message #651132 is a reply to message #651119] Wed, 11 May 2016 17:40 Go to previous message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Quote:
Database tables often require more than one key and you have an example of that in your book on Page 142. The table is called airports and its keys are apt_id and apt_abbr.


That is not a valid example for your purposes. The APT_ID column is a surrogate key... which by definition means it is not a candidate key. It was created for the purpose of acting as the primary key of that table. I note in the study guide why I feel that using natural keys for the PK is not the ideal choice when creating a database application. If you wanted to pick two candidate keys, you could have picked the APT_ABBR column and the APT_NAME column. Both of them are unique for the data shown. Even for a larger sample of data, the APT_NAME column really *shouldn't* ever be duplicated even if there are two airports in the same city -- but it is certainly not an ideal candidate key. In any case, the entity was really normalized to the APT_ABBR column and then the APT_ID surrogate key created to avoid the problems of using a natural key in the table structure.


That aside -- let me propose a better example. The database for an imaginary trucking company needs a table to hold employee data. The table will contain ten columns, three of which are for the employee ID, the employee social security number, and their driver's license number. Each of these columns might seem at first glance to be reasonable candidate keys for the EMPLOYEE entity. However, that is not true. A single employee might have a driver's license from two different states. Alternately, an employee who is not a driver for the company (i.e. an office worker) might not have a driver's license at all. The company might employ someone who was not a US citizen and had no social security number. Alternately, an employee who has a problem with identity theft might submit to have their social security number changed (and PK columns with mutable values is very ill-advised).

One point from the example above is that any issue that would prevent a candidate key from uniquely identifying every row in the table means that the attribute is *not* a valid candidate key. The only column of the above three that would make a good PK is the employee ID. However, if we ignore the above issues and assume a universe where each employee ID linked to one-and-only-one driver's license number and also to one-and-only-one social security number then normalizing the seven non-key attributes for any of the three candidate keys should also produce normal results for the other two. If that is not the case, then one or more of the attributes is not a good candidate key. It is probably not impossible to find an entity with multiple equally valid candidate keys that will not normalize equivalently to all non-key attributes, but I would be shocked if this were a simple task.

In the real world, you seldom find entities with multiple attributes or groups of attributes that make good candidate keys. Devising candidate keys that would make bad primary keys is somewhat easier. For example, in the above employee table, you could propose a multi-column candidate key from the first name, last name, and phone number attributes. While these columns would likely result in a way to uniquely identify employees, using them as a primary key would be a bad idea. Normalizing to these columns because they'd been identified as a candidate key would be beyond stupid.


Quote:
Irrespective of that, I think professional courses and exams ought to get scientific and technical facts right.


An example of a scientific fact is that the standard acceleration due to gravity near the surface of the Earth is 9.8 meters per second per second.
An example of a technical fact is that the USB 2.0 specification allows for a power delivery level up to 100W.


The first is a physical constant that can be demonstrated conclusively. The second is part of a well-documented technical specification. What you are arguing about is neither of those. Codd's paper is about relational algebra, not database design. As I said in my earlier answer -- the two are not synonyms. Pointing to a set of books that have the specific text you prefer does not mean that dozens of other sources that use a different definition are automatically wrong. Codd is the father of relational theory, but he is not the be-all end-all source on how RDBMS software functions or how relational databases are designed. Just one example -- he created a set of 12 rules that he felt all relational databases should adhere to. However, there are no commercial databases in existence that actually follow all twelve of these rules.


Quote:
The following books all agree on the correct definitions of Normal Forms


Once again by indicating what is 'correct', you are demonstrating your bias. I am not of the opinion that you have to be wrong in order for me to be right. Both of your posts clearly show that you feel differently. Anyone who does not accept the specific definition that you have decided is gospel for 2NF and 3NF is wrong and should be burned at the stake (or maybe just singed a little). I don't have copies of the books in question. However, based on what you've indicated, all four of your books 'agree' because they use a definition pulled word-for-word from Codd's paper. Five secondary sources quoting from a primary source do not make the original statement five times better.

Search the following string (with quotes) in Google: "relation R is in second normal form". What you will find is dozens of results, about three-quarters of which use 'primary key' rather than 'all candidate keys' in the definition. This result does not prove that 'primary key' is the only conceivable correct definition any more than the five books you reference prove that 'all candidate keys' is the only conceivable correct definition.


I did not state in my earlier response that the definition you supplied was wrong, nor will I in this post. What I indicated before, and will repeat here, is that there is a good reason why the vast majority of 2NF/3NF definitions refer to the primary key rather than all candidate keys. Most database designers normalize to the primary key. If that's not how you normalize database tables -- more power to you. It should work, but it's not what I do and not what I would recommend to someone learning how to design database applications.


Going to the heart of the issue, though: I took the exam months ago. I could not state with absolute certainty whether questions about 2NF and 3NF utilized 'candidate key' or 'primary key' in their wording. However, I can state with complete confidence that there are no questions where exam candidates will have to choose between answers that differ only in their use of 'all candidate key' or 'primary key'.

So -- from the standpoint of a study guide for the exam, I am positive that the wording I have used will not cause any of my readers to miss a question on the normal forms. From the standpoint of teaching my readers how to design relational database applications, I am of the opinion that normalizing to the primary key makes a great deal more sense than normalizing to all candidate keys. As I indicated in my previous post, I am not going to change anything based on the input you have provided to date.
Previous Topic: 1Z0-068 sample question
Next Topic: OCP 11g DBA help
Goto Forum:
  


Current Time: Thu Mar 28 12:35:24 CDT 2024