DBMS database management system 22SCCCS4, Study notes of Computer Science

Download DBMS database management system 22SCCCS4 and more Study notes Computer Science in PDF only on Docsity!

Roll No :

Name :

Dept :

PG DEPARTMENT OF COMPUTER SCIENCE

BHARATH COLLEGE OF SCIENCE AND MANAGEMENT,TNJ- 5

CONTENT

UNIT I

DATABASE SYSTEMS

Objective : To provide the basic concepts of the Database Systems including Data Models, Storage Structure, Normalization and SQL Unit I Introduction: Database-System Applications- Purpose of Database Systems - View of Data - Database Languages - Relational Databases - Database Design - Data Storage and Querying Transaction Management - Data Mining and Analysis - Database Architecture - Database Users and Administrators - History of Database Systems. Unit II Relational Model: Structure of Relational Databases - Database Schema - Keys - Schema Diagrams - Relational Query Languages - Relational Operations Fundamental Relational Algebra Operations Additional Relational-Algebra Operations- Extended Relational-Algebra Operations - Null Values - Modification of the Database. Unit III SQL:Overview of the SQL Query - Language - SQL Data Definition - Basic Structure of SQL Queries - Additional Basic Operations - Set Operations - Null Values Aggregate Functions - Nested Subqueries - Modification of the Database - Join Expressions - Views - Transactions - Integrity Constraints - SQL Data Types and Schemas - Authorization Unit IV Relational Languages: The Tuple Relational Calculus - The Domain Relational Calculus Database Design and the E-R Model: Overview of the Design Process - The Entity Relationship Model - Reduction to Relational Schemas - Entity-Relationship Design Issues - Extended E-R Features - Alternative Notations for Modeling Data - Other Aspects of Database Design

Unit V Relational Database Design: Features of Good Relational Designs - Atomic Domains and First Normal Form - Decomposition Using Functional Dependencies - Functional- Dependency Theory - Decomposition Using Functional Dependencies - Decomposition Using Multivalued Dependencies-More Normal Forms - Database-Design Process Text Book:

1. Database System Concepts, Sixth edition, Abraham Silberschatz, Henry F. Korth, S.Sudarshan, McGraw-Hill-2010. Reference Books: 1 Database Systems: Models, Languages, Design and Application, Ramez Elmasri, Pearson Education 2014

1.1 What is Management System?

1. A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data.

2. This is a collection of related data with an implicit meaning and hence is a database.
3. The collection of data, usually referred to as the database , contains information relevant to an enterprise.
4. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.
5. By data, we mean known facts that can be recorded and that have implicit meaning. 1.2 Database System Applications: 1.2.1 Enterprise Information
6. Sales: For customer, product, and purchase information.
7. Accounting: For payments, receipts, account balances, assets and other accounting information. 3. Human resources : For information about employees, salaries, payroll taxes, and benefits, and for generation of paychecks.
8. Manufacturing: For management of the supply chain and for tracking production of items in factories, inventories of items in warehouses and stores, and orders for items.
9. Online retailers: For sales data noted above plus online order tracking , generation of recommendation lists, and maintenance of online product evaluations. 1.2.2 Banking Finance
10. Banking: For customer information, accounts, loans, and banking transactions.
11. Credit card transactions: For purchases on credit cards and generation of monthly statements.
12. Finance: For storing information about holdings, sales, and purchases of financial

instruments such as stocks and bonds; also for storing real-time market data to enable online trading by customers and automated trading by the firm. 1.2.3 Others

1. Universities: For student information, course registrations, and grades (in addition to standard enterprise information such as human resources and accounting ).
2. Airlines: For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner.
3. Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks. 1.3 Purpose of Database Systems Database systems arose in response to early methods of computerized management of commercial data.
4. Data redundancy and inconsistency: Since different programmers create the files and application programs over a long period, the various files are likely to have different structures and the programs may be written in several programming languages
5. Difficulty in accessing data : Suppose that one of the university clerks needs to find out the names of all students who live within a particular postal-code area.
6. Data isolation : Because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult.
7. Integrity problems : The data values stored in the database must satisfy certain types of consistency constraints.
8. Atomicity problems : A computer system, like any other device, is subject to failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure.
9. Concurrent-access anomalies : To guard against this possibility, the system must maintain some form of supervision. But supervision is difficult to provide because data may be accessed by many different application programs that have not been coordinated previously.
10. Security problems: Not every user of the database system should be able to access all the data.

1. Physical level (or Internal View / Schema) :
   1. The lowest level of abstraction describes how the data are actually stored.
   2. The physical level describes complex low-level data structures in detail.
