DATABASE CONCEPTS

1. DATABASE CONCEPTS Definition:- • Database:- A database is a collection of interrelated data stored together to serve multiple applications; • Database Management System (DBMS):-Database management system is basically record computer based record keeping system. • Advantage of DBMS:- Database systems help:- 1. ReduceData Redundancy, 2. Controlled Data Inconsistency, 3. Facilitate To Sharing Of Data, 4. Standardization Of Data, 5. Data Security. 6. Integrated Data.

2. Database Security:- • Data Security refers to protection of data against accidental or intentional discloser to unauthorized persons, or unauthorized modification or destruction. Database Privacy:- • Privacy of data refers to the rights of individuals and organizations to determine for themselves when, how, and to what extent information about them is to be transmitted to others.

3. DATA ABSTRACTION Definition:- A good database system ensures easy, smooth and efficient data structures in such a way so that every type of database user: End users (Computer untrained users, at view level ), Application system (at logical level), Storage system analyst (at internal level or physical implementation ), is able to access its desired information efficiently

4. Data Abstraction Type (Various levels of database Implementation ) There are three levels of data abstraction:- • Internal Level (Physical level) • Conceptual level • External level (View level)

5. Internal level (Physical Level):- The lowest level of data abstraction, the internal level, is the one closet to physical storage. It describes:- • How the data are actually stored in storage device or medium? • What will be the storage technique? • What will be the starting address of the database? • Conceptual Level:- It is next higher level of internal level. In this level data abstraction describe:- • What data are actually stored in the database? • What all the constitute the database? • What are the relationships between the data entities? • External level (View Level):- This is the level closet to the users and is concerned with the way in which the data are viewed by individual users . It describes:- • What is the way of viewing information to the concerned user?

6. External Level Conceptual Level Physical Level Three levels of Data Abstraction Diagrams View 1 View 3 . . . . View --- n View 2 External Level Conceptual Level Internal Level

7. Teacher School Principal External level (Individual User Views) View 1 School_record View 2 Student_detail Conceptual Roll_No Number(3) Not Null Name Char(10) Class Number(3) Grade Char(2) Address Vachar2(10) Internal Student_record Length=40 Roll_No Type= Byte(6), offset=0,index=Ix Name Type= Byte(10), offset=0 Class Type= Byte(6), offset=0 Grade Type= Byte(2), offset=0 Address Type= Byte(10), offset=0 Fig. Illustration of various level with example (Application programs are used to fetch the desired information)

8. Data Independence The ability to modify a scheme definition in one level without affecting a scheme definition in the next higher level is called Data Independence. There are two levels of data independence: • Physical Data Independence:- It is refers to ability to modify the scheme followed at the physical level without affecting the scheme followed by the conceptual levels. • Logical Data Independence:- It is refers to ability to modify the conceptual scheme without causing any changes in the scheme followed at the view levels. Note:- • It is more difficult to achieve logical data independence rather than the physical level data independence. • The abstract data types in modern programming language implement concept of data independence to large extent.

9. DATA MODELSModel refers to the representation way of data or information in the database management system. The three data models that are used for database management are:- • Relational data model. • Hierarchical data model. • Network data model.

10. Relational data model • Relational data model:-The relational represents data and relationships among data by a collection of tables known as relations • Network data model:-Network Data model is represented by collection of records and relationships among data are represented by link. • Hierarchical data model:-The hierarchical model is similar to the network model in the sense that data and relationships among data are represented by records and links respectively. It is differs from the network model in that the records are organized as collections of trees rather than arbitrary graphs.

11. Relational Database Terminology • The relational model was propounded by E.F. Codd of IBM and has since been acknowledged as a very important concept in DBMS technology. • The relational model has established itself as the primary data model for commercial data processing application. • Relation:- A Relation is a table i.e. data arranged in rows and columns. • Domain:- A Domain is pool of values from which the actual values appearing in given column are drawn. Exp. Roll_No# , Student_Name etc. • Tuple:-The rows of tables (relations) are generally referred to as Tuple. • Attributes:-The columns of tables (relations ) are generally referred to as attributes. • Degree:- The number of columns (attributes ) in a relation determine the degree of a relation. • Cardinality:- The number of tuples (rows) in a relation is called the cardinality of the relation

12. Database KeysIt is important to be able to specify how rows in a relation are distinguished conceptually, rows are distinct from one another. Database perform following keys:- • Primary Key • Candidate Key • Alternate Key • Foreign Key • Referential Integrity

