Database System Concept us Toplk database. — Three Level Architecture for Database System. — Overall structure of DBMS Types of Attributes, E-R Diagrams 1 Concept of Data, Database, DBMS > (MSBTE - W-13, S-15, W-15, S-17) = Concept of Data, Database, DBMS, Advantages of DBMS over File Processing System, Applications of = Data Abstraction : Different Levels of Data Abstraction, Instance and Schema, Data Independence : Logical and Physical Independence. — Data Modeling : Record Based Logical Model-relational, Network, Hierarchical. ; Data modeling using the E-R Model : Entity Relationship Model, Strong Entity Set, Weak Entity Set, — Syllabus Topic : Concept of Data, Database, DBMS eee 0.1.1.1 Define the following terms : ) Data (i) Database (fi) DBMS. (Reter section 1.1) TESA [Bata : Data is the information which has been translated into a form that is more convenient to process or move. Database : The collection of related data is termed as Database which is organized in such a way that it can be easily retrieved and managed. Database Management System (DBMS) system software which manages the data. It can perform various tasks like creation, retrieval, insertion, modification and deletion of data to manage it ber requirement. in a systematic way as = Database systems are designed to manage large amount of data by providing security from accidental crash of system and unauthorized access. DBMS provides convenient and efficent environment which used to handle the data, 1.2 Purpose of Database Systems Programming languages like Java, .Net are used to develop customized software. Every software or application has its data to be stored permanently. Programming languages cannot store data permanently. For this purpose we have to use the Database Management System. The DBMS plays a significant role in storing and. managing data. {In an application we store data in DBMS and for operations like insertion, modification or deletion we write code in programming languages. That is a software is usually created ‘ith the help of both Programming language and Database, When the application is executed on client side, the client or user interacts with interface of application which is created in programming language. ‘The database always remains backside and do not come in front of the user. Hence the database is knows as backend While programming language is termed as frontend.To understand the purpose oF need of database system WE ‘need to study the previous option to store data which is called as File Processing System, 1.2.1. File Processing System = In our day today life, numberof times we need to store data in such way that it should be easily accessible whenever required. = The data may be of hank transaction detail, daily expenses employee details, product details ete, Before computers sucht data was stored with the help of papers. = After invention of computers, it becomes easy to store data ‘ith the help of files. In the ealy days, database applications ‘were built on top of file systems. = Traditional File Processing. System is a computer based system in which all the information is stored in various ‘compute files. — It stores data in a systematic: way that the different (MSBTE -W-13, 5-15) @.4.21 Explain the disadvantages of file processing | system, (Refer section 1.2.2) $-15, 4 Marks ‘= Consider an example of IT company database system where the data ofall the employees is stored, In a professional IT firm the tasks are always done as teamwork. "2. Atomicty Problem Fig, 122: Disadvantages of traditional fle processing ssa a1 Sometimes as per requirement same data may be stord i Data Redundancy rultple files. Consider an employee having recordin be Employee and Team files. The mame and address ‘employee is stored in both of these files. = Means the data get duplicated. If such data increases, iss to higher storage and access cost. This duplication of 28 various files is termed as data redundancy. ~ Intraditional file system, it is very dificult to avid this redundancy. > 2 Datatnconsisteney ~ When data is to be updated the data redundancy may #8? * data inconsistency, Data inconsistency occurs whe ‘not updated in all the files simultaneously. oe — Forexample ifthe designation of employee get cans 3 the respective changes should be made in bth Emplo}DBMS (MSBTE-Som. 3-Comy ‘Team file. I for some reason, it is not done, then it leads t0 Ata inconsistency. Because for the sample employee, we may get different information which may create problems in the processing of sata A Limited Data Sharing is difficult to share data in traditional file system. Each application has its own private files and users have Ii choice to share the data out their own applications. ‘To share data, we have to write complex programs, 4. Difficulty in Accessing Data ‘The need of data access varies time to time, Means different types of information is needed at different situations. For example just consider that we want to retrieve the data of employees who do not have taken any leave throughout the quarter. In such ease we have two options. We can access the data by manual method or we have to write an application program to retrieve such customized data. Both the options are not convenient as both of them Jeads to wastage of time, If we do it, then also it may be possible that after some time We may require data with some another filter eriteria. The data retrieval for customized information becomes difficult because the conventional files system does not provide any efficient and convenient way to retrieve the data. 5. Data Dependence In the files, data is stored in some specific format tab, ‘semicolon or comma. If the format of any of the file is changed, then we have to make changes in program which rocesses the file. But sometimes there may be many programs related withthe fame file. In such case changes in ail such programs should be done. Missing changes i ftilure of whole application, ingle program may lead to 6 Poor Data Control ‘The Traditional Fle System does not have centralized data Sool the data is decentralized or disteibuted. In this ‘ylem the same field may have diferent names in files of ~itrent departments of an organization, RN Database System Con ‘This situation may lead to different meaning of same data field in different context or same meaning for different fields ‘This causes poor data control, 7. Problem of Security Itis very difficult to enforce security checks and access rights in a traditional file system. To the file we can set password protection But what if we have to give access to only few records in the file? For example, in our database system, the project manager should be able to see all the data regarding teams under ‘The team leader should be able to see data about his specific team, But payment details of one project manager should not be accessible to his team members oF any another project ‘manager. In the conventional file processing system, the application programs are added in ad hoe manner (For specific purpose) which makes it difficult to enforce security constraints. 8. Concurrency Problems Concurrency means access of same data by multiple users at the same time. This is very important aspect as it leads to increase in performance of a system and faster response, Many advanced systems allow the concurrent access and ‘manipulation of data, For example in our system, consider a record of an employee is accessed and updated by multiple users simultaneously ata time. This may lead to inconsistency of data, if the ‘concurrency is not controlled in a proper manner. Jn another example if multiple transactions are make ‘updations on a same bank account, then it may show incorrect balance, if any other transactions try to access balance amount in between, It is very difficult to implement concurrency control mechanism on file processing system, wl incorrect or wrong data retrieval. 9. Poor Data Modelling of Real World It is difficult for File Processing System to represent the ‘complex data and interfile relationships. This results in poor data modelling properties.DBMS (MSBTE-Sor 14 ‘That means the the real world applications are difficult © implement using File Processing System, > 10. Data tsotation — tis difficult to store the entire data in a single file Iti Aistributed in different files as per the category. ‘These files may be in decomes difficult to write application programs to access the desired data from these files. > 11 Integrity Problems Every enterprise has its own constraints while maintain dat ferent formats because of which in the files. Suppose in employee files the employee ID must start with “E’. Such constraints can be added while writing application programs. But later on if any new constraints are introduced by the enterprise, then it becomes difficult to add these constraints again, The File processing system does not provide ay functionality to handle ths situation. > 2 Atomicity Problem Failure in a computer system may occur any time, When failure occurs, if any transaction is in its midway then it may ead to some incorrect data updation in the system. Consider another example of bank transaction where some amount is transferred from account A to account B. Initially the balance from account A is accessed and debited by Rs. 1000. Then we are going to credit it in account B. But before that system crash occurs which halts the transaction. Now this situation leads to incorrect data updation in the balance of account A. In file processing system, it very difficult to handle such situation to maintain the atomicity of database. The purpose of Database Management System is to solve all these problems and give functionality to store and ‘manage data in efficient and convenient way. “Advantages of DBMS over File Processing System ‘Syllabus Topi 1.2.3. Advantages of DBMS over File Processing System > (MSBTE - W-14, W-15, W-16) grantages of DBMS over ile Processing seq, Data Redundancy Fig. 123+ Controlling: aL Processing System the different applications hy In Bie ° repartee for ata storage: 19 sce, edgy copies of the same data are created st many places, In DBMS, all the data of an organization is integrated in single database. = The data is recorded at only one place in the database andi not duplicated. For example, the Employee file andthe Te file contain several items that are identical. ‘When they are converted into database, the data is integra into a single database so that multiple copies of the same d are reduced to-single copy. Controlling the data redundancy helps to save storage sp Similarly, it is useful for retrieving data from database ws queries. > 2 Data Consistency The data consistency is obtained by controling te ¢ redundancy, If a data item appears only once, any pdt its value has to be performed only once and the wat is immediately available to all users. 7 — For example if there is change in designation — centralized file oe and explain four advantages of DBMS over file processing system, AS s (Refer section 1.2.3) (EVRVEEAVAGR MICS then the changes are made in single available to all the users. —-a3 DBMS (MSBTE-Som, 2. ‘Sharing of Data In DBMS, data can be easily shared by different applications. ‘The database administrator manages the data and gives rights to users to access the data, Muliple users can be authorized to access the same data simultancously. The remote users can also share same data. 4 Data Independence In DBMS we can completely separate the data structure of database and programs or applications which are used to access the data. This is called as data independence. If any changes are made in structure of database then there is no need to make changes, inthe programs. For example you can modify the size or data type of a data items (fields of a database table) without ‘making any change in application program, 5. Data Controt ‘The DBMS provides centralized data storage. Hence keeping control on data is very much easy as compared to Traditional File Processing System, ‘As data is common for all the application, no possibility of any confusion or complication. 6 Security In DBMS the different users can have different levels of access to data based on their roles. In the college database, students will have access to their own data only, while their teachers will have access to data of all the students whom they are teaching. (Class teacher will be able to see the reports of all the students in that class, but not other classes. The principal will have access to entire data. ‘Similarly, in a banking system, individual operator and clerk will have limited access to the data while the bank manager ‘can access the entire data. All these levels of security and access are not allowed in file system. 7. Control over Concurrency In a computer file-based system, if multiple users are accessing data simultaneously, itis possible that it may lead Databa Concept, to some irrelevant ‘operation is executed by both the users on the same record, then value updated by one may get overwrite by other, generation. For example, if update Mostly the database management systems have sub-systems to control the concurrency so that accuracy is maintained in transaction recording. 8. Data Modelling of Real World “the DBMS has many functionalities are provided to represent the complex data and intrfile relationships. “This helps to map the database with real world applications. .2.4/ Disadvantages of Database 1 124/“Blenavetag System a. Disadvantages of Database Management System 1. Increased costs| 2. Complexity ] 3. Size | “4, Frequent upgrade/replacement cycles 5. Higher impact of a failure 6. Performance ] Fig. 1.24: Disadvantages of database management system Increased costs To install Database Systems, we require standard software and hardware, Also to handle the Database System, highly skilled personnel are required. ‘The cost of maintaining the software, hardware, and personnel required to operate and manage the database system is more. ‘The cost of training, license, and regulation compliance also, increases the overall expenses. 2. Complexity ‘Sometimes because of higher functionality expectations, the design of Database may become very complex. cy, all the To utilize such database with complete effi stakeholders like database designers, developers, database administrators and end-users must _ understand the functionality.wcetading the system ca Teal to wrong design organization may coeur, Pk Sire ‘The DBMS becomes extremely lange piece of soft $ecause of the complenty of functionality occupying tage amount of disk space and requiing substantial memory to nun efficiently, mounts of F 4 Frequent upgeaderreptacement eyetes New functionalities are fanetionaiies are often added into DBMS ty their ‘vendors, These new features often come bundled in new ‘upgraded versions of the same software, Sometimes these versions require hardware upgrades which Increases expenses, Also work to train database users and ‘administrators to propery use and manage the new features get increased. > 5. Higher impact ofa failure ‘The DBMS is placed at centralized location which increases, the vulnerability of the system. — That means the DBMS may get attacked and harmed. Since all users and applications rely on the centralized database, the failure of any component can bring operations toa halt. > 6. Performance = Usually, a File Based system is written for a specific application, Hence the performance is generally very good. While the DBMS is written to be more general, to cater for ‘multiple applications rather than any specific one. Because of which some applications may not run as fast as they used to. ‘There are number of advantages of DBMS over File System. 1.2.5 Difference between File Processing and DBMS ae Processing System re dat consistency cained BY CONtTOIINg thy gata redundancy the |_____———__| tn DBMS, data can 4 censily shared by ditferg, applications. | In DBMS we can, completely separate the data structure of database and programs or applications which are used to access the data ances!) sata co share iticul tis ste system intraaitional data is stored in spose formats TE the fomat otany oF he wes changed then we have (O make changes in proBrar” which processes te FC. rete fess File have The DBMS provide centralized data. stone Hence keeping contol oq datas very much eay, ‘the Traditional ‘system does 10 ‘centralized data control, the data is de-centralized or istributed. Duplicate data may exist in multiple files. which lead to data redundancy, ‘The data is integrated into a single database which avoids data redundancy. | It is very difficult to enforce security checks ‘and access rights in a traditional file system. In DBMS the iterest used can have diffest levels of access to daa based on their roles whic provides strong security data. Concurrency means updation of same problems data by multiple users ‘may generate irrelevant results, DBMS have sub-systems to control the concurrency. It is difficult for File Processing System to represent data relationships. This results in poor data modelling. and the complex interfile ‘The DBMS has musty) functionalities = provided to represent ‘complex data and ire relationships. This bls ® map the database wihplications of DBMS. io tes 1.3 _ Applications of DMS. ERECT = For any enters its data is very important which helps to manage the business as well as decide some strategies to survive and grow the business in this competitive world. A. Database Management Sysiem is a computerized reconl- Ikceping system. It works as a container for collection of computerized data files. ~The overall purpose of DBMS is to provide functionality to the users (0 create store, retrieve and update the information ‘contained in the database as per requirement. Information ean be of an individual or an organization. Databases touch all aspects of our lives. Some of the major areas of application are as follows : [7-Ravayresnain Son] Fig, 1331: Applications of DBMS. D 1 Telecom : In Telecom sector database is maintained 10 keep track of the information about calls made, customer (sere -s-14, wg, 518) As we have seen a data base system is collection of related data and system software which manages the data. The data is generally stored in a detailed and complex manner. It is {important to provide an abstract view of data tothe user. — To understand the view of data, first we have to leam the concept of abstraction. ——___—_— Syllabus Topic : Data Abstraction 1.4.1 Data Abstraction D> (uSBTE - S-14, 5-15, W-17) — Database systems are usually made-up of complex data structures as per their requirements. ~ To make the user interaction easy with database, the internal irrelevant details can be hidden from users. This process of hiding irelevant details from user is called data abstraction. = The complexity of database can be hiding from user by different level of abstraction as follows. Syllabus Topic Different Levels of Data Abstraction Different Levels of Data Abstraction ‘There are three levels of abstraction as physical, logical and Yew level, ” schema revel is the lowest level Th, ‘actually stored in the physic physical Level / internal jon Physical action Physical | data is ray be bard disks, MOET PS, “The physical MEMOP TE gds like hashing are used fy In Physical level the jon puspose- organization P' . would know te requiementy S26 and aig eee cords clearly in this Tevel which makg frequency of the rec easy to design this evel: Logica! Level / Conceptual ‘Schema sis is the next higher level of abstraction whichis wed describe what data the database stores, and what relatontin exist in between the data items. The logical level ty deseibes an entire database in terms of a small number ¢ relatively simple structures. ‘Although implementation of the simple structures at & Iogical level may involve complex physical evel stun the user of the logical level does not need to be aware of i complexity. This is considered as physical data independ Database administrators use the logical level of abstraction decide what information to keep in a database, View Level / External Schema Itis the highest level of data abstraction. This level de the user interaction with database system. Inthe lgial ke simple structures are used but still complexity = because in the large database various type of information stored. Many users are not aware of technical deals of te mation £0 oi i and also they need not to access whole info database. Hence it is necessary to provide a simple interface for such users as per their requirements views canbe erated for same database formulethat we are storing informat employees ofan organization fn employee table, At phystnt qevel these records can be described as blocks of storage coves: sienbyesferabytes ete) in memory. these details are usually hidden from the developer. the records can be described as fields and attributes along ‘with their data types at the logical level. The relationship tetween these fields ean be implemented logically. Usually the doveloper works at this level because they have knowledge of such things about database systems. Bad user interacts with system with the help of GUT and centers the details at the screen at view level User isnot aware of how the data is stored and what data is stored; such details are hidden from them. Syllabus Topic : Instance and Schema 1.5-— Instance and Schema © Schema = To understand schema we can consider an example of a ‘program of an application. A variable or array declared with its structure (data type and/or size) is schema. The changes in schema or not frequent. © Types of Schema According to the level of abstraction, the database schema is, divided into three types. Physical schema, Logical schema and View schema, Types of Schema 1.Physical Schema 2.Logical schema 3.View schema Fig. 1.5.1 : Types of schema 19 Database Sy Physleat schema : is the design of physical level, ic, how the data stored in the blocks of storage is described in this level Logleal schema + is the design of database at logical level. Developers and database administrators work at this level. Here the data can be described as certain types of data records gets stored in data structures, however the internal details like the implementation of data structure are hidden at this level. View schema : refers to design of database at view level. This usually describes the end user interaction with database systems. There may be multiple schemas at view level. ‘= Anétance \ 415.2 Defina : Instance. (Refer section 1.5) = In database changes are quite frequent ie. insertion, deletion ‘or updation are the frequent operations on database, ‘The data is stored in the database at | particular moment is called as instance of the database. Eee In the example of application program, the value of a variable at particular time or situation is called as instance of database schema, Syllabus Topic : Data Independence - Logical and Physical Independence Data Independence : Logical and Physical Independence 1.6 > (MSBTE - S-14, W-15) Q. 1.6.1) Desctibe data independence with Its types. (Refer section 1.6) _ Ee ‘The ability to modify schema definition in one Tevel without affecting schema definition in the next higher level is called data independence. | DEFINITIONwy ooms (MSBTE-Som. 3-Comy ‘Ther are two Feels of data independence hey are Pe data independence and Logica data independence ‘Types of 1. Physical Data Independence 2, Logical Data Independence Fig. 1.6.1 : Types of Data Independence (Q)_ Physical data independence It is the ability to modify the physical schema without ‘causing application programs to be rewritten. “Modifications at the physical level are occasionally necessary to improve performance. Tt means we change the physical storage/level without affecting the conceptual or external view ofthe data. ‘The new changes are absorbed by mapping techniques. (2) Logical data independence Itis the ability to modify the logical schema without causing application program to be rewritten. ‘Modifications at the logical level are necessary whenever the logical structure of the database is altered (For example, when money-market accounts are added to banking system). Logical Data independence means if we add some new columns or remove some columns from table then the user view and programs should not change. For example : consider two users A & B. Both are selecting the fields "EmployeeNumber” and "EmployeeName". Iruser B adds a new column (e.g. salary) to his table, it will not affect the external view for user A, though the internal schema of the database has been changed for both users A&B. Logical data independence is more difficult to achieve than physical data independence, since application programs are heavily dependent on the logical structure of the data that they access. Database Systom oo, Syrabue Tople = overall Structure of Dans ‘Overall Structure of DBMS > (msBTE S45, 8, 18 Wp DBMS (Database Management SyStEm) 2545 an iggy the user and the database. between sme user requests the DBMS to perform various opening (etive, insert, delete and update) onthe databace, fe User jn cone [Gury Opttzn Fig. 7.1 ‘The components of DBMS perform these requested operations on the database and provide necessary data ote users, The various components of DBMS are as short below: © Structure of DBMS ‘The Structure of DBMS contains following components. |. DDL Compiler ‘The DDL Compiler converts DDL commands into set of tored in a data diction the fils, dats ites formation tables containing metadata s ‘The metadata information is name of storage ‘details of each file, mapping constraints etc.= eee 3 PML Compl ‘The DML commands such as retrieve, insert, upstate, delete cs. from the application program are sent to the DML compiler for compilation. Tt converts these commands into ojct cake foe understanding of database. 1nd Query optimizer “The abet cove is then optimized in the best way to execute a goery by the query optimizer and then send to the data manages. Data Manager ” ‘The Data Manager is the central software component of the DBMS also knows as Database Control System. ‘Toe main functions of Data Manager are : © Itconverts the requests received from query optimizer to machine understandable form. It makes actual request inside the datahase. Controls DBMS information access that is stored on disk. Ik cootrts handling buffers in main memory. 1k enforces constraints to maintain consistency and integrity of the data. Ik synchronizes the simultaneous operations performed by the concurrent users (© Italso controls the backup and recovery operations. Data Dictionary Data Dictionary is a repository of description of data in the database. It contains information about Data - names of the tables, names of attributes of each table, length of ansibutes, and numberof rows in each table. Relationships between database transactions and data items referenced by them which are useful in determining which transactions are affected when certain data definitions are changed. Constraints on data ie. range of values permitted. Detaled information on physical databgse design such as ‘orage structure, access paths files and record sizes. ‘Access Authorization ~ is the Description of database users their responsibilities and thei access rights ‘Usage statistics such a frequency of query and transactions. Database System Con Data dictionary is used to actually control the data integrity land accuracy. It may be used as an important part of the DEMs. °F Importance of Data Dictionary Data Dictionary is necessary in the databases due to following reasons: It improves the control of DBA over the information system and user's understanding for the use ofthe system. Ie helps in documenting the database design process by storing documentation ofthe result of every design phate and design decisions. Iehelps in searching the views on the database definitions of those views. 1k provides great asistance in producing a report of which data elements (2. data values) are used in all he programs. Data File Tt contains the data portion of the database i, it has the real data stored init. It can be stored as magnetic disks, magnetic tapes or optical disks. Compited DML. ‘The DML complier converts the high level Queries into low level file access commands known as compiled DML. Some of the processed DML statements (insert, update, delete) are stored in it so that if there is similar requests, the data can be reused. End Users ‘They are the real users of the database, They can be developers, designers, administrator or the actual users of the database. Syllabus Topic : Data Modeling — Record Based Logical Model-relational, Network, Hierarchical eee Data Models ‘7 Basic Concept ‘The process of analysis of data object and their relationships to other data objects is known as data modeling. Its the conceptual representation of data in database. It is the first step in database‘designing. Data models define how data is connected to each other and how they are processed and stored inside the system, A data ‘model provides a way to describe the design of a database at the physical. logical and view levels, ka / Types of Data Models > (MSBTE ~ S-16, W-16) “Q41.8.1 List Various Gaia mado Se —_ (Refersécion 6.1) Sry 1.82 List any two data models, * (Refer section 1.8.1) — = Inthe relational model, every record most have & unig ‘deniication or key based on the data = In Toble 18.1 Stud 1D f the key throug Which we gy ienty the recor uniquely inthe relation. Relational ay, model isthe most widely used record-based data mode, ‘There are different types of data models Tuple—} Fig. 1.8.1: Types of data models 1.8.1(A) Record Based Logical Model -Relational Model > (MsBTE - W-13, s-16) Q.1.8.3 Describe basic concepts of relational model. _ (Refer section 1.8.1(A)) TEE 1.8.4 Describe relational model with example. (Refer section 1.8.1(A)) SET ~The relational model is developed by ELF, Codd. Relational database isa type of record-based relations. Relational database is an attempt to simplify the data structure by making use of tables. Tables are used to represent the data and their relationships. Table is a collection of rows and columns. Tables are also known as relations. — Records are known as tuples and fields are known as attributes. The relational model is called as record based model because the database is structured in fixed format records of different types. A record is consists of fields or attributes. Scuola 102 Rakesh 13/01/1996 103 Rahul 16089015 [ I I | Rational enone oe ‘Advantages of Relational Data Model Mosel tabase. rientod Mode Mosel | “Advantages of Relational Data Mod Hierarchical Entity Fees baal Ret F oat oda Model ') Supports SQL Fig. 1.8.2: Advantages of relational data model > @ SupportssQu. For accessing the data in Relational data model we have a Tanguage known as Structured Query Language (SQL). — This language is used to access, insert, update or delete the ‘data from the table, By using relational data model we can ‘execute the complex queries > ©) Flexible — We can easily manipulate the information which is linked with various tables. — We can extract the information from different table simultaneously by using this model. 1.8.1(B)_- Hierarchical Model A data model in which the data is organized into a tree structure is known as hierarchical data model. — Hicrarchical data model structure contains parent- (@ Simple to understand : Due to its hierarchical structure it is easy to understand. Most of the time data have hierarchical relationship. Therefore, ‘the data in that manner. s easy fo arrange > (b) Database Integrity + In hierarchical data model the always a pareat child association between different records on different level. Due to this inherent structure integrity gets maintained. > (© Efficient : The performance of this model is very } structure when database contain large amount of data which has various related eeords efficient due to its hierarc Database (C) Network Database Model = his extended type of hierarchical data model, This data model is also represented as hierarchical, but any child in the tree can have multiple parents In network data model there is no need of parent child association. There is no downward tree structure. = his the flexible way of representing the objects and their relationship. A network data model allows multiple records ced in the same file. = Basically, network database model forms a network Tike structure between the entities. Teacher I> Cuan [tesco Geach Fig. 18.5 : Network database model > naomi visi ‘ee Fig. 1.8.6: Advantages of network model 2) Design is simple : The network model is simple and easy to design and understand. There is no complex structure in network model. 1) Ithas capability to handle various relationships : The network model ean handle the one to many and many to ‘many relationships which is useful to develop the database. Easy to access : The data access is easy and flexible than the hierarchical data model. In network mode! there is mo any hierarchy in the objects and their ‘elations therefore it network model very easy to access the data inata Modeling using the El Relationship Modal” Data Modelin ig usin, I Model ig the E-R Entity Relationship Model ‘This model describes inter-related things of interest in a Specific domain of knowledge. An ER model is composed of ‘entity types (which classify the things of interest) and ‘Specifies relationships that can exist between instances of ‘those entity types. [a] Fig. 19.1 : ER Model ‘© Advantages of Entity Relationship Model ‘Advantages of Entity Relationship Model 2) Simple Design ’) Effective representation ‘) Connected with Relational Model Fig. 1.9.2: Advantages of entity relationship model (a) Simple Design : The ER model is simple and easy to design. It shows the logical view of the data and its relationships. This model is easy to understand. (®) Effective representation : The presentation of Entity Relationship Model is very simple and effective. The programmer and designer can easily understand the flow of the system by referring the ER Model. > (©) Connected with Relational Model : Relationship Model is connected with the relational ‘model. Due to this advantage we can develop a well structured design. Database Systom Concepts of ER Modo! 1 ns ein 19 0 WETS Fhe wchenn a represents he OVE ope terprise ‘an ent structure of the database. database related t0 real world Applicaton, ast and complex. Representing. relation, Jements of the database becomes Now a days the becomes very ¥ tween the different el jul ER Model simplifies this task, Ht is noting But the design vee for daakae. 1 # sapal Yshlque why eps to understand and organize the complex data which oid not depend upon the actual database implementation, ‘The real world objects can be easily mapped with enies of ER model. — In Enity Relationship Model a graphical representation of database system is generated. Diagrams are used in this model. These diagrams are known as entity-relationship diagrams, ER diagrams or ERDs. = Basic concepts of ER Mode! are as follows : Entity, attribute, Relationship, constraints and keys. oO Syllabus Topic : Strong Entity Set, Weak Entity Set Syllabus Tork Se eee 1.10.1(A) Entity and Entity Set > (MSBTE - S-14, W-14, S-16, W-16, S-17) Q.1.10.1 State weak and strong entity set, A (Refer section 1.10.1(4)) ES EMUAERPLENS | Q. 1.10.2 Define entity. (Refer section 1.10.1(A)) 1.10.3 Explain strong entity and weak entity sot. (Refer section 1.10.1(4) STEERS ‘The E-R model consists of entities and relationships betwee? those entities, An entity is a thing that existe either Physically or logically. An entity is nothin’ but a thing having its own properties. These Properties helps to differentiate the obleet (entity) from other objects. An entity set is of entities which share the same properties.DBMS (MSBTE-Som. 3-Comy 4 = Aneentity may be a physical object such as a house oF a ear, for an entity may be a logical concept like an event such as @ ‘house sale oF a car service, oF a concept such as a customer transaction or onder. = Ina Company employee is the entity set which has similar properties like Employee_ID, emp_name, salary ete. = There is difference between an entity and an entity-type. An cetity-type is a category. An entity, strictly speaking, is an instance of a given entity-type. There are usually many instances of an entity-type & ‘Types of Entities = There are two types of entities in Database management system. 1. Strong Entity or Regular Entity 2. Weak Entity Fig. 1.10.1 : Types of entities 1. Strong Entity or Regular Entity = Ian entity having it's own key attribute specified then it is a strong entity. Key attribute is used to indentify that entity uniquely among set of entities in enity-set. - Example : In a parentchild relationship, a parent is considered as a trong entity = Strong entity is denoted by a single rectangle. — The relation between two strong entities is denoted by a single diamond simply called relationship. 2. Weak Entity - Theentity which does not have any key attribute is known as ‘weak entity. The weak entity has a partial discriminator key. ‘Weak entity depends on the strong entity for its existence. ‘Weak entity is denoted with the double rectangle. ~ Example : Ina parentchild relationship, a child is considered 8 a weak entity which is completely depends upon the strong entity ‘parent’. Database System Concept ‘Syllabus Topic : Types of Attribut 1.10.1(B) Types of Attribute > (mseTE -5-46, 5-17, W-17) GAA0A Delineatiibuto. (Rotor soction 1.10.18) 8 1.40.5 Explal single value and mult-value attribute of model. (Aefer section 1.10.1(B)) Explain any 4 types of attributes. Di z (Refer section 1.10.1(6) __ ECARNTETTS “An atiribute is a characteristic of an entity. LT ‘Entities are represented by means of their | attributes. All attributes have their specific values. ee 7” = For example, an employee entity may have Employee ID, cemp_name, salary as attributes. = Ina database management system an attribute is a database ‘component, such as field or column of a table. = Example : The entity student has attributes like student_id, student_name, In this every attribute has a value, Here 101 is the value for the attribute student_id, Kunal is the value for attribute student_name. Fig. 1.102 ‘There are five different types of attributes in Database ‘Management Syster Types of Attribute 7. Single-valued Attibute 2, Mutivalved anrouto | 3. Simple Attibuto “4. Composite Attribute Derived Atbute Fig, 1.10.3 : Types of attributeFL Singlevatuod Attribute te which ean bold & = A single-valued attribute is the attribut single value for the single entity. student nae is the single" = Bxample : In the entity student, ingle value for name valued attribute since a student have a si attribute. > 2X Malti-vatucd Attribute 'A mmicvalucd attribute is the attribute which a0 hold Je entity the attribute student_contact adent multiple values for the ‘Example # In the entity student, value attribute since & ‘no could be considered a mult could have multiple contact numbers. > 3. Simple Attribute ‘An attribute whose value cannot be as simple attribute. That means itis atomic in nature: further divided is known Example + Tn the entity student, the attribute student-o5° ‘cannot be divided, Therefore student.age is the simple attribute of student entity. 4. Composite Attribute ‘The composite attributes are the attributes which can be fanher divided into sub parts. These sub parts represent the basic entities with their independent meaning. Example : In the entity student, student.name is the we can divide this attribute in three composite attribute, Middle_name and different sub parts: Firstname, Lastname. > 5. Derived Attribute ‘The attribute which is not physically exist in database, but its value can be calculated from the other present attributes is known as derived attribute. Example : In the entity student, we can calculate the average ‘age of students. This average age is not physically present in the database but it can be derived from the attribute student_age. 1.10.1(C) Relationships = The association between two different entities is called as relationship. In the real world application, what does ; Sone entity do with the other, how do they connect 1o each other ? > 1. Unary Relationship Datapasy soon us works at a department, a stud, An employee works at and Enrolls are caljey sf readonsin refer £2 Mame of participated jationships 73 Temary Relationship 7 Quaternary Relationship + of entities participated in degree of relationship Fig. 1104 : Number ‘A unary relationship exists when there is relation between single ent, A unary relationship is also known as recursive relationship in which an entity relates with itself. Example: A person can be in the relationship with another person, such as : ‘A woman who can be someone’s mother ‘A person that is someone's child. Fig. 1.10.5 2. Binary Relationship A bi , binary relationship exist only when there is relation between only two entities. In this case the degree of relation is two. Exar : mia + A teacher teaches student. In this teacher and student i at are two different entities which are connected wit ‘ach other via relation Teaches.DBMS (MSBTE-Som. 3-Cor <> Fig. 1.10.6 > Ternary Relationship three entities. In ternary relation the degree of relation i six. ‘Atemary relationship exists when there are relations between Database System Concept ‘This model defines the data objects and their relationshi Is the popular model in database, It ‘This model consists of entities and relationships between ‘those entities. Am entity is nothing but a thing having its own. properties. These properties help to differentiate the object (entity) from other objects. 1.11.1, Components of E-R Model and Conventions Example : A person can be a student and a person also can ‘be teacher. Here teacher, student and person are three entities ‘The pictorial representation of data using different which are elated to each other. conventions which state that how these data are related with each other is known as Entity Relationship Diagram. E-R diagrams express the logical structure of database in graphical manner. ‘Special symbols are used to draw an ER-Diagram. Every symbol ‘has its own meaning. Student] © Example of various symbols used in ER Diagram Fig. 1.10.7 > 4 Quaternary Relationship > (MSBTE - W-14, S15, W-17) 4 List four Symbols séd in ER diagram, Be ee teaeay Ht) A quaternary relationship exists when there are relations between four entities. In quaternary relation the degree of relation is eight. Tau ‘Symbol Name [eee Je fe (Ey | Weak entity faculty rectangle Fig. 1108 ‘Attributes ~ Example : The four entities Employee, Management Faculty, CPD | rime Atwibute Teaching Faculty, and Non-Teaching Faculty are connected with each other via isa relationship. Zp JPirr win | ey atibre Under Line Syllabus Topic : E-R Diagrams C= Double ‘Molti-valued 131 E-A Diagrams pipse | ante - , aot ved atibute In 1976 Entity relationship model was developed. It is useful REM | Dashed | Derived jn conceptual design. Its the high level data model. ~ Ellipse| Symbol Description | aah Relationships, < Diamond | The reatonship ‘Line ‘Links attributes to cently sets and entity sets 10 reationsh sets, == J Dowtterine | Represents total participation of an ‘entity in relationship ‘7 Representations 1. Bitity An Entity is any object, place, person or class. In E-R Disgram, an entity is represented using rectangles, Consider fan example of an Organization. Employee, Manager, Department, Product ete. are considered as entities. Fig. 101.1 — Here employee and department are entities. 2 Weak Entity — Weak entity is an entity which depends upon another entity, ‘Weak entity is represented by double rectangle. Subject is the weak entity. Because subject is depends on course Bottom-up Fig. LI2 “ Attribute Attributes. are nothing but the properties of entity. Here Fig. 1.11.3 4. Key Attribute (Primary key) ‘To identify attribute uniquely we set the key t0 the atibute _Ttis denoted by underline, Fig. 1.114 5: Mulll valued Attribute = The attribute which have multiple valves is known 36 mali valued attribute, Here Phone No is multi valued attribute as a person can have ‘more than one phone numbers. Fig. 1115 6._Derived Attribute — Derived attributes are the attributes that do not exist physically in the database, but their values can be derived from other attributes present in the database. ‘Stud_id, Name and address are attributes of entity Student. ~ For example age can be derived from date_of birth.o~ pams (MSBTE-Sem. 3-Comp) 4 For example, the entity “carpenter” may be related to the entity “table” by the relationship “builds”. Relationships are represented by diamond shapes and are labeled using verbs. Fig. 1.11.7 Recursive Relationship If the same entity participates more than once in a relationship itis known as a recursive relationship. Consider an example where an employee can be a supervisor and be supervised by manager, so there is a recursive relationship. ‘Supervision Employee Fig 1118, Following E-R diagram represents the relationship between two entity sets teacher and student related through binary relationship guide. ‘Student i Namo Project name Fig 1119 base System “The attributes of entity set teacher are ou © Name © Qualification “The attrbutes of entity set student are ou Name © Project.name 4.11.2 Mapping Cardinality in E-R Diagram In the two entities Teacher and Student, the relationship Guide may be ‘Mapping Cardinallty in E-R Diagram Fig. 1.11.10: Mapping cardinality in E-R diagram > 1. OnetoOne [Error Fig. 111.01 In one to one mapping cardinality directed lines from relationship Guide are drawn towards both entity sets Teacher and Student. In this, the teacher can guide at most one student and the student can take guidance from at most one teacher. > 2 ‘OnetoMany "Teacher i Namo Qualification Fig 1.1142DEMS (MSBTE-Sem. 3-Comp) Im one to many mapping cardinality directed line from relationship Guide to entity set Teacher is drawn and undirected line from relationship Guide to entity set Student is drawn. In this, the teacher can guide many students but a student can take Buidance from at most one teacher. > 3. Many to One Name | Qualification et Fig. 1.11.13 In many to one mapping cardinality undirected line from relationship Guide to entity set Teacher is drawn and directed line from relationship Guide to entity set Student is drawn. In this, the teacher can guide at most one student but a student can take ‘guidance from many teachers. > 4. Many toMany Guide Fig. 111.14 In one to one mapping cardinality directed lines from relationship Guide are drawn towards both entity sets Teacher and the teacher can guide many students and the Student. In student can also take guidance from many teachers. Database Sys!om Cong Points to remember while drawing an ER diagram itll idemity all entities and ther relationships with ggg ‘other in the given database system. 2. Noentity should be repeated in a particular diagram, 3, Provide @ precise and appropriate name for each entiy, attribute, and relationship in the diagram. Try to give wer fiiendly words while naming, The name should also be meaningful, unique and easily understandable Do not set unclear, redundant or unnecessary relationships between entities. 5, Never connect a relationship to another relationship, 6. Using colors helps to make the diagram easily ‘understandable. It helps in differentiation and classification 1.11.3. Examples of ER Diagram = E-R diagram with multi valued and derived attributes Fig. L115 ‘% Total Participation of an Entity set If in a relationship set, every entity in entity set has one relationship then it can called as total participation of entity st. Fig. 1.11.16DAMS (MSBTE-Sem. 3: 421 Database System [A Total participation ofan entity set represents that each entity in entity set must have at feast one relationship in a relationship set oceample: Inthe above diagram we can observe that each college must have atleast one associated Student, Ree eee Mea eon ee, ng Fig. 1.11.18DBMS (MSBTE-Som. 3-Cor 22 ana Fig 1.1119 1415 © ~ Draw ER diagram for customer and oan. Assume suitable att (Refer section 1.11.3) — z : /Q.1.11.6 Draw E-R diagram for airine reservation system. Here a passenger can book tickel from personal for a fight on hae same date. (Refer section 1.11.3)i ams (MSBTE-Som. 3 1-23 Database System Concept Fig. 1.11.22 gaa