DO_NOTES_FOR_INTERNAL_TEST.pdf

UNIT 1 INTRODUCTION TO DATABASE MANAGEMENT SYSTEM Data: the facts and figures that can be recorded in computer system and have some special meaning assigned to it is called data. Example: Data of a customer like name, mobile number, address, and produc...

UNIT 1 INTRODUCTION TO DATABASE MANAGEMENT SYSTEM Data: the facts and figures that can be recorded in computer system and have some special meaning assigned to it is called data. Example: Data of a customer like name, mobile number, address, and product purchased etc. Database: A database is a collection of related data items stored at one place. Example: College database stores information about students, teachers, classes, subjects (All related data) A database is nothing but set of data having some relation between them. Sample database structure, Student table sid Name class Address Course table cid cname Roomid Hours Table or Relation: Table is a collection of related records. Data is logically organized in a row- and-column format. Each row represents a unique record. The rows are called Tuples or Records. Record: Collection of related data items. sid Name class Address 1 Sahil FYBScIT Andheri 2 Poonam SYBAF Vashi The columns of this relation are called Fields or Attributes. Database Management System (DBMS) DBMS refers to the technology for creating and managing databases. DBMS is a software tool to organize (create, retrieve, update, and manage) data in a database. A Database Management System is a software system that helps in the process of defining, constructing, manipulating the database. It is also known as computerized record keeping system. Example: MySQL, ORACLE, SQL Server Purpose of Database Now a day’s data have become an integral part of the information systems of many organizations, so it is very important to manage data in the system. 1. Data as a Corporate Resource Data management and control is very important for efficient working of an organization. 2. Data Availability As most of the organizational functions are computerized there is increased need to keep data available for user. 3. Sharing Data As many users are accessing data simultaneously, sharing of data is required for improving availability of data. 4. Maintaining Complex Data As complexity of the data grows complex, relationships between them need to be managed in simpler way. 5. Data Consolidation. There should be fixed approach toward consolidation of data in any organizations. 6. Backend The database always remains on the back end, is never shown to the user. Hence database is known as Backend. Programming language is termed as FRONTEND. Characteristics of DBMS (Advantages) 1. Data Integrity Integrity constraints provide a way of ensuring that changes made to the database by authorized users do not result in a loss of data consistency and correctness. Example of incorrect data are as below: i) Student taking admission to branch which is not available in college. ii) Employee assigned with non-existing department. 2. Data Security Data in database should be given to only authorized users. Only authorized users should be allowed to modify the data. 3. Data Independence Data Independence can be defined as the capacity to change data kept at one place without changing data kept at other locations. 4. Transaction Control – Rollback The changes made in database can be reverted back with the help of rollback command. 5. Concurrency Control The Database management system allows so many users to access databases at the same time. Such operations are allowed by sharing same data between multiple users. 6. Data Recovery backup and restore Database recovery is the process of restoring the database to original state after database failure. Backup and recovery are the two main methods which allow users to protect the data from damage or loss. File Processing System In file processing system all the information is stored in various computer files. Initially the traditional file processing system was useful later the size of the data increased, the drawbacks also increased. Drawbacks of using file systems to store data 1. Data redundancy and inconsistency Multiple file formats, duplication of information in different files. Inconsistency– means different copies of the same data are not matching. Example Raj DBMS=50 in one file, Raj DBMS=60 in another file. 2. Difficulty in accessing data Need to write a new program to carry out each new task. Employee salary>20000, two ways execute this. One way is hand picking second way is writing program. But next time if you want to retrieve different data again you need to write new program. 3. Data isolation - multiple files and formats It is difficult to store the entire data in a single file so it is distributed in different files. These files may be in different formats, difficult to write application programs to access the desired data from these files. 4. Integrity problems Integrity constraints (e.g. account balance > 0) become part of program code Hard to add new constraints or change existing ones 5. Atomicity of updates Failures may leave database in an inconsistent state with partial updates carried out. E.g. transfer of funds from one account to another should either complete or not happen at all. 6. Concurrent access by multiple users Concurrent access needed for performance Uncontrolled concurrent accesses can lead to inconsistencies. E.g. two people reading a balance and updating it at the same time. 7. Security Problem It is very difficult to enforce security checks and access rights in a traditional File Systems. What if we want to give access to only few records in the file? Not possible with File systems. 8. Poor Data Control File System does not have centralized data control, it is de-centralized. The same field may have different names in files of different departments of an organization. File System Vs Database System 1. Redundancy can be reduced Data is not stored in more than one location. Repetition of information can be avoided which in turn saves storage space. 2. Inconsistency can be avoided With the usage of database, it is assured that all the users access actual data present in the database. 3. Data can be shared Multiple users can login at a time into the database to access information. 4. Standards can be enforced Rules and regulations for coding and designing can be enforced on the database to regulate access to the database. 5. Security Restrictions can be applied Security is for restricting user access to the database server. Security of data is very crucial and needs to be carefully handled. 6. Integrity can be maintained Through integrity, one can ensure only accurate data is stored within the database. 7. Data Independence can be provided None of the users need to know the technical aspects of the database to access it. They are physically as well as logically independent to access the database. Data Abstraction Data abstraction in a Database Management System (DBMS) refers to the process of hiding the complexity of the database system from the users and presenting a simplified view of the database. It allows users to interact with the data without needing to understand the intricate details of how the data is stored and maintained. There are three levels of data abstraction in DBMS: 1. Physical Level (Internal Level) 2. Logical Level (Conceptual Level) 3. View Level (External Level): Database Architecture (Three Tier Architecture) There are three level architecture for database. The goal of three schema architecture is to separate the front end and the back end. The description of the database is called database schema, which is specified during database design and it is not expected to change frequently. Fig: Three schema architecture There are mainly three levels: 1. Internal Level: Actual PHYSICAL storage structure and access paths. 2. Conceptual or Logical Level: Structure and constraints for the entire database 3. External or View level: Describes various user views Internal/Physical Level The internal schema is the lowest level of data abstraction It describes how the data is stored in the physical memory. Physical memory may be hard disk, magnetic tapes etc. You can get the complex data structure details at this level. Used by the DBA Example: create table Employee { Sno number(15), Name varchar2(20), Age number, Salary number(10,2), Mobileno number }; Conceptual Level/ Logical Level This is the middle level of 3-tier architecture. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, etc. Describes what data is stored. Used by designers, programmers Example Blue print External Schema/View Level Highest level of data abstraction, Low complexity. Describes the part of the database which specific user is interested in. It hides the unrelated details of the database from the user. There may be "n" number of external views for each database. Data Independence Concept of data independence can be explained with the help of three schema architecture. Definition of Data Independence: it is the capacity to change one level of schema without changing the schema at the next higher level. There are two types 1. Logical data independence 2. Physical data independence Logical Data Independence: it is the capacity to change the conceptual or logical schema without any changes to external schemas. Separating the external views from the conceptual view enables us to change the conceptual view without affecting the external views. This separation is called logical data independence. Example: if we change the conceptual schema by removing a data item. In this case the external schemas that refer to the remaining data should not be affected. Physical Data Independence: the ability to change the internal schema without changing conceptual or logical schema. Example: using new storage devices (hard sidk1 to hard disk2), using different data structures etc. Following is the diagram of data independence Database Model It is a conceptual representation of data, including the relationships and constraints that govern the data. It is used to show how the data is stored, updated and accessed. Data model will give you an idea how your final system or software will look like after development is completed. This concept is exactly like real world modelling in which before constructing any building, bridges engineers create a model for it, and likewise data model gives you an idea about how your project will look like after completion. Types of Data Models 1. Relational Model 2. Hierarchical Model 3. Network database Model 4. Entity Relationship Model 5. Object Oriented Data Model Relational Model ▪ This concept was introduced by Dr. E.F.Codd. ▪ It is based on Relational Model, which stores data in the form of tables. ▪ Data is arranged systematically into rows and columns in the form of relation or table ▪ In every cell there is one and only one value, which is known as scalar value. Attribute RollNo Sub1 Sub2 10 DBMS DS Tuple ▪ Here RollNo sub1, sub2 are Attributes or fields. ▪ Each column should have distinct name ▪ All values entered in the column are of same data type Relational Model Concepts ▪ Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., RollNo,sub1 etc.  ▪ Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes. ▪ Tuple – It is nothing but a single row of a table, which contains a single record. ▪ Relation Schema: A relation schema represents the name of the relation with its attributes. ▪ Degree: The total number of entities participating in the relation is called the degree of the relation. ▪ Cardinality: Total number of rows present in the Table. Hierarchical Model ▪ A Data Model in which the data is organized into a tree structure is known as hierarchical model ▪ In this model each entity has only one parent but can have several children. ▪ At the top of the hierarchy there is only one entity which is called the ROOT ▪ This type of model is not in use currently ▪ This Model was used by IBM’s Information Mgt Systems in the 60s and 70s. Advantages of Hierarchical Model * It promotes data sharing * Simple * Parent/ child relationship promotes data integrity * Database security is provided. * It is efficient with 1:M relationship  Disadvantages * Complex implementation * Difficult to manage. * Lack of structural independence * Extensive programming efforts. Network Database Model ▪ Data is stored in a graph like structure ▪ It is an extended type of hierarchical model. ▪ This model is also represented as hierarchical but any child in the tree can have multiple parents. ▪ Each node can be accessed through several paths Advantages of Network Model * Simple and easy to design * It can handle 1:M and M:M relationship * Data access is flexible. Disadvantages * System complexity * Query access is difficult * Maintenance and handling is difficult. Entity Relationship Model ▪ ER model is based on their Attributes  ▪ An Entity can be real world thing having properties called Attributes. ▪ An Entity can be a logical concept like house sale, car service ▪ Entities are linked to each other through Relationship. Basic Building Blocks Entity: is a real-life object. Example: student, Faculty, Hospital, Library etc. Attributes: various properties that describe an entity are known as attributes. Example: For student entity Rollno, name, class are the attributes. Relationships: is an association among several entities. We use diamond shape to illustrate relationships. It is to be read from left to right. Example: Employee works for Department Advantages * It is simple and easy to design. * Ease of representation, everybody can understand easily. * Integrated with relational databases. Disadvantages * Limited relationship representation. (Relationship between attributes of same entity cannot be represented) * No DML, meaning ER model does not have any language by which we can insert data in database. Object oriented Database Model ▪ It is an extension of ER model  ▪ In this model both the data and relationship are present in a single structure known as an object. ▪ Two or more objects are connected through links. ▪ All the data and relationship of each object are contained as a single unit. ▪ The attributes like Name, job title of the Employee and the methods are stored as a single object. ▪ Two objects are connected through a common attribute i.e, Deptid Advantages * It is easy maintain and modify. * Object oriented features provide a clear modular structure which is good for defining abstract data types where internal implementation details are hidden. Disadvantage * Practically very complex and inapplicable many a times. Weak Entity An entity type should have a key attribute which uniquely identifies each entity in the entity set, but there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type. The entity sets which have a primary key are known as strong entity sets. As the weak entities do not have any primary key, they cannot be identified on their own, so they depend on some other entity (known as owner entity). The relation between one strong and one weak entity is represented by double diamond. Difference between Strong and Weak Entity S.NO STRONG ENTITY WEAK ENTITY While weak entity has 1. Strong entity always has primary key. partial discriminator key.  Strong entity is not dependent of any Weak entity is depending 2. other entity. on strong entity. Strong entity is represented by single Weak entity is represented 3. rectangle. by double rectangle. While the relation between Two strong entity’s relationship is one strong and one weak 4. represented by single diamond. entity is represented by double diamond. Strong entity has either total While weak entity always 5. participation or not. has total participation.