Download Database Management Systems Lecture Notes and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
Database Management Systems Lecture Notes
UNIT-I
Data:
It is a collection of information.
The facts that can be recorded and which have implicit meaning known as 'data'.
Example: Customer ----- 1.cname. 2.cno. 3.ccity.
Database:
It is a collection of interrelated data
. These can be stored in the form of tables. A database can be of any size and varying complexity. A database may be generated and manipulated manually or it may be computerized. Example: Customer database consists the fields as cname, cno, and ccity
Cname Cno Ccity
Database System:
It is computerized system, whose overall purpose is to maintain the information and to make that the information is available on demand.
Advantages:
1.Redundency can be reduced.
2.Inconsistency can be avoided.
3.Data can be shared.
4.Standards can be enforced. 5.Security restrictions can be applied. 6.Integrity can be maintained. 7.Data gathering can be possible. 8.Requirements can be balanced.
Database Management System (DBMS):
It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.
Disadvantages in File Processing
Data redundancy and inconsistency.
Difficult in accessing data.
Data isolation.
Data integrity.
Concurrent access is not possible.
Security Problems.
.
Advantages of DBMS:
1.Data Independence. 2.Efficient Data Access. 3.Data Integrity and security. 4.Data administration. 5.Concurrent access and Crash recovery. 6.Reduced Application Development Time.
Applications
Database Applications: Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized recommendations Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions
People who deal with databases
Many persons are involved in the design, use and maintenance of any database. These persons can be classified into 2 types as below.
Actors on the scene: The people, whose jobs involve the day-to-day use of a database are called as 'Actors on the scene', listed as below.
1.Database Administrators (DBA):
The DBA is responsible for authorizing access to the database, for Coordinating and monitoring its use and for acquiring software and hardware resources as
a. Design of the conceptual and physical schemas: The DBA is responsible for interacting with the users of the system to understand what data is to be stored in the DBMS and how it is likely to be used. The DBA creates the original schema by writing a set of definitions and is Permanently stored in the 'Data Dictionary'. b. Security and Authorization: The DBA is responsible for ensuring the unauthorized data access is not permitted. The granting of different types of authorization allows the DBA to regulate which parts of the database various users can access.
c. Storage structure and Access method definition: The DBA creates appropriate storage structures and access methods by writing a set of definitions, which are translated by the DDL compiler.
d. Data Availability and Recovery from Failures: The DBA must take steps to ensure that if the system fails, users can continue to access as much of the uncorrupted data as possible. The DBA also work to restore the data to consistent state.
e. Database Tuning: The DBA is responsible for modifying the database to ensure adequate Performance as requirements change.
f. Integrity Constraint Specification: The integrity constraints are kept in a special system structure that is consulted by the DBA whenever an update takes place in the system.
2.Database Designers:
Database designers are responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data.
3. End Users: People who wish to store and use data in a database. End users are the people whose jobs require access to the database for querying, updating and generating reports, listed as below.
a. Casual End users: These people occasionally access the database, but they may need different information each time.
b. Naive or Parametric End Users: Their job function revolves around constantly querying and updating the database using standard types of queries and updates.
c. Sophisticated End Users: These include Engineers, Scientists, Business analyst and others familiarize to implement their applications to meet their complex requirements.
d. Stand alone End users: These people maintain personal databases by using ready-made program packages that provide easy to use menu based interfaces.
4.System Analyst: These people determine the requirements of end users and develop specifications for transactions.
5.Application Programmers (Software Engineers): These people can test, debug, document and maintain the specified transactions.
Department of CSE,JBIET
b. Workers behind the scene: Database Designers and Implementers: These people who design and implement the DBMS modules and interfaces as a software package.
2.Tool Developers : Include persons who design and implement tools consisting the packages for design, performance monitoring, and prototyping and test data generation.
3.Operators and maintenance personnel: These re the system administration personnel who are responsible for the actual running and maintenance of the hardware and software environment for the database system.
3.LEVELS OF DATA ABSTRACTION
This is also called as 'The Three-Schema Architecture’, which can be used to separate the user applications and the physical database.
1.Physical Level:
This is a lowest level, which describes how the data is actually stores. Example: Customer account database can be described.
2.Logical Level:
This is next higher level that describes what data and what relationships in the database. Example: Each record type customer = record cust_name: sting; cust_city: string; cust_street: string; end;
3.Conceptual (view) Level: This is a lowest level, which describes entire database. Example: All application programs.
- DATA MODELS
The entire structure of a database can be described using a data model. A data model is a collection of conceptual tools for describing Data models can be classified into following types. 1.Object Based Logical Models. 2.Record Based Logical Models. 3.Physical Models. Explanation is as below.
1.Object Based Logical Models: These models can be used in describing the data at the logical and view levels. These models are having flexible structuring capabilities classified into following types. a) The entity-relationship model. b) The object-oriented model. c) The semantic data model. d) The functional data model.
2.Record Based Logical Models:
These models can also be used in describing the data at the logical and view levels.
These models can be used for both to specify the overall logical structure of the database and a higher-level description.
These models can be classified into,
Department of CSE,JBIET
UNIT-
Entity Relational Model (E-R Model)
The E-R model can be used to describe the data involved in a real world enterprise in terms of objects and their relationships. Uses: These models can be used in database design. It provides useful concepts that allow us to move from an informal description to precise description. This model was developed to facilitate database design by allowing the specification of overall logical structure of a database. It is extremely useful in mapping the meanings and interactions of real world enterprises onto a conceptual schema. These models can be used for the conceptual design of database applications.
OVERVIEW OF DATABSE DESIGN
The problem of database design is stated as below.
'Design the logical and physical structure of 1 or more databases to accommodate the information needs of the users in an organization for a defined set of applications'. The goals database designs are as below. 1.Satisfy the information content requirements of the specified users and applications. 2.Provide a natural and easy to understand structuring of the information. 3.Support processing requirements and any performance objectives such as 'response time, processing time, storage space etc..
ER model consists the following 3 steps.
a. Requirements Collection and Analysis:
This is the first step in designing any database application. This is an informal process that involves discussions and studies and analyzing the expectations of the users & the intended uses of the database. Under this, we have to understand the following. 1.What data is to be stored n a database? 2.What applications must be built? 3.What operations can be used? Example: For customer database, data is cust-name, cust-city, and cust-no.
b. Conceptual database design:
The information gathered in the requirements analysis step is used to develop a higher-level description of the data. The goal of conceptual database design is a complete understanding of the database structure, meaning (semantics), inter-relationships and constraints.
Characteristics of this phase are as below.
1.Expressiveness: The data model should be expressive to distinguish different types of data, relationships and constraints.
2.Simplicity and Understandability: The model should be simple to understand the concepts.
3.Minimality: The model should have small number of basic concepts. 4.Diagrammatic Representation: The model should have a diagrammatic notation for displaying the conceptual schema. 5.Formality: A conceptual schema expressed in the data model must represent a formal specification of the data. Example: Cust_name : string; Cust_no : integer; Cust_city : string;
c. Logical Database Design :
Under this, we must choose a DBMS to implement our database design and convert the conceptual database design into a database schema. The choice of DBMS is governed by number of factors as below. 1.Economic Factors. 2.Organizational Factors. Explanation is as below.
Department of CSE,JBIET
3.Transaction Throughput: This is the average number of transactions that can be processed per minute.
5. Security Design: In this step, we must identify different user groups and different roles played by various users. For each role, and user group, we must identify the parts of the database that they must be able to access, which are as below.
2.ENTITIES
- It is a collection of objects.
- An entity is an object that is distinguishable from other objects by a set of attributes.
- This is the basic object of E-R Model, which is a 'thing' in the real world with an independent existence.
- An entity may be an 'object' with a physical existence.
- Entities can be represented by 'Ellipses'. Example: i. Customer, account etc.
3. ATTRIBUTES
Characteristics of an entity are called as an attribute. The properties of a particular entity are called as attributes of that specified entity. Example: Name, street_address, city --- customer database. Acc-no, balance --- account database. Types: These can be classified into following types.
1.Simple Attributes. 2.Composite Attributes. 3.Single Valued Attributes. 4.Mutivalued Attributes.
5.Stored Attributes. 6.Derived Attributes. Explanation is as below.
1.Simple Attributes:
The attributes that are not divisible are called as 'simple or atomic attributes'. Example: cust_name, acc_no etc..
2.Composite Attributes:
The attributes that can be divided into smaller subparts, which represent more basic attributes with independent meaning. These are useful to model situations in which a user sometimes refers to the composite attribute as unit but at other times refers specifically to its components. Example:
Street_address can be divided into 3 simple attributes as Number, Street and Apartment_no. Street_address
City State Zip
3.Single Valued Attribute:
The attributes having a single value for a particular entity are called as 'Single Valued Attributes'.
Example: 'Age' is a single valued attribute of 'Person'.
4.Muti Valued Attribute:
The attributes, which are having a set of values for the same entity, are called as 'Multi Valued Attributes'. Example: A 'College Degree' attribute for a person.i.e, one person may not have a college degree, another person may have one and a third person may have 2 or more degrees.
A multi-valued attribute may have lower and upper bounds on the number of values allowed for each individual entity.
5.Derived Attributes: An attribute which is derived from another attribute is called as a ‘derived attribute. Example: ‘Age’ attribute is derived from another attribute ‘Date’.
6.Stored Attribute: An attribute which is not derived from another attribute is called as a ‘stored attribute. Example: In the above example,’ Date’ is a stored attribute.
4. ENTITY SETS
Entity Type:
A collection entities that have the same attributes is called as an 'entity type'. Each entity type is described by its name and attributes.
Entity Set:
Collection of all entities of a particular entity type in the database at any point of time is called as an entity set. The entity set is usually referred to using the same name as the entity type. An entity type is represented in ER diagrams as a rectangular box enclosing the entity type name. Example: Collection of customers.
5. Relationships
It is an association among entities.
6. Relationship Sets
It is a collection of relationships. Primary Key: The attribute, which can be used to identify the specified information from the tables.
Weak Entity:
A weak entity can be identified uniquely by considering some of its attributes in conjunction with the primary key of another entity.
The symbols that can be used in this model are as follows.
1.Rectangles ---- ___ Entities.
2.Ellipses ----- ------ Attributes.
3.Lines ------ ------ Links.
4.Diamonds ----- Relationships.
Example: James of ‘Employees’ entity set works in a department since 1991.
Name
Dno
Street Dname
City
Since
Budget
Customer Works_in Departments
Instance: An instance of a relationship set is a set of relationships. It is a snapshot of the relationship at some instant of time.
EX: 1111 1/1/
Ternary Relationship:
A relationship set, which is having 3 entity sets, is called as a ternary relationship.
7.Additional Features of the E-R Model
1.Key Constraints: These can be classified into 4 types as below. 1.Many to Many: An employee is allowed to work in different departments and a department is allowed to have several employees.
Name
Dno
Street Dname
City
Since
Budget
Customer Works_in Departments
2.One to Many: 1 employee can be associated with many departments, where as each department can be associated with at
most 1 employee as its manager.
Name
Dno
Street Dname
City
Since
Budget
Customer Works_in Departments
3.Many to One: Each employee works in at most 1 department.i.e, many employees can work in same department.
Name Street
City
Since
Customer Works_in
4.One to One:
Each employee can manage at most 1 department.
Name Street
City
Since
Customer Works_in
2.Participation Constraints:
Dno
Dname
Budget
Departments
Dno
Dname
Budget
Departments
Department of CSE,JBIET
Example:
‘Dependents’ is an example of a weak entity set.
Partial key of the weak entity set:
The set of attributes of a weak entity set that uniquely identify a weak entity for a given owner entity is called as ‘partial key of the weak entity set’. Example:
‘Pname’ is a partial key for dependents.
The dependent weak entity set and its relationship to employees is shown in the following diagram. Linking them with a dark line indicates the total participation of dependents in policy. To understand the fact that dependents is a weak entity and policy is its identifying relationship, we draw both with dark lines. To indicate that ‘pname’ is a partial key for dependents, we underline it using a broken line.
No Cost
Pname
Age
Name --------
Employees Policy Dependents
4.Aggregation:
1.Explain ‘Aggregation’? (3 Marks, Jan-2005) 2.Explain how to use a ternary relationship instead of ‘aggregation’? (5 Marks, Jan-2005) 3.Explain ‘Aggregation in ER model? (4 Marks, July-
- (5 Marks, March-2003) (4 Marks, July-2002)
Aggregation is an abstraction for building composite objects from their component objects. Aggregation is used to represent a relationship between a whole object and its component parts. Aggregation allows us to indicate that a relationship set (identified through a dashed box) participates in another relationship set. This is illustrated with a dashed box around sponsors. If we need to express a relationship among relationships, then we should use aggregation. Aggregation versus Ternary Relationship: We can use either aggregation or ternary relationship for 3 or more entity sets. The choice is mainly determined by a. The existence of a relationship that relates a relationship set to an entity set or second relationship set. b. The choice may also guided by certain integrity constraints that we want to express.
Department of CSE,JBIET
Name Employees No
Monitors
Until
Pid Budget Since Dname Budget
Sponsors
Departments
Projects
According to the above diagram,
1.A project can be sponsored by any number of departments. 2.A department can sponsor 1 or more projects. 3.1 or more employees monitor each sponsorship. (Many to Many Relationship)
Consider the constraint that each relationship be monitored by at most 1 employee. We cannot express this constraint in terms of the ternary relationship in the following diagram. In that we are using a ternary relationship instead of aggregation. Aggregation groups a part of an E-Are diagram into a single entity set allowing us to treat the aggregate entity set as a single unit without concern for the details of it’s internal structure. Thus, the presence of such a constraint serves as another reason for using aggregation rather than a ternary relationship set.
8.Conceptual Database Design With The ER Model
The information gathered in the requirements analysis step is used to develop a higher-level description of the data. The goal of conceptual database design is a complete understanding of the database structure, meaning (semantics), inter-relationships and constraints. Characteristics of this phase are as below.
1.Expressiveness:
The data model should be expressive to distinguish different types of data, relationships and constraints.
2.Simplicity and Understandability:
The model should be simple to understand the concepts.
3.Minimality:
The model should have small number of basic concepts.
4.Diagrammatic Representation:
Department of CSE,JBIET
Name
Dno
Street Dname
City
Since
Budget
Customer Works_in Departments
Since
Mgr_appt
Dbudget
Conceptual Database Design For Large Enterprises
The process of conceptual database design consists describing small fragments of the application in terms of E-R diagrams. For a large Enterprise, the design may require, 1.More than 1 designer. 2.Span data and application by a number of user groups. Using a high level semantic data model such as ER diagrams for conceptual design offers the additional advantages that, 1.The high level design can be diagrammatically represented. 2.Many people, who provide the input to the design process, easily understand it. An alternative approach is to develop separate conceptual schemas for different user groups and then integrate all those. To integrate, we must establish correspondences between entities, relationships and attributes, so that this process is somewhat difficult. The relations of degree 1 are called as ‘Unary Relations’. The relations of degree 2 are called as ‘Binary Relations’. The relations of degree 3 are called as ‘Ternary Relations’. The relations of degree n are called as ‘nary Relations’.
Department of CSE,JBIET
UNIT-
RELATIONAL MODEL
A database is a collection of 1 or more ‘relations’, where each relation is a table with rows and columns. This is the primary data model for commercial data processing applications. The major advantages of the relational model over the older data models are, 1.It is simple and elegant. 2.simple data representation. 3.The ease with which even complex queries can be expressed.
Introduction:
The main construct for representing data in the relational model is a ‘relation’. A relation consists of 1.Relation Schema. 2.Relation Instance. Explanation is as below.
1.Relation Schema:
The relation schema describes the column heads for the table. The schema specifies the relation’s name, the name of each field (column, attribute) and the ‘domain’ of each field. A domain is referred to in a relation schema by the domain name and has a set of associated values. Example: Student information in a university database to illustrate the parts of a relation schema.
Students (Sid: string, name: string, login: string, age: integer, gross: real)
This says that the field named ‘sid’ has a domain named ‘string’. The set of values associated with domain ‘string’ is the set of all character strings.
2.Relation Instance:
This is a table specifying the information. An instance of a relation is a set of ‘tuples’, also called ‘records’, in which each tuple has the same number of fields as the relation schemas. A relation instance can be thought of as a table in which each tuple is a row and all rows have the same number of fields. The relation instance is also called as ‘relation’. Each relation is defined to be a set of unique tuples or rows.
Example: Fields (Attributes, Columns)
sid login age Field names 1111 Dave dave@cs 19 1. 2222 Jones Jones@cs 18 2.3 (^) Tuples (Records, Rows) 333 Smith smith@ee 18 3. 4444 Smith smith@math 19 4.
This example is an instance of the students relation, which consists 4 tuples and 5 fields. No two rows are identical.
Department of CSE,JBIET
