Page 2 :
Database Concepts, ● Data :, Data is a collection of information gathered by observations, measurements, research or, analysis., Data is representation of facts,concepts and instruction in formalized manner., Example: Departments of Organization(Tech employee,HR,Finance,Legal),, , Number of people got vaccinated for dose 1 or both.
Page 3 :
Database, ●, , ●, ●, ●, , Data is in random format and database is systematic collection of data. It is a, collection of inter-related data which is used to retrieve, insert and delete the data, efficiently. It is also used to organize the data in the form of a table, schema, views,, and reports, etc., It’s an application that manage the data and allow fast storage and retrieval of that, data., The industry database organizes the data about the different departmental staff, such as HR,Finance,Tech employee,Legal,Facility etc., The college Database organizes the data about the admin, staff, students and, faculty etc.
Page 4 :
Why we need database?, ●, , Databases can store very large numbers of records efficiently, , ●, ●, ●, ●, ●, ●, , It is very quick and easy to find information., It is easy to add new data and to edit or delete old data., Data can be searched easily, eg 'find all students from Computer and IT branch'., Data can be sorted easily, for example into 'date first registered' order., More than one person can access the same database at the same time - multi-access., Security may be better than in paper files.
Page 5 :
DBMS(Database Management System), ●, , Database management system is a software which is used to manage the database., , ●, , DBMS provides an interface to perform various operations like database creation, storing, data in it, updating data, creating a table in the database and a lot more., , ●, , It provides protection and security to the database. In the case of multiple users, it also, maintains data consistency., Example: Manufacturing company: Which stores data such as product_id,name,price, Collage:Which stores the data such as branch,student name,roll no
Page 6 :
Definition of DBMS, ●, ●, , DBMS is collection of data and programs to access the data.It is back end, process use to store,retrieve and display the information., Example: Facebook-data related members,their friends,members, activities,Advertisement,Online Telephone Directory,MSEB-Manage, bills,client related issue,
Page 7 :
History of DBMS, ●, , ●, ●, ●, , In 1960 Charles Bechmen’s integrated data store(IDS) is said to be first DBMS, The design and standardization of a language called Common Business, Oriented Language (COBOL). The Database Task Group presented this, standard in 1971, 1976- Peter Chen coined and defined the Entity-relationship model also known as the, ER model, 1997: XML applied to database processing.
Page 8 :
Functions of DBMS, ●, , Data Definition: It is used for creation, modification, and removal of definition that defines the organization of, data in the database., , ●, , Data Updation: It is used for the insertion, modification, and deletion of the actual data in the database., , ●, , Data Retrieval: It is used to retrieve the data from the database which can be used by applications for various, purposes., , ●, , User Administration: It is used for registering and monitoring users, maintain data integrity, enforcing data, security, dealing with concurrency control, monitoring performance and recovering information corrupted by, unexpected failure.
Page 9 :
Characteristics of DBMS, ●, ●, ●, ●, ●, ●, , Provides security and removes redundancy, User File approach, Self-describing nature of a database system, Sharing of data and multiuser transaction processing, Database Management Software allows entities and relations among them to form tables., DBMS supports multi-user environment that allows users to access and manipulate data in, parallel.
Page 10 :
Difference between Flat file and DBMS
Page 11 :
Three Level Architecture of DBMS
Page 12 :
Mappings, ●, , It shows the DBMS architecture., , ●, , Mapping is used to transform the request and response between various database levels of architecture., , ●, , Mapping is not good for small DBMS because it takes more time., , ●, , In External / Conceptual mapping, it is necessary to transform the request from external level to conceptual, schema., , ●, , In Conceptual / Internal mapping, DBMS transform the request from the conceptual to internal level.
Page 13 :
External Level, ●, , In external level user only see entities,relationship and attribute that means hides the rest of db from user.At the, external level, a database contains several schemas that sometimes called as subschema. The subschema is, used to describe the different view of the database., , ●, , An external schema is also known as view schema., , ●, , Each view schema describes the database part that a particular user group is interested and hides the remaining, database from that user group., , ●, , The view schema describes the end user interaction with database systems.
Page 14 :
Conceptual Level, , ●, , It hides how database has been implemented in particular dbms system.The conceptual schema describes the, design of a database at the conceptual level. Conceptual level is also known as logical level., , ●, , The conceptual schema describes the structure of the whole database., , ●, , The conceptual level describes what data are to be stored in the database and also describes what relationship, exists among those data., , ●, , In the conceptual level, internal details such as an implementation of the data structure are hidden., , ●, , Programmers and database administrators work at this level.
Page 15 :
Internal Level, , ●, , The internal level has an internal schema which describes the physical storage structure of the, database., , ●, , The internal schema is also known as a physical schema., , ●, , It uses the physical data model. It is used to define that how the data will be stored in a block., , ●, , The physical level is used to describe complex low-level data structures in detail.
Page 16 :
Data Abstraction, ●, ●, , ●, , Data Abstraction means hiding the database storage at complex level from users.That means user, don't know where data is stored and how it is stored.Simply means hide database from user., Example University management system, in which once you open the first page of University site it, will ask to fill the username and password then student can see their marks,fees paid ,fees pending, like parameters but when faculty login then they can see attendance,salary like parameters., In this scenario some point are not accessible to student and some of not accessible faculty,this, function can be implemented using view and its virtual table.
Page 17 :
Level of data abstraction, 1. External level or schema, 2. Conceptual level or schema, 3. Physical level or schema
Page 18 :
Schema, ● Description or structure of database is called database schema, ● Specified during database design and does not expect any changes, frequently, ● Database schema defines columns of tables, ● Itr defines DB entities and relationship among tables.
Page 19 :
Instance, Value in the columns is called instance of database i.e row value, E.g Name=A, , Roll No=10, , Address=Pune
Page 20 :
Data Independence, Property to change the overall logical and physical structure of data without, changing application program view of data., , Types:, 1. Logical Independence, 2. Physical Independence
Page 21 :
Overall DBMS System Structure, Refer paint image
Page 22 :
Data Model, ● Data model give an idea how the final system look like after development, ● Data model is and overview of software system which describes how data, can be represented and accessed from software system after its complete, implementation., ● It defines data elements and and relationship among them.
Page 23 :
Types of Data Models, ●, ●, ●, ●, , Relational Database Model, Hierarchical Model, Network Model, Object Oriented Database Model
Page 24 :
Relational Database Model, ●, ●, ●, ●, ●, ●, ●, ●, , Relational Model first proposed by Edgar Codd hence he is known as father of Relational Model., Relational Database used to simplify DB structure by using tables and columns., It is collection of 2 dimensional model having rows and columns., Basic building blocks are Tables(relations),columns(attributes),rows(tuples), Set of record with similar attributes called as table., It supports relational algebra and set theory i.e union and intersection, It support dynamic view,Structured query Language., It provides security,distributed system and good performance.
Page 25 :
Hierarchical Model, ●, ●, ●, ●, ●, ●, ●, , One to many relationship is maintained, It is based on Tree like structure, This model is used in Mainframe systems., The data is stored in hierarchically either by top to down or down to up approach, It used pointers to navigate between stored data, It is based on rule that one parent can have many children but children are allowed only one, parent., It implies that record can have repeating information
Page 26 :
Network Model, ●, ●, ●, ●, , One to many and Many to many relationship is maintained, It uses client server model, Tree like structure with many routes, Upgraded version of hierarchical model
Page 27 :
Object Oriented Model, ●, ●, ●, ●, ●, ●, , Data is stored in the form of object which are structure called as classes, The fields are instances of these classes called as objects, It involves complex applications manufacturing,geographic info, It provides clear modular structure, It is easy to maintain and modify, It is provided through cpp and java
Page 28 :
E-R (Entity Relationship) Model, ●, ●, ●, ●, , It is high level conceptual data model developed by Chen, E-R model used for logical representation of data, It is database designing tool., E-R model is shown diagrammatically using E-R diagram which, represent the elements of conceptual model that shows meaning and, relationship between those elements and implementation details.
Page 29 :
Features of E-R Model, ● This model represent using E-R diagrams used to convert into, tables., ● It is used for purpose of good database design., ● On later modifications the entities can be easily inserted into, model., ● It is simple and easily understandable to designers.
Page 30 :
Components of E-R Model, 1., , Rectangle represents entity, , 1., , Ellipse represents attributes, , 1., 2., , Diamonds represents relationship among entities, Lines represents link between entities and attributes
Page 31 :
Entity, ● Any object which has physical existence or logical existence is, called as Entity., ● Entity is an object in the system that we want to model and store, the information about database., ● Entity has its own properties that are called as attributes., ● Collection of all entities of of same type known as entity set
Page 32 :
Types of Entity Set, 1. Strong entity Set :Entity type which has its own key attributes by which we can, identify specific entity uniquely is called as strong entity set. E.g Specific Bank, account can be recognise, 2. Weak entity Set:Entity type which cannot from distinct key from their, attributes and takes help from corresponding strong entity is called weak entity, set. E.g Loan and payment
Page 33 :
Attributes, ●, ●, , Various properties that describes an entity are known as attributes., Types of attributes:, 1)Simple and composite attributes, 2)Single valued and multivalued, 3)stored and derived, 4)Null attribute, 5)key attribute
Page 34 :
Mapping Cardinality, 1., 2., 3., 4., , One to One, One to Many, Many to One, Many to Many
Page 35 :
Unit 2 Relational Data Model, ●, ●, 1., 2., 3., 4., 5., , Fundamentals of RDBMS, Components of RDBMS, Attributes/Fields, Tuples/Records, Data Types, Tables, Database
Page 36 :
12 Codd’s Rule, 1., 2., 3., 4., 5., 6., 7., 8., 9., , Information Rule, Guaranteed Rule, Systematic treatment of null values, Self describing database, Comprehensive data sublanguage, View Updating rule, High Level Insert,Update and delete, Physical Data Independence, Logical Data Independence
Page 37 :
12 Codd’s Rule, 10. Integrity Independence, 11. Distribution Independence, 12. Non Subversion Rule
Page 38 :
Introduction to SQL, 1., 2., 3., 4., 5., 6., 7., , SQL is standard language for accessing and manipulating databases., It stands for Structured Query Language, Data definition Language(DDL) :Create ,drop,alter,rename,Truncate, Data Manipulation Language(DML):Insert,Update,delete,Upsert,Merge, Data Control Language(DCL):Grant,Revoke, Data Query Language(DQL):Select, Data Transition Language(DTL):commit,rollback,savepoint
Page 39 :
Datatypes in Oracle, 1., 2., 3., 4., 5., 6., 7., , Number, Date, Char, Varchar2, Long, Raw, Long Raw
Page 40 :
Key Concept, ● Key is vital part of structure of the table., ● Keys ensure each record within a table can be uniquely, identified by one or combination of the fields within the, table., ● It is helps to identify the relationship of the table .
Page 41 :
Types of Keys, 1. Super key : An attribute or set of all attributes that used to identify the each, row in the table, 2. Candidate Key: It is nothing but minimal set of super key.Key whose proper, subset is not super key, 3. Primary Key: key in which contain unique values and must not be null and, uniquely identify each record in table.Repetition is not allowed., 4. Foreign Key:It is primary key from one table appears as field in another where, first table has relationship to second.
Page 42 :
Components of SQL, ●, 1., 2., 3., 4., 5., 6., , DDL Commands, Create, Alter, Drop, Truncate, DESC, Rename
Page 43 :
Components of SQL, ●, 1., , Data Integrity constraints, Entity Integrity Constraints, Primary key Constraints, Foreign Key Constraints
Page 44 :
Components of SQL, ●, 1., 2., 3., , Domain Integrity Constraints, Null Constraint, Not Null Constraint, Check Constraint
Page 45 :
DML Command, 1. Insert, 2. Update, 3. delete
Page 47 :
DCL-Data Control Command, 1. Grant, 2. revoke
Page 48 :
DQL Command, 1., 2., , Select all column, Select distinct statement
Page 49 :
Arithmetic operators
Page 50 :
Normalization, It is technique to remove or reduce redundancy or duplicity from table, , Types of Duplicity:, 1) Row Level, 2) Column Level-Insertion Anomaly,Updation Anomaly.Deletion, Anomaly
Page 51 :
Types of Normalization, ●, , 1NF(First normal form):Table should not contains any, multivalued., , ●, , ●, , 2NF(Second normal form): 1.Table or relation must be in first, , normal form. 2. There should not have partial dependency., 3NF(Third normal form): 1.Table or relation must be in second, normal form. 2. There should not have transitive dependency.
