CU-BCA-SEM-III-Database Management System- Second Draft-converted

BACHELOR OF COMPUTER APPLICATIONS SEMESTER-III DATABASE MANAGEMENT SYSTEMS BCA132

CHANDIGARH UNIVERSITY Institute of Distance and Online Learning Course Development Committee Prof. (Dr.) R.S.Bawa Pro Chancellor, Chandigarh University, Gharuan, Punjab Advisors Prof. (Dr.) Bharat Bhushan, Director – IGNOU Prof. (Dr.) Majulika Srivastava, Director – CIQA, IGNOU Programme Coordinators & Editing Team Master of Business Administration (MBA) Bachelor of Business Administration (BBA) Coordinator – Dr. Rupali Arora Coordinator – Dr. Simran Jewandah Master of Computer Applications (MCA) Bachelor of Computer Applications (BCA) Coordinator – Dr. Raju Kumar Coordinator – Dr. Manisha Malhotra Master of Commerce (M.Com.) Bachelor of Commerce (B.Com.) Coordinator – Dr. Aman Jindal Coordinator – Dr. Minakshi Garg Master of Arts (Psychology) Bachelor of Science (Travel &Tourism Management) Coordinator – Dr. Samerjeet Kaur Coordinator – Dr. Shikha Sharma Master of Arts (English) Bachelor of Arts (General) Coordinator – Dr. Ashita Chadha Coordinator – Ms. Neeraj Gohlan Academic and Administrative Management Prof. (Dr.) R. M. Bhagat Prof. (Dr.) S.S. Sehgal Executive Director – Sciences Registrar Prof. (Dr.) Manaswini Acharya Prof. (Dr.) Gurpreet Singh Executive Director – Liberal Arts Director – IDOL © No part of this publication should be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording and/or otherwise without the prior written permission of the authors and the publisher. SLM SPECIALLY PREPARED FOR CU IDOL STUDENTS Printed and Published by: TeamLease Edtech Limited www.teamleaseedtech.com CONTACT NO:- 01133002345 For: CHANDIGARH UNIVERSITY 2 Institute of Distance and Online Learning CU IDOL SELF LEARNING MATERIAL (SLM)

First Published in 2021 All rights reserved. No Part of this book may be reproduced or transmitted, in any form or by any means, without permission in writing from Chandigarh University. Any person who does any unauthorized act in relation to this book may be liable to criminal prosecution and civil claims for damages. This book is meant for educational and learning purpose. The authors of the book has/have taken all reasonable care to ensure that the contents of the book do not violate any existing copyright or other intellectual property rights of any person in any manner whatsoever. In the event the Authors has/ have been unable to track any source and if any copyright has been inadvertently infringed, please notify the publisher in writing for corrective action. 3 CU IDOL SELF LEARNING MATERIAL (SLM)

CONTENT Unit 1: Introduction....................................................................................................................5 Unit 2: Introduction To Database Languages & Environments...............................................18 Unit 3: Architecture .................................................................................................................29 Unit 4: Data Models 1..............................................................................................................41 Unit 5: Roles And Structural Constraints: ...............................................................................63 Unit 6: Data Models 2..............................................................................................................77 Unit 7: File Organization 1 ......................................................................................................86 Unit 8: File Organization 2 ....................................................................................................102 Unit 9: Relational Data Model...............................................................................................120 Unit 10: Relational Algebra Sql.............................................................................................128 Unit 11: Eer And Er To Relational Mapping.........................................................................145 Unit 12: Data Normalization 1...............................................................................................161 Unit 13: Data Normalization 2...............................................................................................168 Unit 14: Concurrency Control 1 ............................................................................................180 Unit 15: Concurrency Control 2 ............................................................................................200 4 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT 1: INTRODUCTION Structure 1.0 Learning Objective 1.1 Introduction 1.2 Characteristics of Database Approach 1.3 Various Views of Data Models 1.4 Summary 1.5 Keywords 1.6 Learning Activity 1.7 Unit End Questions 1.8 References 1.0 LEARNING OBJECTIVE Upon successful completion of this chapter, students will be able to: ▪ Describe the fundamental elements of relational database management systems. ▪ Explain the importance of data models and the different views. 1.1 INTRODUCTION Database The database is a collection of inter-related data which helps in efficient retrieval, insertion, and deletion of data from the database and organizes the data in the form of tables, views, schemas, reports, etc. For Example, a university database organizes the data about students, faculty, and admin staff, etc. which helps in efficient retrieval, insertion, and deletion of data from it. Database Management System The software which is used to manage the database is called Database Management System (DBMS). For Example, MySQL, Oracle, etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks: Data Definition: It helps in the creation, modification, and removal of definitions that define the organization of data in the database. Data Updation: It helps in the insertion, modification, and deletion of the actual data in the database. Data Retrieval: It helps in the retrieval of data from the database which can be used by applications for various purposes. 5 CU IDOL SELF LEARNING MATERIAL (SLM)

User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information corrupted by unexpected failure. Paradigm Shift from File System to DBMS File System manages data using files in a hard disk. Users are allowed to create, delete, and update the files according to their requirements. Let us consider the example of a file-based University Management System. Data of students is available to their respective Departments, Academics Section, Result Section, Accounts Section, Hostel Office, etc. Some of the data is common for all sections like Roll No, Name, Father Name, Address and Phone number of students but some data is available to a particular section only like Hostel allotment number which is a part of hostel office. Redundancy of data: Data is said to be redundant if the same data is copied at many places. If a student wants to change the Phone number, he has to get it updated in various sections. Similarly, old records must be deleted from all sections representing that student. Inconsistency of Data: Data is said to be inconsistent if multiple copies of the same data do not match with each other. If the Phone number is different in Accounts Section and Academics Section, it will be inconsistent. Inconsistency may be because of typing errors or not updating all copies of the same data. Difficult Data Access: A user should know the exact location of the file to access data, so the process is very cumbersome and tedious. If the user wants to search the student hostel allotment number of a student from 10000 unsorted students’ records, how difficult it can be. Unauthorized Access: File Systems may lead to unauthorized access to data. If a student gets access to a file having his marks, he can change it in an unauthorized way. No Concurrent Access: The access of the same data by multiple users at the same time is known as concurrency. The file system does not allow concurrency as data can be accessed by only one user at a time. No Backup and Recovery: The file system does not incorporate any backup and recovery of data if a file is lost or corrupted. 1.2 CHARACTERISTICS OF THE DATABASE APPROACH Self-Describing Nature of Database System: Database systems contain the complete definition of database structure and constraints along with the database. The definition is stored in the catalogue and it is called metadata which describes the structure, type, storage format, and various constraints on data items. DBMS software facilitates the functioning of any number of database applications provided the definition is stored in the catalogue. DBMS refers to the catalogue to identify the structure 6 CU IDOL SELF LEARNING MATERIAL (SLM)

