DIego's Normalization

Friends ,

Normalization is a way of splitting up data until each table represents propositions about a single type of thing. Normalization is a beloved subject in academic courses on database management systems and in job interviews .


If you tell people that you took a DBMS course and then look blank when they ask you whether your data are in Third Normal Form, you'll never get any respect from database nerds.
Abnormal
Suppose that you're Die Hard fan of Diego Maradona , the Soccer Legend who singly handled 1986 soccer World Cup. You'll want to be well-informed about Diego Maradona’s work of both Domestic and international career . You decide to make a little database to coach you to prove yourself how great you are !






If somebody called u up and asked u some questions to test ur knowledge about Diego Maradona such as "How many club Diego Maradona played for ?" Then u will within fraction by seeing ur database would tell the answer of that question and impressed that Guy .
Suppose that Guy is sufficiently impressed by your knowledge to come back to your place . Taking advantage of the in-room inernet connection, you've left a remote SQL Plus window up and running on your laptop. He sees your SQL queries in the source code for your coaching app and shouts out "Hey, these data aren't even in First Normal Form (1NF)."
If your data aren't in First Normal Form, there isn't even DBMS term for what you've got. Loosely speaking, your data are abnormal.
What is wrong with your data model?
For ARGENTINOS JUNIORS,BOCA JUNIORS,BARCELONA,NAPOLI,NEWELL'S OLD BOYS,SEVILLA

you've got a single name in the Club Level column. For TEAM_CLUB
, you've got a many names. This is known as a repeating group or a multivalued column and it has the following problems:
• you might not have enough space if the number of values in the column grows larger than anticipated
• the combination of table name, column name, and key value no longer specifies a datum
• the basic INSERT, UPDATE, and SELECT operations are not sufficient to manipulate multivalued columns
• programmers' brains will have to adapt simultaneously to unordered data in table rows and ordered data inside a multivalued column
• design opacity. If you use multivalued columns even once, people will never know what to expect when they look under the hood of your design; did you use multiple tables to express a many-to-one relation or multivalued columns?
Do these problems mean that multivalued columns are useless? Probably not. Oracle introduced two kinds of support for multivalued columns with the 8.0 release of their server:
For modelling one-to-many relationships, Oracle supports two collection
datatypes: varrays and nested tables. For example, a purchase order has
an arbitrary number of line items, so you may want to put the line items
into a collection.

Varrays have a maximum number of elements, although you can change
the upper bound. The order of elements is defined. Varrays are stored
as opaque objects (that is, raw or BLOB).

Nested tables can have any number of elements, and you can select,
insert, delete, and so on the same as with regular tables. The order
of the elements is not defined. Nested tables are stored in a storage
table with every element mapping to a row in the storage table.

If you need to loop through the elements in order, store only a fixed
number of items, or retrieve and manipulate the entire collection as a
value, then use varrays.

If you need to run efficient queries on collections, handle arbitrary
numbers of elements, or do mass insert/update/delete operations, then
use nested tables. If the collections are very large and you want to
retrieve only subsets, you can model the collection as a nested table
and retrieve a locator for the result set.

For example, a purchase order object may have a nested table of line
items, while a rectangle object may contain a varray with 4 coordinates.
With the nested tables option, Oracle is simply doing what an RDBMS purist would have told you to do in the first place: use multiple tables to represent many-to-one relations.


Follow are the create statement to create table and establish the required relationship




1) Create table Club
( sr_no number,
Club varchar2(100) CONSTRAINT cb_pk PRIMARY KEY,
Country varchar2(100) NOT NULL
);


2) Create table Club_Details
( Season varchar2(100) NOT NULL,
Team varchar2(100) ,
League varchar2(100),
Apps NUMBER,
Goals NUMBER,
Final_Position varchar2(100),
CONSTRAINT tm_fk FOREIGN KEY(Team ) References Club (club)
)
/
3) Create table International
(
Competition Varchar2(100) CONSTRAINT com_pk PRIMARY KEY,
Apps number(10) NOT NULL,
Goals NUMBER(10)
);
4) Create table International_Tournaments
( Year Varchar2(100),
Competition Varchar2(100),
Location Varchar2(100),
Apps NUMBER NOT NULL,
Goals NUMBER,
Final_Position Varchar2(100) ,
CONSTRAINT inCom_PK FOREIGN KEY (Competition) References International(Competition)
);