How to manipulate the data contained in a quiz database

oracleoracle-12c

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.

How to prove there's nobody working on multiple projects, though multiple people are working on the same project?
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.

Best Answer

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.

Related Solutions

Oracle – Drop Table Without Dependencies and Solve Block Corruption

Apparently you dropped the object already. Now to calm you down - corrupted blocks now most probably are in free space and do no harm. They just annoy you during backups. To check that:

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) corr_start_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
  , 'Free Block' description
from dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1
  and f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

If it really shows here you have two options. You can simply ignore it. Once the block will be assigned to some object it will be reformated and corruption will go away. If you want to fix corrupted blocks then you will have to create the object which would occupy your corrupted blocks.

Let's say your corrupted block exists in tablespace USERS and datafile /ora/users01.dbf. First you have to make datafiles of tablespace not autoextendable. That is not to inflate datafile size. And then you'll create filler table.

create table filler (
  padding varchar2(1000)
) tablespace users nologging pctfree 90 pctused 10;

Then you have to extend table's segment so that it would incorporate corrupt blocks:

alter table filler allocate extent
  (datafile '/ora/users01.dbf' size 100m);

In such way you can add several extents adjusting their sizes. After you'll check that corrupt blocks now longer belong to free space you can fill the table:

insert /*+append */ into filler  select rpad('x',1000) from
dba_objects
--  filler
where rownum <= 50000;

First insert/select goes from dba_objects then it is faster to insert/select from filler table itself.

Now you just have to check datafile using RMAN:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 333;

And drop filler table:

DROP TABLE filler PURGE;

Easy. :)

Oracle Database Design – Table Design for Multi-Type Data

Not sure why one of the answers made such bold statement . Regardless: everything can be modeled in relational model

create table entry 
( 
  id number(12) primary key, 
  name varchar2(256) not null unique,  
  customer_id number not null references customer(id)
);

create table datatypes
(
 id number primary key,
 name varchar2(256) not null unique
);
create table entry_data ( 
  id number(12) primary key, 
  datatype_id  number not null references datatypes(id),
  value     varchar2(256),  --or CLOB ,no index
  value_geometry geom  ,    -- no index
  --value_blob   blob -- if you neeed, no index
  entry_id number not null references entry(id),
);

your task is related to the multi-valued attributes task. Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value or value_geometry.

Best Answer

Related Solutions

Oracle – Drop Table Without Dependencies and Solve Block Corruption

Oracle Database Design – Table Design for Multi-Type Data

Related Question