belong to a specific database stored in the system. But in a traditional file processing system, the data definition will be part of the application programs and this will work with only one specific database. The structure of the database will be defined as a part of the application program itself Whenever we need to access a particular student record in the university database the DBMS software refers to the catalogue to determine the size and structure of the student file whereas in traditional file systems it is coded within the programs itself. Insulation between Programs and Data, and the Data abstraction The structure of the database file is stored in a catalogue independently from the programs; this property is known as Program data independence. Suppose if we want to add a particular data item say Birth Date into a STUDENT record then we only need to change the description of the student file in the catalogue whereas in file processing systems the program itself needs to be changed. Another important property is program operation independence. The implementation of an operation can be changed without affecting the interface. User programs can invoke an operation by passing the arguments without knowing the implementation of the operation. The above-mentioned characteristic is called data abstraction. All the storage details of the data item and implementation details of operations are hidden from the end-user Thus DBMS provides only the conceptual representation of data. The data model is one type of data abstraction that hides most of these details from the end-user. Consider figure 1.2. the internal implementation of the file can be defined by the record length and each data item can be represented by the starting position and the length. A student record can be represented as Table 1.1: Student Record Representation Data Item Name Starting position in Record Length in characters Name 1 30 31 4 Student Number 35 4 Class 39 4 Major But database users are not concerned with the location and length of the data item in the record. And only concerned about the value should be returned when a reference is made to the Name of the student. Only conceptual representation is there in figure 1.2, many other details like file organization such as the access paths are hidden from the end-users by DBMS. 7 CU IDOL SELF LEARNING MATERIAL (SLM)

Support Multiple Views of the Data A database typically has many users, each of whom may require a different perspective or view of the database. A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored. Some users may not need to be aware of whether the data they refer to is stored or derived. A multiuser DBMS whose users have a variety of distinct applications must provide facilities for defining multiple views. For example, one user of the database may be interested only in accessing and printing the transcript of each student; the view for this user is shown in Table 1.2. A second user, who is interested only in checking that students have taken all the prerequisites of each course for which they register, may require the view shown in Table 1.3. Table 1.2: Transcript Table 1.3: Course Prerequisites Sharing of Data and Multiuser Transaction Processing The main role of the multiuser DBMS is to ensure that concurrent transactions run appropriately. With the help of concurrency control software DBMS ensures that multiple users are trying to update the database in a controlled manner so that the results produced must be correct. E.g.: Ticket Reservation System. When many clerks try to reserve a seat DBMS must ensure that each seat can be accessible by only one clerk. Such types of applications are called online transaction processing applications. A transaction is executing programs that can have multiple database accesses. Properties are isolation property which ensures that all transactions execute independently without interfering other transactions and atomicity property which ensures that either all 8 CU IDOL SELF LEARNING MATERIAL (SLM)

operations in the transaction must be executed in a proper way or none are. ACID properties of transactions are Atomicity, Concurrency, Isolation, and Durability. Atomicity Property: a bank transfer should be an atomic set of 2 operations: a debit from one account and a credit to a different account. The debit and credit should be enforced as an atomic cluster. If those 2 operations don't each succeed, then the transfer is either not fairly in favour of the bank or the account holder. Consistency property: For the bank transaction example, the consistency could also be outlined as having the combined account balance of the 2 accounts is relentless. To implement consistency within the bank transfer example, the debit and credit operations merely got to be for an identical amount of cash. Isolation property: An electronic commerce website needs that a replacement product class navigation page to look at precisely the same time because the product detail pages that describe the new product. During this case, there's a necessity to update and add multiple directory entries underneath the management of a transaction. Not solely is it necessary to own the updates be atomic, however, it's additionally necessary that a client who is presently looking should not see the updates current. Durability: Within the previous example, durability may be provided just by guaranteeing adequate information recovery so that all new filing system entries that represent the addition of a replacement product to the positioning seem when a system stops responding. This needs a system with information backup, recovery, and high accessibility mechanisms. Data Abstraction in DBMS: Database systems are made-up of complex data structures. To ease the user interaction with database, the developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is called data abstraction. Types of abstraction: Physical level: This is the lowest level of data abstraction. It describes how data is actually stored in database. You can get the complex data structure details at this level. Logical level: This is the middle level of 3-level data abstraction architecture. It describes what data is stored in database. View level: Highest level of data abstraction. This level describes the user interaction with database system. 9 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 1.1 Three Levels of abstraction Example: Let’s say we are storing customer information in a customer table. At physical level these records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory. These details are often hidden from the programmers. At the logical level these records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented. The programmers generally work at this level because they are aware of such things about database systems. At view level, user just interact with system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them. Self-Describing nature: A database is self-describing; it always describes and narrates itself. It contains the description of the whole data structure, the constraints, and the variables. It makes it different from the traditional file management system in which definition was not part of the application program. These definitions are used by the users and DBMS software when needed. Logical Relationship Between Records and Data: A database gives a logical relationship between its records and data. So, a user can access various records depending upon the logical conditions by a single query from the database. 10 CU IDOL SELF LEARNING MATERIAL (SLM)

Control of data redundancy In the database approach, ideally, each data item is stored in only one place in the database. In some cases, data redundancy still exists to improve system performance, but such redundancy is controlled by application programming and kept to a minimum by introducing as little redundancy as possible when designing the database. Restriction of unauthorized access Not all users of a database system will have the same accessing privileges. For example, one user might have read-only access (i.e., the ability to read a file but not make changes), while another might have read and write privileges, which is the ability to both read and modify a file. For this reason, a database management system should provide a security subsystem to create and control different types of user accounts and restrict unauthorized access. Backup and recovery facilities Backup and recovery are methods that allow you to protect your data from loss. The database system provides a separate process, from that of a network backup, for backing up and recovering data. If a hard drive fails and the database stored on the hard drive is not accessible, the only way to recover the database is from a backup. If a computer system fails in the middle of a complex update process, the recovery subsystem is responsible for making sure that the database is restored to its original state. These are two more benefits of a database management system. 1.3 VARIOUS VIEWS OF DATA MODELS Data modelling is the process of creating a data model for the data to be stored in a database. This data model is a conceptual representation of Data objects, the associations between different data objects, and the rules. Data modelling helps in the visual representation of data and enforces business rules, regulatory compliances, and government policies on the data. Data Models ensure consistency in naming conventions, default values, semantics, security while ensuring the quality of the data. Data Model The Data Model is defined as an abstract model that organizes data description, data semantics, and consistency constraints of data. The data model emphasizes what data is needed and how it should be organized instead of what operations will be performed on data. Data Model is like an architect's building plan, which helps to build conceptual models and set a relationship between data items. The two types of Data Modelling Techniques are 1. Entity Relationship (E-R) Model 11 CU IDOL SELF LEARNING MATERIAL (SLM)

