Database basic

As I had a job interview position called 'data admin', I reviewed what I had studied from Univ.

Entity Relationship Modelling
: is a set of diagrammatic standards for depicting both the logical and physical structure
of a database system
P.Chen 1976 first proposed E-R models

E-R Modelling symbols
: Entities, Relationships, Attributes

Entities
:an entity is a thing, which is recognisable as being capabl of an independent existence,
and may be uniquely identified, this definition allows for both physical objects(person, car, house)
and events (a meetings, loans).
- the names of entities must be in the singular as it refers to a single instance (in relationship terms)

Relationships
: a specific line between two entities and indicates that the primary key of one entity is a foreign key in the other
One to One
: signifies that for each instance of entity A there is one related instance of entity B.
it is not often used or seen in full-scale relationship databases

One to Many (1:M)
: signifies that for each instance of entity A there is one or more related instances of entity B.
the primary key of the 1 entity of the relation is present as a foreign key in the M entity,
However the M entities primary key is NOT a foreign key in the 1 entity.
the most common relationship used in db modelling.

Many to Many
: signifies that for each instance of A there is one or more related instances of entity B and visa versa. the primary keys of each entity exists as a foreign key in the other. Due to the complexity in actually implementing the logic behind this type of relationship they are routinely modified(resolved) To resolve M:M relationship a new entity is created as an intermediatory between the two existing entities. then the entities both from 1:M relationship withthe intermediatory entity rather that the M:M with each other. this new entity simply inherits the primary key attributes of the two entities.

Optionality
: it is possible to show wether the involvement of a surgeon is optional or mandatory by using the conventions
Mandatory – Optional
operation must be managed by a surgeon, but each surgeon may manage zero or more operations
Optional-Optional
each operation may be managed by a surgeon and each surgeon may manage zero or more operations
Mandatory-Mandatory
each surgeon must have managed at least one operation and each operation must be managed by a surgeon

Exclusivity
: it is possible to show that two or more relationships are mutually exclusive using the Arc operator. By placing the arc through the exclusive relationships indicates that only one of the arced relationships can exist for any given instance of the parent entity an employee may only have casual rates, part time rates or full time rates but not a combination of these

Attributes
: after entities and relationships have been discovered and documented attributes are then added to the model.
1.identify the primary key and foreign keys: usually primary key is along side their associated entity and underlined: Student#
2.non-key attributes are added in order priority or logical after primary key is separated by commas: Student#, Surname, First Name, DOB
3.associating attributes using Oracle CASE tool (places attributes in side the entities) either listing attributes in a data dictionary
4.foreign keys are usually left off for logical model as foreign keys are of a more physical nature. Foreign keys only flow from 1 to many, but never many to 1:
Teacher#, Surname, Title
Student#, Teacher#, Surname, First Name, DOB

SELECT
Data Retrieval
INSERT
DELETE
UPDATE
Data Manipulation Language
(DML)
CREATE
ALTER
DROP
RENAME
TRANCATE

Data Definition Language (DDL)
BEGIN TRANSACTION
COMMIT / ROLLBACK
Transaction Control
GRANT
REVOKE

Data Control Language (DCL)


Oracle
: is the #1 database and has the most advanced feature set.
Oracle is made up of a set of processes running in your operating system.
These processes manage how data is stored and how it accessed.
It is a program that is running in the background, maintaining your data for you and
figuring out where it should go on your hard drive.
Data is almost accessed through SQL, and Oracle is no exception.
SQL allows you to SELECT your data,
INSERT new records,
UPDATE existing records and
DELETE records you want to get rid of.
SQL can be embedded or you can run scripts of SQL directly against the DB
PL/SQL (procedural language extension to SQL) is a programming language like C, Java.
From inside a program is the best way to access your data.
PL/SQL is feature-rich language geared toward developing database applications, can be written in Java.
To learn Oracle ==> http://otn.oracle.com/

Normalization
: is a systematic way of ensuring that a database structure is suitable for general purpose querying and free of certain undesirable characteristics of insertion, update, and deletion anomalies that could lead to a loss of data.
First normal form (1NF) Table faithfully represents a relation and has no repeating groups
Second normal form (2NF) No non-prime attribute in the table is functionally dependent on a part (proper subset) of a candidate key remove dependency
Third normal form (3NF) Every non-prime attribute is non-transitively dependent on every key of the table