Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Help with Oracle creating a database

    Hi I'm new to Oracle and am finding it hard to create a database. I am trying to create a database similar to this ERD attached to the post. The ones highlighted in blue are the primary keys. How would I write the sql I'm really unsure of.

    thank you in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Hi I'm new to Oracle and am finding it hard to create a database.
    Answer depends upon what is really meant by "database".
    Is Oracle RDBMS installed on your local system or any remote system you can access?
    How do we know what you have to start with?
    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
    Apr 2011
    Posts
    3
    I'm trying to implement an OLTP database using the ERD. How would I create a table for each of the headings in the diagram using sql. I'm using SQL 9i

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How would I create a table for each of the headings in the diagram using sql.
    CREATE TABLE ......
    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.

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Thank you for the help so far I have created all the tables, just have two questions which i am unsure about.

    1. Employees table has a recursive relationship as managers are employees to. For this I have created a Manage table is this correct?

    CREATE TABLE Manage
    (Employee_ID PRIMARY KEY,
    Manager_ID NOT NULL,
    FOREIGN KEY (Manager_ID) REFERENCES Employees,
    FOREIGN KEY (Employee_ID) REFERENCES Employees);

    2. Departments is related to Employees via the manager ID how would I show this relationship as manager ID is not a PK?

    thank you in advance.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You don't need the MANAGE table - create a foreign key within the EMPLOYEES table instead:
    Code:
    SQL> create table employees
      2  (employee_id number primary key,
      3   manager_id  number,
      4                 constraint fk_mgr_emp foreign key (manager_id)
      5                 references employees (employee_id)
      6  );
    
    Table created.
    
    SQL> insert into employees values (1, null);
    
    1 row created.
    
    SQL> insert into employees values (2, 1);
    
    1 row created.
    
    SQL> insert into employees values (3, 5);
    insert into employees values (3, 5)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SCOTT.FK_MGR_EMP) violated - parent key not found
    
    
    SQL>
    As of your second question: DEPARTMENTS should be in relation with EMPLOYEES via the DEPARTMENT_ID column, not MANAGER_ID.

Posting Permissions

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