DBMS-NOTES-UNIT-1.docx

**Database Management System** **UNIT 1** Question carrying 2 marks. 1. Expand and define DBMS. 2. What are the primary goals of a DBMS? 3. List any 2 applications of DBMS. 4. List any 2 characteristics of DBMS. 5. Who are the database users? 6. Who are the workers behind the scene? 7...

**Database Management System** **UNIT 1** Question carrying 2 marks. 1. Expand and define DBMS. 2. What are the primary goals of a DBMS? 3. List any 2 applications of DBMS. 4. List any 2 characteristics of DBMS. 5. Who are the database users? 6. Who are the workers behind the scene? 7. What is a data model? 8. Name the categories of data model. 9. Explain hierarchical data model. 10. Explain network database model. 11. Explain relational data model. 12. Explain object oriented database model. 13. What is schema? Explain with example. 14. Name the different levels of 3 schema architecture. 15. Explain internal schema. 16. Explain conceptual schema. 17. Explain external schema. 18. What is data independence? Name the types. 19. What is database language? Name the types. 20. List out the different classification of DBMS. 21. What are the classifications of DBMS based on data model? 22. What are the classifications of DBMS based on number of users? 23. What are the classifications of DBMS based on data distribution (sites)? 24. Explain ER model. 25. Name the components of ER Diagram. 26. What is an entity? Explain with example. 27. What is a weak entity? Explain with example. 28. What is an attribute? Explain with example. 29. What is a relationship? Name the types of relationship. 30. Name the kinds of entities. 31. Name the different types of attributes. 32. What is degree of relationship? Name the types. 33. What is ER diagram? Name the components. 1. **Explain DBMS applications.** **[Introduction to database systems:]** - A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. - The collection of data, usually referred to as the database, contains information relevant to an enterprise. - The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. - Database systems are designed to manage large bodies of information. Management of data involves both defining structures for storage of information and providing mechanisms for the manipulation of information. - In addition, the database system must ensure the safety of the information stored, despite system crashes or attempts at unauthorized access. If data are to be shared among several users, the system must avoid possible anomalous results. **[Database-System Applications]** 1. **Enterprise Information** 2. **Banking and Finance** 3. **Universities**: 4. **Airlines:** 5. **Telecommunication:** 2. **Explain the characteristics of DBMS.** **[Characteristics of DBMS:]** - **Real-world entity** − A modern DBMS is more realistic and uses real-world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use students as an entity and their age as an attribute. - **Relation-based tables** − DBMS allows entities and relations among them to form tables. A user can understand the architecture of a database just by looking at the table names. - **Isolation of data and application** − A database system is entirely different than its data. A database is an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process. - **Less redundancy** − DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Normalization is a mathematically rich and scientific process that reduces data redundancy. - **Consistency** − Consistency is a state where every relation in a database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of data storing applications like file-processing systems. - **Query Language** − DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data. Traditionally it was not possible where file-processing system was used. 3. **Explain the different Database users.** **[Database users:]** 1. **Database Administrators:** - In any organization where many people use the same resources, there is a need for a chief administrator to oversee and manage these resources. - In a database environment, the primary resource is the database itself, and the secondary resource is the DBMS and related software. Administering these resources is the responsibility of the database administrator (DBA). - The DBA is responsible for authorizing access to the database, coordinating and monitoring its use and acquiring software and hardware resources as needed. - The DBA is accountable for problems such as security breaches and poor system response time. 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. - These tasks are mostly undertaken before the database is actually implemented and populated with data. It is the responsibility of database designers to communicate with all prospective database users in order to understand their requirements and to create a design that meets these requirements. - The final database design must be capable of supporting the requirements of all user groups. 3. **End Users:** - End users are the people whose jobs require access to the database for querying, updating, and generating reports; the database primarily exists for their use. There are several categories of end users: Casual end users: occasionally access the database, but they may need different information each time. They use a sophisticated database query language to specify their requests and are typically middle- or high-level managers or other occasional browsers. Naive or parametric end users: make up a sizable portion of database end users. Their main job function revolves around constantly querying and updating the database that have been carefully programmed and tested. Employees at receiving stations for shipping companies enter package identifications via bar codes and descriptive information through buttons to update a central database of received and in-transit packages. Sophisticated end users include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with the facilities of the DBMS in order to implement their own applications to meet their complex requirements. Standalone users maintain personal databases by using ready-made program packages that provide easy-to-use menu-based or graphics-based interfaces. 4. **System Analysts and Application Programmers (Software Engineers):** - System analysts determine the requirements of end users, especially naive and parametric end users, and develop specifications for standard canned transactions that meet these requirements. 5. **Workers behind the Scene:** - DBMS system designers and implementers design and implement the DBMS modules and interfaces as a software package. The DBMS must interface with other system software such as the operating system and compilers for various programming languages. - Tool developers design and implement tools---the software packages that facilitate database modeling and design, database system design, and improved performance. Tools are optional packages that are often purchased separately. They include packages for database design, performance monitoring, natural language or graphical interfaces, prototyping, simulation, and test data generation. In many cases, independent software vendors develop and market these tools. - Operators and maintenance personnel (system administration personnel) are responsible for the actual running and maintenance of the hardware and software environment for the database system. **\ ** 4. **Explain workers behind the scene.** - DBMS system designers and implementers design and implement the DBMS modules and interfaces as a software package. The DBMS must interface with other system software such as the operating system and compilers for various programming languages. - Tool developers design and implement tools---the software packages that facilitate database modeling and design, database system design, and improved performance. Tools are optional packages that are often purchased separately. They include packages for database design, performance monitoring, natural language or graphical interfaces, prototyping, simulation, and test data generation. In many cases, independent software vendors develop and market these tools. - Operators and maintenance personnel (system administration personnel) are responsible for the actual running and maintenance of the hardware and software environment for the database system. 5. **Explain the different categories of data models.** **[Data Models:]** A data model is a collection of concepts that can be used to describe the structure of a database. Most data models also include a set of basic operations for specifying retrievals and updates on the database. **[Categories of Data Models]** High-level or conceptual data models provide concepts that are close to the way many users perceive data. Low-level or physical data models provide concepts that describe the details of how data is stored on the computer storage media. 1. Hierarchical data model: C:\\Users\\LAB2\\Desktop\\Untitled.png 2. Network database model:  3. Relational Data Model: 4. Object Oriented database model: 6. **Explain database schema.** **[Database Schema:]** The description of a database, which is specified during database design and is not expected to change frequently, is called the database schema. A displayed schema is called a schema diagram. We call each object in the schema---such as STUDENT or COURSE---a schema construct. A schema diagram displays only some aspects of a schema, such as the names of record types and data items, and some types of constraints. Other aspects are not specified in the schema diagram. The data in the database at a particular moment in time is called a **database state** or **snapshot.** It is also called the current set of occurrences or instances in the database. Every time we insert or delete a record or change the value of a data item in a record, we change one state of the database into another state. The distinction between database schema and database state is very important. When we define a new database, the corresponding database state is the empty state with no data. We get the initial state of the database when the database is first populated or loaded with the initial data. From then on, every time an update operation is applied to the database, we get another database state. 7. **Explain 3- schema architecture.** **[Three-Schema Architecture]** The goal of the three-schema architecture is to separate the user applications from the physical database. 1. The internal schema is also known as a **physical schema**. 2. 3. An external schema is also known as view schema. The view schema describes the end user interaction with database systems. 8. **Explain the different database languages.** **[Database languages:]** A Database Management System has all the required interfaces and languages for the purpose of expressing the updates and queries in the database. These database languages can be used for reasons like storing, reading, and updating the data in the database. 1. - DDL is an acronym for Data Definition Language. This language is used for defining pattern or database structure. - The creation of schema, indexes, tables, etc. are made using Data Definition Language. - DDL is crucial in creating the basic framework of the database. - The information of metadata is stored using the Data Definition Language. This metadata includes numbers of the schemas and tables, indexes, relations, among other things. - The tasks that come under Data Definition Language are: Create, Alter, Drop, Truncate, and Comment.   ***2.    [Data Manipulation Language]*** - Data Manipulation Language can be shortened to DML. - It accesses and manipulates the data in a database. It also handles the user request. - Some tasks which come under the Data Manipulation Language are: Select, Insert, Merge, and Explain plan, Lock Table. ***3.    [Data Control Language]*** - Data Control Language is also called DCL in short. It is used for the retrieval of stored data in the database. - Since the Data Control Language is transactional, it has parameters for rollback. ***4.    [Transaction Control Language]*** - The changes made by the Data Manipulation Language are run by the Transactional Control Language. Transaction Control Language can be grouped together in a logical transaction. - Tasks which come under Transaction Control Language are: - **Commit:** This task is used for saving a particular transaction in the database. - **Rollback:** This task is used to restore all the data since the previous transaction. **\ ** 9. **Explain the different classification of DBMS.** **[Classification of DBMS:]** Database management systems can be classified based on several criteria, such as the data model, user numbers and database distribution. **Classification based on data model:** 1. **Relational data model:** The main data model used in many current commercial DBMSs like Oracle, MS SQL Server, DB2 and MySQL support this model. 2. The **object data model** has been implemented in some commercial systems but has not had widespread use. - This model is a database management system in which information is represented in the form of objects as used in object-oriented programming. - Object-oriented databases are different from relational databases, which are table-oriented. - Object-oriented database management systems (OODBMS) combine database capabilities with object-oriented programming language capabilities. **Classification based on number of users:** 1. ***Single-user database system***, which supports one user at a time. 2. ***Multiuser database system***, which supports multiple users concurrently. ***Classification based on sites (Database distribution):*** 1. Centralized systems: - With a *centralized database system*, the DBMS and database are stored at a single site that is used by several other systems too. 2. Distributed database system: - In a *distributed database system,* the actual database and the DBMS software are distributed from various sites that are connected by a computer network. 3. Homogeneous distributed database systems: - *Homogeneous distributed database systems* use the same DBMS software from multiple sites. Data exchange between these various sites can be handled easily. For example, library information systems by the same vendor. 4. Heterogeneous distributed database systems - In a *heterogeneous distributed database system,* different sites might use different DBMS software, but there is additional common software to support data exchange between these sites.  10. **Explain the components of E R diagram.** **[Entity Relationship modeling]** **[ER Model Concepts:]** - ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system. - It develops a conceptual design for the database. It also develops a very simple and easy to design view of data. - In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram. **For example,** suppose we design a school database. In this database, the student will be an entity with attributes like address, name, id, age, etc. The address can be another entity with attributes like city, street name, pin code, etc and there will be a relationship between them. **[Components of ER Diagram]**  **[1. Entity:]** An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles. Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity. a. **Weak Entity:** **[2. Attribute]** The attribute is used to describe the property of an entity. Ellipse 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. **c. Multivalued Attribute** An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute. **For example,** a student can have more than one phone number. **d. Derived Attribute** An attribute that can be derived from other attribute is known as a derived attribute. It can be represented by a dashed ellipse. **For example,** a person\'s age changes over time and can be derived from another attribute like Date of birth. **[3. Relationship]** A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship. Types of relationship are as follows: **a. One-to-One Relationship** When only one instance of an entity is associated with the relationship, then it is known as one to one relationship. **For example,** A female can marry to one male, and a male can marry to one female. **b. One-to-many relationship** When there is only one instance of the entity on the left and more than one instance of an entity on the right it associates with the relationship, then this is known as a one-to-many relationship. **For example,** Scientist can invent many inventions, but the invention is done by the only specific scientist. **c. Many-to-one relationship** When more than one instance of the entity on the left, and only 1 on the right it associates with the relationship then it is known as a many-to-one relationship. **For example,** Student enrolls for only one course, but a course can have many students. **d. Many-to-many relationship** When there is more than one instance of the entity on the left, and more than one instance of an entity on the right it associates with the relationship then it is known as a many-to-many relationship. **For example,** Employee can assign by many projects and project can have many employees. 11. **Explain the different types of attributes.** **[Attribute]** The attribute is used to describe the property of an entity. Ellipse 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. DBMS ER model concept **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.  **c. Multivalued Attribute** An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute. **For example,** a student can have more than one phone number. DBMS ER model concept **d. Derived Attribute** An attribute that can be derived from other attribute is known as a derived attribute. It can be represented by a dashed ellipse. **For example,** a person\'s age changes over time and can be derived from another attribute like Date of birth.  12. **Explain the different relationship types.** **[Relationship]** A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship. DBMS ER model concept Types of relationship are as follows: **a. One-to-One Relationship** When only one instance of an entity is associated with the relationship, then it is known as one to one relationship. **For example,** A female can marry to one male, and a male can marry to one female.  **b. One-to-many relationship** When there is only one instance of the entity on the left and more than one instance of an entity on the right it associates with the relationship, then this is known as a one-to-many relationship. **For example,** Scientist can invent many inventions, but the invention is done by the only specific scientist. DBMS ER model concept **c. Many-to-one relationship** When more than one instance of the entity on the left, and only 1 on the right it associates with the relationship then it is known as a many-to-one relationship. **For example,** Student enrolls for only one course, but a course can have many students.  **d. Many-to-many relationship** When there is more than one instance of the entity on the left, and more than one instance of an entity on the right it associates with the relationship then it is known as a many-to-many relationship. **For example,** Employee can assign by many projects and project can have many employees. DBMS ER model concept 13. **Explain the different degree of relationship.** **[Degree of Relationship]:** The number of participating entities in a relationship defines the degree of the relationship. There are different types of relationships based on the degree of relationship. 1. **[Unary Relationship (Recursive Relationship) -]** In unary relationship the entity has relationship with itself. It is also called recursive relationship. Unary relationship is with degree 1. **Ex:**  Each employee is managed by only one manager. 2. **[Binary Relationship:]-**A binary relationship exists when two entities are associated in a relationship. A binary relationship can be weak or strong based on the participating entities. Binary relationship is with degree 2. Ex:- C:\\Users\\LAB-4\\Downloads\\Untitled.png 3. **[Ternary relationship:] -**A simultaneous relationship that exists between instances of three entities is called ternary relationship. Ternary relationship is with degree 3.  - A Doctor writes one or more prescriptions. - A Patient may receive one or more prescriptions. - A Drug may appear in one or more prescriptions. - Prescription is an associative entity since many-to-many relationships exist between participating entities. 4. **[N-ary relationship:]** A relationship type of degree n is called n-ary relationship. C:\\Users\\LAB-4\\Downloads\\Untitled.png 14. **Explain cardinality ratio for binary relationships.** **[Cardinality and cardinality ratio:]** **[Cardinality:]** The number of entities in one entity set, which can be associated with the number of entities of other entity set via relationship set is called as cardinality. **[Cardinality ratio:]** Cardinality ratio tells us how many times the entity of an entity set participates in a relationship. **[Cardinality Ratios for Binary Relationships.]** The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in. For example, in the WORKS\_FOR binary relationship type, DEPARTMENT:EMPLOYEE is of cardinality ratio 1:N, meaning that each department can be related to any number of employees (N),but an employee can be related to at most one department (1). This means that for this particular relationship type WORKS\_FOR, a particular department entity can be related to any number of employees. On the other hand, an employee can be related to a maximum of one department. The possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1, and M:N. **1. One to one cardinality** - When a single instance of an entity is associated with a single instance of another entity, then it is called a one to one cardinality. **2. One-to-Many cardinality** - When a single instance of an entity is is associated with more than one instance of another entity then this type of relationship is called one to many relationships. **3. Many-to-one cardinality** - When entities in an entity set can participate only once in a relationship set and entities in another entity can participate more than once in the relationship set, then such type of cardinality is called many-to-one. **4. Many-to-many cardinality** - Here  more than one instance of an entity is associated with more than one instance of another entity then it is called many to many relationships - In this cardinality, entities in all entity sets can **take participate any number of times in the relationship** cardinality is many to many.