13. Definition of Keys • Primary Key:-A primary key is a set of one or more attributes that can uniquely identify tuples (rows) within the relation/table. For example, Roll_no# is a primary key of “student_rec” table. • Candidate Key:- All attributes combines inside a relation that can serve as a primary key are Candidate Keys as they are candidates for the primary key position. • Alternative Key:- A candidate key that is not the primary key is called an alternative key. For example, student_name is a alternative key in “student_rec” table. • Foreign Key:- A non key attribute, whose values are derived from the primary key of some other table, is known as Foreign Key in its current table. • Referential Integrity:-Referential Integrity is a system of rules that a DBMS uses to ensure that relationships between records in related tables are valid, and that users don’t accidentally delete or change related data.

14. View:-View is a (virtual) table that does not really exist in its own right but it is instead derived from one or more underlying base table(s).Views are like windows through which you view desired information that is actually stored in a base table. Normalization:- Normalization is the process of transformation of the conceptual schema (logical data structures) of the database into a computer representable from. In other words, the normalization process helps in attaining good database design thereby avoiding undesirable things like repetition of information, inability to represent information, loss of information etc.

15. Type of Normalization • First Normalization:- A relation R is in first normal form(1NF) if and only if all underlying domains of the relation contain atomic (indivisible ) values. • I-NF perform following:- • Removing all repeating groups form the relation. • Decompose non-atomic attributes to atomic attributes. • All key attributes defined and all attributes depends on primary key. Fig.—

16. Second Normal Form (2NF):-A relation R is in Second Normal Form(2NF) if and only if it is 1NF and every non key attribute is fully dependent on the primary key. 2NF perform following:- • In I-NF. and Includes no partial dependencies. • Still possible to exhibit transitive dependencies. Fig.—

17. Third Normal Form (3NF):-A relation R is in Third Normal Form(3NF) if and only if it is 2NF and every non key attribute is non transitively dependent upon the primary key. 2NF perform following:- • In 2-NF and Contains no transitive dependencies

18. Boyce - Codd Normal Form (BCNF):-A relation R is in Boyce - Codd Normal Form(2NF) if and only if it is 3NF and all of its determinants are candidate keys . • BCNF perform following:- • In 3NF. • Every determinant in the table is a candidate key.

19. Advantage of Normalization • It reduces data redundancies. • It help eliminate data anomalies. • It produces controlled redundancies to link tables. Need of Normalization:- Normalization is needs for :- • Most databases to grow by adding new attributes and new relations. • For improving a efficiency of database. • Minimizing the need for rewriting the application programs (Front end)

20. Front End and Back End:- • Front End:- A front end refers to the client side end i.e. the end at which request is made. Some popular front end software are:- • Visual Basic (VB) • ASP (Active Server Page) • Visual C++ • Power builder • MS-Access • Back End:-A Back End refers to the server side, where the client requests are processed. Some popular back end software are:- • SQL Server. • Oracle • Sybase • My SQL etc.

22. Oracle Many DBMSs available in the market that are capable to storing and maintaining the database of an enterprise. But Oracle is one of the most popular DBMS’ s that is being used by millions of organizations across the world. Oracle falls under the category of Relational Database Management System (RDBMS). History of Oracle:- The RDBMS Oracle was developed by a company called Relational Software Incorporation (RSI), which was formed in year 1977 by Larry Ellison.

23. Version of Oracle

24. Features of Oracle Some key features of Oracle are:- • Client/Server (Distributed processing) environment. • Large databases and space management. • High availability • Control availability • Manageable security • Portability • Convertibility • Many concurrent database users • Openness, Industry Standard

25. The Oracle Server • The Oracle Server is an object-relational database management system (ORDBMS) that provides an open, comprehensive and integrated approach to information management. Oracle server show in fig.:- Oracle Sever Consists of :- • An Oracle database and • An Oracle instance • Oracle database:-The term database is used to refer to the physical storage of information. The database is stored on the disks attached to the server. • Oracle Instance:-The term instance refers to the software executing on the server that provides access to information stored in the database. The instance runs on the computer or server.

27. Oracle Instance • Every time a database is started, a” System Global Area (SGA)” is allotted and Oracle background processes are started. Therefore, Oracle instance has two types of processes:- • User Processes:- it executes the code of an application program or an Oracle Tool.. • Oracle Processes:- its are server process that perform work for the user process and background processes (oracle background processes: RECO, PMON, SMON, DBWR, LGWR, ARCH ). Fig.

29. SGA SGA:- System Global Area (SGA) is an area of memory used for database information shared by the database users. SGA consists of three major memory structures :- • Database buffer cache • Shared Pool • Redo Log buffer. Background Processes for Oracle Instance, are:- • Database Writer (DBWR) • Log Writer (LGWR) • System Monitor (SMON) • Process Monitor (PMON) • Archiver (ARCH) • Recoverer (RECO).

