data base management system (dbms) unit -1 base management system (dbms) unit -1
TRANSCRIPT
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 1© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 1© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.1
Data Base Management System(DBMS)
Unit -1
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 2© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 2© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 2© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 2© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.2
Data Base Management System
Data: Data is the basic raw,fact and figuresEx: a name, a digit, a picture etc.
Data Base: Collection of related data Ex. the names, telephone numbers and addresses of all
the people you know
Data Base Management System: A DBMS is a set of software
programs that controls the organization, storage, management, and retrieval of data in a database.
Ex: MS-Excel, MS-Access, Oracle, MS SQL, Sybase, IBM DB2
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 3© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 3© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 3© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 3© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.3
Use of DBMS
Corporate AirlinesHotelsBanksColleges /universityRailway reservationShopping MallsTelecommunication
Industry
Weather forecastingPattern RecognitionData miningSpace ResearchSoftware Industry
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 4© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 4© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 4© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 4© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.4
Advantages of Using DBMS
Mass Data Storage Centralized Access Automatic Backup Possible Data Recovery Possible Security restrictions can be applied Easily updation & fetching of data Only authorized Access No Data Redundancy Data Consistency etc…….
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 5© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 5© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 5© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 5© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.5
Flat Files Notepad , WordPad
Light Duty
Medium Duty
Heavy Duty
Database software...
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 6© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 6© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 6© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 6© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.6
No centralized controlData Redundancy Data Inconsistency Data can not be sharedStandards can not be enforcedSecurity issues Integrity can not be maintainedData Dependence
Disadvantages of Flat File Systems
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 7© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 7© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 7© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 7© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.7
Controls data redundancy. Enforces user defined rules. Ensures data sharing. It has automatic and intelligent backup and recovery
procedures. It has central dictionary to store information. Pertaining to data and its manipulation. It has different interfaces via which user can
manipulate the data. Enforces data access authorization. Represents complex relationship between data.
Data Base Characteristics
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 8© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 8© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 8© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 8© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.8
Naive UserA person who does not know what is DBMS.
Like:- A user of ATM. A user of credit card.
Application ProgrammerA programmer who is responsible for developing application programs. This user can make change
in the data base also and can run the queries.
Data Base Users
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 9© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 9© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 9© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 9© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. Professor U2.9
9
Sophisticated User: sophisticated user interact with the system
without writing programs. Instead, they form their requests in a database query language.
Specialized User:Specialized users are sophisticated users
who write specialized database applications that do not fit into the traditional data- processing framework.
Ex: In expert system, AI, CAD design etc.
Data Base Users contd…..
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 10© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 10© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 10© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 10© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.10
A database administrator (DBA) is a person responsible for the design, implementation, maintenance and repair of an organization's database.
Data Base Administrator
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 11© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 11© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 11© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 11© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.11
To Provide space to each user.To create the Physical and logical Schema.To Provide security from unauthorized access.To grant permission to the userInstallation, configuration and upgrading of Oracle
server software and related products.To take Back up and Recovery of data.Performance monitoring of the machine and database.
Roles of Data Base Administrator
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 12© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 12© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 12© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 12© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.12
Data Base Architecture
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 13© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 13© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 13© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 13© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.13
Physical Schema
Conceptual Schema
External Schema - 2External Schema - 1 External Schema - 3
Disk
Three-Layer Abstraction
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 14© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 14© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 14© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 14© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.14
Users Level: At user level different -2 type of users of database are accessing the data by firing their queries.
Conceptual Level:This level is designed by data base administrator. Under this level a schema of data base is created by DBA.This is also known as Logical Level.
Internal Level:It indicates how the data will be stored ad describes the data structures and access methods to be used by data base.
Description of Levels
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 15© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 15© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 15© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 15© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.15
According to Hoberman (2009),
"A data model is a way finding tool for both business and IT professionals, which uses a set of symbols and text to precisely explain a subset of real information to improve communication within the organization and thereby lead to a more flexible and stable application environment”.
Data Model
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 16© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 16© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 16© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 16© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.16
Flat file Hierarchical Data Model Network Data model Relational Data model
Different Data Models
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 17© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 17© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 17© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 17© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.17
This may not strictly qualify as a data model. The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another.
Flat Data model
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 18© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 18© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 18© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 18© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.18
In this model data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list.
Hierarchical Data Model
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 19© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 19© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 19© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 19© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.19
Example :Hierarchical DBMS
Data is represented by a tree structure
P1 Nut Red 12 London
S2 Jones 10 Paris 300
S1 Smith 20 London 300
P2 Bolt Green 17 Paris
S3 Blake 30 Paris 200
S2 Jones 10 Paris 400
S1 Smith 20 London 200
P3 Screw Blue 17 Rome
S1 Smith 20 London 400
P4 Screw Red 14 London
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 20© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 20© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 20© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 20© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.20
Drawbacks: Hierarchical DBMS
Can not handle Many-Many relations
Can not reflect all real life situations
Anomalies in insert, delete and update operations.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 21© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 21© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 21© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 21© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.21
Network Data ModelThis model organizes data using two fundamental
constructs, called records and sets. Records contain fields, and sets define one-to-many
relationships between records: one owner, many members.
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 22© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 22© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 22© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 22© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.22
Relational Data Model
Relational model is based on relations of the tables. It is bounded with 12 codd ’s rules. Every information will be stored in the form of columns
and rows.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 23© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 23© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 23© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 23© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.23
Relational Data Model
Example of tabular data in the relational model
customer-name
Customer-idcustomer-street
customer-city
account-number
Johnson
Smith
Johnson
Jones
Smith
192-83-7465
019-28-3746
192-83-7465
321-12-3123
019-28-3746
Alma
North
Alma
Main
North
Palo Alto
Rye
Palo Alto
Harrison
Rye
A-101
A-215
A-201
A-217
A-201
Attributes
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 24© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 24© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 24© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 24© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.24
Relational Database schema
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 25© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 25© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 25© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 25© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.25
Poor Table Design
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 26© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 26© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 26© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 26© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.26
Problems with Design
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 27© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 27© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 27© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 27© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.27
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 28© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 28© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 28© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 28© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.28
Name of the Data Models
Relational Model – DB2, Oracle, Informix, Sybase, MS-Access, Foxbase, Paradox, etc.
Hierarchical Model – IMS DBMS
Network Model – IDS & IDMS
Object-Oriented Model – ObjectStore & Versant
Object-Relational Model – Products from IBM, Oracle, ObjectStore, Versant.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 29© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 29© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 29© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 29© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.29
• When a schema at a lower level is changed, only the mappings between this schema and higher-lever schemas need to be changed in a DBMS that fully supports data independence. The higher-level schemas themselves are unchanged. Hence, the application programs need not be changed since they refer to the external schemas.
• Disadvantages of two levels of mappings:Overhead during compilation or execution of a query or program
Data IndependenceData Independence
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 30© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 30© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 30© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 30© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.30
Data IndependenceData Independence
Logical Data Independence: The capacity to change the conceptual schema withouthaving to change the external schemas and their application programs.
Physical Data Independence: The capacity to change the internal schema without having to change the conceptual schema.
Reorganize physical files to improve performancee.g. List all sections offered in Fall 1998
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 31© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 31© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 31© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 31© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.31
Instance
The collection of information stored in the database at a particular moment is called an instance of the database.
Ex: Amit, 101 etc.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 32© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 32© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 32© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 32© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.32
Schema
The overall design of the database is called the database schema.
A schema is the structure of the table which is decided before storing the data.
Example: Create table student( rollno number(5),name char(15),address varchar2(25));
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 33© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 33© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 33© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 33© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.33
Tuple
A tuple is a related record stored in a row of the table.
Ex: 101,Alok,MCA,85%
Tuple : Record Attributes: columns
Entity : Tables
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 34© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 34© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 34© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 34© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.34
E-R Diagram
Entity Relationship DiagramSet of some predefined symbols E-R diagram is used to create a relational data base or data model. Before designing the data base we have to first create the E-R Diagram for whole data base than it will convert into the tables and relations.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 35© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 35© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 35© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 35© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.35
E-R Diagrams
Rectangles represent entity sets.
Diamonds represent relationship sets.
Lines link attributes to entity sets and entity sets to relationship sets.
Ellipses represent attributes
Double ellipses represent multivalued attributes.
Dashed ellipses denote derived attributes.
Underline indicates primary key attributes (will study later)
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 36© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 36© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 36© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 36© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.36
Symbols Used in E-R Notation
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 37© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 37© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 37© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 37© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.37
Summary of Symbols (Cont.)
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 38© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 38© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 38© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 38© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.38
Cardinality E-R Notations
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 39© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 39© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 39© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 39© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.39
E-R Diagram With Composite, Multivalued, and Derived Attributes
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 40© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 40© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 40© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 40© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.40
Types of Keys
Superkey is a set of one or more attributes that allow us to identify uniquely an entity in the entity
set. Like: s.no , name ,age
Candidate Key are minimal superkey in an entity, one of those keys is selected to be the primary key
like: s.no and name both
Primary Key is a candidate key that is chosen to identify entities within an entity set like: rollno
Foreign Key is a primary key of another relation schema like: Any key which is primary key
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 41© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 41© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 41© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 41© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.41
Weak Entity set
An entity set which is not having any primary key or candidate key is termed a weak entity set.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 42© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 42© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 42© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 42© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.42
Strong entity set
An entity set that has a primary key is termed as strong entity set.
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 43© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 43© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 43© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 43© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.43
Mapping Cardinalities
Express the number of entities to which another entity can be associated via a relationship set.Most useful in describing binary relationship
sets.For a binary relationship set the mapping
cardinality must be one of the following types: One to one One to many Many to one Many to many
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 44© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 44© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 44© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 44© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.44
Mapping Cardinalities
One to one One to many
Note: Some elements in A and B may not be mapped to any elements in the other set
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 45© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 45© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 45© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 45© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.45
Mapping Cardinalities
Many to one Many to many
Note: Some elements in A and B may not be mapped to any elements in the other set
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 46© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 46© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 46© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 46© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.46
Overall Database Design
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 47© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 47© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 47© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 47© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.47
Dr. Edgar F. Codd (1923-2003)
Codd completed his PhD at the University of Michigan in 1963, and presented a thesis on the topic of a self- reproducing computer consisting of a large number of simple identical cells, each of which interacts in a uniform manner with its four immediate neighbors.
Codd reported this work in a book entitled Cellular Automatapublished by Academic Press in 1968.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 48© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 48© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 48© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 48© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.48
Relational Terminology
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 49© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 49© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 49© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 49© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.49
12 Codd's Rules
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 50© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 50© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 50© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 50© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.50
Rule 1 : The Information Rule
All data should be presented in table form.
Rollno Name Age college
10 Rohit 20 Bv
11 Rahul 21 Abes
12 Amit 22 Jss
13 Simran 23 its
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 51© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 51© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 51© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 51© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.51
Rule 2 : Guaranteed Access Rule
All data should be accessible without ambiguity.
This can be accomplished through a combination of the table name, primary key and column name.
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 52© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 52© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 52© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 52© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.52
Rule 3: Systematic treatment of null values
A field should be allowed to remain empty. This involves the support of null value, which is distinct from an empty string or a number with a value of zero.
Most database implementations support the concept of a not –null field constraint that prevent null values in a specific table column.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 53© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 53© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 53© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 53© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.53
Rule 4: Dynamic on-line catalog based on the relational model
A relational database must provide access to its structure through the same tools that are used to access the data.
This is usually accomplished by storing the structure definition with in special system table.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 54© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 54© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 54© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 54© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.54
Rule 5 : Comprehensive data sub-language Rule
The database must support at least one clearly defined language that include functionality for data definition, data manipulation, data integrity and data transaction control.
All commercial relational databases use forms of standard SQL( structure Query Language) as their supported comprehensive language.
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 55© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 55© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 55© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 55© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.55
Rule 6 : View updating Rule
Data can be presented into different logical combinations called views.
Each view should support the same full range of data manipulation that has direct access to a table available.
In practical, providing update and delete access to logical views is difficult and is not fully supported by current database.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 56© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 56© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 56© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 56© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.56
Rule 7 : High-level Insert, Update and Delete
Data can be retrieved from a relational database in sets constructed of data from multiple rows and multiple tables.
This rule states that insert, update, delete operations should be supported for any retrievable set rather just for a single row in a single table.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 57© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 57© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 57© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 57© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.57
Rule 8 : Physical data independence
The user is isolated from the physical method of storing and retrieving information from the database.
Changes can be made to the underlying architecture ( hardware, disk storage methods) without affecting how the user accesses it.
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 58© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 58© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 58© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 58© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.58
Rule 9 : Logical Data Independence.
How the data is viewed should not be changed when the logical structure (table’s structure) of the database changes.
This rule is difficult to satisfy.
Most databases rely on strong ties between the data viewed and the actual structure of underlying tables.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 59© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 59© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 59© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 59© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.59
Rule 10 : Integrity Independence
SQL should support constraints on user input that maintain database integrity.
At a minimum, all databases do preserve two constraints through SQL.
Primary key should be not null and unique.
If a foreign key is defined in one table, any value in it must exist as a primary key in another table.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 60© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 60© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 60© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 60© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.60
Rule 11 : Distribution Independence
A user should be totally unaware of whether or not the database is distributed ( whether parts of the database exist in multiple locations).
A variety of reasons make this rule difficult to implement.
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 61© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 61© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 61© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 61© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.61
Rule 12: The Non subversion rule
There should be no way to modify the database structure other than through the multiple row database language( SQL).
Most databases today support administrative tools that allows some direct manipulation of the data structure.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 62© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 62© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 62© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 62© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.62
Specialization
An entity set may include sub grouping of entities that are distinct in some way from other entities in the set.
For Instance, a subset of entities with in an entity set may have attributes that are not shared by all the entities in the entity set.
The process of designating sub grouping with in an entity set is called specialization.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 63© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 63© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 63© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 63© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.63
Specialization Example
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 64© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 64© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 64© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 64© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.64
Generalization
The commonality can be expressed by generalization, which is containment relationship that exists between a higher level entity set and one or more lower level entity sets.
The refinement from an initial entity set into successive level of entity sub grouping represent s a top down design process in which distinctions are made explicit.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 65© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 65© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 65© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 65© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.65
Generalization
A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set.
Specialization and generalization are simple inversions of each other. They are represented in an E-R diagram in the same way.
The terms specialization and generalization are used interchangeably.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 66© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 66© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 66© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 66© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.66
Aggregation
Aggregation is an abstraction through which relationships are treated as higher- level entities.
One limitation of E-R Model is that it can not express relationship among relationship
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 67© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 67© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 67© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 67© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.67
Aggregation Consider the ternary relationship works-on, which we saw earlier
Suppose we want to record managers for tasks performed by an employee at a branch.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 68© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 68© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 68© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 68© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.68
Q1. What is Data Base Management system?A. file B. Tables with recordsC. Software C. none of these
Q2. A data base follows how many rules at max?A. 6 B. 7C. 11 D.11.5
Q3. What is Primary Key?A. NotNull B. UniqueC. Unique +Notnull D. None of these
Objective Questions
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 69© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 69© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 69© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 69© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.69
Q4. Which is not a DBMS?A. None of the these B. DB2C. Access C. Sybase
Q5. SQL stands for ………………………………Q6. What is candidate Key?
Q7. E- R Diagram is used for what ?
Q8……………….. is specialized user?
Objective Questions
MCA 203 , Data Base Management System
© Bharati Vidyapeeth’s Institute of Computer Applications and Management,
New Delhi-63, By Vaibhav Singhal, Asst. ProfessorU1.
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 70© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 70© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 70© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 70© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.70
Q9.Logical Data Independency is done at which level?
A. Conceptual Level B. User LevelC. Internal level C. None of the above
Q10. DBA stands for ……………………………
Q11. What is generalization …………………….
Objective Questions
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 71© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 71© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 71© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 71© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.71
Q.1 What is data independence?Q.2.What do you mean by DBMS?Q.3 A transaction is a set of operations that must be performed completely or not at all.
Explain with suitable example.Q.4What is the difference between Generalization
and Specialization?Q.5 Describe the characteristics of DBMS.Q.6 Explain all components of E-R Diagram.Q.7 What is of keys in DBMS and explain how
many types of keys are there.
Short Questions:
© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 72© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63. 72© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 72© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63 72© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Vaibhav Singhal, Asst. ProfessorU2.72
Q.1 Describe 3-level of abstractions of DBMS.Q.2 Differentiate between physical and logical
data independency.Q.3 Discuss all 12 rules of Dr. E.F. Codd’s.Q.4 What is data model? Discuss various data
models available in DBMS.Q.5Differentiate between weak and strong
entity sets with example.Q.6 What is a DBMS? How does it differ from a
conventional file system?
Long Questions: