Unit 1 Database Management System PDF

Unit: 01 BCA SEM : III Database Management System C.P.PATEL & F.H.SHAH COMMERCE COLLEGE (MANAGED BY SARDAR PATEL EDUCATION TRUST) BCA, BBA (ITM) & PGDCA PROGRAMM...

Unit: 01 BCA SEM : III Database Management System C.P.PATEL & F.H.SHAH COMMERCE COLLEGE (MANAGED BY SARDAR PATEL EDUCATION TRUST) BCA, BBA (ITM) & PGDCA PROGRAMME BCA SEM: III Database Management Systems Introduction to DBMS − Database and DBMS. − Basics of databases (Data, Information, field, record, file) − Database Management System advantages and disadvantages − Components of a DBMS − Data Models concepts: Hierarchical, Network and Relational − Relation data models concept − Terminologies: tuple, attribute, domain, relation − Relationships and relationship types − Dr. E.F.Codd Rules − Keys: super key, candidate key, primary key, alternate key, foreign key  BASIC CONCEPT: The data can be anything E.g. no., name, person, city, etc. Meaningful data is called the information “Database is an organized collection of information.” “Database is an collection of related information so that it is available to many users for different purpose.” Only organized information is called the database. The contain of the database is obtain by combining data from the different source in an organization. So that data are available to all user and redundant data can be eliminated or at least minimized. The database can be used for  Retrieving the desired information  Taking meaningful decision  Recognizing information  Processing information Example of database are Telephone Directory ,Dictionary, Personal department database, Superstore database, Customer database. Database can handle business inventory, accounting filling and use that information to prepare summaries, estimates and reports. Typical Database operations are Adding Modifying Arranging Removing information. The Management of data in database system is done by means of a general purpose software package called DBMS Some commercial available DBMS are FOXPRO, ORACALE, INGRESS, and SQL, MS-Access DBMS is a combination of hardware and software can be use to setup and monitor a database. It manage information stored in it. Compiled by : Mr.Shani Patel 1 Unit: 01 BCA SEM : III Database Management System  Data A data is the terms used to describe basic facts about the activities of business. Example: No. of our working amount and type of material, no of person etc.  Information Information is obtained by assembling items of data into meaningful form. e.g. Payroll, affiance report etc.  Record The physical representation of entity set is made by aggregating the attributes to model the entity set, such a representation is called Record.  Database File Database file is the collection of related records.  Fields In DBMS terminology the column under which the information in the record are stored is called the field.  Database It is organized collection of related information OR It is an integrate set of interrelated data.  ADVANRAGES OF DBMS 1. Data Security: The more accessible and usable the database, the more it is prone to security issues. As the number of user’s increases, the data transferring or data sharing rate also increases thus increasing the risk of data security. It is widely used in the corporate world where companies invest large amounts of money, time, and effort to ensure data is secure and used properly. A Database Management System (DBMS) provides a better platform for data privacy and security policies thus, helping companies to improve Data Security. 2. Data integration: Due to the Database Management System we have access to well-managed and synchronized forms of data thus it makes data handling very easy and gives an integrated view of how a particular organization is working and also helps to keep track of how one segment of the company affects another segment. 3. Data abstraction: The major purpose of a database system is to provide users with an abstract view of the data. Since many complex algorithms are used by the developers to increase the efficiency of databases that are being hidden by the users through various data abstraction levels to allow users to easily interact with the system. 4. Reduction in data Redundancy: When working with a structured database, DBMS provides the feature to prevent the input of duplicate items in the database. for e.g. – If there are two same students in different rows, then one of the duplicate data will be deleted. Compiled by : Mr.Shani Patel 2 Unit: 01 BCA SEM : III Database Management System 5. Data sharing: A DBMS provides a platform for sharing data across multiple applications and users, which can increase productivity and collaboration. 6. Data consistency and accuracy: DBMS ensures that data is consistent and accurate by enforcing data integrity constraints and preventing data duplication. This helps to eliminate data discrepancies and errors that can occur when data is stored and managed manually. 7. Data organization: A DBMS provides a systematic approach to organizing data in a structured way, which makes it easier to retrieve and manage data efficiently. 8. Efficient data access and retrieval: DBMS allows for efficient data access and retrieval by providing indexing and query optimization techniques that speed up data retrieval. This reduces the time required to process large volumes of data and increases the overall performance of the system. 9. Concurrency and maintained Atomicity: That means, if some operation is performed on one particular table of the database, then the change must be reflected for the entire database. The DBMS allows concurrent access to multiple users by using the synchronization technique. 10. Scalability and flexibility: DBMS is highly scalable and can easily accommodate changes in data volumes and user requirements. DBMS can easily handle large volumes of data, and can scale up or down depending on the needs of the organization. It provides flexibility in data storage, retrieval, and manipulation, allowing users to easily modify the structure and content of the database as needed.  DISADVANTAGES OF DBMS 1) Cost  A significant disadvantage of DBMS is cost.  The cost of development or purchasing the software is very high.  Hardware has to be upgrade to allow for the extensive programs and the work spaces required for their execution and storage. 2) The DBMS Processing Overhead The processing overhead introducing the DBMS to implement security, integrity and sharing of data cause a degradation of the response and through-put times. 3) Migration Cost Migrated cost from a traditionally separate environment to an integrated one. 4) Requirement of Backup Facilities Since duplication is less in DBMS, appropriate backup facilities must be provided because in case of system crash the data must be recovered. Compiled by : Mr.Shani Patel 3 Unit: 01 BCA SEM : III Database Management System  COMPONENT OF DBMS The major components of DBMS are follows. Naïve User Casual User DBA Telecom Telecom Telecom System System System Compiled User Query DDL Compiler Interface Processor Batch Compiled DBMS and its User Application Data Manager Program OS or own File Manager OS Disk Manager Data files and Data Dictionary 1) Data Definitions Computer  The DDL compiler converts the data definition statement a set of table.  These table contain the data are in a form that can be use by other components of the DBMS. 2) Data Manager  The data manager is the central component of the DBMS.  It is referred a Database control system  It’s faction is to convert operation in the user’s quarries coming directly via the query processor or indirectly via an application program from the user’s logical view to a physical file system.  Data manager is responsible to maintain data integrity, Data consistency, Data security. It is also responsible for interfacing with the file system. Compiled by : Mr.Shani Patel 4 Unit: 01 BCA SEM : III Database Management System  Synchronizing the simultaneous operations performed by the concurrent users is under the control of the data manager.  It is also entrusted with backup and recovery. 3) File Manger  File manager is responsible for the structure of file.  It managing the space required on the disk.  It also responsible for locating the block containing the required record to the, record to the data manager. 4) Disk Manager  The Disk Manger is part of the operating system of the host computer.  All physical input and output operation by it.  The Disk manager transfer the block or pages request by the file manager so that the letter need not be concerned with the physical characteristic of the underlying storage media. 5) Query Processor  The Database Manager can be retrieve the data by formulating a query in the data manipulation provided with the data.  The query processor is used to interpret the online user’s query and convert it into an efficient series of operation in a form capable of being sent to the data manager for execution.  The query processor user the data dictionary to find the structure of database.  It uses the information in modifying query and preparing an optimal plan to access the database. 6) Telecommunication system  Online user of computer weather remote or local communication with it by sending and receiving message over communication line.  These message are routed via an independent software system called a telecommunication system or co medication control program.  The telecommunication is not part of the DBMS but DBMS work closely with system. 7) Data file  Data files contain the data portion of the database. 8) Data Dictionary  Information pertaining to the structure and usage of the data contained in the database in a data dictionary.  The data dictionary which is database itself documents the data.  Each database user can consult the data dictionary to learn what each piece of data and various synonym ms of the data field means.  In an integrated system the data dictionary stores information concerning the external, conceptual and internal level of database.  It contain the source of each data field value, the frequency of its use, and an audit trail conceding update, including who and when of each update. Compiled by : Mr.Shani Patel 5 Unit: 01 BCA SEM : III Database Management System  INTRODUCTION TO DATA MODELS  A data model is a picture or description which depicts how data is to be arranged to serve a specific purpose.  The data model depicts what that data items are required, and how that data must look.  Some data models, which data records are connected or related within a file structure. These are called record or structural data models.  Some data models are used to identify the subjects of corporate data processing - these are called entity-relationship data models.  Still another type of data model is used for analytic purposes to help the analyst to solidify the semantics associated with critical corporate or business concepts.  Advantages of Data Models  Data Models help us in representing data accurately.  It helps us in finding the missing data and also in minimizing Data Redundancy.  Data Model provides data security in a better way.  The data model should be detailed enough to be used for building the physical database.  The information in the data model can be used for defining the relationship between tables, primary and foreign keys, and stored procedures.  Disadvantages of Data Models  In the case of a vast database, sometimes it becomes difficult to understand the data model.  You must have the proper knowledge of SQL to use physical models.  Even smaller change made in structure require modification in the entire application.  There is no set data manipulation language in DBMS.  To develop Data model one should know physical data stored characteristics. DATA MODELS can be classify…. 1. Hierarchical 2. Network 3. Relational 1) Hierarchical FACULTY Table STUDENT Table Name Dep Course-taught Name Course-enroll Grade John CSE CA Gami CA 2.0 Jake CSE SE Mary SE 3.0 Royal CSE DBMS Mayen SE 4.0 Compiled by : Mr.Shani Patel 6 Unit: 01 BCA SEM : III Database Management System  The hierarchical data model organizes data in a tree structure.  There is a hierarchy of parent and child data segments.  This structure implies that a record can have repeating information, generally in the child data segments.  Data in a series of records, which have a set of field values attached to it. It collects all the instances of a specific record together as a record type.  These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows.  To create links between these record types, the hierarchical model uses Parent Child Relationships.  These are a 1:N mapping between record types.  i.e. it represent a one-to-many relationship between two entities where the two are respectively parent and child  For example, an organization might store information about an employee, such as name, employee number, department, salary.  The organization might also store information about an employee's children, such as name and date of birth. The employee and children data forms a hierarchy, where the employee data represents the parent segment and the children data represents the child segment.  If an employee has three children, then there would be three child segments associated with one employee segment.  In a hierarchical database the parent-child relationship is one to many. This restricts a child segment to having only one parent segment.  Hierarchical DBMSs were popular from the late 1960s, with the introduction of IBM's Information Management System (IMS) DBMS, through the 1970s. 2. Network Model Compiled by : Mr.Shani Patel 7 Unit: 01 BCA SEM : III Database Management System  In the above figure, member TWO has only one owner ‘ONE’ whereas member FIVE has two owner’s i.e, TWO and THREE. Here, each link between the two record types represents 1 : M relationship between them. This model consists of both lateral and top-down connections between the nodes. Therefore, it allows 1: 1, 1 : M, M : N relationships among the given entities which helps in avoiding data redundancy problems as it supports multiple paths to the same record  The popularity of the network data model matched with the popularity of the hierarchical data model.  Some data were more naturally displayed with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data.  In 1971, the Database Task Group of the Conference on Data Systems Languages (DBTG / CODASYL) formally defined the network model. The basic data modeling construct in the network model is the set construct.  A set consists of an owner record type, a set name, and a member record type.  A member record type can have that role in more than one set, hence the multi parent concept is supported.  An owner record type can also be a member or owner in another set.  The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them.  Thus, the complete network of relationships is represented by several pair wise sets; in each set some (one) record type is owner (at the tail of the network arrow) and one or more record types are members (at the head of the relationship arrow).  Usually, a set defines a 1:M relationship, although 1:1 is permitted. The CODASYL network model is based on mathematical set theory. 3. Relational Model  In relational model, it has the advantage of being simple in principle, user can express their queries in a powerful query language.  (RDBMS - relational database management system) A database based on the relational model developed by E.F. Codd.  A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organized in tables.  A table is a collection of records and each record in a table contains the same fields.  The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns, and each column has a unique name. Tables are also known as relations.  The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types. Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes.  The columns of the table correspond to the attributes of the record type. The relational data model is the most widely used data model, and a vast majority of current database systems are based on the relational model. Compiled by : Mr.Shani Patel 8 Unit: 01 BCA SEM : III Database Management System  Properties of Relational Tables:  Values Are Atomic(tiny)  Each Row is Unique  Column Values Are of the Same Kind  The Sequence of Columns is Insignificant  The Sequence of Rows is Insignificant  Each Column Has a Unique Name  Certain fields may be designated as keys, which mean that searches for specific values of that field will use indexing to speed them up.  Where fields in two different tables take values from the same set, a join operation can be performed to select related records in the two tables by matching values in those fields.  Often, but not always, the fields will have the same name in both tables.  RDBMS stands for….. Relational Database Management System.  Often RDBMS is referred just as “Database”.  RDBMS technology is mature now. That’s why it’s a good idea to use RDBMS for Strong AI development.  RDBMS examples:  MS SQL Server, Oracle, MySQL, MS Access, SyBase, …  Any of these RDBMS may serve as the main memory. THE RELATIONAL DATA MODEL  CONCEPT: - A relational database matches data by using common characteristics found within the data set. The resulting groups of data are organized and are much easier for many people to understand. For example, a data set containing all the real-estate transactions in a town can be grouped by the year each transaction occurred, the sale price, a buyer's last name and so on. Such a grouping uses the relational model (a technical term for this is schema). Hence, such a database is called a "relational database." The software used to do this grouping is called a relational database management system (RDBMS). The term "relational database" often refers to this type of software  TERMINOLOGY :- The term relational database was originally defined by and is attributed to Edgar Codd at IBM Almaden Research Center in 1970. Relational database theory uses a set of mathematical terms, which are roughly equivalent to SQL database terminology. The table below summarizes some of the most important relational database terms and their SQL database equivalents. Compiled by : Mr.Shani Patel 9 Unit: 01 BCA SEM : III Database Management System A relation is defined as a set of tuples that have the same attributes. A tuples usually represents an object and information about that object. Objects are typically physical objects or concepts. A relation is usually described as a table, which is organized into rows and columns. All the data referenced by an attribute are in the same domain and conform to the same constraints.  DOMAIN : A Domain is a pool of values from where one or more attributes (column/field) can draw their actual values. Ex. For above table domain name for this field is COUNTRY..if we takes values from country field.  TUPLE : According to the relational model , every relation or table is made of many Tuples.They are also called row/records.  ATTRIBUTES : The term Attributes refers to characteristics. The characteristics of the tuple are reflected by its attributes or fields.This simply means that the column contains will be defined by the attributes of that column. RELATIONSHIP AND RELATIONSHIP TYPES  A relationship can be defined as an association among ENTITIES.  An association of several entities in a r-y model is called relationship.  The relationship represents the fact that the LECTURER teaches several STUDENTS and the STUDENT, And STUDENT is taught by several LECTURERS. Compiled by : Mr.Shani Patel 10 Unit: 01 BCA SEM : III Database Management System ENTITIES AND RELATIONSHIP Three Types of Relationship exists among entities…. 1. One-to-One (1:1) 2. One-to-Many (1:M) 3. Many-to-Many (M:M) 1. One-to-One (1:1)  A One-to-One relationship is an association between two entities. For Ex. 1) In University each Department have one head of department. 2) More Over, One member cannot be a head of more than one department. 2. One-to-Many  A One-to-Many relationship exists when one entity is related to more than one entity. For Ex… 1) A father may have many children but, a child has only one father. 2) More than one subject is taught by one lecturer only. 3. Many-to-Many Relationship  A many-to-many relationship describes entities that may have many relations in both the directions. For Ex… 1) One customer may buy many items and one item may be bought by many customers. 2) Another Ex. Students can take many courses in university, and many students can register for given course. Compiled by : Mr.Shani Patel 11 Unit: 01 BCA SEM : III Database Management System  Codd’s rules There are 12 Codd’s Rules for RDBMS. Rule 1: The Information Rule - All data should be presented in table form Rule 2: Guaranteed Access Rule - All data should be accessible without ambiguity. This can be accomplished through a combination of the table name, primary key, and column name. Rule 3: Systematic Treatment of Null Values - A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty' string or a number with a value of zero. Of course, this can't apply to primary keys. In addition, most database implementations support the concept of a not-null field constraint that prevents null values in a specific table column Rule 4: Dynamic On-Line Catalog based on the Relational Model - A relational database must provide access to its structure through the same tools that are. used to access the data. This is usually accomplished by storing the structure definition within special system tables Rule 5: Comprehensive Data Sublanguage Rule - The database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity, and database transaction control. All commercial relational databases use forms of standard SQL (i.e. Structured Query Language) as their supported comprehensive language Rule 6: View Updating Rule - Data can be presented in different logical combinations called views. Each view should support the, same full range of data manipulation that has direct access to a table, available. In practice, providing update and delete access to logical views is difficult and is not fully supported by any current database Rule 7: High-level Insert, Update, and Delete - Data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table Rule 8: Physical Data Independence - The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture (hardware, disk storage methods) without affecting how the user accesses it Rule 9: Logical Data Independence - How data is viewed should not be changed when the logical structure (table's structure) of the database changes. This rule is particularly difficult to satisfy. Most databases rely on strong ties between the data viewed and the actual structure of the underlying tables Rule 10: Integrity Independence - The database language (like SQL) should support constraints on user input that maintain database integrity. This rule is not fully implemented by most major vendors. At a minimum, all databases do preserve two constraints through SQL. No component of a primary key can have a null value. If a foreign key is defined in one table, any value in it must exist as a primary key in another table. Rule 11: Distribution Independence - A user should be totally unaware of whether or not the database is distributed (whether parts of the database exist in multiple locations). A variety of reasons make this rule difficult to implement. Rule 12: Non subversion Rule - There should be no way to modify the database structure other than through the multiple row database language (like SQL). Most databases today support administrative tools that allow some direct manipulation of the data structure. Compiled by : Mr.Shani Patel 12 Unit: 01 BCA SEM : III Database Management System  Keys: Keys are one of the basic requirements of a relational database model. It is widely used to identify the tuples(rows) uniquely in the table. We also use keys to set up relations amongst various columns and tables of a relational database. 1) Candidate Key: A candidate key is a field or a set of fields that can uniquely identify each row in a table. A candidate key can be either a primary key. A candidate key can contain null values, but not duplicate values. For example, in a table of students, the student ID and the student email could both be candidate keys. 2) Primary Key: A primary key is a field or a set of fields that uniquely identifies each row in a table. A primary key cannot contain null values or duplicate values. A table can have only one primary key. For example, in a table of customers, the customer ID could be the primary key. 3) Super Key: The set of attributes that can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc. A super key is a group of single or multiple keys that identifies rows in a table. It supports NULL values. 4) Foreign Key: A foreign key is a field or a set of fields in one table that references the primary key of another table. A foreign key establishes a relationship between two tables and enforces referential integrity, which means that the values in the foreign key must match the values in the referenced primary key or be null. For example, in a table of orders, the customer ID could be a foreign key that references the customer ID in the customer table. ________________________________________________________________________________ SHANI PATEL C P PATEL AND F H SHAH COMMERCE (AUTONOMOUS) COLLEGE, ANAND BCA Department. Compiled by : Mr.Shani Patel 13