SQL and PL/SQL


Applying Normalization - but cannot avoid redundancy


Dear  SQL EXPERTS , I am trying to understand data modeling.Just for example , i have  made two tables from one table  >> One Big table - Without Normalization  -----------------------------------------------------------------------
EMPID | ENAME    | DESG      |   SAL   |    JOB           |   DEPT_ID |
------|----------|-----------|---------|------------------|-----------|
01    |   SMITH  |  EXECU    |   2000  | SALES, MARKETING |   10,20   |
02    |   SCOTT  |  ENGG     |   2800  | QA,PROD          |   30,40   |
03    |   FORD   |  HR       |   3000  | HUMAN-RESOURCE   |   50      |    
------|----------|-----------|-------- |------------------|-----------|
   1ST NORMAL FORM----------------- I have apples 1NF here.  (1 cell has 1 value) 
--------------------------------------------------------------------------------
EMPID | ENAME    | DESG              |   SAL    | DEPT             |   DEPT_ID |
------|----------|------------------ |----------|------------------|-----------|
01    |   SMITH  |  SALES-EXECU      |   2000   |  SALES           |   10      |
01    |   SMITH  |  MARKETING-EXECU  |   2000   |  MARKETING       |   20      |
02    |   SCOTT  |  TEST-ENGG        |   2800   |  QA              |   30      |
02    |   SCOTT  |  PROD-ENGG        |   2800   |  PROD            |   40      |
03    |   FORD   |  HR               |   3000   |  HUMAN-RESOURCE  |   50      |    
------|----------|------------------ |----------|------------------|-----------|
   I have applied 2ND NORMAL FORM ( Main table broken into two tables)But the problem is i cannot avoid duplications.How do i make it sense ? -------------------------------------------------
EMPID | ENAME    | DESG              |   SAL    |
------|----------|------------------ |----------|
01    |   SMITH  |  SALES-EXEC       |   2000   |
01    |   SMITH  |  MARKETING-EXEC   |   2000   |
02    |   SCOTT  |  TEST-ENNGG       |   2800   |
02    |   SCOTT  |  PROD-ENGG        |   2800   |
03    |   FORD   |  HR               |   3000   |
------|----------|-------------------|----------|
  -----------------------------
  DEPT          |   DEPT_ID |
----------------|-----------|
SALES           |   10      |
MARKETING       |   20      |
QA              |   30      |
PROD            |   40      |
HUMAN-RESOURCE  |   50      |    
----------------|-----------|
 FYI : Basically i am NOT a DEVELOPER but DBA
You're missing the dept_id column in your first tablue, you can then join the tables together using that column if you want to see the name of the department an employee works in. If you want to display multiple departments on the same row then you can aggregate the rows together and use the listagg function to aggregate the department names into a list. Let us know if that's unclear. -editActually missed another obvious thing, you should be storing empid, name and salary in its own table (as you only need to know the empid to know the name and salary).Then only store empid in this bridge table, when you need to know the names and salary you can do a join to your employee table.
To add to Andrew's answer, I'm going to use words from the "entity relationship model", and I hope I use them right. You have two "entities": employee and department - each with attributes;The "entities" have a many to many relationship: an employee can work in more than one department, and a department can have more than one employee.The EMP table should just have EMPID (a primary key that represents the entity), ENAME, SAL (the attributes).The DEPT table should just have DEPT_ID (a primary key that represents the entity), DEPT (the attribute).Then there should be what Andrew calls a "bridge table" that shows which employee is related to which department(s).Here is what I would put in that table: --------------------------------------
EMPID | EMP_DEPT_ROLE     |  DEPT_ID |
------|------------------ |----------|
01    |  EXECU            |  10      |
01    |  EXECU            |  20      |
02    |  ENGG             |  30      |
02    |  ENGG             |  40      |
03    |  HR               |  50      |
------|------------------ |----------|
 I consider "execu, engg, hr" as attributes of the relations, not as attributes of the employees alone, so that stuff goes in the bridge table. Best regards, Stew Ashton
  I consider "execu, engg, hr" as attributes of the relations, not as attributes of the employees alone, so that stuff goes in the bridge table.I think by adding 'role' to OPs example you have inadvertently complicated the issue and that could make it even harder for OP to understand their example. If 'execu, engg, hr' is an attribute of the relation then 'relation' is an entity and one, two, or even more tables are needed: 1. a table to define the allowable 'global' roles for roles that are NOT specific to a department (e.g. assistant)2. a table to define the allowable 'dept-specific' roles for roles specific to a dept (e.g. 'lathe operator' may ONLY be allowed for a manufacturing dept) Then rather than having emp_dept_role and dept_id in the 'intersect' table you posted you would have the primary key of new table #2 above. That model also allows, if needed, for an employee to have two different roles withing a dept - perhaps because they work on multiple projects.
