BCA Semester III Database Management System PDF

Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Table of Contents Module No. Module Name Content Data, information, database, database...

Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Table of Contents Module No. Module Name Content Data, information, database, database users Role of DBA Three levels architecture of DBMS Mappings Fundamentals of database I & II Concept of data definition language concept & Data Model Concept of data manipulation language Entity-Relationship model, ER diagram Data, information, database, database users: Reference :Database Concept , Abraham Silberschatz,Henry F. Korth,S. Sudarshan,MGH publisher ,6th Edition.Chapter -1 Page 27-28. Role of DBA One of the main reasons for using DBMSs is to have central control of both the data and the programs that access those data. A person who has such central control over the system is called a database administrator (DBA). Or The DBA is a person or a group of persons responsible for overall control of the database system. The functions of a DBA include: Ø Schema definition -The DBA creates the original database schema by executing a set of data definition statements in the DDL. Ø Storage structure and access-method definition. Schema and physical-organization modification - The DBA carries out changes to the schema and physical organization to reflect the changing needs of the organization, or to alter the physical organization to improve performance. Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Granting of authorization for data access - By granting different types of authorization, the database administrator can regulate which parts of the database various users can access. The authorization information is kept in a special system structure that the database system consults whenever someone attempts to access the data in the system. Routine maintenance - Examples of the database administrator’s routine maintenance activities are: Periodically backing up the database, either onto tapes or onto remote servers, to prevent loss of data in case of disasters such as flooding. Ensuring that enough free disk space is available for normal operations, and upgrading disk space as required. Monitoring jobs running on the database and ensuring that performance is not degraded by very expensive tasks submitted by some use Three levels architecture of DBMS- Reference:Database Concept by Abraham Silberschatz,Henry F. Korth,S. Sudarshan,MGH publisher ,6th Edition.Chapter - 1 Page- 6-7. Mappings - Database Concept by Abraham Silberschatz,Henry F. Korth,S. Sudarshan,MGH publisher ,6th Edition.Chapter -7, Page-269-270. Concept of data definition language - Database Concept by Abraham Silberschatz,Henry F. Korth,S. Sudarshan,MGH publisher ,6th Edition.Chapter -1, Page-10-13. Concept of data manipulation language - Database Concept by Abraham Silberschatz,Henry F. Korth,S. Sudarshan,MGH publisher ,6th Edition.Chapter -1, Page-9-10. Entity-Relationship model, ER diagram - Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Q. Why ER model is needed for designing a database? Ans. ER model means Entity-Relationship model. Conceptual schema is usually built with the ER model. We take a brief example – suppose we are building a banking solution. One of the first thing we have to know is that in a bank there should be an Account, there should be Customers, there should be of course monitory transactions, ledger account books etc. All of these represent some kinds of data elements. These are nothing but Entities. Using ER model we describe all the entity types, relationships among entities and constraints for ease of understanding to communicate with the non-technical users. We need not think about any implementation details. This is only for human understanding. The database designer after getting this ER model converts it to the machine understandable internal schema. Q. Define the following: (i) Simple and Composite attribute (ii) Single Valued and Multi Valued attribute (iii) Stored and Derived attribute (iv) Complex attribute (v) NULL attribute Ans. Simple attribute: Attributes that cannot be divided into sub parts are called simple or atomic attribute. Example: Eno,age. Composite attribute: Attributes that are made by one or more simple or composite attributes. Example: Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Single Valued Attribute: A single valued attribute can have only a single value. For example a person can have only one “date of birth”, “age” etc. Single valued attribute can be simple (eg. age) or composite (eg. date of birth). Multi valued attributes: It can have multiple values. Example: Color_of_a_ peacock, Phone no. Stored attribute: An attribute that that supplies a value to the related attribute is called stored attribute. Example: date_of_birth. Derived attribute: An attribute whose value can be inferred from the value of other attribute is called derived attribute. Example: Age. Complex attribute: Attribute that is both composite and multi valued is called complex attribute. NULL Attribute: Attributes that do not have any applicable value is called NULL attribute. Actually they are missing or unknown values. Q. How multi valued attribute differs from composite attribute? Ans. In composite attribute such as Street, City, State, Zip all of these need not be the same type but in multi valued attribute all the different values we take are of same type. Degree of a relationship Unary Binary N-ary Example of Unary relationship Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Example of Binary relationship Example of N-ary relationship SOME IMPORTANT NOTES ON ER DIAGRAM 1. Constraints on Relationship Types ​ Constraints limit the set of possible combination of entities that can participate in the relationship type. ​ Two main kinds of constraints ​ Cardinality Constraints ​ Participation Constraints Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Cardinality Constraints 1:1 relationship 1:N or N: 1 relationship M: N relationship Participation Constraints Department Handles Project 1 N If every project has to be associated with a department, then the “existence” of an instance of Project entity type depends on the existence of an instance of the Handles relation type. A department may handle several different projects. Suppose there is a restriction (or constraint) that a project has no existence unless it is associated with the department. This constraint is called participation constraint. Above example shows total participation. Total participation is shown with a double line in the ER diagram. Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 2. Identifying Relationships Insurance Record is a weak entity type with no key. It has no existence without its association with entity type Employee. An employee is uniquely identified by PAN number (if he is a Tax payer) Let the company prepare an insurance records for each employee. However Insurance record has no existence or no meaning unless it is associated with some employee. Here the relationship Insurance Details is an “Identifying relationship. EER model (Enhanced ER Model) Subclasses or Inheritance ​ Subclasses means there is a “is-a” relationships ​ An entity class B is said to be a subclass of another entity class A, if it shares an “is-a” relationship with A. ​ Example-1: Car “is-a” Vehicle. ​ Example-2: Manager “is-an” Employee. ​ An entity of class B is said to be a specialization of entities of class A. Conversely entities of class A are called generalization of class B entities. Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 ​ If we notice it we can see that “is-a” relationship identifies a specialization of some particular entity type. For example, A car is a vehicle but not all vehicle is a car. So vehicle is more general class of car. ​ Each member of the subclass has the same attributes as the super class entities, and participates in the same relationship types. ​ Any key attribute which is uniquely identifies entities of a super class can also uniquely identify entities of the sub (special) classes. Example PAN is the key of an Employee as well as a Manager. ​ Special classes may have more attributes in addition to the key attributes of the super class. For example, a manager has one more identification i.e. in which department he is a manager. Specialization and Generalization ​ The process of creating subclasses out of a given entity type is called Specialization. Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 ​ The reverse process of taking two or more entity types and clubbing them under a common super class is called Generalization. In ER diagram it can be shown by the following two ways: Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Predicate-Defined Subclasses Disjoint Subclasses Subclass entity types whose members can be defined based on the value of an attribute are called “Predicate-Defined” subclasses. In the example above job-type is called the defining predicate. So, this is the case how entities of one specialized class distinguish with the other specialized class. ​ The “D” in the circle represents that the subclasses are disjoint. ​ An entity of type Employee may belong to at most one of the subclasses. ​ Here disjoint means they are mutually exclusive, i.e. no secretory is a professor and no professor is a secretory. ​ All secretory have to have job-type as admin and all professor have to have job-type as academic. So set of all secretaries are disjoint from set of all professor. Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 Overlapping Subclasses The “O” in the circle represents that the subclasses are overlapping. Subclasses that are not disjoint are said to overlap. Disjoint or overlap may be either partial or total. Aggregation ​ One limitations of the ER model is that it cannot express relationships among relationships. The solution is Aggregation. ​ Aggregation basically aggregates a particular ER schema and makes it into an Entity. Programme Name: BCA Semester III Course Name: Database Management System Course Code : BCA37107 Class : Academic Session: Odd sem 2024-25 ​ Here [Center-Course] is an aggregated entity which contains offers relationships. It is also called containment relationship.