2. UML (Unified Modelling Language) A Data Model in Database Management System (DBMS), is the concept of tools that are developed to summarize the description of the database. It is classified into 3 types: Figure 1.2: Types of Data Model Conceptual Data Model This Data Model defines WHAT the system contains. This model is typically created by Business stakeholders and Data Architects. The purpose is to organize, scope, and define business concepts and rules. A Conceptual Data Model is an organized view of database concepts and their relationships. The purpose of creating a conceptual data model is to establish entities, their attributes, and relationships. At this data modelling level, there is hardly any detail available on the actual database structure. Business stakeholders and data architects typically create a conceptual data model. The 3 basic tenants of the Conceptual Data Model are 1. Entity: A real-world thing 2. Attribute: Characteristics or properties of an entity 3. Relationship: Dependency or association between two entities Data model example: • Customer and Product are two entities. Customer number and name are attributes of the Customer entity • Product name and price are attributes of the product entity • The sale is the relationship between the customer and the product 12 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 1.3: Data Model Characteristics of a conceptual data model • Conceptual Data Models Offers Organisation-wide coverage of the business concepts. • Conceptual Data Models are designed and developed for a business audience. • The conceptual model is developed independently of hardware specifications like data storage capacity, location, or software specifications like DBMS vendor and technology. The focus is to represent data as a user will see it in the \"real world.\" Representational Data Model or (Logical Data Model) The logical data model is used to represent only the logical part of the database and does not represent the physical structure of the databases. The representational data model allows us to focus primarily, on the design part of the database. A popular representational model is a Relational model. Figure 1.4: Logical Data Model Characteristics of a Logical data model • Describes data needs for a single project but could integrate with other logical data models based on the scope of the project. • Designed and developed independently from the DBMS. • Data attributes will have data types with exact precisions and length. • Normalization processes to the model are applied typically till 3NF. Physical Data Model All data in a database is stored physically on a secondary storage device such as discs and tapes. This is stored in the form of files, records, and certain other data structures. It has all the information of the format in which the files are present and the structure of the databases, the presence of external data structures, and their relation to each other. 13 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 1.5: Physical Data Model Characteristics of a physical data model • The physical data model describes data need for a single project or application though it may be integrated with other physical data models based on project scope. • Data Model contains relationships between tables that address cardinality and nullability of the relationships. • Developed for a specific version of a DBMS, location, data storage or technology to be used in the project. • Columns should have exact data types, lengths assigned, and default values. • Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are defined. 1.4 SUMMARY • DBMS characteristics are Atomicity, Consistency, Isolation, Durability, Data isolation, Data Security/Protection, Integrity, Consistency, Less redundancy • Limitation of DBMS are Higher cost of hardware/software & training, Complex system, Data loss • Transaction management is a logical unit of processing in a DBMS that entails one or more database access operation • It is a transaction is a program unit whose execution may or may not change the contents of a database. • Not managing concurrent access may create issues like hardware failure and system crashes. • Active, Partially Committed, Committed, Failed & Terminate are important transaction states. • The full form of ACID Properties in DBMS is Atomicity, Consistency, Isolation, and Durability • Three DBMS transactions types are Base on Application Areas, Action, & Structure. • A Schedule is a process of creating a single group of multiple parallel transactions and executing them one by one. 14 CU IDOL SELF LEARNING MATERIAL (SLM)

• Serializability is the process of search for a concurrent schedule whose output is equal to a serial schedule where transactions are executed one after the other. 1.5 KEYWORDS • DBMS: (Database Management System) is an application designed especially to enable user interaction with the software application • DBMS Users: Application Programmers, Database Administrators & End-users • Applications: Banking, Finance, Education, Medical, Airlines, etc. • ACID: Atomicity, Consistency, Isolation, and Durability 1.6 LEARNING ACTIVITY Every organization has an inventory to manage, which takes up a lot of resources. Usually, an organization would assign the duty of inventory management to two or more people who’ll keep an eye on it and ensure that all the supplies are available. If any item is missing, the manager would order the same. This system works effectively if the organization is tiny, but that’s not always the case. You can solve this problem of businesses and build an inventory management system. The goals of your design would be the following: • Increase the inventory turnover • Optimize the inventory and the stock levels • Reduce the number of safe stock levels • Get low material prices. • Make it easy to understand and access. • Reduce the operational costs of the inventory (storage cost, insurance cost, etc.) • Classify the objects in the inventory according to their stock levels An inventory management system will allow its client to save a lot of time and resources. They can find the stock levels in their inventory and plan accordingly. It will make the enterprise more efficient and productive. This is one of the beginner-level database projects on this list. You can work on it even if you don’t have much experience with developing database solutions. ___________________________________________________________________________ ____________________________________________________________________ 1.7 UNIT END QUESTIONS A. Descriptive Questions Short Questions 15 CU IDOL SELF LEARNING MATERIAL (SLM)

1. What are the characteristics of database approach? 2. What is Data Model and its types? 3. What does Conceptual Data Model mean? 4. Compare between logical data model and physical data model? 5. What are ACID properties in DBMS? Long Questions 1. Briefly explain the Data Modelling Techniques. 2. What are the major advantages of DBMS? 3. Explain the various types of data models. 4. What are the main characteristics of the DBMS? 5. Explain in detail all the characteristics of data models. B. Multiple choice Questions 1. Data Model is a collection of conceptual tools for describing. a. Data b. Data Schema c. Consistency Constraints d. All of these 2. Which of the following is not a level of data abstraction? a. Physical Level b. Critical Level c. Logical Level d. View Level 3. Data Models in DBMS are classified into ______ types. a. 5 b. 6 c. 3 d. 4 4. In database management systems, the executing process or executing program which considers the updating or reading of record stored in database is called_________. a. Conceptualization b. Execution c. Implementation d. Transaction 16 CU IDOL SELF LEARNING MATERIAL (SLM)

5. The property of DBMS which ensures the execution of all the operations in transaction or none of the operation is executed is classified as________________. a. Isolation property b. Atomicity property c. Online execution property d. Offline execution property Answer 1.(a) 2. (b) 3. (c) 4. (d) 5. (b) 1.8 REFERENCES Text Books: • T1 R. Elmasri and S.B. Navathe, Fundamentals of Database Systems, Pearson Education, New Delhi. • T2 C.J. Date, An Introduction to Database Systems Pearson Education, New Delhi. • T3 Data, C. and Darwen, H, Reading, A Guide to the SQL Standard, Addison-Wesley Publications, New Delhi. Reference Books: • R1 A. Silberschatz, H.F. Korth and S. Sudarshan, Database System Concepts, McGraw-Hill, International Edition. • R2 Ivan Bayross, SQL / PL/SQL, BPB Publications. 17 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT 2: INTRODUCTION TO DATABASE LANGUAGES & ENVIRONMENTS Structure 2.0 Learning Objective 2.1 Introduction to Database Languages 2.2 Environments 2.3 Advantages of DBMS Over File Processing Systems 2.4 Summary 2.5 Keywords 2.6 Learning Activity 2.7 Unit End Questions 2.8 References 2.0 LEARNING OBJECTIVES After studying this Unit, the student will be able to: ▪ To have a high-level understanding of major DBMS components and their function. ▪ Install, configure and interact with a relational database management system. ▪ To describe the various types of Database Languages. 2.1 INTRODUCTION TO DATABASE LANGUAGES DBMS Languages In many DBMS when there is no separation between levels Data Definition Language (DDL) is used by database designers to define schemas. When there is a clear separation between conceptual and internal levels DDL is used to define Conceptual Schema only. Storage Definition Language (SDL) is used to define an internal schema. The mappings between these languages can be specified in any of these languages. Database Language DDL DML DCL TCL Figure 2.1: Database Language View Definition Languages (VDL) is used to spiffy user views and their mappings to the conceptual schema. DBMS provides Data Manipulation Language (DML) for specifying manipulation operations such as retrieval, insertion, deletion, and updating of data. 18 CU IDOL SELF LEARNING MATERIAL (SLM)

There are two types of DMLS. A High-level or nonprocedural DML is used on its own to specify database operations concisely. A low-level or procedural DML is embedded in the general-purpose programming language. Low-level DML is also called record at a time DML retrieves each record and processes separately. Users can access, update, delete, and store data or information in the database using database languages. Figure 2.2: Types of Database Language Data Definition Language (DDL) It is a language that allows the users to define data and their relationship to other types of data. It is mainly used to create files, databases, data dictionary and tables within databases. It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and authorization information for each table and physical storage structure of each table on disk. CREATE - used to create objects in the database ALTER - used to alters the structure of the database DROP - used to delete objects from the database TRUNCATE - used to remove all records from a table, including all spaces allocated for the records are removed COMMENT - used to add comments to the data dictionary RENAME - used to rename an object Data Manipulation Language (DML) 19 CU IDOL SELF LEARNING MATERIAL (SLM)

It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the database. Data Manipulation Language is mainly of two types: Procedural DML: This type of DML describes what data is to be accessed and how to get that data. Declarative DML or Non-procedural DML: This type of DML only describes what data is to be accessed without specifying how to get it. SELECT - It retrieves data from a database INSERT - It inserts data into a table UPDATE - It updates existing data within a table DELETE - It deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - It calls a PL/SQL or Java subprogram EXPLAIN PLAN - It explains the access path to data LOCK TABLE - It controls concurrency Data Control Language (DCL) There are two other forms of database sub-languages. The Data Control Language (DCL) is used to control privilege in Databases. To perform any operation in the database, such as for creating tables, sequences, or views, we need privileges. Privileges are of two types, Grant - It gives user access privileges to a database. Revoke - It takes back permissions from the user. Transaction control language (TCL) Transaction Control statements are used to run the changes made by DML statements. It allows statements to be grouped into logical transactions. COMMIT - It saves the work done SAVEPOINT - It identifies a point in a transaction to which you can later roll back ROLLBACK - It restores the database to original since the last COMMIT SET TRANSACTION - It changes the transaction options like isolation level and what rollback segment to use 2.2 ENVIRONMENTS A database environment is a collective system of components that comprise and regulates the group of data, management, and use of data, which consist of software, hardware, people, techniques of handling database, and the data also. 20 CU IDOL SELF LEARNING MATERIAL (SLM)

The hardware in a database environment means the computers and computer peripherals that are being used to manage a database, and the software means the whole thing right from the operating system (OS) to the application programs that include database management software like M.S. Access or SQL Server. Again, the people in a database environment include those people who administrate and use the system. The techniques are the rules, concepts, and instructions given to both the people and the software along with the data with the group of facts and information positioned within the database environment. The database management system can be divided into five major components, they are: 1.Hardware 2.Software 3.Data 4.Procedures 5.People Database User / Programmers System Application Programs / Queries DBMS Software Software to Process Queries / Programs Software0to Access Stored Data Stored Database Stored Database Definition ( Meta-Data ) Figure 2.2: Components of DBMS Hardware: When we say hardware, we mean computer, hard disk, I/o channels for data and any other physical components involved before any data is successfully stored into the memory. Consists of a set of physical electronics devices such as computers, I/o devices, storage devices, etc. This provides the interface between computers and the real-world system. This DBMS component is used for keeping and storing the data in the database. When we run oracle or MySQL on our personal computer, then our computers hard disk, our keyboard 21 CU IDOL SELF LEARNING MATERIAL (SLM)

using which we type in all the commands, our computers RAM, ROM all become a part of the DBMS hardware. Software This is the set of programs used to control and manage the overall database. This includes the DBMS software itself, the operating system, the network software being used to share the data among users, and the application program used to access data in the DBMS. This is the main component, as this the program which controls everything. The DBMS software is more like a wrapper around the physical database, which provides us with an easy-to-use interface to store, access and update data. This software component is capable of understanding the database access language and converts it into actual database commands to execute or run them on the database. The software in DBMS environment includes operating system, database management system, application programs, support utility programs. Data The main task of DBMS is to process the data. Here database is defined, constructed, and then data is stored, retrieved, and updated to and from the database. It is the most important component of the database management system. The database contains both the metadata (data about data) and the actual(operational) data. DBMS exists to collect, store, process and access data, the most important component. In a typical database, the user saved data is present and meta data is stored. For example- when I store my name in a database, the DBMS will store when the name was stored in the database, what is the size of the name, is it stored as related data to some other data, or is it independent, all this information is Metadata. Procedures These are the instructions and rules that assist on how to use the DBMS, and in designing and running the database, using documented procedures, to guide the users that operate and manage it. Procedures are used to setup and install a new DBMS to login and logout of DBMS software, to manage DBMS or application programs, to take backup of the database, and to change the structure of the database, etc. People 22 CU IDOL SELF LEARNING MATERIAL (SLM)

The people are who control and manage the databases and perform different types of operations on the database in the DBMS. The people include database administrator, software developer and End user. Database administrator-database administrator is the one who manages the complete database management system. DBA takes care of the security of the DBMS, its availability, managing the license keys, managing user accounts and access, etc. Software developer-This user group is involved in developing and designing the parts of DBMs. End user- These days all the modern applications, web or mobile, store user data. How do you think the do it? Yes, applications are programmed in such a way that they collect user data and store the data on DBMS system running on their server. End user are the one who store, retrieve, update and delete data. 2.3 ADVANTAGES OF DBMS OVER FILE PROCESSING SYSTEMS File System In a typical file processing system, each and every subsystem of the information system will have its own set of files. The typical file-oriented system is supported by a conventional operating system. Permanent records are stored in various files and a number of different application programs are written to extract records from and add records to the appropriate files. Disadvantages of file system 1. Data redundancy and inconsistency 2. Difficulty in accessing data 3. Concurrent access anomalies 4. Security problem 5. Integrity problems 6. Data isolation Data redundancy and inconsistency: Redundancy is the concept of repetition of data. In each data may have more than a single copy. The file system cannot control redundancy of data as each user defines and maintains the needed files for a specific application to run. There may be a possibility that two users are maintaining same files data for different applications. Hence changes made by one user does not reflect in files used by second users, which leads to inconsistency of data. Whereas DBMS controls redundancy by maintaining a single repository of data that is defined once and is accessed by many users. As there is no or less redundancy, data remains consistent. Scalability The database management system is scale up and scale down the data dynamically. To address every business requirement to store data, there is availability from small scale to 23 CU IDOL SELF LEARNING MATERIAL (SLM)

