Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2013
    Posts
    36

    Unanswered: Foreign keys help, pleasee.

    So I am using Oracle Application Express, I've built my tables, added primary keys and some data, but of course I just have foreign keys left, this is where I need some help from more experienced users.

    I want to know from you guys which field the foreign key would go into in each table, and maybe explain to me why.

    The database is basically a movie downloading system. Customers have memberships, they download movies, movies classification codes are tracked, movies titles etc. It's 5 simple tables, my queries will typically grab data from each others tables e.g. listing customers who download movies between two dates or movies with this classification code etc.

    I've listed all the primary keys I've set with (P) and the one foreign key I think I've got down correctly? with (F).

    TABLE 1: MOVIES_AVAILABLE_MOVIES

    (P) MOVIE_ID
    MOVIE_TITLE
    MOVIE_DURATION
    CLASSIFICATION_CODE
    MOVIE_DIRECTOR

    TABLE 2: MOVIES_CLASSIFICATION_CODE

    (P) CLASSIFICATION_CODE
    DESCRIPTION_OF_CODE

    TABLE 3: MOVIES_CUSTOMER

    (P) CUSTOMER_ID
    LAST_NAME
    FIRST_NAME
    D_O_B
    MEMBERSHIP_TYPE_CODE (F) links to MEMBERSHIP_TYPE_CODE (P) in TABLE 5.
    COUNTRY_OF_RESIDENCE

    TABLE 4: MOVIES_DOWNLOADED

    (P) CUSTOMER_ID
    (P) MOVIE_ID
    (P) DOWNLOAD_DATE

    TABLE 5: MOVIES_MEMBERSHIP

    (P) MEMBERSHIP_TYPE_CODE
    MEMBERSHIP_TYPE
    MONTHLY_FEE
    MAX_DOWNLOADS_MONTHLY

    Thank you guys.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Which parent record must exist before child record can exist?
    Foreign Key exists to prevent orphan child record from occurring.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by anacedent View Post
    Which parent record must exist before child record can exist?
    Foreign Key exists to prevent orphan child record from occurring.
    I guess table 1 - MOVIES_AVAILABLE_MOVIES is my main table, that's where the Movie.ID is. I just don't understand where to put my foreign keys before working on queries.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    foreign key points to a primary key in another table
    think of it as something "has a"
    ferisntance an order has one or many order items

    the order must exist before an order item is created (or must be created immediately before an order item (assuming that an order can have multiple items)

    for a fk to be valid there MUST be a value as the PK in another table, but there converse isn't true a PK exists whether or not there are FKs' referencing
    ferinstance say you have a system that contain information about cars. you might have the car maker as a table, you my have details of the car maker but no actual models of that maker declared.

    that sort of realtionship is usually a one to zero, one or many. the PK must have a value, there may be zero, one or many rows in the other table that reference that particular value

    one area of possible confusion is where you have a many to many relationship say you decode to allow multiple categories for a film/programme. classically in a relational DB thats tricky to model unless you go the route of an interesction table, where you have three tabel to represent the realtionship
    table: films
    ID PK
    table: categories
    ID PK
    table films_categories
    film_id PFK
    category_id PFK

    in films_categories the primary key is a composite of film_id and category_id both of which are foreign keys to their respective tables film and categories
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Not every table that has a Primary Key has any corresponding table with a Foreign Key.

    Every table with a Foreign Key constraint does have a corresponding parent table.
    It is strictly data dependent.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by healdem View Post
    foreign key points to a primary key in another table
    think of it as something "has a"
    ferisntance an order has one or many order items

    the order must exist before an order item is created (or must be created immediately before an order item (assuming that an order can have multiple items)

    for a fk to be valid there MUST be a value as the PK in another table, but there converse isn't true a PK exists whether or not there are FKs' referencing
    ferinstance say you have a system that contain information about cars. you might have the car maker as a table, you my have details of the car maker but no actual models of that maker declared.

    that sort of realtionship is usually a one to zero, one or many. the PK must have a value, there may be zero, one or many rows in the other table that reference that particular value

    one area of possible confusion is where you have a many to many relationship say you decode to allow multiple categories for a film/programme. classically in a relational DB thats tricky to model unless you go the route of an interesction table, where you have three tabel to represent the realtionship
    table: films
    ID PK
    table: categories
    ID PK
    table films_categories
    film_id PFK
    category_id PFK

    in films_categories the primary key is a composite of film_id and category_id both of which are foreign keys to their respective tables film and categories
    Quote Originally Posted by anacedent View Post
    Not every table that has a Primary Key has any corresponding table with a Foreign Key.

    Every table with a Foreign Key constraint does have a corresponding parent table.
    It is strictly data dependent.
    Ok, I think understand now. The thing is, the 5 tables I have shown are set to a certain scenario so the fields within them can't be changed neither can I add anymore tables. I need to deal with what I have.

    Am I right in thinking the following foreign keys set ups are correct then?

    TABLE 3: the MEMBERSHIP_TYPE_CODE is a foreign key which links to MEMBERSHIP_TYPE_CODE (P) in TABLE 5.

    TABLE 1: the CLASSIFICATION_CODE is a foreign key which links to CLASSIFICATION_CODE (P) in TABLE 1.

    Here is where I am confused, in Table 4 I have;
    (P) CUSTOMER_ID
    (P) MOVIE_ID
    (P) DOWNLOAD_DATE

    I've made all 3 primary keys for now but I know the downlaod date can't be a foreign key as it's isn't primary anywhere else..and the Customer ID has to be a primary key obviously..does this mean Movie ID is foreign key and I just leave downlaod date alone?

    Thanks.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    A Primary Key is comprised of 1 or more columns & must be UNIQUE (no duplicates)
    Generally speaking & more often than not, a Foreign Key (FK) is comprised of a single column.

    http://en.wikipedia.org/wiki/Foreign_key
    Last edited by anacedent; 10-27-13 at 17:22.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by anacedent View Post
    A Primary Key is comprised of 1 or more columns & must be UNIQUE (no duplicates)
    Generally speaking & more often than not, a Foreign Key (FK) is comprised of a single column.

    Foreign key - Wikipedia, the free encyclopedia

    Ok, but can you give me some feedback on my previous post?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by london34 View Post
    Ok, but can you give me some feedback on my previous post?
    A given column can be both a primary and foreign key. This gets a little bit confusing because I have to mix both logical and physical nomenclature to make the following discussion make sense, please bear with me... A PK (Primary Key) is a logical attribute (physical column) or group of attributes that uniquely identify exactly one row in the current entity (table). An FK (Foreign Key) is the attribute/column link to another entity's Primary Key. In the case of a multi-attribute PK there can be any number (zero to the attribute count) of foreign keys.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •