Chapter O1 OVERVIEW OF DATABASE SYSTEMS 4.1. INTRODUCTION In computerized information system, data is the basic resource of the organization. The amount of data or information available to us is exactly exploding and to handle these large and complex datasets a tool is needed. So, proper organization and management for data is required for organization to run smoothly. Database management system deals the knowledge of how data stored and managed on a computerized information system. It is also referred as a software to assist in maintaining and utilizing large collection of data. It is a repository or container for a collection of computerized data files. 1.2 DATA AND INFORMATION Let us discuss about the data and information. : Data are known raw facts about a place, person, thing or object tnvolving in the transactions of an organization that can be recorded and which have implicit meaning. It is also referred as representation eee or instruction in a formal manner, which is suitable “crstanding and processing. Data can be represented in alphabets, ie using special characters. For example, names (‘dbms’), elephone numbers C237.) ~ Information is the result of processing raw data on which decisions actions are based, The term information refers to data that has been12 i i ingful v: organized and classified to provide meaningfu ect ons processing is the acquisition, storage, organisation, retrieval, display i dissemination of information. It can be presented in the tabular form, bar graph or an image. 1.2.1 Metadata Metadata is the special data that describes the characteristics op properties of the data. It consists of name, data type, length, specification and special constraints. It allows the database designers and users understand what data exists and what data means. Metadata is generally stored in a repository. 1.2.2 Characteristics of data ~ Shared - It should be sharable among different users and applications + Persistence — Data should exist permanently in the database. Changed in the database must not be lost because of any failure. * Validity/ Integrity/ Correctness — It should maintain integrity so that there is always correct data in the database. + Security It should be Protected from unauthorized access. Non-redundancy ~ No two data items in a database should Tepresented the same real world entity. + Consistency — jt should satisfy all the Constraints in the databas® and also Consistent with the changes, 1.3 DATABASE + Database can be det fined as . Fi f logical!’ related data, Organized collection o1.3 overview of Databse Systems Student database consists of field such as Example for database — Year and Course. Name, RollNo, Table 1.1 Student Database Name Roll No Year Course XXX ool 1 Computer Science YYY 113 2 Computer Science ZZZ 225 3 Computer Science 4.4 DATABASE SYSTEMS Database systems are usually designed to manage large bodies of information. It involves: ~ Defining structures for storage of information i.e., Data Modelling & Providing mechanisms for the manipulation of information ~ Concurrency control if the system is shared by users ~ Security (unauthorize access) or crash recovery Fig 1.1 shows the simplified picture of the database system and it is clear that the several users can access the data in an organization still the integrity of the data should be maintained. A database is integrated when same information is not recorded in two places. Application Programs Database Management System(0BMS) End USeFS Fic 7 ‘gure 1.1 Simplified Picture of a Database Syste™Relational Database Management System el uM ‘ ines, Unversities, atabase systems are widely used in Banking, Ait By, itt a Finance, Sales, On-line retailers, i nes es systems, Health care, Scientific Human resources, Knowl esearch, networking etc. . ; BMS) 4.5 DATABASE MANAGEMENT SYSTEM (DI database management system consists of a collection of logically A database me i related data and a set of application programs used to access, update and ‘manage that data, It js designed to assist in maintaining and utilizing large collection of data, The goal of DBMS is to provide an environment that is both Convenient and efficient to use in retrieving information from the database and storing information into the database. Therefor collectively known as database systems, e, Database and DBMS 1.5.1 Function of DBMS Data Storage, retrieval and Update + User accessible catalog + Transtetion support + Concurrency Control services + Recovery services + Authorization services % Integrity services + Data independence and Utility Services Applications of DBMS Some Database applications are if Telecommunications ~ i «pills aE “eping cal] Tecords, generating is ‘maintaining balances in pre aid calls and storing informal about the networks,werview of Databse Systems ie 2. Banking - Customer information, Accounts, Loans and ‘Transactions. 1.5.2 Objectives of DBMS ‘The main objectives of database management system are : ~ Data Availability — It is the process of ensuring that data is available to end users and applications in understanding format. Data Integrity —It refers to the accuracy and consistency of data. In other words, keeping data intact and unmodified throughout its life cycle is referred to as data integrity. Data Security — It refers that only authorised users can able to access the database. i.e, When several users accessing a particular data at same time, the DBMS does not allow them to make the conflicting changes. It is protected with password facilities. Data Independence - The three levels in the schema should be independent of each other so that the changes at one level should not affect the other levels, It helps you to keep data separated from all programs that make use of it. 1.5.3 Components of DBMS. 1. Hardware = Hardware refers to the Physical parts of the computer and also includes . a cludes some various Storage devices like hard disk, Input/output devices that offey er the interface between Computers and real-world systems. 2. Software Software js date, ‘ofiware is the set Of programs used to manage and control the ase and j ‘ Soften, nd eles the database software, operating system, network Software Used ( © share the data with other users, and the applications used 0 decess the data,ae Relational Database Management System, 1 3. Data Data are raw facts and information that need to be organized ang processed to make it more meaningful. The primary reason behind the DBMS was to store and maintain data within the database. A typical database stores both actual data’or metadata. A metadata is the data about the data or self-describing nature of databases. A data dictionary contains metadata that is data about the database. 41.6 PURPOSE OF DATABASE SYSTEM From this section, we will discuss about the traditional file processing system and Database Management System (DBMS). 1.6.1 File processing system structure File processing system was an early attempt to computerize the manual filling system that we are all familiar with. A file system is a method for storing and organizing computer files and the data they contain to make 4 it easy to find and access them. File system may use a storage device such as hard disk, CD-ROM ett and involve maintaining the physical location of the files. The file-based system was developed in response to the needs of organization for mor efficient data access. This information was stored as group of records it separate files. Jn this system, each user defines and implements the files needed for Specific sofiware application as part of programming the application. THs typical file processing system is supported by a conventional operatins system. The system stores permanent records in various files and it different application programs to extract records from, and add records the appropriate files, abou! etc!" fH For example, consider University database it contains details the student, courses offered, list of faculty members, result details traditional system, for each database there is separate application proe which is shown in fig 1.2.‘overview of Databse Systems 17 Users Roll_no, Roll_no. St_Name StName, || Course Course | Phone _no, Sub_Name ‘Address, Matksete, | Room. | \| Sub.Neme | | | | Marks etc. | | Figure 1.2 File System Structure of a Database 1.6.2 Disadvantages of File processing system 1, Data redundancy and inconsistency Redundancy refers to data duplication i.e. storing the same data multiple times in different files leads to several problems. Since different programmers create the files and application programs, the various files are likely to have different formats and the programs may be written in several programming languages. Therefore, every user group maintains its own files handling its data-processing applications. Due to this, a lot of duplications of data leads to inconsistency. For example, entering data on anew student data multiple time. Once foreach file where student data is recorded. Redundancy problem leads to Higher Storage space and access cost % Files that represent the same data are no longer consistent, updating of files has to be followed clearly to avoid inconsistency. reRelational Database Management System 2. Difficulty in accessing data File processing environments do not allow needed data to be retrieveq in convenient and efficient manner. For example, if the university clerk needs to find out the names of all student who live within a particular postal code area. Due to unavailability of application programs to handle this request, the officer has two choices to solve this problem. Either to obtain list of all students and extract the needeq information manually or ask a system programmer to write a necessary application program. Both alternatives are obviously unsatisfactory. 3. Data isolation Data are scattered in various files and files may in different formats, writing new application programs to retrieve the appropriate data is difficult, 4. Integrity problems The data values stored in the database must satisfy certain types of consistency constraints. Suppose, the department in the university has idual accounts and they have to maintain the minimum balance in each of them account. The balance should not be falling below the minimum level. Developers enforce these constraints in the system by adding appropriate code in the various application programs. Howevel, when new constraints are added, it is difficult to change the programs ad enforce results. 5. Atomicity problems The data is restored to a consistent state, if the system crashes wile changes are being made, Consider an example, a transaction of amout 1000/- taken place between the two departments in the university: pur this execution of the program, a system failure occurs and that the aout from the account of department A will be reduced but the deparime™ will not be credited with that amount, It leads to inconsistent databases"‘overview of Databse Systems is ‘overview ol Dotabse systems as ‘The fund transfer must be atomic, means the transaction must happen nits entirety or not at all. It is difficult to ensure atomicity in a conventional file-processing system. 6. Concurrent-access anomalies In order to improve the overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent update may result, in inconsistent data. For example, suppose a registration program maintains a count of students registered for a course, in order to enforce the limits on the number of students registered. If two students register the course concurrently, the seat will be allotted to both and the total seat count 50 exceeds the limit available. This two-registration program execution may read the value 49, and both would then write back 50 leading to an incorrect increase of one seat which violates the limits of 50 students. 7. Security problems Not every user of the database system should be able to access all the data, For example, in a university, payroll personnel need to be only part of the database that has financial information. They need not have the access permission for the student academic database. However, imposing such security requirements is challenging when these type of applicatio: ae are introduced {o the file processing system on an ad-hoc basis. YG apvantaces OF DBMS 1. Data Independence I refers the characteristic of being able to modify the schema at on level ofthe database system without the altering the schema at higher leve 1 here are two lypes of data independence. 1, Logical data independence li, Physical data independenceRelational Database Management system i. Logical data independence It can be defined as the capacity to change the conceptual schema without having to change the external schemas or application programs, If wwe do any changes in the conceptual view, then the user view data wouig not be changed. It occurs at the user interface level. ii, Physical data independence Itcan be defined as the capacity to change the internal schema without having to change the conceptual schema. Physical data independence is used to separate conceptual levels from the internal levels. It occurs at the logical interface level. 2. Efficient data access b DBMS utilizes a variety of sophisticated techniques to store and Tetrieve data efficiently. This feature is especially important if the data stored on external storage devices, 3. Data integrity and security If the data is always accessed through the DBMS, it can enforce integrity constraints on the data. Also, the DBMS can enforce access controls that govern what data is visible to different classes of users. 4. Data administration Data administration refers to the whole set of activities performed by strator to ensure that a database is always available ® needed. When several users share the data, centralizing the administra of data can offer significant improvements, 5. Concurrent access and crash recovery thst It schedules concurrent accesses to the data in such a manner users can think of the data as being accessed by only one user at atime | DBMS also protects users from the effects of system fail) Booverview of Databse Systems ay 7 @s PEOPLE WHO DEAL WITH DATABASES 4. Database Administrator One of the main reasons for using DBMS 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 Database Administrator. They are responsible for authorizing access to the database, for co-ordinating and monitoring its use and for acquiring software and hardware resources ‘as needed. The functions of DBA include: Schema Definition Storage structure and access-method definition Schema and physical organization modification + Granting of authorization for data access 2, Database Designers Database designers are responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data. They interact with each potential group of users and develop views of the database that meet the data and processing requirements of these groups. 3. End Users End users are going to perform various database operations like querying, updating and generating reports. The different types of end users are as follows: 4. Categories of End users % Causal End users - These people occasionally access the database, but they need different information each time. They are usually middle or high-level managers or other occasional browsers who employ a complex database query language to Specify their requests.12 Relational Database Management system, % Parametric End users or Naive users ~ These are the database users who are communicating the databases through a meny driven application program. ~ Sophisticated End users — Sophisticated users are those who are creating the database. These types of users do not use any software and not writing the program code. They directly interact with the database system using query languages like SQL. 4 Stand Alone users ~ Users who have personal databases with the help of ready-made program packages that provide easy-to-use menu based or graphics-based interfaces. > System Analyst — They determine the requirements of end users, | especially native end users and develop specifications for canned | transactions that meet these requirements. } ~ Application Programmers — The users who are responsible for developing the application programs or user interface. The application programs could be written in high level language such as Java, .net, php et.) C_—9 1.9 VIEW OF DATA A major purpose of a database system is to provide users with at abstract view of the data. That is, the system hides certain details of hov data are stored and maintained, 1.9.1 Data Abstraction or Data independence Database systems are made up of complex data structures. TO cast the user interaction with database, the developers hide irrelevant desis from users. The process of hiding irrelevant details from user is called & abstraction. There are three levels of abstraction in DBMS which iss in fig 1.3, they are 1. Physical or Internal level 2. Logical or Conceptual level 3. View or External et!‘Overview of Databse Systems. 4a cee SS (Logical Level) Conceptual Level {Internal Schema) Physical Level _ Figure 1.3 Levels of Abstraction in DBMS 1. Physical or Internal level The physical level is a very low-level representation of the entire database and tell us how the data are stored, It represents actual physical Storage structure and access paths. 2. Logical or Conceptual level This is the middle level of 3-level data abstraction architecture. The Conceptual schema describes the database structure of the whole database for the Community users, It hides information about the physical storage Structures and focuses on describing data types, entities, relationships etc. Mt describes what data js stored in the database, 3. View or External level Highest level of data abstraction. This level describes the user : action with database system, External level is only related to the data Which is viewed by specit external views of the d inter fic end users. It consists of a number of different latabase,Relational Database Management system, 1.14 1.9.2 Instances and Schema The data which is stored in the database at a particular moment of time is called an instance of the database. The overall design of the database is called database schema. The database schema is the skeleton structure of the database and it represents the logical view of the entire database, Schema will not be changed frequently. A database schema is designed by the database designers to help programmers whose software will interact with the database. The process of database creation is called data modelling. Database systems have several schemas, based on the levels of abstraction. The physical schema describes the database design at the physical level and the logical schema describes the database design at the logical level. At the view level, a database may have numerous schemas which is also known as subschemas, that describes various views of the database. y (1.9.3 Data Models ‘The entire structure of the database can be described at each level of abstraction using a data model. It Provides the conceptual tool for data description, data relationships, consistency constraints and semantics. The purpose of a data model is to represent data and to make the data understandable. The following three data models are: 1. Relational Model or Record-based Model This is the most widel. 'Y accepted data model. In this model, data in the form of rows and colum : ns within a table and also shows the in-betwee" relationships among them unique name. In record-b; of fixed format records o. - Each table contains multiple columns o ‘ased model, the database consists of a numbe' é xed f various types. Each record type defines a fix" number of fields or attributes with fixed length. 2. Entity-Relationship model‘world element called as an entity and the association between those objects is said to be relationship. An entity set is a collection of abovesaid entities of same type and relationship set is a collection of relationships of same ype. 3. Object-Based model Object-Base model is a combination of object-oriented programming and relational model. It is an extension of E-R model with concept of functions or methods, encapsulation and object identity. 4, Semi-structured Data model The semi-structured data model allows the data specifications at places where the individual data items of the same type may have different attribute set. This in contrast to the data models mentioned earlier, where every data item of particular type must have the same set of attributes. The Extensible Markup Language (XML) is widely used to represent semi structured data.) g-2 1.10 DATABASE LANGUAGES A database system provides a data-definition language to specify the database schema and a data manipulation language to express database queries and updates, 1.10.1 Data Manipulation Language DML is a language that provides a set of operations for accessing and ‘manipulating data ina database, It is applied in the external, conceptual and Internal level. The following operations on database comes under DML: ~ Insertion of new data in the database * Modification of existing data in the database % Retrieval of data from the database * Deletion of data in the database1s with the a DML that deal i for information to be ge isthe part of Aqueny langua a reval and statement that £64 SE oor isa cd scaly two types of DME surat DME = Ht requires © 08 specify what data are 1, Procedu : if and exactly howto reve ME dat need procedural DML~Htrequitesausefo Declarative DML or Non : .d without specit specify what data are neede ying how to get those cata 4.40.2 Data Definition Language hat allows the user to define the data and their + with any associated integrity fying the database schema and A language t relationships to other type of data, togethe and security constraints Its used for spec seating table, schema, indexes, constraints etc rructure and access methods used by the database ge and also for ‘The storage st system a st of statements in a special type of DDL called a stora definition language. Certain consistency criteria must be met by the data values contained in the database every time it is updated. The following constraints are: 1. Domain Constraints Domain constrains ean be defined as unique or valid set of vals perio for an atribute in a table, The data type of domain include ring, character, integer, time, currency etc, These values of the attribut® are tested easily by the sy yystem Whenever a ne item i info the database, nw data item is entered 2. Referential Integrity There are cases ‘0 ensure that a value appears in one relation fo" given set of attril ributes also appears for a certain set of attributes in anol relation, Forex ‘ample, the department listed for each course must b& that act ually exists. More precisely, the department. name value in @¢™™ iii, are or my of the ee ee117 tems yw of Databse Sys! = ear in the department_name attribute of some record of the St app tt record mus! ° department relation. 3. Assertions An assertion statement should ensure a certain eee i ist in the database. Domain constraints and referential integrity eae are special forms of assertions. DBMS always checks the eon whenever modifications are done in the Corresponding table. 4, Authorization The authorization gives permission to user to access database, modify thedata or display the information, It also controls the user to access another user object schema such as tables or tows. It can be classified as below: i Read Authorization Allows reading, but not modification of data ii, Insert Authorization Allows insertion of new data, but not modification of existing data, il, Update Authorization Allows modification, but not deletion of data iv, Delete Authorization Allows deletion of data, 1 14 DATABASE ARCHITECTURE The datat Ahitecture d through the netwot rk. The architecture OW users connect to the di latabase to completeRelational Database Manageme! ‘A database stores critical information and helps to access data quickly and securely, Therefore, selecting the correct architecture helps in easy ang. efficient data management. Database architecture as shown in the fig 1,4 can be seen as a single tier or multi-tier. They are: client Application Client Network: Network, Application Server Database System: Server H Database Sy p (a) Two-tier Architecture (b) Three-tier Architecture Figure 1.4 Database Architecture Single-Tier architecture Itis a simple architecture of database in which the client, server and database all reside on the same machine, It means the user can directly contact with the DBMS to make changes and there is no tool for end users. This architecture is used to create local application in which programmes can directly interact with the database for the quick response. Two-Tier architecture {tis based on client/server in which the applications on the clit Side can directly communicate with the database at the server side throv!* query language statements. Application Program interface like ODBC a JDBC are used for Corresponding interactions,Re’ overview of Databse Systems Three-Tier architecture The client machine act as a front end and does not contain any direct database calls. The application on the client-end interacts with an application server which further communicates with the database system. Three-Tier applications are more appropriate for large applications, and for applications that run on the world wide web. 4.11.1 Functional components of Database system structure ‘The major software components in a DBMS environment are depicted infig 1.5. fesse application sopbisticanes, | | jdatbase i programmer casio) | L2dRREE Sor =| _ DMUqueise | [Bouman ‘pplication + Teen DXA compiter bet cod ond erates Cy avery walaaron Lt] ‘Buthorization and Integrity manager disk storage Figure 1.5 Database System Structurei. Relational Database Mang, A database i cach of the responsibilities of the overall system. The a storage manager and query processor. The storage manager otis lal the allocation of storage space and query processor facilitate the 7 % Mlabay tem is partitioned into two modut a Si, system to access the data. 1, Storage Manager A storage manager is a program that provides the interface betes the low-level data stored in the database and the application programs nd queries submitted to the system The communication with the file manage, is handled by the storage manager. ‘Typically provided by a traditional operating system, the file system is used to store the raw data on the disk. It translates the various DML statements into low-level file-system commands. Thus, the storage manager is responsible for storing, retrieving and updating data in the database. Components of Storage manager & Authorization and Integrity Manager ~ Which tests for the satisfaction of integrity constraints and checks the authority of users to access the data. ~ Transaction Manager — It makes sure that concurrent transaction executions happen without conflicting and that the databos maintains consistency despite system faults. ; stort % File Manager ~It manages the allocation of space 0” disk sto® et data structures used to represent information st is Buffer Ma nih vag wate Itis responsible for fetching dal from ee main mi main memory and deciding what data © © lemory. The buffer manager isa critical part of the syste system, since it enables to handle data sizes that are ™°" than the size of main memory, - f la 4overview of Databse Systems i 2. Query Processor This is a major DBMS component that transforms queries into series of low-level instructions directed to the database manager. 3, Database Manager The DM interfaces with user-submitted application programs and queries. The DM accepts queries and examines the external and conceptual schemas to determine what conceptual records are required to satisfy the request. The data manager then communicates with the file manager to perform the request. : 4, DML Preprocessor This module converts DML statements embedded in an application program into standard function calls in the host language. The DML preprocessor must interact with the query processor to generate the appropriate code. 5. DDL Interpreter It interprets DDL statements and records the definitions in the data dictionary. 6. DDL Compiler DDL statements are transformed into a collection of tables with meta-data by the DDL compiler. These tables are then stored in the system catalog while control information is stored in data file headers.s Relational Database Management sy, 1.22 ‘Stem QUESTIONS AND ANSWERS PART - A (2 MARKS) 1. Define Database Management System (DBMS). Database Management System is a collection of interrelated data and a set of programs to access those data. The collection of data is database, 2. What is a database system? The database and DBMS software together is called as database systems. 3. What is the goal of DBMS? The goal of DBMS is to provide an environment that is both convenient and efficient use in storing information into the database and retrieving information from the database. 4. Give the disadvantages of file processing system. Data redundancy and inconsistency ++ Difficult in accessing data Data isolation + Integrity problems * Concurrent access anomalies + Security problems 5. What are the three levels of abstraction? i. Physical level ii. Logical level iii. View level 6. What is physical, logical, view level data abstraction? ‘> Physical level — r . . The lowest level of abstraction describes '°" data are stored. |overview. of Databse Systems i +p Logical level — The next higher-level abstraction, describes what data are stored in database and the relationship among those data. View level — The highest level of abstraction describes only part of entire database. 7, What do you mean by instances and schemas? The collection of information stored in the database at a particular moment is called instance. The overall design of the database is called database schema. 8, What are the two levels of data independence? i. Physical data independence ii. Logical data independence 9. Define data independence Data independence means that the application is independent of the storage structure and access strategy of data. 10. Define Data Dictionary. A data dictionary is a data structure which stores meta data about the structure of the database i.e. the schema of the database. 11. What are the components of storage manager? The components are: +> Authorization and Integrity Manager 4 Transaction Manager + File Manager Buffer Manager 12. Define data model. ‘A data model is a collection of conceptual tools for describing data, data relationships, data semantics and inconsistency constraints.Relational Database Management System, 7 DDL)? 13. What is Data Definition Language ( ifies constructs for schema definition, relation definition, DDL specifies col modification. integrity constraints, views and schema i 2 14. What is DML? The language that enables user to access or manipulate data as e langui 6 organized by appropriate data model. 15. What is storage manager? A storage manager is a program module that provides the interface between the low level data stored ina database and the application programs and queries submitted to the system. REVIEW QUESTIONS PART B - 5 MARKS Differentiate File processing system with Database Management system 2. Discuss about different types of Data models. 3. Explain the purpose of database system. 4: Explain the advantages of Database Management Systems. PART C - 10 MARKS Describe the architecture of DBMS basesChapter 02 ENTITY-RELATIONSHIP MODEL 2.4 INTRODUCTION An Entity-Relationship model is a high-level data model. It is based on the perception of a real world that consists of a collection of data elements and relationships among them. The ER model was created to make database design easier by allowing the specification of an enterprise schema, which defines a database's overall logical structure. ER model describes the structure of a database with the help of a diagram is known as Entity-Relationship diagram (ER diagram). 2.2 ENTITY-RELATIONSHIP DIAGRAM AE:R diagram is a visual representation of data that describes how data is related to each other. Geometric shapes and their meaning is given below: Table 2.1 Entity-Relationship Diagram S.No Articles Representation ———— 1 Entity Ld 3 Weak Entity4 Relational Databa: 22 ise Menaemens, S.No Articles Representation 4 Relationship oO 5 Identifying Relationship © Link attributes to entity 6 sets and entity sets to eee relationship sets 7 Multivalued attributes Ce» 8 Key attribute c= 9 Composite attribute SO —_— = 10 Derived attribute ( ) ~~ _— 2.2.1 Components of ER diagram The three main components of Entity-Relationship Diagram #** 1, 2. 35 Entities Attributes Relationships |Entity: Relationship Model 2.3 1. Entities Entity belongs to the real-world elements i.e., it may be any object, class, person or place. An entity can be represented as rectangle. For example, The “students” in a class is an entity and the “employee” in a company is an entity. An Entity set is a set of entities of the same type that share the same properties or attributes. 2. Attributes An attribute describes the properties or characteristics of an entity. Eclipse is used to represent an attribute. 3. Relationship The association among entities is called a relationship. For example, a student enrolls in a course, an employee works at a department. Here, enroll and works at are called relationships. It is represented by diamond shape. 2.2.2 Relationship Degree A relationship degree indicates the number of entities or participants associated with a’ relationship. There are three types in degree of relationships. They are: 1. Unary relationship 2. Binary relationship 3. Ternary relationship Unary relationship Unary Relationship Figure 2.1 Degiee~ !Ss In unary relationship, Iris also called recursive relationship and having the degree 1, 2. Binary relationship A binary relationship exists when there are two different entig, participating in the relationship. Its degree is two. Binary Relationship 1" Entity — —[ any | Deguee =? Figure 2.2 3. Ternary relationship A temary relationship exists when there are three different ents Participating in the relationship and having the degree 3. ~ Ternary Relationship a) 2 Entity Degree +3 Figure 2.3 23 ENTITY CLASSIFICATION Strong Entity ali’ ie by i atc. tc. It is represented by a single rectane!™ i on ig 0! Wo string entities is represented by a single dit!" A strong entity j 'y is not d , + will have primary key aim “Pendent on any other entity and it wil relationship between t For example, a Stud ; f ent entity can ex; ity io! exist wi entity schema. without need any other_ i .} —§ ert hip Model iniity-Relations! weak Entity Weak entity refers to an entity that cannot be uniquely identified by and must rely on a relationship with another entity. It is, double rectangle. For example, a bank account cannot fied without knowing the bank to which the account jts own attributes represented by @ be uniquely identi belongs, so bank account is a weak entity. 2.4 ATTRIBUTE CLASSIFICATION ‘Attribute can be classified in the following types. They are 1. Key attribute 2. Composite attribute 3. Multivalued attribute 4. Derived attribute } Akey attribute can uniquely identify an entity from an entity set. For example, student roll number can uniquely identify a student from a group of students. + Acomposite attribute is an attribute that consists of several other attributes, For example, in student entity, the address of the student is a composite which composed of other attributes such as pin code, state, country etc. ~ An attribute that contains more than one values is called ‘multivalued attribute. For example, in student entity, each of them have more than one phone number. © A derived attribute is one that can be derived from another attributes and it is dynamic manner. For example, Student age isa derived attribute in which it changes year by year and it is derived from date of birth. Constraints AnE , contents NE-R schema may define certain constraints to which the contents ofa data database must conform,Relational Database Managemen; ot S¥sten, 1, Mapping Car >, Key Constraints 3. Participation Constraints ‘es or Cardinality ratio 2.4.1 Mapping Cardinal Jt is data constraint and defines the numerical attributes of i relationship between two entities or entity set. For a binary relationship sg Ron an entity set A and B, there are four possible mapping cardinaliti, They are 1. One to One (1:1) One to Many (1:M) Many to One (M:1) Many to Many (M:M) aeN 1. One to One Mapping When a single instance of an entity is associated with a single instance of another entity then it is called one to one relationship. For example, ort student can enroll in atmost one course. Student ‘Course Figure 2.4 One to One Mapping _ 2+ One to Many Mapping Customer M [one | Figure 2.5 One to Many MappingEntity-Relationship Model 9 When a single instance of an entity is associated with more than one instances of other entity then it is called one to many relationships. For example, a customer can place many orders but an order cannot be placed by many customers, 3, Many to One Mapping When more than one instances of an entity is associated with a single instance of another entity then it is called many to one relationship. For example, many students can study in a single college but a student cannot study in many college. Student College Figure 2.6 Many to One Mapping 4, Many to Many Mapping When more than one instances of an entity is associated with more than one instances of another entity then it is called many to many relationships. For example, a student can be assigned to many projects and a project can be assigned to many students. Student College Figure 2.7 Many to Many Mapping 2.4.2 Key Constraints Key is an attribute or collection of attributes that uniquely identifies an entity among entity set. For example, the rollno of ‘a student is uniquely identifiable attribute among students.oY Relational Database Managemen, Syston, 28 1. Candidate Key ‘The minimal set of attributes that uniquely identifies each OCCUTTen, of an entity type is called candidate key. An entity type may have more th one candidate key. For example, an employee in a company has a Unig employee number and also unique passport number. 2. Primary Key ‘The candidate key that is selected to uniquely identify each ‘Occurrence of an entity type. For example, from the two candidate keys from the employee entity, one of which is selected as a primary key either employee number or passport number. 3. Alternate Key Except primary key, other candidate keys are called as alternate ke). For example, Passport number of an employee entity is referred as alternate key. 4. Composite Key Whenever a prim ary Key consists of more than one attribute, it Known as Composite key 2.4.3 Participation Constraints instar ttiPaion consis deine the least number of relations? stances in Which "0 an entity must compulsorily participate, There are on constraints, They are: Total Participation ‘pes of participat 1, 2. Partial Participation 1. Total Participation Each entity in the Entity set must one relationship instance in that relat entity set and re, st inat leo Compulsorily participate in at | vag! Onship set. It is represented usi"® lationship set, double line betweenentiy-Relationship Model Total Student Course Figure 2.8 Total Participation 29 2. Partial Particip Each entity in the entity set has the option of participating or not participating in the relationship instance in that relationship set. It is also called as optional participation and represented in single line. Partial Enrolled Course in ‘Student Figure 2.9 Partial Participation 2.5 ER DIAGRAM TO TABLES In previous sections we have discussed about the components, building blocks of the ER diagram. First we will convert simple FE diagram into tables Inthe end we will take a complex diagram and then it Will convert into set of tables. 1. Strong Entity sets with simple attributes ‘The strong entity set becomes the table and the attributes of the entity ity set becomes Set becomes the table attributes. The key attribute of the ent {he primary key of the table. Pp e ‘ibutes: Let us consider an example entity set Employee with the a ‘nto EName, Age, Emp. id, and salary. When we convert the BR diag" id, > ° lAbe, the entity set become table named Employee with come attributes,Y D rr Relational Database Managemen, Ne, EMPLOYEE C satay Empld| EName | Age | Salary Figure 2.10 Employee Relation with simple attributes ot PS Emplq | FName | LName age _| Salat Figure 2.41 Employee Relation with Composite attributes é adRelationship Model 2.11 Conversion of strong entity set with composite attributes ER to table. ere the entity set will be the table and simple attributes of the composite vill become the attributes of the table. The composite attribute itself will ye ignored during conversion. Let us consider the above entity set Employee having the attributes as EName, Empid, Age and Salary. The attribute EName is a composite having simple attributes such as FName (First Name) and LName (Last Name). When converting ER to table, we have not used composite attributes instead we have used simple attributes in the table representation. 3. Strong Entity set with Multi-valued attributes Entity set with multi-valued attributes will require two tables in the relational model. Lets take same entity set Employee and we added a new multi-valued attribute Department (Dept). An employee can work in multiple department, therefore the attribute Dept is marked as multi~ valued. The multi-valued attribute represents there is a need to include more than one table in the ER diagram. As you can see we have created two tables to represent the ER diagram. Empld [—FName—|_LName | Empld__[ Dept Figure 2.12 Employee Relation with Multi-values attributes1 Relational Database Management sy,, 212 Shem 4. Relationship set to Table conversion While converting the relationship set to a table, the primary ati of the two entity sets becomes the table attributes and if the relationship ‘| has any attribute that also becomes the attribute of the table, From the following example, two entity sets Employee ang Department. They are associated to each other using the Works Telationship set. To convert this relationship set to table, the primary attributes of ty, table such as Empld, Deptld and all the other attributes should be added in the new table. EMPLOYEE Works DEPARTMENT aD | Empld | Deptfd | Experience Figure 2.13 Relation set to Table conversion 2.6 ISA RELATIONSHIP The ISA relationship is most frequently used to rept generalization and Specialization in an ER diagram, It is used in situatio® ‘when an entity type contains certain entities that has special properties shared by all the entities. Then those two entities willbe represented” “ISA” relationship between them, hen two or more entities of lower level combined to form @ vig i «aed level if they have some common attributes i.e., subclasses are combi™ Jsnfity-Relationship Model 2.13 ake a superclass is said to be generalization. It is a bottom-up approach. mi zor example, Faculty and student entities can be generalized and create a aigher level entity person. Person 4 isa > Yn Yo Z Faculty Student Figure 2.14 Bottom-Up Approach One higher level entity can be broken down into two lower-level entities is said to be specialization. It is used to identify the subset of an entity set that shares some distinguishing characteristics. Specialization is top-down approach. For example, when considering employee entity can be specialized as tester or developer based on what role they play in the company, EMPLOYEE TESTER DEVELOPER. Figure 2.15 Top-Down Approachsf Relational Database Management S¥stem, a2 LO Sst 2.7 AGGREGATION AND COMPOSITION Aggregation refers to the process by nie entities are combined to form a meaningful entity. The relationship with iB corresponding ent is aggregated in to a higher-level entity. The specific entities are combine because they do not make sense on their own. It represents a “HAS-A” o, “|S-PART-OF” relationship between entity types. Composition is a stronger form of aggregation where the part cannot exist without its containing whole entity type and the part can only be pan of one entity type. je Employee Project | Manager Figure 2.16 Aggregation & Composition From the above ER diagram, the “Manager” entity makes “Manes relationship with either “Employee” or “Project” entity alone the it "" nol make any sense because he has to manage both, The relationst? 7 " i ° Works-on” between “Employee” and “Project” act as one entity that iS relationship “Manages” with the entity “Manager”. 2.8 ADVANTAGES oF ENTITY-RELATIONSHIP MODEL ran ofl © Conceptual Simplicity — tt shows the pictorial representation relationships between o tities and attributes.Eniity-Retationship Model ais enihy-Relotionship Model ats 4 Explicit Visual Representations ~ The database design can be easily comprehended after consulting the diagram Communication Effectiveness — Standard symbols representing different information facilitate understanding of the working of the database after completion. + Immense Flexibility — ER data model can be easily converted into any other data model with minor manipulations.Relational Database Management Syste, el 2.18 NSWERS QUESTIONS AND Al PART - A (2 MARKS) i 2 1. What is an Entity-Relationship model? del is a collection of basic objects called entities and ie ER model coe ; ; li an hip among those objects. An entity is a thing or object in the Teal relationshi, y world that is distinguishable from other objects. H i 2 2. What are the basic notations available in E-R model? + Entity Sets + Relationship Sets + Attributes 3. What are attributes? Give examples. Anentity is represented by aset of attributes. Attributes are descriptive Properties possessed by each member of an entity set. Example: Attrib tes of Student entity are Student Name, Roll No, Address, Course ete, Entity set — The set aa | k an entity set of all entities of the same type is termed be divided j . attrib, ed into fy Student Name wig tS tha have suby rther With Finge and Last N Parts is co ‘ame’ q is simple (E.g., stud? Mposite attribute (E#Entity-Relationship Model n—TK_ 6. Define weak and strong entity sets, ‘The entity set that do not have key attribute of their own are called weak entity sets. The entity set that has a primary key is termed as strong entity. 7. What does the cardinality ratio specify? Mapping cardinalities or cardinality ratios express the number of entities to which another entity can be associated. Mapping cardinalities must be one of the following: + One to One One to Many + + Many to One + Many to Many REVIEW QUESTIONS PART B - 5 MARKS Explain the components of E-R diagram. 2. Discuss in detail about the Relationship degree. 3. Explain Mapping cardinalities. 4. Explain the advantages of Entity-Relationship model. PART C - 10 MARKS '- Explain in detail about the Constraints. 2. Write in detail about the conversion of E-R diagram to Tables. 3. Describe i) ISA Relationship ii) Aggregation & Composition. i Draw ER diagram for University database. D 7 "aw ER diagram for Company database.Chapter 03 RELATIONAL MODEL 3.4 INTRODUCTION A data model is a collection of conceptual tools for describing data relationships, data semantics and consistency constraints. Based on this, the chapter deals with relational model. The relational model uses a collection of tables to represent both data and the relationships among those data. The relational model describes data at the logical and view levels, abstracting away low-level details of data storage. When compared to the older models such as hierarchical and network model, relational model provides simplicity and ease of use to the programmers. Now a days relational model is the predominant data model for commercial data-processing applications. The relational model is based on set theory and order predicate logic. The term relation is derived from the set theory of mathematics. 3.2 CODD’S RULE CODD’s rule were developed by Dr.Edgar F.Codd in 1985 who has vast research knowledge on the relational model of database systems. In order to compare the idea of DBMS with the relational model, Codd presented 13 rules for database and if it adheres to the rule they are represented as a eal relational database. These 13 rules are called as Codd’s 12 rule, + Rule 0 - Foundation Rule: it states that for a system to quality as an RDBMS, it should manage database entirely through the telational capabilities.YS Relational Database Management Systen, “ Rule 1 — Information Rule: A database contains user dat ff metadata and each of these should be stored in the way of Tow, and columns. In other words, the data should be in table format Rule 2 — Guaranteed Access Rule: Each and every piece of da, can be accessed logically with the combination of table name primary key and column name (attribute value). Rule 3 — Systematic Treatment of Null Values: This rule handle; the NULLs in the database. In which if any cell contains missing data, no values or not applicable data, then it is treated as a NULL value instead of not specifying zero or empty. + Rule 4 — Active Online Catalog: Catalog represents Dat Dictionary is the structured description of complete databax and it stores online. It should Hiave the access privileges wher it is accessed by the users. Implementation of query languages needed to access the database. * Rule 5— Comprehensive Data Sub-Language Rule: Any RDBMS database should not be accessed directly and it needs some Strong query language to access. Such language should posses linear syntax that supports data definition, data manipulation ao) transaction management operations, Suppose if the database * accessed without any query language, it will be treated as violatiot + = Vi " Rule 6 ~ View Updating Rule: All the view of a table # ‘heoretically and practically updatable by the database systems + Rule langu; Delet 7~ High-Level Insert, Update and Delete Rule: Every See developed to handle the database should support Is Delete and Update operations Ht should "ke Union, union All, Minus, Intersect operations are not limited h able to handle multiple ati also support set oper and Intersect All. AIL to handle a single row or a table. Ht" ables or rows in its operations. * Rule 8 ~ Physica, Data | nda, , . seal ston of data must be Pendence Rule: The physical SO" Independent on he application prog"Relational Medel any d changed, it will not affect the external application program which is used to access the database. } Rule 9— Logical Data Independence Rule: If there is a change in the logical structure of the database, the user view of data should not change just like physical data independence. For example, suppose if two tables are merged into one table, there should be no impact of these changes in the user view applications. Rule 10— Integrity Independence Rule: Database should be able to apply integrity rules by any query languages and must not dependent on any front-end applications. The key and constraints in the database should be strong enough to handle the integrity. ~ Rule 11 — Distribution Independence Rule: The database can be located at the user server or any other various network locations. When it is accessed by the end user, they will only get the impression that the data is located at one site only. ~ Rule 12 — Non-Subversion Rule: If low level access is allowed to a system, it should not be able to subvert or bypass integrity rules to change the data. This can be achieved by some sort of encryption. 3.3 RELATION DATA MODEL The relational model represents the database as a collection of relations. Each relation is referred as tables. A table is organized in a row and column format, where rows represent records and columns represent the attributes. The intersection of a row and column represents a single data value, A tuple is a single row of a table that contains a single record for that telation. The data type describing the types of values that can appear ineach column is called a domain. A relation consists of two main components, they are:gelalional Database Management gy, en oo 1. Relation Schema 2, Relation Instance 4. Relation Schema ; specifies the relatio f each column. For example, relatig, niversity database. The schema 5 n’s name (Table name), name , each column (attribute) and domain 0 schema for Student information inaul ‘Student (Sid: string, Name: string, Age: integer, Course: String) Here Student is the table name, column or attributes are Sid, Name, ‘Age and Course an string is the domain name of attribute Sid, Course ax ‘Name. 2. Relation Instance ‘Arelation instance is a table. An instance of a relation is a set of tuples also called records, in which each tuple has the same number of fields the relation schema For example, Table Name —Student, Attributes — Sid, Name, Age an! Course Attributes S| Name | Age Course io | x5 BS i 4 ae 2 c Cont Science Tuples pm i BSc Maths Tain deny From the al ' he rows i i relation, the columns or attributes has a unique vas Ot identical, The degree of the relation i.e Num Column in the table is 4 ay 1e car relation that is ind th inal i : avn ‘dinality of the relation that i weRelational Model e 3.5 3.3.1 Key YT ae 7 ev Gr are no duplicate tuples within a relation. Therefore, we need to beable to identify one or more attributes that uniquely identifies each tuple ina relation. ~ Super Key—Anattribute or combination of attributes that uniquely identifies each row in a table. A super key may contain additional attributes that are not necessary for unique identification. ~ Candidate Key-— A super key that does not contain a subset of attributes that is itself a super key. Primary Key — A candidate key selected to uniquely identify all other attribute values in any given row, cannot contain null values. ~ Foreign Key - An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null) a G 3.3.2 Integrity > Cintegrity constraints is a set of rules that ‘are applied on the table column or relationships to ensure that the overall: validity, integrity and consistency of the data present in the database is maintained. There are three types of integrity, they are: 1. Entity Integrity 2. Domain Integrity 3. Referential Integrity 1. Entity Integrity Entity integrity is used to ensure that the primary key cannot be null. A primary key is used to identify individual tuples in a table. If the Primary key in a table has null value, then it is impossible to identify a Particular record in the table, For example, consider Student table having the attributes such as Sid (Student Id), Name, Age and Course. If the nameRelational Database Managemen, wad the student is same for two records, we can update th of the stu fe rds, w = i help of the Sid (primary key) attri red student details with the help oo it should not be NULL. 2. Domain Integrity Domain integrity contains a certain set of rules or conditions to restrigg the kind of attributes or values a column can hold in the database table. The data type of a domain can be string, integer, character etc. For example, from the Student table the attribute Age should accey only the integer not any string values and attribute Name should have st tring values not any integer values. 3. Referential Integrity Referential Integrity ensures that there must always exist a valid relationship between two relational database tables. This valid relationshig between the two tables confirms that a foreign key exists in a table, I should always reference a corres table or be null) by 3.4 RELATIONAL, ALGEBRA ponding value or attribute in the othe: Relational Algebra is a relational model. [t Collects Occurrences of re} this action, The p database by using Various Ope the data, We have di ided the Procedural query language that works o! instances of relations as input and give itput. It uses various operations to perforn the query language is to retrieve data fron erations such as insert, update and delete 0! Se operations in two categories: ations as ou urpose of 1. Basic Operations Select, Proj im » Project, Ren; Si one ame, Union, - 1 » Set Difference, Cartesian produ!Relational Model Derived Operations Natural Join, Left, Right, Full outer Join, Intersection, Division Here Select, project and rename are unary operators because they ‘operate on one relation. The Union, cartesian product, Join, Division are binary operators which handle pair of relations. Let us discuss with examples. 3.4.1 Basic Operation SELECT Operation Select operation is done by Selection operator (6 sigma). Itis used to retrieve rows (tuples) from the table where the given condition is satisfied. Syntax: © Predicate/Condition (Relation/ Table) Consider a relation Student having the attributes Sid, Name, Age, Course and Employee relation having the attributes Eid, Name and Age. Sid Name Age Course 101 XYZ 19 B.Sc Computer Science 214 ABC 20 BCA 325 EFG 18 B.Sc Maths 435 PQR 18 B.Sc Physics 337 cece 20 BCA 488 yyy 18 B.Sc Computer Sciencese mn Relational Database Mana, Semen g E3 EFG E4 EEF ES AAA Query - 6 couse Boa (Student), select the row from the table Studer where “course” = BCA. The following output for the above query is Sid Name Age Course 214 ABC 20 BCA 337 ccc 20 BCA PROJECT Operation Project operation is done by Projection operator (n= Pi. tis used retrieve certain attributes (columns) from the table. Iti also called vet Partitioning as it separates the table vertically. Syntax: 7 auribute/eolumn (Relation) ‘the urse of Query ~ 1 Name, Course (Student), Project the Name and Co! student relation, ‘The following output for the above query isRelational Model Name Course XYZ B.Sc Computer Science ABC BCA EFG B.Sc Maths PQR B.Sc Physics ece BCA YYY B.Sc Computer Science RENAME Operation 3.9 Rename operation can rename either the relation’s name or attribute name or both. It is denoted by p. Syntax: P attribute-name (Relation/ Table) Query —P (suai, stName, StAge, Steourse) (Student) UNION Operation Union operation is done by Union operator and represented by symbol U. It selects all the tuples from both relations but with the exception that for the union of two relations or tables must have the same set of attributes. It eliminates duplicate tuples. As it is a binary operator, requires two operands. Syntax: RLU R2, here R1 & R2 are relations J Query - 1 ame (Student) U 1 ame (Employee) The following output for the above query is