large scale databases. Today almost every apps and online services are using a database management system. Data sharing In a database, the users of the database can share the data among themselves. There are various levels of authorisation to access the data, and consequently the data can only be shared based on the correct authorisation protocols being followed. Many remote users can also access the database simultaneously and share the data between themselves. Data concurrency Concurrent access to data means more than one user is accessing the same data at the same time. Anomalies occur when changes made by one user gets lost because of changes made by other user. File system does not provide any procedure to stop anomalies. Whereas DBMS provides a locking system to stop anomalies to occur. Data searching For every search operation performed on file system, a different application program has to be written. While DBMS provides inbuilt searching operations. User only have to write a small query to retrieve data from database. Data integrity There may be cases when some constraints need to be applied on the data before inserting it in database. The file system does not provide any procedure to check these constraints automatically. Whereas DBMS maintains data integrity by enforcing user defined constraints on data by itself. Backup and Recovery Database Management System automatically takes care of backup and recovery. The users don't need to backup data periodically because this is taken care of by the DBMS. Moreover, it also restores the database after a crash or system failure to its previous condition. Data security A file system provides a password mechanism to protect the database but how longer can the password be protected? No one can guarantee that. This doesn’t happen in the case of DBMS. DBMS has specialized features that help provide shielding to its data. The following are the advantages of DBMS 1. Controlling Data redundancy 2. Elimination of inconsistency 3. Better services to the users 4. Better flexibility 24 CU IDOL SELF LEARNING MATERIAL (SLM)

5. Integrity is improved 6. Standards can be enforced. 7. Security can be improved etc. Database management tools There are lots of database management tools are available. It is lagging in earlier file system data management. Database backup is important. It ensures the availability of a stable database in case if data gets corrupted. Data replication is required for faster availability of database in the various regional areas. Suppose there are large numbers of the user accessing data. Load balancing reduces the load on a particular database by sharing a load among all the replicated databases. There is a need to prevent unwanted data access. So, database monitoring and security have become the biggest concern. 2.4 SUMMARY DDL stands for Data Definition Language. It is used to define database structure or pattern. ▪ Create: It is used to create objects in the database. ▪ Alter: It is used to alter the structure of the database. ▪ Drop: It is used to delete objects from the database. ▪ Truncate: It is used to remove all records from a table. ▪ Rename: It is used to rename an object. ▪ Comment: It is used to comment on the data dictionary. Data Manipulation Language: It is used for accessing and manipulating data in a database. It handles user requests. ▪ Select: It is used to retrieve data from a database. ▪ Insert: It is used to insert data into a table. ▪ Update: It is used to update existing data within a table. ▪ Delete: It is used to delete all records from a table. ▪ Merge: It performs UPSERT operation, i.e., insert or update operations. ▪ Call: It is used to call a structured query language or a Java subprogram. ▪ Explain Plan: It has the parameter of explaining data. ▪ Lock Table: It controls concurrency. Data Control Language: It is used to retrieve the stored or saved data. ▪ Grant: It is used to give user access privileges to a database. ▪ Revoke: It is used to take back permissions from the user. Transaction Control Language’s is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction. ▪ Commit: It is used to save the transaction on the database. ▪ Rollback: It is used to restore the database to original since the last Commit. 25 CU IDOL SELF LEARNING MATERIAL (SLM)

2.5 KEYWORDS • DBMS: (Database Management System) is an application designed especially to enable user interaction with the software application • DDL: Data Definition Language • DML: Data Manipulation Language • DCL: Data Control Language • TCL: Transaction Control Language 2.6 LEARNING ACTIVITY Every organization has an inventory to manage, which takes up a lot of resources. Usually, an organization would assign the duty of inventory management to two or more people who’ll keep an eye on it and ensure that all the supplies are available. If any item is missing, the manager would order the same. This system works effectively if the organization is tiny, but that’s not always the case. You can solve this problem of businesses and build an inventory management system. The goals of your design would be the following: ▪ Make it easy to understand and access. ▪ Reduce the operational costs of the inventory (storage cost, insurance cost, etc.) ▪ Classify the objects in the inventory according to their stock levels An inventory management system will allow its client to save a lot of time and resources. They can find the stock levels in their inventory and plan accordingly. It will make the enterprise more efficient and productive. This is one of the beginner-level database projects on this list. You can work on it even if you don’t have much experience with developing database solutions. ___________________________________________________________________________ ____________________________________________________________________ 2.7 UNIT END QUESTIONS A. Descriptive Questions Short Questions 1. Define the terms DDL and DML? 2. List out the types of DBMS language. 3. What are the disadvantages of file processing system? 4. What are the advantages of using a DBMS file system? 5. What is data redundancy and inconsistency? 26 CU IDOL SELF LEARNING MATERIAL (SLM)

Long Questions 1. Explain the SQL Commands. 2. With a neat diagram, explain the structure of a DBMS environment. 3. Explain the Components of DBMS. 4. Discuss in detail database languages. 5. What are the limitations of File processing systems? How that can be solved by using Database system? B. Multiple choice Questions 1. In SQL, which of the following is not a data Manipulation Language Commands? a. Delete b. Truncate c. Update d. Create 2. Which of the following is not a type of SQL statement? a. Data Manipulation Language (DML) b. Data Definition Language (DDL) c. Data Control Language (DCL) d. Data Communication Language (DCL) 3. Which of the following is generally used for performing tasks like creating the structure of the relations, deleting relation? a. DML (Data Manipulation Language) b. Query c. Relational Schema d. DDL (Data Definition Language) 4. A database management system (DBMS) is a________. a. Hardware system used to create, maintain and provide controlled access to a database b. Hardware system used to create, maintain and provide uncontrolled access to a database c. A software system used to create, maintain and provide controlled access to a database d. A software system used to create, maintain and provide uncontrolled access to a database 5. A database application can perform which of the following activities? 27 CU IDOL SELF LEARNING MATERIAL (SLM)