Hi Rp, If you do not mind , can you simply make diagram (table structure) to understand  relationship between tables.Which satisfied 1NF, 2NF, & 3NF. I know 'n' number of links available.Already i gone through many more links. but did NOT satisfied.i am NOt getting subject exactly to apply normalization without duplicate records.
Hi, Sorry, I don't understand what you're sying.955912 wrote:...i am NOt getting subject exactly to apply normalization without duplicate records.What is a "record"?  Do you mean row?None of the suggestions given above involve duplicate rows. The values of foreign keys are repeated.  That is, dept_id is the primary key in the dept table, nd dept_id is a foreign key in the "bridge" table linking employees and departments, so you cn have the value 10 stored in the dept_id column of both tables.  That's normal; it's nothing to worry about.  That's how foreign keys work.  The foreign key in the child table is always a value that's already stored n the parent table. If you're trying to implement any of the suggestions above, and you have duplicate rows, then you didn't understand something.  To tell what you're doing wrong, we need to know what you're doing.  Post your CREATE TABLE and INSERT statements, and point out where you have duplicate rows.  Say which idea you're trying to impement (e.g/ "I'm trying to do what Stew said in reply #2.")
There are MANY sources on the web for descriptions and diagrams of normalization. Here is just onehttps://en.wikipedia.org/wiki/Database_normalization

Related Links

update taking more time - MERGE
Minus Function not working for date
List of date values in the current month, previous month, and next month
issue in long running procedure
To use Autonomous within procedure
XMLEXISTS Question
Run unix command from pl/sql
query help.
Issue while Updating CLOB column in table
Parsing an xml and storing the details into hierarchical tables
Change time in column values
How to know what programs\events executed on particular time stamp?
Mutating error
Log exact error line
table creation
Query Mapping Count

Categories

ORACLE DEV
Oracle Community (OTN)
WebCenter Content
Replication
Oracle Applications
JHeadstart
Performance & Availabi...
Database Security
Advanced Queueing
Business Intelligence ...
OLAP
User Groups
Human Capital Manageme...
Applications Desktop I...
EBS on Linux
Email Server
Rules Manager & Expres...
Berkeley DB XML
Applications (Korean)
E-Business Suite (Korean)
Oracle Clusterware
Oracle Providers for A...
Business Intelligence ...
Business Intelligence ...
Retail
Performance Management...
General CRM
CRM On Demand Administ...
Logistics
SOA Integration
Application Integratio...
WebLogic Server - Secu...
MessageQ
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
AquaLogic Service Bus ...
AquaLogic BPM Newsgroups
Primavera Portfolio Ma...
技术专区
BEA WebLogic Portal (C...
程序人生
Contact On Demand
Java HotSpot Virtual M...
Servers
Serialization [ARCHIVE]
Java Speech [ARCHIVE]
Java Runtime Environme...
Java Secure Socket Ext...
Java 3D
Java Plug-In
ActiveX Bridge for Jav...
Signed Applets
Java Technology & XML
General Java Mobile Qu...
Solaris Networking
Systems Networking
Systems Maintenance
Sun Java Studio Enterp...
Database Firewall
General Java Embedded ...
Oracle Commerce
Architecture - General...
About the Community
Oracle Fusion Middlewa...
Topliners
Oracle Marketing Cloud...
Seed Your Success
Road to Revenue Tour
Non-profits and Member...
Eloqua-Netsuite Integr...
ZFS Storage Appliance
JDBC & UCP
Eloqua Brasil
Darios Sonera group
Oracle Eloqua Training...
Java Community Process
...
Process Manufacturing
POUG
Manufacturing Intellig...
Coming Soon - Oracle A...
SouJava Campinas JUG
OU Guided Boot Camps
Solver
Oracle Cloud Developer...
OTN Tour Latin America...
OTN Women in Technology
Java Microservices
JUG Africa Social Group
Modern Marketing Exper...
Oracle Database Develo...
Application Server
Data Mining
Financial Consolidation
Identity Services
Primavera Risk Analysi...
Primavera Risk Analysis
Tuxedo (Chinese)
Storage General Discus...
Do It
Test Partner Industry ...
Oracle Financials Clou...

Resources

Encrypt Message



code
soft
python
ios
c
html
jquery
cloud
mobile