3

Well, here's the database structure I've created for a quiz. 

    DROP TABLE "E20079852" cascade constraints;
    DROP TABLE "E20079852_DEPARTMENT" cascade constraints;
    DROP TABLE "E20079852_MANAGEMENT" cascade constraints;
    DROP TABLE "E20079852_PROJ" cascade constraints;
    DROP TABLE "E20079852_ASSIGNMENTS" cascade constraints;

    CREATE TABLE E20079852 
    (
      EID NUMBER(4) NOT NULL 
    , EFNAME VARCHAR2(20) NOT NULL 
    , ELNAME VARCHAR2(20) NOT NULL 
    , EDOB DATE NOT NULL 
    , EDOJ DATE NOT NULL 
    , EDOR DATE NOT NULL 
    , DID NUMBER(4) NOT NULL 
    , CONSTRAINT E20079852_PK PRIMARY KEY 
      (
        EID 
      )
      ENABLE 
    );

    CREATE TABLE E20079852_DEPARTMENT 
    (
      DID NUMBER(4) NOT NULL 
    , DNAME VARCHAR2(20) NOT NULL 
    , DTOTEMPCOUNT NUMBER(4) NOT NULL 
    , CONSTRAINT TABLE1_PK PRIMARY KEY 
      (
        DID 
      )
      ENABLE 
    );

    CREATE TABLE E20079852_MANAGEMENT 
    (
      EID NUMBER(4) NOT NULL 
    , EMNGRID NUMBER(4) NOT NULL 
    , CONSTRAINT E20079852_MANAGEMENT_PK PRIMARY KEY 
      (
        EID 
      )
      ENABLE 
    );

    CREATE TABLE E20079852_PROJ 
    (
      PID NUMBER(4) NOT NULL 
    , PROJNAME VARCHAR2(20) NOT NULL 
    , DID NUMBER(4) NOT NULL
    , PROJCOST NUMBER(20) NOT NULL 
    , CONSTRAINT E20079852_PROJ_PK PRIMARY KEY 
      (
        PID 
      )
      ENABLE 
    );

    CREATE TABLE E20079852_ASSIGNMENTS 
    (

      PID NUMBER(4) NOT NULL 
    , EIDS VARCHAR2(20) NOT NULL
    , ESTMTCOST NUMBER(20) NOT NULL 
    , ACTUAL NUMBER(20) NOT NULL 
    , CONSTRAINT E20079852_ASSIGNMENTS_PK PRIMARY KEY 
      (
        PID 
      )
    );

    -- INSERTing data:

    Insert into E20079852 values(1,'Jacalyn','Agarwal',TO_DATE('08/09/2007','dd-MM-YYYY'),TO_DATE('06/08/2037','dd-MM-YYYY'),TO_DATE('08/09/2067','dd-MM-YYYY'),2);
    Insert into E20079852 values(2  ,'Jace','Khatri',TO_DATE('02/06/2019','dd-MM-YYYY'),TO_DATE('02/06/2039','dd-MM-YYYY'),TO_DATE('02/06/2069','dd-MM-YYYY'),1);
    Insert into E20079852 values(3  ,'Jacey','Ahuja',TO_DATE('24/09/2024','dd-MM-YYYY'),TO_DATE('24/09/2044','dd-MM-YYYY'),TO_DATE('23/09/2074','dd-MM-YYYY'),4);
    Insert into E20079852 values(4  ,'Jack','Anand',TO_DATE('08/12/2025','dd-MM-YYYY'),TO_DATE('08/01/2045','dd-MM-YYYY'),TO_DATE('08/12/2075','dd-MM-YYYY'),3);
    Insert into E20079852 values(5  ,'swami','Laghari',TO_DATE('27/10/2035','dd-MM-YYYY'),TO_DATE('27/01/2065','dd-MM-YYYY'),TO_DATE('27/10/2065','dd-MM-YYYY'),2);
    Insert into E20079852 values(6  ,'Jackie','Patel',TO_DATE('08/07/2071','dd-MM-YYYY'),TO_DATE('08/03/2091','dd-MM-YYYY'),TO_DATE('08/07/2099','dd-MM-YYYY'),1);
    Insert into E20079852 values(7  ,'Jackson','Reddy',TO_DATE('27/07/2021','dd-MM-YYYY'),TO_DATE('27/04/2031','dd-MM-YYYY'),TO_DATE('27/07/2061','dd-MM-YYYY'),4);
    Insert into E20079852 values(8  ,'Jaclyn','Swami',TO_DATE('22/11/2021','dd-MM-YYYY'),TO_DATE('22/12/2041','dd-MM-YYYY'),TO_DATE('22/11/2071','dd-MM-YYYY'),4);
    Insert into E20079852 values(9  ,'Jacob','Rangaswami',TO_DATE('07/12/2022','dd-MM-YYYY'),TO_DATE('07/10/2066','dd-MM-YYYY'),TO_DATE('07/12/2096','dd-MM-YYYY'),3);
    Insert into E20079852 values(10 ,'Jacque','Nagaswami',TO_DATE('28/07/2025','dd-MM-YYYY'),TO_DATE('28/06/2035','dd-MM-YYYY'),TO_DATE('28/07/2065','dd-MM-YYYY'),2);
    Insert into E20079852 values(11 ,'Jacques','Ramaswami',TO_DATE('02/10/2034','dd-MM-YYYY'),TO_DATE('02/08/2054','dd-MM-YYYY'),TO_DATE('02/10/2084','dd-MM-YYYY'),3);
    Insert into E20079852 values(12 ,'Jada','Balaswami',TO_DATE('08/10/2071','dd-MM-YYYY'),TO_DATE('08/02/2081','dd-MM-YYYY'),TO_DATE('08/10/2091','dd-MM-YYYY'),2);
    Insert into E20079852 values(13 ,'Jody','Balakrishna',TO_DATE('08/03/2072','dd-MM-YYYY'),TO_DATE('08/08/2087','dd-MM-YYYY'),TO_DATE('08/03/2097','dd-MM-YYYY'),1);


    Insert into E20079852_DEPARTMENT values(1,'Slogging',3);
    Insert into E20079852_DEPARTMENT values(2,'Boring',4);
    Insert into E20079852_DEPARTMENT values(3,'Scoring',3);
    Insert into E20079852_DEPARTMENT values(4,'Exploring',3);


    Insert into E20079852_MANAGEMENT values(1,1);
    Insert into E20079852_MANAGEMENT values(2,4);
    Insert into E20079852_MANAGEMENT values(3,8);
    Insert into E20079852_MANAGEMENT values(4,8);
    Insert into E20079852_MANAGEMENT values(5,6);
    Insert into E20079852_MANAGEMENT values(6,7);
    Insert into E20079852_MANAGEMENT values(7,7);
    Insert into E20079852_MANAGEMENT values(8,6);
    Insert into E20079852_MANAGEMENT values(9,8);
    Insert into E20079852_MANAGEMENT values(10,11);
    Insert into E20079852_MANAGEMENT values(11,3);
    Insert into E20079852_MANAGEMENT values(12,9);
    Insert into E20079852_MANAGEMENT values(13,10);


    Insert into E20079852_PROJ values(1,'A',2,8107948);
    Insert into E20079852_PROJ values(2,'B',1,16076753);
    Insert into E20079852_PROJ values(3,'C',2,3201912);
    Insert into E20079852_PROJ values(4,'D',3,8999724);
    Insert into E20079852_PROJ values(5,'E',2,16720391);
    Insert into E20079852_PROJ values(6,'F',3,3564970);
    Insert into E20079852_PROJ values(7,'G',2,18714509);
    Insert into E20079852_PROJ values(8,'H',4,12458138);


    Insert into E20079852_ASSIGNMENTS values(1,'8',5482978,2142205);
    Insert into E20079852_ASSIGNMENTS values(2,'7,9',16847801,13506375);
    Insert into E20079852_ASSIGNMENTS values(3,'6',12370094,8939168);
    Insert into E20079852_ASSIGNMENTS values(4,'5,10',1161815,5302872);
    Insert into E20079852_ASSIGNMENTS values(5,'4',16137139,18486736);
    Insert into E20079852_ASSIGNMENTS values(6,'3,11',5244118,6315452);
    Insert into E20079852_ASSIGNMENTS values(7,'2,12,13',18336797,10989185);
    Insert into E20079852_ASSIGNMENTS values(8,'1',17989109,2950031);


    alter table "E20079852" add constraint Department_DID_FK foreign key("DID") references "E20079852_DEPARTMENT"("DID");
    alter table "E20079852" add constraint Management_EID_FK foreign key("EID") references "E20079852_MANAGEMENT"("EID");
    alter table "E20079852_PROJ" add constraint Assignments_PID_FK foreign key("PID") references "E20079852_ASSIGNMENTS"("PID");