a. Add records b. Read records c. Update records d. All of these Answer 1.(d) 2. (d) 3. (d) 4. (c) 5. (d) 2.8 REFERENCES Text Books: • T1 R. Elmasri and S.B. Navathe, Fundamentals of Database Systems, Pearson Education, New Delhi. • T2 C.J. Date, An Introduction to Database Systems Pearson Education, New Delhi. • T3 Data, C. and Darwen, H, Reading, A Guide to the SQL Standard, Addison-Wesley Publications, New Delhi. Reference Books: • R1 A. Silberschatz, H.F. Korth and S. Sudarshan, Database System Concepts, McGraw-Hill, International Edition. • R2 Ivan Bayross, SQL / PL/SQL, BPB Publications. 28 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT 3: ARCHITECTURE Structure 3.0. Learning Objective 3.1. DBMS Architecture and Data Independence 3.2. DBA and Its Role 3.3. Summary 3.4. Keywords 3.5. Learning Activity 3.6. Unit End Questions 3.7. References 3.0 LEARNING OBJECTIVE In this unit, the students will be able to: ▪ Describe the design and build a simple database system. ▪ Explain the describe about DBMS Architecture and Data Independence. 3.1 DBMS ARCHITECTURE AND DATA INDEPENDENCE DBMS Architecture The database is a collection of related data. The data can be a collection of facts and figures that can be processed to derive more information. A database management system stores data and retrieves it whenever required. User can also manipulate and produce more information from the present data. A database has characteristics like real-world entity, relation-based tables, isolation of data and application, less redundancy of data and consistency. DBMS also provides a query language which makes it more efficient and which follows the ACID properties. DBMS can be used by multiple users concurrently. The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. The tiers are classified as follows: 1. 1-Tier Architecture 2. 2-Tier Architecture 3. 3-Tier Architecture 4. n-Tier Architecture 1-Tier Architecture 29 CU IDOL SELF LEARNING MATERIAL (SLM)

When a single-tier architecture is used the database is available directly to the user. The user can directly access the database and use the data. Any changes that are done here will be reflected directly in the database. It does not provide a handy tool that can be used by end- users. The 1 tier architecture is mainly used for the development of local applications and they can be used directly to communicate with the database and get quick response. Figure 3.1: 1-Tire Architecture 2-Tier Architecture In two-tier architectures, there are applications on the client-side and they can easily communicate with the database which would be present at the server-side. In order to make this interaction successful user can use APIs like ODBC and JDBC. All the applications and user interfaces that need data for their processing place on the client-side. The server takes up the responsibility of providing functionalities like query processing and transaction management. To connect both these and have a connection with DBMS the client-side application can establish it with the server-side. 30 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 3.2: 2-Tire Architecture 3-Tier Architecture 3-Tier architecture is widely used and helps in designing a database effectively. This architecture has different usages with different applications. It can be used in web applications and distributed applications. The strength in particular is when using this architecture over distributed systems. Figure 3.3: 3-Tire Architecture Database (Data) Tier − At this tier, the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level. Application (Middle) Tier − At this tier reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End-users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. 31 CU IDOL SELF LEARNING MATERIAL (SLM)

Hence, the application layer sits in the middle and acts as a mediator between the end-user and the database. User (Presentation) Tier − End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier. N-Tier Architecture N-tier architecture contains the above three architecture and it involve dividing an application into three different tiers. These would be the 1. Logic tier, 2. Presentation tiers 3. Data tier. Figure 3.4: N-Tire Architecture It is the physical separation of the different parts of the application as opposed to the usually conceptual or logical separation of the elements in the model-view-controller (MVC) framework. Another difference from the MVC framework is that n-tier layers are connected linearly, meaning all communication must go through the middle layer, which is the logic tier. In MVC, there is no actual middle layer because the interaction is triangular; the control layer has access to both the view and model layers and the model also accesses the view; the controller also creates a model based on the requirements and pushes this to the view. Data Independence Data Independence is defined as a property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level. Data independence helps you to keep data separated from all programs that make use of it. You can use this stored data for computing and presentation. In many systems, data independence is an essential function for components of the system. 32 CU IDOL SELF LEARNING MATERIAL (SLM)

External Level At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different view of the database. An external schema is also known as view schema. Each view schema describes the database part that a particular user group is interested and hides the remaining database from that user group. The view schema describes the end user interaction with database systems. Conceptual Level The conceptual schema describes the design of a database at the conceptual level. Conceptual level is also known as logical level. The conceptual schema describes the structure of the whole database. The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data. In the conceptual level, internal details such as an implementation of the data structure are hidden. Programmers and database administrators work at this level. Internal Level The internal level has an internal schema which describes the physical storage structure of the database. The internal schema is also known as a physical schema and It uses the physical data model. It is used to define that how the data will be stored in a block. The physical level is used to describe complex low-level data structures in detail. Types of Data Independence In DBMS there are two types of data independence 1. Physical data independence 2. Logical data independence 33 CU IDOL SELF LEARNING MATERIAL (SLM)

User 1 User 2 User 3 User n View 2 View 3 View n External View 1 Schema Conceptual Conceptual View Schema Internal Internal View Schema Database Figure 3.5: DBMS Data Independence Physical Data Independence Physical data independence helps you to separate conceptual levels from the internal/physical levels. It allows you to provide a logical description of the database without the need to specify physical structures. Compared to Logical Independence, it is easy to achieve physical data independence. Physical independence, you can easily change the physical storage structures or devices with an effect on the conceptual schema. Any change done would be absorbed by the mapping between the conceptual and internal levels. Physical data independence is achieved by the presence of the internal level of the database and then the transformation from the conceptual level of the database to the internal level. Logical Data Independence Logical Data Independence is the ability to change the conceptual scheme without changing 1. External views 2. External API or programs Any change made will be absorbed by the mapping between external and conceptual levels. When compared to Physical Data independence, it is challenging to achieve logical data independence. Difference between Physical and Logical Data Independence Table 3.1: Data Independence 34 CU IDOL SELF LEARNING MATERIAL (SLM)

Logica Data Independence Physical Data Independence Logical Data Independence is mainly Mainly concerned with the storage of the concerned with the structure or changing the data. data definition. It is difficult as the retrieving of data is It is easy to retrieve. mainly dependent on the logical structure of data. Compared to Logic Physical independence Compared to Logical Independence it is it is difficult to achieve logical data easy to achieve physical data independence. independence. You need to make changes in the A change in the physical level usually does Application program if new fields are added not need change at the Application program or deleted from the database. level. Modification at the logical levels is Modifications made at the internal levels significant whenever the logical structures may or may not be needed to improve the of the database are changed. performance of the structure. Concerned with conceptual schema Concerned with internal schema Example: Add/Modify/Delete a new Example: change in compression attribute techniques, hashing algorithms, storage devices, etc 3.2 DBA AND ITS ROLE A Database Administrator is a person or a group of persons who are responsible for managing all the activities related to database system. This job requires a high level of expertise by a person or group of persons. There are very rare chances that only a single person can manage all the database system activities, so companies always have a group of people who take care of database system. A database administrator's (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed. The role of database administrator is 1. Software installation and Maintenance 35 CU IDOL SELF LEARNING MATERIAL (SLM)

A DBA often collaborates on the initial installation and configuration of a database. The system administrator sets up hardware and deploys the operating system for the database server, then the DBA installs the database software and configures it for use. As updates and patches are required, the DBA handles this on-going maintenance. and if a new server is needed, the DBA handles the transfer of data from the existing system to the new platform. 2. Data Extraction, Transformation, and Loading Data extraction, transformation, and loading refers to efficiently importing large volumes of data that have been extracted from multiple systems into a data warehouse environment. This external data is cleaned up and transformed to fit the desired format so that it can be imported into a central repository. 3. Specialised Data Handling Many data contain unstructured data types such as images, documents, or sound and video files. Managing a very large database (VLDB) may require higher-level skills and additional monitoring and tuning to maintain efficiency. 4. Database Backup and Recovery DBAs create backup and recovery plans and procedures based on the use requirement, then make sure that the necessary steps are followed. Backups cost time and money, so the DBA may have to persuade management to take necessary precautions to preserve data. System admins or other personnel may actually create the backups, but it is the DBA’s responsibility to make sure that everything is done on schedule. In the case of a server failure or other form of data loss, the DBA will use existing backups to restore lost information to the system. Different types of failures may require different recovery strategies, and the DBA must be prepared for any eventuality. With technology change, it is becoming ever more typical for a DBA to backup databases to the cloud, Oracle Cloud for Oracle Databases and MS Azure for SQL Server. 5. Security A DBA needs to know potential weaknesses of the database software and the company’s overall system and work to minimise risks. No system is one hundred per cent immune to attacks, but implementing best practices can minimise risks. In the case of a security breach or irregularity, the DBA can consult audit logs to see who has done what to the data. Audit trails are also important when working with regulated data. 6. Authentication 36 CU IDOL SELF LEARNING MATERIAL (SLM)

Setting up employee access is an important aspect of database security. DBAs control who has access and what type of access they are allowed. For instance, a user may have permission to see only certain pieces of information, or they may be denied the ability to make changes to the system. 7. Capacity Planning The DBA needs to know how large the database currently is and how fast it is growing in order to make predictions about future needs. Storage refers to how much room the database takes up in server and backup space. Capacity refers to usage level. If the company is growing quickly and adding many new users, the DBA will have to create the capacity to handle the extra workload. 8. Performance Monitoring Monitoring databases for performance issues is part of the on-going system maintenance a DBA performs. If some part of the system is slowing down processing, the DBA may need to make configuration changes to the software or add additional hardware capacity. Many types of monitoring tools are available, and part of the DBA’s job is to understand what they need to track to improve the system. 3rd party organisations can be ideal for outsourcing this aspect, but make sure they offer modern DBA support. 9. Database Tuning Performance monitoring shows where the database should be tweaked to operate as efficiently as possible. The physical configuration, the way the database is indexed, and how queries are handled can all have a dramatic effect on database performance. With effective monitoring, it is possible to proactively tune a system based on application and usage instead of waiting until a problem develops. 10. Troubleshooting DBAs are on call for troubleshooting in case of any problems. Whether they need to quickly restore lost data or correct an issue to minimise damage, a DBA needs to quickly understand and respond to problems when they occur. 3.3 SUMMARY ▪ An Architecture of DBMS helps in design, development, implementation, and maintenance of a database ▪ The simplest database system architecture is 1 tier where the Client, Server, and Database all reside on the same machine 37 CU IDOL SELF LEARNING MATERIAL (SLM)

▪ A two-tier architecture is a database architecture in DBMS where presentation layer runs on a client and data is stored on a server ▪ Three-tier client-server architecture consists of the Presentation layer (PC, Tablet, Mobile, etc.), Application layer (server) and Database Server ▪ A Database Administrator is a person or a group of person who are responsible for managing all the activities related to database system ▪ The First one is Install and configure the software. ▪ Taking Back up and planning for recovery of the database. ▪ database schema designing and make required fundamental database objects. ▪ Design the database schema and create any necessary database objects. ▪ Define ideal application SQL. ▪ Execute Guarantee database security to defend the information. ▪ Work intimately with application engineers and framework chairmen to guarantee all database needs are being met. ▪ Apply patches to fixes or upgrade the database as per requirements. 3.4 KEYWORDS • DBA: Database Administrator • DBMS: Database Management Systems • ACID: ACID Properties are used for maintaining the integrity of database during transaction processing. ACID in DBMS stands for Atomicity, Consistency, Isolation, and Durability. • SQL: Structure Query Language 3.5 LEARNING ACTIVITY Every organization has an inventory to manage, which takes up a lot of resources. Usually, an organization would assign the duty of inventory management to two or more people who’ll keep an eye on it and ensure that all the supplies are available. If any item is missing, the manager would order the same. This system works effectively if the organization is tiny, but that’s not always the case. You can solve this problem of businesses and build an inventory management system. The goals of your design would be the following: ▪ Optimize the inventory and the stock levels ▪ Reduce the number of safe stock levels ▪ Get low material prices. ▪ Make it easy to understand and access. An inventory management system will allow its client to save a lot of time and resources. They can find the stock levels in their inventory and plan accordingly. It will make the enterprise more efficient and productive. 38 CU IDOL SELF LEARNING MATERIAL (SLM)

This is one of the beginner-level database projects on this list. You can work on it even if you don’t have much experience with developing database solutions. ___________________________________________________________________________ ____________________________________________________________________ 3.6 UNIT END QUESTIONS A. Descriptive Questions. Short Questions 1. What is a DBA? 2. What is the role of DBA? 3. What is Logical Data Independence? 4. What is the difference between logical and physical data independence? 5. What are the ACID properties in DBMS? Long Questions 1. Describe about DBA and its roles. 2. Explain the different types of database users.? 3. Illustrate the difference between Physical and Logical Data Independence. 4. What is data independence? Why it is needed in database? 5. Elaborate in detail about DBMS Architecture. B. Multiple choice Questions 1. The type of data independence in which the internal schema can be modified without modifying the conceptual schema is classified as___________. a. Logical data independence b. Physical data independence c. Conceptual level independence d. External level independence 2. Data independence means____________. a. Data is defined separately and not included in programs b. Programs are not dependent on the physical attributes of data c. Programs are not dependent on the logical attributes of data d. Both B and C 3. DBMS helps achieve________. a. Data independence b. Centralized control of data 39 CU IDOL SELF LEARNING MATERIAL (SLM)

c. Neither A nor B d. Both A and B 4. _______ is the collection of the interrelated data and set of the program to access them. a. Programming Language b. Database c. Data Structure d. Database Management System 5. A database is the complex type of the ______. a. Application b. Data Structure c. Manager d. File Answer 1.(b) 2. (d) 3. (d) 4. (d) 5. (b) 3.7 REFERENCES Text Books: • T1 R. Elmasri and S.B. Navathe, Fundamentals of Database Systems, Pearson Education, New Delhi. • T2 C.J. Date, An Introduction to Database Systems Pearson Education, New Delhi. • T3 Data, C. and Darwen, H, Reading, A Guide to the SQL Standard, Addison-Wesley Publications, New Delhi. Reference Books: • R1 A. Silberschatz, H.F. Korth and S. Sudarshan, Database System Concepts, McGraw-Hill, International Edition. • R2 Ivan Bayross, SQL / PL/SQL, BPB Publications. 40 CU IDOL SELF LEARNING MATERIAL (SLM)

