IT Officer-2

8.  3 SCHEMAS DBMS ARCHITECTURE:

EXPLANATION:   THE INTERNAL SCHEMA IS BASICALLY DERIVED FROM ACTUAL PHYSICAL STORAGE OF DATA BASE ... WHILE CONCEPTUAL SCHEMA IS DERIVED FROM INTERNAL SCHEMA AND THEN CONCEPTUAL SCHEMA DERIVES EXTERNAL VIEW FOR THE USERS.

9.  DBA :  A person having who has central control over data and programs that access the data is called DBA. ( DATA BASE ADMINISTRATOR )

10. DATABASE MODELS:

EG:  HIERARCHICAL :

AN EXAMPLE IS SHOWED ( LIKE TREES FORM )

NOTE :   THERE ARE MANY OTHER DATA BASE MODELS LIKE : TABULAR DATA BASE MODELS ... NETWORK DATA BASE MODELS ETC.

11.  Entity :   An entity is an object

Eg: Employee

12.  Attribute :  Each entity has attributes—the particular properties that describe it.

For example, an employee entity may be described by the employee’s name

13.  Simple attribute : Those are not separable ...

Eg: First name ...

14.  Composite attribute : Those are separable ...

Eg: Name ...

15. Singled Valued Attribute :  Attributes which have only one value for a entity are called single valued attributes.

Eg: Your unique id ...

16.  Multi - valued Attribute : Having more than one value ...

Eg: Phone number ...

17.  Stored attribute :  The attribute from which another attribute value is derived is called stored attribute.

Eg: Person’s birth date is stored attribute while age attribute can be derived from birth date attribute

18.  Super – Key :  An attribute or a combination of attribute that is used to identify the records uniquely is known as Super Key. A table can have many Super Keys.

Eg: Combination of name and address ...

19.  Candidate Key : It can be defined as minimal Super Key ...

Eg:  Combination of name and address ...

( Note : Separately ... Name and address can’t be defined as unique key )

20.  Primary Key :  A Candidate Key that is used by the database designer for unique identification of each row in a table .

Eg : Your Unique-id number

21. Foreign Key :  A foreign key is an attribute or combination of attribute in one base table that points to the candidate key (generally it is the primary key) of another table.

Eg: Here your unique – id can be used as foreign – key in another table named “ Scores in GK tests ”

22. Composite Key : If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key).

If we have used “Name, Address” as a Primary Key then it will be our Composite Key.

23.  RELATION : Let’s check one ER diagram ...

 ( Note : E-R diagram i.e. Entity Relation diagram represents description of a database )

Example: 

 Customer ... Loan .. are entities

Customer id ...customer name ... are attributes of the entity “ Customer “ ...

Borrower is Relation between entities customer and loan

24.  Weak Entity : An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set.

25.  SQL :  STRUCTURED  QUERY   LANGUAGE (It’s set of commands used by data base management system. )

26.  DDL : DATA DEFINITION LANGUAGE

These types of SQL commands operate on tables rather than directly on data.

 EG: CREATE ...ALTER ... DROP ...

27.  DML : DATA MANIPULATION LANGUAGE

These types of SQL commands operate on data rather than directly on whole table.

EG : INSERT ...DELETE ... UPDATE ...

28. Select query :

Select-From-Where Statements :

EG: select * from students where uid > 50

(Here ... Records of all students will be appeared having uid  > 50)

29.  Embedded SQL :

 Imagine ... We mix SQL in java ... Then we can say that SQL is embedded.

30.  Triggers :  The concept of a trigger is relatively straightforward. For any event that causes a change in the contents of a table, a user can specify an associated action that the DBMS should carry out.

The three events that can trigger an action are attempts to INSERT, DELETE, or UPDATE rows of the table.

31.  Normalization: Database normalization is a technique by which an existing schema is modified to minimize redundancy and dependency data.

It splits a large table into smaller tables and define relationships between them to increase the clarify in organizing data.

The words normalisation and normal refers to the structure of database.

Normalisation was developed by IBM researcher E.F. Codd in in 1970s.

Normalization of a database is achieved by following a  set of rules called ‘forms’ in creating the database.

32.  First Normal Form(1NF) : Each column is unique in 1^st normal form.

33.  2^nd Normal Form (2NF): The entity should be considered already in 1NF and all attributes within the entity should depend solely on the unique identifier of the entity.

34.  3^rd Normal Form (3NF): The entity should be considered already in 2NF and no column entry should be dependent on any other entry (value) other than the key for the table.

If such an entity exists, move it outside into a new table.

3NF is achieved are considered as the database is normalised.

35.  BCNF: Boyce & Codd Normal form:

Tables should be in 3NF and all tables in the database should have only one primary key.

36.  4NF:  Tables cannot have multi – valued dependencies on a primary key.

37.  5NF:  Composite key should not have any cyclic dependencies.