Question

I'm able to handle setting up easy and intermediate questions by myself, but I am not able to figure out what would be the best way to frame the DML operations to answer these two questions.

  1. How to prove there's nobody working on multiple projects, though multiple people are working on the same project?
  2. Who is the top most manager in each of the departments that have spent less than 80% of their estimation?

The meaning carried by the columns under consideration

Here are some ideas about the columns and the meaning they carry:

  • EID: Employee ID
  • DID: Department ID
  • PID: Project ID
  • DTOTEMPCOUNT: Total number of employee in a department
  • ESTMTCOST: Estimated cost of project by the employee in charge
  • ACTUAL: Actual expense fr the project till date
  • PROJCOST: Overall project cost allowed by organisation
  • EMNGRID: Manager's ID for the respective employee

  • DOR: Date of Retirement(Estimated)

  • The Assignments table EID column have retained multiple EID values as a VARCHAR/STRING.

Current considerations

I'm clueless about obtaining an answer for the first question so couldn't handle it at all, but regarding the second one I have figured out how to get the projects that have spent less than 80% of their estimation with the following SELECT operation:

    SELECT unique(D.DNAME)
      FROM Assignments A, PROJ P, DEPARTMENT D  
    WHERE A.PID = P.PID 
      AND P.DID = D.DID
      AND A.ACTUAL < (A.ESTMTCOST*0.8);

