-1

I am trying to use the Relational Database Tools to check if my schema is in Boyce-Codd Normal Form and to understand the relevant functional dependencies.

However, I am having trouble "converting" my schema to ABC etc. format as listed in the tool.

For example:

CREATE TABLE train (
train_code SERIAL PRIMARY KEY,
name TEXT NOT NULL); 

CREATE TABLE journey (
journey_id SERIAL PRIMARY KEY, 
int INTEGER,
train_code REFERENCES train(train_code)); 

CREATE TABLE price (
journey_id REFERENCES journey(journey_id),
price INTEGER); 

Is A = train, B = journey, and C = price, or is A = train_code, B = name, and A → B as the train_code determines the name?

4
  • 1
    I'm sorry, what is the question here? How to use the tool?
    – Jacob H
    Commented Jun 14, 2017 at 13:07
  • How the sample schema above translates into ABC etc in the tool so that I can understand the dependancies and whether the schema is in Boyce-Codd Normal form. :) Commented Jun 14, 2017 at 15:32
  • 1
    You've got it backwards. You have to understand the dependencies before you go through normalization. A tool has to do the same thing. The tool can look at the existing data and make some pretty intelligent guesses about dependencies. But there is no substitute for analyzing the subject matter to understand the rules. Commented Jun 14, 2017 at 15:44
  • Yeah. But I created a schema (just an example above) and want to understand it through the tool further. :) Is it possible? Commented Jun 14, 2017 at 16:24

1 Answer 1

2
  1. The tool mentioned does not require trasforming your attributes into single letter identifier, you can use your identifiers (you just need to remove the ‘_’ character) like journeyid, name, etc. (and it transforms them automatically to uppercase).
  2. Those kind of tools requires that you give a single relation, containing all the attributes, like R(traincode, name, int, journeyid, price) and then all the functional dependencies holding among them, like for instance traincode -> name, etc. Nobody but you can give those functional dependencies, since they describe the semantics of your data. So you need to understand what a functional dependency is, and describe those holding among the attributes of your problem.
  3. Finally, this kind of systems will give you a decomposition of the single relation in several, normalized, relations.
10
  • Just to confirm: R(input all columns across all tables here). The relations are primary key --> column. What about references to other columns? Commented Jun 15, 2017 at 11:25
  • 1
    In the relational data model there is no concept of "reference to a column", so I don't understand your question. If you are asking about which functional dependencies fo define, I suppose that in your problem they are: traincode -> name; journeyid -> int, traincode; journeyid -> price (or something similar). If you are asking about the primary and foreign keys of the decomposed relations, then the primary key is determined by the dependencies used to produce the table, and the foreign keys are the attributes in a table which are primary keys in other tables.
    – Renzo
    Commented Jun 15, 2017 at 12:19
  • Taking that last example. traincode; journeyid -> price. But what is the relation for journeyid here given it references another table. That was the piece I didn't fully understand here. Commented Jun 15, 2017 at 22:02
  • 1
    @DinoAbraham, as I said, there is only a table, called "R", and in the example there are 5 different attributes, called A, B, C, AB and ABC. So these are just examples of names attributes, i.e. name of columns, with no particular meaning (they could have been called Name, Age, Address, etc. or anything else).
    – Renzo
    Commented Jun 18, 2017 at 18:30
  • 1
    @DinoAbraham, the relation is correct, the functional dependencies (not the relations!) should be only traincode -> name; journeyid -> int; journeyid -> traincode; journeyid -> price (the others are unuseful, since an attribute always determines itself). If you try this you discover that the relation is not in 3NF (it is in 2NF), and that the system (at the end of the printing) produces a decomposition in two relations. In your schema there are no multivalued dependencies (this is a topic which should require a few pages to be explained!).
    – Renzo
    Commented Jun 20, 2017 at 13:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.