Page 1 :
Introduction to RDBMS, Concept of Data:Data is defined as facts or figures, or information that's stored in or used by a computer., An example of data is information collected for a research paper. Data is the name given to, basic facts and entities such as names and numbers. The main examples of data are weights,, prices, costs, numbers of items sold, employee names, product names, addresses etc., Computer data is information processed or stored by a computer. This information may, be in the form of text documents, images, audio clips, software programs, or other types of, data. Computer data may be processed by the computer's CPU and is stored in files and folders, on the computer's hard disk. Computer data is a bunch of ones and zeros, known as binary, data. Because all computer data is in binary format, it can be created, processed, saved, and, stored digitally. This allows data to be transferred from one computer to another using a, network connection or various media devices. It also does not lose quality after being used, multiple times., Information:-Information is the processed, organized and structured data. It provides context, for data. However, both the terms are used together, information can be easily understood than, data., Difference Between Data and Information, Data, , Information, , Data is unorganized raw facts that need, processing without which it is seemingly ., random and useless to humans, , Information is a processed, organized data, presented in a given context and is useful to, humans, , Data is an individual unit that contains raw, material which does not carry any specific, meaning., , Information is a group of data that collectively, carry a logical meaning., , Data doesn’t depend on information., , Information depends on data., , It is just text and numbers., Data cannot be used for decision making., , It is refined data., , No conclusion made on data., Data is not time bound., Data is in Unorganized form., An example of data is a student’s test score, , Information play an important role in decision, making., Information can be used to make conclusion., Information is time bound., Information is in Organized Form., The average score of a class is the information, derived from the given data.
Page 2 :
Concept of database:A database is an organized collection of data, generally stored and accessed, electronically from a computer system. Database can be defined as a collection of, interrelated data of any organization. Every organization has to maintain the record of all its, activities. Databases store data and provide facilities (tools) to search for specific records in, a given set of data. They store special information used to manage the data. Examples of, databases such as citizens use every day include banking systems, computerized medical, records, and online shopping., , Store, , Purchase, , Account, , Productio, n, , Sale, , In Company database store department give report of products with stock, purchase, department cannot purchase as the stock has reach at minimum level, production, department cannot process with production and sale department cannot fulfill the order. If, any of the department fails to produce proper report the whole organization may be in, trouble., Database Management System(DBMS):A system that handles the management of manually or by machine. The database, management system can be define as collection of programs that enable users to create and, maintain the database it is the collection of inter related data & set of programs to access, that data DBMS provides., Defining a database :- Specify the data types structure & constrains for the data to be stared, in database., Constructing a database :-Insertion, updation & deletion or data as per the need of, transaction., Manipulating the database :Querying the database & generating reports.
Page 3 :
Levels of Abstraction:-, , A database is a collection of interrelated data which is used by different people . The, data are stored in database with control redundancy & inconsistency data manipulation, which evolve data retrieval , addition all stored , each one needs only the portion of database, so measure purpose of database system is to provide users with abstract view of data i.e., the system hides certain details how the data is stored & maintained., The users of database remain unaware of the physical implementation & internal, complexity . These is so because of the organization of DBMS . In different layers the, architecture of database system is divided into 3 general levels i.e. physical or internal ,, conceptual or logical & external or view level., 1.Internal level:The internal is the very closed to physical stored i.e. the way how the data is, actually stored in a database it doesn't depend on hardware or operating system., 2.Conceptual level:Conceptual level depend on hardware , operating system , DBMS etc. there will, be many external views used to represent some portion of database but there is only, one conceptual & internal views used to represent the total database . These level, describes what data actually stored in the database & relationship between data a
Page 4 :
system analysis. DBA design the structure of data in terms of records , attributes &, relationship between them ., 1. External level:The external level is close to the end users i.e. the way in which the data is viewed, by individual users of database need only the parts of database , so to simplify their, interaction with the system view level is define . The system may provide many views, for the same database., Architecture of DBMS :The design of a DBMS depends on its architecture. It can be centralized or decentralized or, hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An ntier architecture divides the whole system into related but independent n modules, which, can be independently modified, altered, changed, or replaced., In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS, and uses it. Any changes done here will directly be done on the DBMS itself. It does not, provide handy tools for end-users. Database designers and programmers normally prefer to, use single-tier architecture., If the architecture of DBMS is 2-tier, then it must have an application through which the, DBMS can be accessed. Programmers use 2-tier architecture where they access the DBMS by, means of an application. Here the application tier is entirely independent of the database in, terms of operation, design, and programming., 3-tier Architecture, A 3-tier architecture separates its tiers from each other based on the complexity of the users, and how they use the data present in the database. It is the most widely used architecture, to design a DBMS.
Page 5 :
•, , •, , •, , Database (Data) Tier − At this tier, the database resides along with its query processing, languages. We also have the relations that define the data and their constraints at this, level., Application (Middle) Tier − At this tier reside the application server and the programs, that access the database. For a user, this application tier presents an abstracted view, of the database. End-users are unaware of any existence of the database beyond the, application. At the other end, the database tier is not aware of any other user beyond, the application tier. Hence, the application layer sits in the middle and acts as a, mediator between the end-user and the database., User (Presentation) Tier − End-users operate on this tier and they know nothing about, any existence of the database beyond this layer. At this layer, multiple views of the, database can be provided by the application. All views are generated by applications, that reside in the application tier., , • Database Administer(DBA):The person who has control over both programs & data of the system is called, database administrator., DBA has overall control on database & everything related to the database he is, responsible for administer the resources like database objects in database , use of data, & monitoring the data.
Page 6 :
• Functions or responsibility of DBA :1. Schema definition :The DBA creates the original database by writing set of definitions (create table) which, is translated by DDL compiler (data definition language). In a set of tables that are stored, permanently., 2. Storage structure & accesses Method :DBS creates appropriate storage structure & accesses method by writing set of, definition which is translated by data storage & DDL compiler., 3. Schema & physical modification :DBA completes the modification either to schema or physical storage by writing a set, of definition to generate modification for appropriate internal system., 4. Granting of authorization for data access :The granting of different types regulate which part of database various uses can, accesses., 5. Integrity Constraints specification:The data value stored in database must satisfies some types of constraints., • Data models:A data model is a conceptual tool that can be used to describe the structure of, database. A database model instants of, I. Description of data type's relationship & constraints., II. Set of basic operation for access & update database., III. Specification of a set of valid users to define operation on database., Types of data models :-, , 1. Low level or physical data model., 2. High level or conceptual data model., 1) Low level or physical data model:These model describes a details of how the data is stored in the storage media., These is used to describe data at lowest level which are only for computer expert not, for end users., 2) High level data model:These model provide the concept regarding concept regarding conceptual or view, of data it against divide into, I. Object based logical model., II. Record based logical model., 1. Object based logical data :These is higher level implementation of data model some models are
Page 7 :
a. Entity relationship model., b. Object oriented model., c. Functional data model i.e. DFD, object & objects contains the codes that are called method., 2. Record base logical model:In these model the database is structured in fix format record each record is made up, of fixed no. of fields or attributes & each attribute has fixed length. The three most accepted, models are :I. Relational model., II. Network model., III. Hierarchical model., I. Relational data model:-The relational data model was introduced in 1970 by E.F. codd., The structure of relational data model is based on simple data structure. The relation, is mathematical term which define as a subset of the domains. Domain is a set of, permissible values e.g. marks should be any values between 0 to 100, name should be, any character between A to Z. These set is called domain of attribute., Relation :-A relation or table is collection of the name of relation ,number of attributes, &, set of values., Entity:-The real word object is called entity. e.g. Doctor, Attribute:-Identification features of an entity, Tuple :- A tuple is one row of relation., Degree :-A degree of relation is the no. of attributes present in the relation., I. Doctor (Did , Dname , Dqual), Doctor, Did, Dname, Dqual, 101 Dr.Joshi, M.D, 102 Dr.Kale, M.B.B.S., 103 Dr.Puranik B.H.M.S., Name of relation =Doctor., Entity=doctor., Attributes=Did , Dname , Dpual., Degree of relation= 3., II., , Network data model :In 1971 network data model was introduced by database task group. The, database task group uses two different data structure i.e. entity & relationship, between entities.
Page 8 :
The data is stored in the form of records containing related data values. The, record represent an entity. The record type is a set of records that stored same type, of information it is also called as record type of an entity. A set type is name describe, 1 to many relationship between the record type., The record type at one side of relationship is called owner record type and at, many side it is called member record type., Record type Owner, 1, Set type, Record type, , Member, , M, 1, , Doctor, , Patients, , I., II., III., , M, , The definition consists of 3 basic elements:name of the set type, an owner record type, member record type, The symbols used in data structure diagram are boxes representing record types &, lines representing links between record types., 3) Hierarchical model :The Hierarchical data model storage has been used for long time, in these type of information , data is stored in the form of parent child relationship., The Hierarchical database consist collection of records which are connected to one, another through links. A record is a collection of fields that provide information of an, entity., The parent child relationship is 1 to M between two record. the record type at one, side is called parent record child record type diagrammatically it is represented by, tree structure. Boxes represent link e.g.
Page 9 :
Did, , Pid, , Pname, , Dname, , Pdis, , Date, , 101, , 11, , Mr.Dhane, , Dqual, , Dr.Joshi, , Amount, , M.D, , Fever, , 01/08/2017, , 500, , Relational Algebra :I. Select Operation:The select operation select the tuple i.e. row that satisfies the given, predicate (condition). The lower case Greek letter sigma (σ) is used to represent. The, select operation., The predicate appears as a subscript to sigma (σ) & relation is given in parenthesis, following sigma (σ) predicate can be define using operators = , = , < , > , < , > & they, may connected using connectives OR & AND, Syntax:- σ predicate (Relation), e.g. player (Pid , Tid , Country , Age , Run , Wkt), PID, 1, 2, 3, 4, , TID, 100, 101, 102, 103, , COUNTRY, India, Pakistan, Australia, India, , AGE, 25, 30, 27, 24, , RUN, 7500, 8450, 6550, 5200, , WKT, 3, 4, 5, 3, , Query. Find all Tuples from player relation whose country is India., , σ country = "India " (player), PID, 1, 4, , TID, 100, 103, , COUNTRY, INDIA, INDIA, , AGE, 25, 24, , RUN, 7500, 5200, , WKT, 3, 3
Page 10 :
II., , Project Operation :The Project is unary operator and return certain attribute. It is denoted by, Greek letter pie (𝜋) . The attribute which appear in the result are denoted as subscript to pie, following relation., Syntax :- 𝜋 attribute (relation), Query:- List ages of all the player, , 𝜋age(player), Age, 25, 30, 27, 24, III.Union Operation:Two relations R and S are said to be compatible if they satisfy following conditions:, 1)The relation R and S are of same entity i.e. number of attributes are the same., 2)The domains of attribute of R and S must be same., Union operation is denoted by 'U'., R, Id Name, 1, A, 2, B, 3, C, 4, D, , Id, 1, 2, 5, 6, , S, Name, A, B, P, Q, , RUS, Id Name, 1, A, 2, B, 3, C, 4, D, 5, P, 6, Q, , IV.Set intersection operation:Intersection selects common tuples from two relation and it is denoted by '∩'., R∩S, Id, Name, 1, A, 2, B
Page 11 :
V.Set difference operation:It removes common tuples from the first relation. It is denoted by ' - '., R-S, Id, Name, 3, C, 4, D, 5, P, 6, Q, VI.Cartesian product operation:Cartesian product of two relations is the concatenation of tuples. If the R and S are, two relations then result in new relation contains all possible combinations of tuples. It is, denoted by ' X '., Emp(R), Eno Ename, 1, P, 2, Q, 3, R, , Project(S), Pname, DBMS1, DBMS2, , Eno, 1, 1, 2, 2, 3, 3, , RXS, Ename, P, P, Q, Q, R, R, , Pname, DBMS1, DBMS2, DBMS1, DBMS2, DBMS1, DBMS2, , Relational Database Structure:A relational database consists of a collection of tables, each having a unique name. A, row in a table represents a relationship among a set of values. Thus a table represents a, collection of relationships., The relational database consider the integrity constraints to be followed by data in, the relation are key constraints., SQL term, , Relational database term, , Description, , Row, , Tuple or record, , A data set representing a single item, , Column, , Attribute or field, , A labeled element of a tuple, e.g. "Address" or "Date of birth", , Table, , Relation or Base relvar, , A set of tuples sharing the same attributes; a set of columns and rows, , View or result set Derived relvar, , Any set of tuples; a data report from the RDBMS in response to a query
Page 12 :
Domain, A domain describes the set of possible values for a given attribute, and can be, considered a constraint on the value of the attribute. Mathematically, attaching a domain to, an attribute means that any value for the attribute must be an element of the specified set., The character string "ABC", for instance, is not in the integer domain, but the integer value, 123 is. Another example of domain describes the possible values for the field "Coin Face" as, ("Heads", "Tails"). So, the field "Coin Face" will not accept input values like (0,1) or (H,T)., Constraints, Constraints make it possible to further restrict the domain of an attribute. For instance,, a constraint can restrict a given integer attribute to values between 1 and 10. Constraints, provide one method of implementing business rules in the database and support subsequent, data use within the application layer. SQL implements constraint functionality in the form of, check constraints. Constraints restrict the data that can be stored in relations. These are, usually defined using expressions that result in a Boolean value, indicating whether or not, the data satisfies the constraint. Constraints can apply to single attributes, to a tuple, (restricting combinations of attributes) or to an entire relation. Since every attribute has an, associated domain, there are constraints (domain constraints). The two principal rules for, the relational model are known as entity integrity and referential integrity., Referential integrity is based on the simple concept of relational vector based analytic, algorithms, commonly employed in cloud platforms. This enables multiple interface, processing within the referential database, with the additional feature of adding an, additional security layer over the dynamically defined virtual environment., Primary key, Unique key and Primary key, Each relation/table has a primary key, this being a consequence of a relation being a, set. A primary key uniquely specifies a tuple within a table. While natural attributes, (attributes used to describe the data being entered) are sometimes good primary keys,, surrogate keys are often used instead. A surrogate key is an artificial attribute assigned to an, object which uniquely identifies it (for instance, in a table of information about students at a, school they might all be assigned a student ID in order to differentiate them). The surrogate, key has no intrinsic (inherent) meaning, but rather is useful through its ability to uniquely, identify a tuple. Another common occurrence, especially in regard to N:M cardinality is the, composite key. A composite key is a key made up of two or more attributes within a table, that (together) uniquely identify a record., [16]
Page 13 :
Foreign key, A foreign key is a field in a relational table that matches the primary key column of, another table. It relates the two keys. Foreign keys need not have unique values in the, referencing relation. A foreign key can be used to cross-reference tables, and it effectively, uses the values of attributes in the referenced relation to restrict the domain of one or more, attributes in the referencing relation. The concept is described formally as: "For all tuples in, the referencing relation projected over the referencing attributes, there must exist a tuple in, the referenced relation projected over those same attributes such that the values in each of, the referencing attributes match the corresponding values in the referenced attributes.", Super key, The set of attribute that can uniquely identify each entity in the entities set is called, super key., Composite key, When a key is form of more than one attribute of a relation then it is called composite, key., Entity set:Entity is a any real world object. The set of all entities in a particular organization can be, defined as entity set., E.g. The set of all employees in a particular organization can be defined as the entity set of, employees. There are two types of entity set1)Weak entity set:When entity set does not have sufficient attributes to form a primary key then these, entity set is called weak entity set and entities in these ser are called as weak entities., 2)Strong entity set:The entity set that have a primary keys are called strong entity set.
Page 14 :
Database Language, DBMS must provide appropriate languages and interfaces to express database, queries. Database languages are used to create and maintain database on computer. There, are large number of database languages Oracle,MySQL,MS-Access,dBase,Foxpro etc., •, •, , A DBMS has appropriate languages and interfaces to express database queries and, updates., Database languages can be used to read, store and update the data in the database., , Types of Database Language, , 1. Data Definition Language, •, •, •, •, , DDL stands for Data Definition Language. It is used to define database structure or, pattern., It is used to create schema, tables, indexes, constraints, etc. in the database., Using the DDL statements, you can create the skeleton of the database., Data definition language is used to store the information of metadata like the number, of tables and schemas, their names, indexes, columns in each table, constraints, etc., , Here are some tasks that come under DDL:, •, •, •, •, , Create: It is used to create objects/table in the database., Alter: It is used to alter the structure of the database., Drop: It is used to delete objects/table from the database., Rename: It is used to rename an object/table., , These commands are used to update the database schema that's why they come under Data, definition language.
Page 15 :
2. Data Manipulation Language, DML stands for Data Manipulation Language. It is used for accessing and manipulating data, in a database. It handles user requests., Here are some tasks that come under DML:, •, •, •, •, , Select: It is used to retrieve data from a database., Insert: It is used to insert data into a table., Update: It is used to update existing data within a table., Delete: It is used to delete all records from a table., , 3. Data Control Language, •, •, , DCL stands for Data Control Language. It is used to retrieve the stored or saved data., The DCL execution is transactional. It also has rollback parameters., (But in Oracle database, the execution of data control language does not have the, feature of rolling back.), , Here are some tasks that come under DCL:, •, •, , Grant: It is used to give user access privileges to a database., Revoke: It is used to take back permissions from the user., , There are the following operations which have the authorization of Revoke:, INSERT, EXECUTE, DELETE, UPDATE and SELECT., 4. Transaction Control Language, TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical, transaction., Here are some tasks that come under TCL:, •, •, , Commit: It is used to save the transaction on the database., Rollback: It is used to restore the database to original since the last Commit.
Page 16 :
Difference between DBMS VS RDBMS, Parameter, , DBMS, , RDBMS, , Storage, , DBMS stores data as a file., , Data is stored in the form of tables., , Database, structure, , DBMS system, stores data in, form., , RDBMS uses a tabular structure where, the headers are the column names, and, the rows contain corresponding values, , Number of Users, , DBMS supports single user, only., , It supports multiple users., , It is the program for managing, the databases on the computer, Type of program, networks and the system hard, disks., Hardware and, Low software and hardware, software needs. needs., Integrity, constraints, , DBMS does not support the, integrity constants. The, integrity constants are not, imposed at the file level., , DBMS does not support, Normalization, Distributed, DBMS does not support, Databases, distributed database., DBMS system mainly deals with, Ideally suited for, small quantity of data., Normalization, , Dr. E.F. Codd, Rules, Client Server, Data Fetching, Data, Redundancy, Data, Relationship, , It is the database systems which are, used for maintaining the relationships, among the tables., Higher hardware and software need., RDBMS supports the integrity, constraints at the schema level. Values, beyond a defined range cannot be, stored into the particular RDMS, column., RDBMS can be Normalized., RBMS offers support for distributed, databases., RDBMS is designed to handle a large, amount of data., , DBMS satisfy less than seven of, DBMS satisfy 8 to 10 Dr. E.F. Codd Rules, Dr. E.F. Codd Rules, DBMS does not support client- RDBMS supports client-server, server architecture, architecture., Data fetching is slower for the, complex and large amount of, data., Data redundancy is common in, this model., No relationship between data, , Data fetching is rapid because of its, relational approach., Keys and indexes do not allow Data, redundancy., Data is stored in the form of tables, which are related to each other with, the help of foreign keys.
Page 17 :
Parameter, , DBMS, , Security, , There is no security., , Data Access, , Data elements need to access, individually., , Examples, , Examples of DBMS are a file, system, XML, Windows, Registry, etc., , RDBMS, Multiple levels of security. Log files are, created at OS, Command, and object, level., Data can be easily accessed using SQL, query. Multiple data elements can be, accessed at the same time., Example of RDBMS is MySQL, Oracle,, SQL Server, etc., , Features of RDBMS Systems:, •, •, •, •, •, •, •, , All data stored in the tables are provided by an RDBMS, Ensures that all data stored are in the form of rows and columns, Facilitates primary key, which helps in unique identification of the rows, Index creation for retrieving data at a higher speed, Facilitates a common column to be shared amid two or more tables, Multi-user accessibility is facilitated to be controlled by individual users, A virtual table creation is enabled to store sensitive data and simplify queries, , These features are the reasons why RDBMS is widely used across the world. In simple, words, RDBMS is the software system that allows storing data in the form of tables. As we, discussed, data stored within RDBMS is in the form of rows and columns and this kind of a, setup is often referred to as tuples and attributes.