2. Logical level (or Conceptual View / Schema) :
   1. The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data.
   2. The logical level thus describes the entire database in terms of a small number of relatively simple structures. Although implementation of the simple structures at the logical level may involve complex physical-level structures , the user of the logical level does not need to be aware of this complexity. This is referred to as physical data independence.
3. View level (or External View / Schema):
   1. The highest level of abstraction describes only part of the entire database.
   2. Even though the logical level uses simpler structures , complexity remains because of the variety of information stored in a large database 1.5 Instances and Schemas: 1.5.1 Schemas 1. The overall design of a database is called schema.
4. A database schema is the skeleton structure of the database. It represents the logical view of the entire database. Fig 1.2 : Levels of Abstraction in DBMS

3. A schema contains schema objects like table, foreign key, primary key, views, columns, data types, stored procedure, etc.
4. A database schema can be represented by using the visual diagram. That diagram shows the database objects and relationship with each other.
5. A database schema is designed by the database designers to help programmers whose software will interact with the database. The process of database creation is called data modeling. A database schema can be divided broadly into two categories −  Physical Database Schema − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.  Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints. 1.5.2 Instances 1. The data which is stored in the database at a particular moment of time is called an instance of the database.
6. Database schema is the skeleton of database.

5. Rename: It is used to rename an object.
6. Comment: It is used to comment on the data dictionary. These commands are used to update the database schema that's why they come under Data definition language. 1.6.2 Data Manipulation Language (DML): It is used for accessing and manipulating data in a database. It handles user requests. Here are some tasks that come under DML:
7. Select: It is used to retrieve data from a database.
8. Insert: It is used to insert data into a table.
9. Update: It is used to update existing data within a table.
10. Delete: It is used to delete all records from a table.
11. Merge: It performs UPSERT operation, i.e., insert or update operations.
12. Call: It is used to call a structured query language or a Java subprogram.
13. Explain Plan: It has the parameter of explaining data.
14. Lock Table: It controls concurrency. 1.6.3 Data Control Language (DCL): 1. It is used to retrieve the stored or saved data.
15. The DCL execution is transactional.
16. It also has rollback parameters. Here are some tasks that come under DCL: o Grant: It is used to give user access privileges to a database. o Revoke: It is used to take back permissions from the user. There are the following operations which have the authorization of Revoke: CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

1.6.4 Transaction Control Language

1. TCL is used to run the changes made by the DML statement.
2. TCL can be grouped into a logical transaction. Here are some tasks that come under TCL: o Commit: It is used to save the transaction on the database. o Rollback: It is used to restore the database to original since the last Commit. 1.7 Data Models:
3. Data models define how the logical structure of a database is modeled.
4. Data Models are fundamental entities to introduce abstraction in a DBMS.
5. Data models define how data is connected to each other and how they are processed and stored inside the system.
6. There are 2 model available a. ER Model b. Relational Model 1.7.1 ER model 1. ER model stands for an Entity-Relationship model.
7. It is a high- level data model.
8. This model is used to define the data elements and relationship for a specified system.
9. It develops a conceptual design for the database.
10. It also develops a very simple and easy to design view of data.
11. In ER modeling, the database structure is portrayed as a diagram called an entity- relationship diagram.

These concepts are explained below. What is ER Modeling? A graphical technique for understanding and organizing the data independent of the actual database implementa tion Component of ER Diagram:

1. Entity: 1. An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles. 2. Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity a. Weak Entity 1. An entity that depends on another entity called a weak entity. 2. The weak entity doesn't contain any key attribute of its own. The weak entity is represented by a double rectangle. 2. Attribute The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute. For example, id, age, contact number, name, etc. can be attributes of a student. a. Key Attribute The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is represented by an ellipse with the text underlined. b. Composite Attribute An attribute that composed of many other attributes is known as a composite attribute. The composite attribute is represented by an ellipse, and those ellipses are connected with an ellipse.

Cardinality defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set.

1. One-to-one − One entity from entity set A can be associated with at most one entity of entity set B and vice versa.
2. One-to-many − One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.
3. Many-to-one − More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A. One-to-one One-to-Many Many - to- one

4. Many-to-many − One entity from A can be associated with more than one entity from B and vice versa. OBJECT-BASED DATA MODEL.
5. Object-oriented programming (especially in Java, C++, or C#) has become the dominant software-development methodology. 2. This led to the development of an object- oriented data model that can be seen as extending the E-R model with notions of encapsulation, methods (functions), and object identity. SEMI-STRUCTURED DATA MODEL.
6. The semi-structured data model permits the specification of data where individual data items of the same type may have different sets of attributes.
7. This is in contrast to the data models mentioned earlier, where every data item of a particular type must have the same set of attributes.
8. The Extensible Markup Language (XML) is widely used to represent semi- structured data. Many - to- Many