© DBMS stands for Database Management System. * Database Management System (DBMS) is a software that is used to define, create and maintain a clatabase and provides controlled access to the data. * A database management system (DBMS) refers to the technology for creating and managing databases. DBMS is a software tool to organize (create, retrieve, update, and manage) data in a database, © The main aimof a DBMS is both convenient and effi ‘Why use DBMS? + To develop software applications In less time. © Data independence an efficient use of data, «© For dataintegrity and security. © For concurrent access to data, and data recovery from crashes, © Touse user-friendly declarative query language. © RDBMS stands for Relational Database Management System. ¢ Relational Database Management System (RDBMS) is an advanced version ofa DBMS. © All modern database management systems like SQL, MS SQL Server, ORACLE, My-SQL and Microsoft Access are based on RDBMS. © It is called Relational Data Base Management System (RDBMS) because it relational model introduced by E.F. Codd. © Datais represented in terms of tuples (rows) in RDBMS. © Relational database is most commonly used database. It contains number of tables and each table has its own primary key. * RDBMS modern DBMS not only manages the data but provides facilities like : Security, Integrity, User Creation and Object Management, Making data available world wide using networks ete. to supply a way to store up and retrie ve database information that nt s based on No. DBMS, RDBMS 1 DBMS stands for Database | RDBMS stands for Relational Database Management System. Management System. 2 — [DBMS applications store data asfile [RDBMS applications store data in a tabular form. 3 | In DBMS, data is generally stored in | In RDBMS, the tables have an identifier called cither a hierarchical form or a | primary key and the data values are stored in the form of tab not present in | Normalization is present in RDBMS. DBMS. 5 |DBMS doesnot apply any” | RDBMS defines the integrity constraint for the security with regards to data vi purpose of ACID (Atomocity. Consistency. manipulation. Isolation and Durability) property. 6 | DBMS uses file system to store data, so | In RDBMS, data values are stored in the form there will be no relation between the | of tables. so arelationship between these data tables. values will be stored in the form of a table as well. 7 ~~ [DBMS is meant to be for small | RDBMS is designed to handle large amount organization and deal with small data it | of data. it supports multiple users. supports single user. [Leer ][D] Data Retrieval: © The user query is accepted by the client, for processing. + After processing, server sends required information tuck to the user through client. «The datais stored in scheduled/specific areas. «Security is maintained by the Server and only *Authorized” weer can access the data. © Thecore part is data, which is stored on the secondary storage device. © Oracle server with the help of OS manages the data. © The datais stored as physical files on the disk. © Oracle does not allow user to access physical files directly. © [cforms layer between user and data. © Server executes Oracle Software and manages database. . validated and passed it to the server Oracle server also takes care of Various issues like concurrency, security and integrity. Structure Query Language (SQL) can be used to access data from the server, Procedural Language SQL (PL/SQL) can be used for programming and programs can be stored on the server. «User can interact with Oracle using SQL and PL/SQL only. DL EE Codd Rules Dr. E. F. Coddis an IBM researcher who first develaped the data mxk! in 1970. © In 1985 he published a list of 12 rules that define an ideal relational database and has provished a guideline for the design of all relational database systems. ‘The rules are : 1, The Information Rule: « Alldata should be presented in table form. 2 Guaranteed access Rul «All data should be accessible without ambiguity. This can be accomplished through acombination of the table name, primary key, and column name. 3, Systematic Treatment of Null Values «A field should be allowed to remain empty (Null value). Which is distinct from an empty stringor a number with a value of zero. This can’t be applied to primary keys 4. Dynamic On-Line Catalog based on the Relational Model: + Arrelational database must provide access to its structure through the same tools thar are used to access the data, 5. Comprehensive Data Sublanguage Rule: «The database must support at least one clearly defined language that inclules functionality for data definition, data manipulation, data integrity, and database transaction control. 6. View Updating Rule: © Data can be presented in different logical combinations called views. Each should support the same full range of data manipulation that has direct aovess to a table available. 7. Wigh-Level Insert, Update and Delete: ‘+ This rule states that Insert, Update and Delete operations should be supported for any retrievable sct rather than just fora single row in a single table. 8 Physical Data Independence: «The user js isolated from the physical method of storing and retrieving information from the database. 9. Logical data Independence: ® How data is viewed should not be changed when the logical structure (table structure) of the database changes.cea Sub: RDBMS using Oracle 10, Integrity Independence: «The database language like SQL should support const maintain database integrity. 11. Distribution Independence: ff whether or not the database « Avuser should be totally unaware o! (whether parts of the database existin multiple locations). 12, Non-Spbversion Rule: a oievre should be no way 10 modify the database structure other than through the multiple row database language like SQL. raints on user input that distributed Instruction of Diagr: ‘© ER Diagram stands for Entity Relations! ram, also known as ERD agrams are created based on three basic concepts: 1. Enities, 2. Attributes 3. Relationships © ERD is a diagram that displays the rel Entity Relationship Diagram Symbol which are rectangle, oval and diamond to represent rel and attributes. Jationship of entity sets stored in a database. Is & Notations mainly contains three basic symbols lationships between elements, entities STEPS TO GET ER-DIAGRAM. When an E-R diagram is built the first step is defining entities. 4 The next step is to define a relationship between entities. The final step to identify the attributes that belong to each entity. Once the E-R diagramis completed the entities will become the files (or table). «Following are the main components and its symbols in ER Diagrams! A rectangle is represent entity or entity set. 2. Diamond: > ‘A diamond symbol is represent a relationship between two ent os ‘An oval symbol is represent aitributes. 4. A‘1"is represent a single occurrence. is represent multiple occurrence. 6. Line: A line which links attributes to an entity or entity sets to relationship.Person i 2. Qne-to-Many = «A relationship is defined as one-to-many when a single records in the first table points to many records in the second table. © For Example : I. One book title can be written by several authors. IL. Department and employees © One department has one or more employees and an employee belongs to only one cepanment. 3. Many-to-Many: Many relationship exists between two tables if a single records in the first 's to more then one records in the second table and a single record in the ind table points to many records in the first table. © For Example = * Atthe same time one book title can be borrowed by more than one members. I. We have already seen the issue table and member table in this case. one member can issued many books at one instance, I. The relationship between doctor and patients. © A patient can visit many doctors, and a doctor can have many patients. Doctor ==> <=} Patient, Normalization Definition and Meaning: ‘Normalization is one of the most important concepts in the study of RDBMS. The process of organizing data to redundancy (duplication) is called Normalization. Normalization involves dividing a database into two or more tables and defining relationships between the tables. Normalization is important form of the database design. Designer's point of view enables him/her to design better. tis a process that helps database designer to desi gn table structure for an application, With this process, the record in single table is divided in multiple tables having relationship among them based on some key. ‘There are two ways of approaching logical database desi 1 The top-down approach. [Leer]2. The bottom —up ap proach. © The ER diagram technique uses the Top-down approach. «Normalization is the technique that makes the relational data files differ from other data files, * For example, A library maintains aregister of all book issued to its members. + The register contain the following columns: No, Book Name, Mem_Name, Date of Issue, Date of Return. © The other register is contains following columns: Mem_Name, Address and telephone number. «First register give the information about book and second register give the information about member who issue the particular book. «Every time a member failed to return the book so, a letter that member's house for cemindin; © These all information are store in the other register. A list of all members name along with their address and telephone numbers are maintained. * Now every time a member defaults in returning a book, the librarian looks the name of that member in the list and makes areminder call. © In this case we can say that the librarian has a relational database on paper. sent or telephone call is made to © Thisis called a bottom-up approach and that data is called normalized data. Different Normalization Forms * Dr. Codd originally defines three levels of normalization. These three levels were called First normal form, Second normal form and Third normal form respectively. * This three forms were defined by [Link]. Lanter [Link] and Boyce introduced one more normal form, which called the Boyce —Cod normal form. Now show details of form: 1, INF :Eliminate Repeating Groups: * Make a separate table for each set of related attributes, and give each table a primary key. © Each field contains at the most one value from its attribute domain. 2. INF :Eliminate Redundant Data: Fan attribute depends on only part of a multi-valued key, remove ble it to a separate 3. SNF ; Eliminate Columns Not Dependent On Key: If attributes do not contribute to a description of the key, remove them to a separate table. © All attributes must be directly dependent on the primary key. 4. BCNF :Boyce- Codd Normal Form: * If there are non trivial dependen attributes, separate them out into di Introduction to SOL * Structure Query Language -SQL is known as the language of database. SQL (Structured Query Language) is a language that provides an interface ta relational database system, * IBM developed SQL in the 1970s for use in data base system. SQL is often pronounced as SEQUEL. © SQL is a non-procedural language. SQL is set oriented means that SQL processes set of data (control by another) between candidate key cttables. [Leeceore]in groups. SQL has been a command language for communication with the oracle 9i server from any tools or application. When SQL statement is entered it is stored in a part of memory called SQL buffer & remains there until a new SQL statement is entered. Features of SOL: ‘SQL is English like language and it has simple command set which is easy toremember. Itis a:non procedural language i.e. no sequence, no iteration, no selection. It reduces the amount of time required for creating and maintaining systems. Use of SOL: SQL used for creating, insert, update and delete operation and also for creating and modifying tables & other database structures. SQL can be used by a range of users, including those with little or no programming experience, Manipulating data stored inside the tables. Querying data stored in the tables. Controlling access to database objects. Ensuring database consistency. SOL Commands and Data types, SOL Commands (Component) © SQL has amany command and all command has a different categories. * The categories of SQL command is DDL, DML, DCL, DQL. © Now we can show this category in detail:: 1, DDL (Data Definition Language): © tis a set of SQL commands used to create, modify and delete database structures but not data, © These commands are normally not used by a general user, who should be accessing the database via an application. © They are normally used by the DBA to a limited extent, a database designer or application developer. * Examples of DOL (CREATE:- To create objects in the database. ALTER:- Alters the structure of the database. DROP:- Delete objects from the database. TRUNCATE:- Remove all records froma table, including all spaces allocated for the records are removed. - Add conmenis to a data dictionary. user's access privilege to database 2+ Withdraw access privilege given with the Grant command, bere nan 2, DML (Data Manipulation Language): * Through this commands SQL allows to change database, This command is used for manipulation of data. © Examples of DML ISERT:- Insert data into a table. Upulate existing data within a ble. Deletes all records from a table, the space for the records remai [Lees ora]Sub: RDBMS using Oracle Chapter-1 B,C.A.Sem3 4. CALL:- Calla PL/SQL or Java subprogram. 5. EXPLAIN PLAN:- Explain access path to data. 6. LOCK:- Table control concurrency. 3, DCL Mata Control Language): ‘Through this commands SQL statement control access to data and to the database. Occasionally DCL commands are grouped with DML. Examples of DCL is:: |. COMMIT:- Save work done. 3+ Identify a point in a transaction to which you can later roll back. + Restore database to original since the last COMMIT. SACTION:- Change transaction option like what rollback segment to use. GRANT/REVOK! from the oracle user 1, 2 i 3, ROLLBAC! 4, - Grant gives permission or tke back permission to or 4. DQL (Data Query Language): It is the component of SQL statement that allows getting data from the database In include SELECT statement. This command is the heart of SQL. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program i.e. a front-end. Examples of DQL is 1. SELECT:- Retrieve data from a database. Datatypes of SOL © Data types come in several forms and sizes, allowing the programmer to create tables suited to the scope of the project. ® SQLhas 6 data types. 1 2 GIAR (size): This data type is used to store character strings values of fixed length, ‘The size in brackets determines the number of characters the cell can hold, The maximum number of characters (size) in this data type is 255 characters. ‘The data heldis right padded with spaces to whatever length specified. VARCITAR (sizey/V ARCHAR2 (size): © This data type is used to store variable length in alphanumeric data © It ismore flexible from of the CHAR data type. © The maximum number of characters can hold up to 4000 byte of characters. © VARCHER values using non-padded values. Le, Interested values will not be padded with spaces, It also represents data type of string. CEIAR data type can hold 1 10255 characters, DATE: © This data type is used to represent date and time. The standard format is DD-MON-YY as in 21-JUN-11. © Toenter dates other than the standard format, use the appropriate functions. © Similarly, time stores in the 24-hour format. By default, the time is. 12:00:00 am, if no times specified. The default date is first day of the current month is set. NUMBER ¢P, §):ISQL is based on American National JStandards Institute (ANSI) standard SQL. SQL manipalates data and table |dcfinitions in the database. SQL is entered into the SQL buffer on jane or more lines. [SQL does not have a continuation |character. fr cannot be abbreviated (shortened/eut). {SQL uses functions to perform some formatting. SQL * Plus SQL * Plus is the Oracle proprietary jinterface for executing SQL statements, ISQL * Plus does not allow n f values in the database. QL * Plus is entered one line at a time, lnot stored in the SQL buffer. SQL * Plus uses a dash (-)as a -ontinuation character if the command is longer than one line. Ir can be abbreviated. ISQL * Plus uses commands to format tata.