I would highly appreciate your help.

Improve this question
1
  • 1
    unique is not a function. It is always applied to all columns in the select list. Writing unique(d.dname) is exactly the same thing as unique d.dname (or the more common distinct d.dname)
    – a_horse_with_no_name
    Sep 6, 2017 at 17:57

1 Answer 1

Reset to default
1

Some suggestions for future projects. What does the name E20079852 represent? I notice that all your tables have this prefix, so I guess it is some kind of grouping of tables, to indicate that they belong together. You can use a schema for that.

CREATE TABLE E20079852.PROJ 
( PID NUMBER(4) NOT NULL 
, PROJNAME VARCHAR2(20) NOT NULL 
, DID NUMBER(4) NOT NULL
, PROJCOST NUMBER(20) NOT NULL 
, CONSTRAINT PROJ_PK PRIMARY KEY 
  (
    PID 
  )
  ENABLE 
);

I also think you should come up with a better name for the schema, what domain do these tables represent? I assume that the table that is named E20079852 represents USERS or something similar:

CREATE TABLE E20079852.USERS 
( EID NUMBER(4) NOT NULL 
, EFNAME VARCHAR2(20) NOT NULL 
, ELNAME VARCHAR2(20) NOT NULL 
, EDOB DATE NOT NULL 
, EDOJ DATE NOT NULL 
, EDOR DATE NOT NULL 
, DID NUMBER(4) NOT NULL 
, CONSTRAINT USERS_PK PRIMARY KEY (EID)
  ENABLE 
);

For the understanding of the schema (not to mention consistency of the information), declare referential constraints. I assume this is the case, but it is only a guess:

CREATE TABLE E20079852.ASSIGNMENTS 
( PID NUMBER(4) NOT NULL
      REFERENCES E20079852.PROJECTS(PID) 
, ... 
);

Also, don't use "," separated lists, at least not for information that references other things. I would suggest:

CREATE TABLE E20079852.ASSIGNMENTS 
( PID NUMBER(4) NOT NULL
      REFERENCES E20079852.PROJECTS(PID)
, EID NUMBER(4) NOT NULL
      REFERENCES E20079852.USERS(EID)
, ...
, CONSTRAINT ASSIGNMENTS_PK PRIMARY KEY (PID, EID)
  ENABLE 
);

If you represent ASSIGNMENTS this way it is very easy to answer question number one:

SELECT EID
FROM E20079852.ASSIGNMENTS 
GROUP BY EID
HAVING COUNT(1) > 1

With your current schema you need to split the EID string into its atoms, and the use a similar query as above.

Improve this answer
2
  • Just to expand a bit on a comment by @Lennart . . . having comma-separated lists in a single column breaks the first rule of relational data design. You should design your tables to what is known as Third Normal Form (google that), based strictly on the nature and relationship of the individual data elements. Design your tables to the data, not to the queries/reports. If you'll do that, most queries become almost trivial.
    – EdStevens
    Sep 6, 2017 at 23:17
  • I have so many things to thank for, but most of all thank you guys for correcting me. I can not teach anyone better if I myself dont know better. Thank you for the corrections and providing a possible answer as well.
    – S4nd33p
    Sep 7, 2017 at 4:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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