30. Client/Server Architecture • Client /Server computing architectures generally has a client tier and a server tier, but there can be more tiers also. • Client/Server model classified into two type:- • Two-Tier Computing Model:- • Three-Tire Computing Model

31. Two-Tier Computing Model

32. Three-Tire Computing Model

33. Some important Definitions • Data Dictionary:- The result of compilation of DDL statements is a set of tables which are stored in a special file called data dictionary or directory. A Data Dictionary is a file that contains “Metadata”. • Metadata:- Data about data called Metadata. • Client:- Client is a end user which request to central control unit (server) and get desired information from the server. • Fat Client:- The client application, which is itself responsible for its processing power and application logic. • Thin Client:- The Client application that does not have much of processing power. It only provides a user interface. • Table Space:- A logical storage unit that comprises of one or more data files. • Transaction:- A Transaction is one complete unit of work.

34. SQL* Plus:-It is a simplest tool for accessing a Oracle Database. It provide a shell (Command-Line Interpreter) for Oracle (SQL relational query language and programming language PL/SQL). Designer/2000. Developer/2000 etc. In the following fig. describe other tools which is helpful for accessing of Oracle Database. Accessing Oracle DatabaseOracle provide a wide range of tools for accessing database:- Fig. The Oracle Tools

35. SQL( Structured Query Language) • First Commercial SQL was released in 1979 by Relational Software Incorporation (RSI) which is today known as Oracle Corporation. Thus, Oracle is the pioneer RDBMS that started using SQL. • Structured Query Language (SQL) is a language that enables you to create and operate on relational databases. • The Original version of SQL was developed by IBM’s San Jose Research Laboratory (Now Almanden Research Center ). SQL, originally called “Sequel” was implemented as a part of System R Project in early 1970s. The “Sequel” name changed to SQL. • In 1986, the American National Standards Institute (ANSI) published an SQL standard that was updated again in 1992. • SQL has clearly established itself as the standard relational database language. • SQL is the set of commands that is recognized by nearly all RDBMSs.

37. Oracle ElementsThe Basic elements of oracle are:- 1. Schema objects 2. Literals 3. Data types 3. Comments 1.Schema objects:- A schema refers to the collection of objects or collection of logical structures of data . Schema Objects are the logical structures that directly refer to the database’s data. Some Schemas Objects are:- 1. Tables. 2. Views 3. Clusters 4. Stored Functions 5. Stored Procedures 6. Packages 7. Triggers 8. Indexes

38. Data Type • VARCHAR2(size) • NUMBER (p, s) • INTEGER (size) • DATE • CHAR (size) • BOOLEAN • LONG • LOB

39. SQL Structural query Language It is classified into five types:- • DDL(Data Definition Language ):- Create , Alter, Drop • DML(Data Manipulation Language): Insert , delete, update • DCL(Data Control Language):-Grant, Revoke • TCL(Transaction Control Language):- Commit, Rollback, Savepoint • DRL(Data Retrieval Language):- Select

40. Joins A join lets us you extract columns from more than one tables. A join is a query that combines rows from two or more tables. A join can combine views also. Note:- In unrestricted join or Cartesian product of two tables, all possible concatenations are formed of all rows of both the tables. Example. Display details of employee like department name and employee no, employee name from emp and dept table along with. SQL>SELECT DNAME, ENAME, EMPNO FROM EMP, DEPT; Using Table Aliases :-A table Alias is a temporary label given along with table name in FROM clause. Example. Display details of employee like department name, department no, employee no, employee name from emp and dept table along with.

41. Type of JoinsSQL support following type of Joins - • Equi Joins:- The Join , in which columns are compared for equality, is called Equi-Join. For Example:- Display details like department number, department name, employee name, employee number , job and salary . And ORDER the rows by employee number with department number SQL> SELECT D.DEPTNO, DNAME, E.DEPTNO,ENAME, EMPNO,JOB,SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY E.DEPTNO,EMPNO; • Non-Equi Joins:- A Non-Equi join is a query that specifies some relationship other than equality between columns. Example: SQL> SELECT EMPNO, ENAME,JOB,SAL, DNAME FROM EMP,DEPT WHERE JOB='MANAGER‘;

42. Natural Join:- The Join in which only one of the identical columns (combining form joined tables) exists, is called Natural Join. For Example:- Display details like department number, department name, employee name, employee number , job and salary with only one identical column . And ORDER the rows by employee number with department number SQL> SELECT E.DEPTNO, DNAME,ENAME, EMPNO,JOB,SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY E.DEPTNO,EMPNO; • Self Join:- A table joined with itself is called Self Join. • SQL>SELECT E1.*, E2.EMPNO, E2.JOB,E2.DEPTNO WHERE E1.EMP<>E2.EMP