UNIT 4: DATA MODELS 1 Structure 4.0 Learning objective 4.1 E-R Diagram 4.2 Mapping Constraints 4.3 Keys 4.4 Reduction of E-R Diagram into Tables 4.5 Summary 4.6 Keywords 4.7 Learning Activity 4.8 Unit End Questions 4.9 References 4.0 LEARNING OBJECTIVE After studying this Unit, student will be able to: ▪ Develop the logical design of the database using data modeling concepts. ▪ Design ER-models to represent simple database application scenarios. ▪ Convert the ER-model to relational tables, populate relational database and formulate SQL queries on data. 4.1 E-R DIAGRAM ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified domain. ER diagrams help to explain the logical structure of databases. The ER Model represents real- world entities and the relationships between them. ER diagrams are created based on three basic concepts: entities, attributes and relationships. It also develops a very simple and easy to design view of data. 41 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 4.1:ER Diagram The components of an ER diagram ER Diagrams are composed of entities, relationships and attributes. They also depict cardinality, which defines relationships in terms of numbers. Entity Figure 4.2: Component of ER Model CU IDOL SELF LEARNING MATERIAL (SLM) 42

Entity is a real word thing such as a person, object, concept or event that can have data stored about it. Think of entities as nouns. Examples: a customer, student, car or product. Typically shown as a rectangle. Figure 4.3: Entity Entity categories: Entities are categorized as strong, weak or associative. A strong entity can be defined solely by its own attributes, while a weak entity cannot. An associative entity associates entities (or elements) within an entity set. Weak Entity A weak entity is an entity that depends on the existence of another entity. In more technical terms it can be defined as an entity that cannot be identified by its own attributes. It uses a foreign key combined with its attributed to form the primary key. An entity like order item is a good example for this. The order item will be meaningless without an order so it depends on the existence of the order. Order Order Item Figure 4.4: Weak Entity Entity type Entity is a group of objects with the same properties that are identified by the enterprise as having an independent existence. The basic concept of the ER model is the entity type that is used to represent a group of 'objects' in the 'real world' with the same properties. Figure 4.5: Entity Type An entity type has an independent existence within a database. Each entity type is shown as a rectangle labelled with the name of the entity, which is usually a singular noun 43 CU IDOL SELF LEARNING MATERIAL (SLM)

Entity Set Entity set is same as an entity type, but defined at a particular point in time, such as students enrolled in a class on the first day. Other examples: Customers who purchased last month, cars currently registered in Florida. A related term is instance, in which the specific person or car would be an instance of the entity set. Entity keys Entity key is used to an attribute that uniquely defines an entity in an entity set. Entity keys can be super, candidate or primary. Super key: A set of attributes (one or more) that together define an entity in an entity set. Candidate key: A minimal super key, meaning it has the least possible number of attributes to still be a super key. An entity set may have more than one candidate key. Primary key: A candidate key chosen by the database designer to uniquely identify the entity set. Foreign key: Identifies the relationship between more than one entity. Attribute The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute. An attribute is a property, trait, or characteristic of an entity, relationship, or another attribute. For example, the attribute Inventory Item Name is an attribute of the entity Inventory Item. An entity can have as many attributes as necessary. Meanwhile, attributes can also have their own specific attributes. For example, the attribute “customer address” can have the attributes number, street, city, and state. Figure 4.6: Attribute Key Attribute The attribute which uniquely identifies each entity in the entity set is called key attribute. For example, Roll_No will be unique for each student. In ER diagram, key attribute is represented by an oval with underlying lines. 44 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 4.7: Key Attribute Composite Attribute An attribute composed of many other attributes is called as composite attribute. For example, Address attribute of student Entity type consists of Street, City, State, and Country. In composite attribute is represented by an oval comprising of ovals. Figure 4.8: Composite Attribute Multivalued Attribute If an attribute can have more than one value it is called a multi-valued attribute. It is important to note that this is different from an attribute having its own attributes. For example, a teacher entity can have multiple subject values. Figure 4.9: Multivalued Attribute Derived Attribute An attribute based on another attribute. This is found rarely in ER diagrams. For example, for a circle, the area can be derived from the radius. Relationship Figure 4.10: Derived Attribute CU IDOL SELF LEARNING MATERIAL (SLM) 45

A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship. Figure 4.11: Relationship Types of relationship: 1. One-to-One Relationship 2. One-to-many Relationship 3. Many-to-one Relationship 4. Many-to-one Relationship One-to-One Relationship When only one instance of an entity is associated with the relationship, then it is known as one to one relationship. For example, A female can marry to one male, and a male can marry to one female. Figure 4.12: One to One Relationship One-to-many relationship When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship. For example, Scientist can invent many inventions, but the invention is done by the only specific scientist. Figure 4.13: One to Many Relationship 46 Many-to-one relationship CU IDOL SELF LEARNING MATERIAL (SLM)

When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship. For example, Student enrols for only one course, but a course can have many students. Figure 4.14: Many to One Relationship Many-to-many relationship When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship. For example, Employee can assign by many projects and project can have many employees. Figure 4.15: Many to Many Relationship 4.2 MAPPING CONSTRAINTS Mapping cardinalities defines the relationship between numbers of entities in one entity set with the number of entities to other entity sets. It is most useful in describing the relationship sets that involve more than two entity sets. For binary relationship set R on an entity set A and B, there are four possible mapping cardinalities. Types of Mapping Cardinalities: 1. One to one (1:1) 2. One to many (1:M) 3. Many to one (M:1) 4. Many to many (M:M) One to one (1:1): As its name implies, it maps one entity of the first entity set with another one in the second one entity set. In the below image this has depicted that one entity from entity set A is associated with at most one entity of other one entity set B. 47 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 4.16: One to One Relationship For example - a customer has only one ID. Figure 4.17: One to One Relationship (Customer and ID) If a single instance of an entity is connected with one instances of another entity, then it is called a relationship between one and many. One to many (1:M): An entity from set A can be aligned with more than one entity from set B, but an entity from set B can be associated with a limit of one entity. Figure 4.18: One to Many Relationships For example - a customer may place many orders, but many customers do not place an order. 48 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 4.19: One to Many Relationship (Customer and Order) If a single instance of an entity is connected to more than one instances of another entity, then it is called a relationship between one and many. Many to one (M:1): More than one entity from set A could be paired with a maximum of one entity from set B, but more than one entity from set B can be paired with a maximum of one entity from set A. Figure 4.20: Many to One Relationship For example – many customers may place 1 order. Figure 4.21: Many to One Relationship (Customer and Order) If a single instance of the second one entity is connected with more than one instance of the first entity, then it is referred to as many to one relationship. Many to many (M:M): It is possible to connect one entity from set A with more than one entity from set B and vice versa. 49 CU IDOL SELF LEARNING MATERIAL (SLM)

Figure 4.22: Many to Many Relationship For example – many customers may place many orders. Figure 4.23: Many to Many Relationship (Customer and Order) If more than one instance of the first entity is connected with more than one instance of the second entity, it is called a many to many relationships. 4.3 KEYS ▪ Keys play an important role in the relational database. ▪ It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables. For example: In Student table, ID is used as a key because it is unique for each student. In PERSON table, passport_number, license_number, SSN are keys since they are unique for each person. 50 CU IDOL SELF LEARNING MATERIAL (SLM)