MODULE-1 (BCS403-DBMS) (VTU 2022 SCHEME) Introduction to Databases: Introduction, Characteristics of database approach, Advantages of using the DBMS approach, History of database applications. Overview of Database Languages and Architectures: Data Models, Schemas, and Instanees. Three schema architecture and data independence, database languages, and interfaces, The Database System environment. Conceptual Data Modelling using Entities and Relationships: Entity typos, Fntity sots and structural constraints, Weak entity types, ER diagrams, Specialization and Generalization, Textbook 1: Ch 1.1 to 1.8, 2.1 to 2.6, 3.1 to 3.10 Introduction to Databases 1.1 Introduction to Databases 1.1.1 Database: A database is a collection of related data. Databases are organized collections of data, generally stored and accessed electronically from a computer system. Databases facilitate the storage, retrieval, modification, and deletion of data. A data mean known facts that can be recorded and that have implicit meaning, Foreg, consider the names, telephone numbers, and addresses of the people you know. You may have recorded this data in an indexed address book or you may have stored it on a hard drive, using a persoaal computer and software such as Microsoft Access or Excel. This collection of related data with an implicit meaning is a database. A database has the following implicit properties: e A database represents some aspect of the real world, sometimes called the miniworld or the universe of discourse (UoD). Changes to the miniworld are reflected in the database. ‘A database is a logically coherent collection of data with some inherent meaning. A random assortment of data cannot correctly be referred to as a database. > A database is designed, built, and populated with data for a specific purpose, It has an intended group of users and some preconceived applications in which these users are interested, w Database Management System (DBMS): A DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore(Defining: Defining 2 database involves specifying the data types, structures, and constraints of the data to be stored in the database. The database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary; it is called meta-data. Gi)Constructing: Constmcting the database is the process of storing the data on some storage medium that is controlled by the DBMS. (ili)Manipulating: Manipulating a database includes functions such as querying the database to retrieve specific data, updating the database to reflect changes in the miniworld, and generating reports from the data. (iv)Sharing: Sharing a database allows multiple users and programs to access the database simultaneously. Other important functions provided by the DBMS include protecting the database and maintaining it over a long period of time. (¥) Protection: Protection includes system protection against hardware or software malfunction (or exashes) and security protection against unauthorized or malicious access. (vi) Maintaining: A typical large database may have a life cycle of many years, so the DEMS must be able to maintain the database system by allowing the system to evolve as ‘requirements change over time sore/Programmers ‘Aepiicaton ProgamelOueies | 1 Queries/ Programe | ‘Software to Access ‘Stored Data Figure 1.1: A simplified database system environment Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreFigure 1.1 shows a simplified database system environment, Here, the application program. accesses the database by sending queries or requests for data to the DBMS. A query typically causes some data to be retrieved; a transaction may cause some data to be read and some data to be written into the database, 1.2 Characteristics of Database Approach: In the database approach, a single repository maintains data that is defined once and then accessed by various users repeatedly through queries, transactions, and application programs, ‘The main characteristics of the database approach versus the file-processing approach are the following: > Self-describing nature of a database system Insulation between programs and data, and data abstraction Support of multiple views of the data > Sharing of daia and multiuser transaction processing vv Self-describing nature: The database system contains not only the database itself but also a complete definition or description of the database structure and constraints. This definition is stored in the DBMS catalog, which contains information such as the structure of each file, the type and storage format of each data item, and various constraints on the data, The information stored in the catalog is called meta-data, An example of a database catalog for the database is in Figure 1.2 Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreRelation_name | No_ol_columna STUDENT 7 COURSE z SECTION 3 (GRADE REPORT 3 PREREQUISITE 2 ‘coLUMNS Column. name DataLipe | Belongs_to roaton Tame ‘Character (90) | STUDENT ‘Sudert number | Gharacter(@) | STUDENT Cass Trteger STUDENT Major Mapr_ipe STUDENT Course_name ‘Character (10) | COURSE Course. number 3OOXNNNN ‘COURSE Prerequisite number | SOOXNNNN PREREQUISITE Figure 1.2 Database catalog Insulation between programs and data, and data abstraction: In traditional file processing, the structure of data files is embedded in the application programs, so any changes to the structure of a file may require changing all programs that access that file. By contrast, DBMS access programs do not require such changes in most cases. The structure of data files is stored in the DBMS catalog separately from the access programs. We call this property program-dlata independence. In some types of database systems, such as object-oriented and object-relational systems, users can define operations on data as part of the database definitions. An operation (also called a funetion or method) is specified in two parts. The interface (or signature) of an operation includes the operation name and the date types of its arguments (or parameters), The implementation (or method) of the operationis specified separately and can be changed without effecting the interface. User application programs can operate on the data by invoking these operations through their names and arguments, regardless of how the operations are implemented. This may be termed program-operation independence. ‘The characteristic that allows program-data independence and program-operation independence is called data abstraction. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreSupport of Multiple Views of the Data: Different users can have different views of the data, A database typically has many types of users, each of whom may require a different perspective or view of the database. Views are virtual data that is derived from the database files but is not explicitly stored. 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; and another user may be interested only in checking that students heve taken all the prerequisites of each course Sharing of data and multiuser transaction processing: Databases allow multiple users and applications to share data, Online ‘Transaction Processing (OLTP): The DBMS must include concurrency control software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is comtect. For example, when several reservation agents try to assign a seat on an airline flight, the DBMS should ensure that each seat can be accessed by only one agent at a time for assignment to a passenger. These types of applications are generally called online transaction processing (OLTP) applications. A fundamental role of multiuser DBMS software is to ensure that concurrent transactions operate correctly and efficiently. 1.3 Advantages of Using the DBMS Approac! 1.3.1 Controlling Redundancy: ‘Traditional File Systems: Redundant data storage leads to duplication of effort, wasted storage, and potential inconsistencies. Database Approach: Uses data normalization to minimize redundancy, ensuring each logical data item is stored once, enhancing consistency and saving space 1.3.2 Restricting Unauthorized Access ‘When multiple users share a large database, its likely that most users will not be authorized to access all information in the database. For example, financial data such as salaries and bonuses is often considered confidential, and only authorized persons are allowed to access such data. A DBMS should provide a security and authorization subsystem, wich the DBA uses to create accounts and to specify account restrictions. Then, the DBMS should enforce these resirictions automatically. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore1.3.3 Providing Persistent Storage for Program Objects Databases can be used to provide persistent storage for program objects and data structures. This is one of the main reasons for object-oriented database systems. The values of program variables or objects are discarded once @ program terminates, unless the programmer explicitly stores them in permanent files. A complex object in C+ can be stored permanently in an object-oriented DBMS. Such an object is said to be persistent, since it survives the termination of program execution and can later be directly retrieved by another program. 1.3.4 Providing Storage Structures and Search Techniques for Efficient Query Processing Database systems must provide capabilities for efficiently executing queries and updates. Because the database is typically stored on disk, the DBMS must provide specialized data structures and search techniques to speed up disk search for the desired records. Auxiliary files called indexes are often used for this purpose. Indexes are typically based on tree data structures or hash data structures that are suitably modified for disk search. ‘The query processing and optimization module of the DBMS is responsible for choosing an efficient query execution plan for each query based on the existing storage structures. 1.3.5 Providing Backup and Recovery ADBMS must provide facilities for recovering from hardware or software failures. The backup and recovery subsystem of the DBMS is responsible for recovery. For example, if the computer system fails in the middle of a complex update transaction, the recovery subsystem is responsible for making sure that the database is restored to the state it was in before the transaction started executing, 1.3.6 Providing Multiple User Interfaces Because many types of users with varying levels of technical knowledge use a database, a DBMS should provide a variety of user interfaces. These include apps for mobile users, query languages for casual users, programming language interfaces for application programmers, forms and command codes for parametric users, and menu-driven interfaces and natural language interfaces for standalone users. Both forms-style interfaces and menu-driven interfaces are commonly known as Graphical User Interfaces (GUIs). Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore1.3.7 Representing Complex Relationships among Data ADBMS must have the capability to represent a variety of complex relationships among the data, to define new relationships as they arise, and to retrieve and update related data easily and efficiently 1.3.8 Enforcing Integrity Constraints A DBMS should provide capabilities for defining and enforcing integrity constraints. The simplest type of integrity constraint involves specifying a data type for each data item. Amore complex type of constraint that frequently occurs involves specifying that a record in one file must be related to records in other files. This is known as a referential integrity constraint. Another type of constraint specifies uniqueness on data item values, such as every course record must have a unique value for Course number. This is known as a key or uniqueness coustraiat. 1.3.9 Permitting Inferencing and Actions Using Rules and Triggers Some database systems provide capabilities for defining deduction rules for inferencing new information from the stored database facts. Such systems are called deductive database systems, For example, there may be complex rules in the miniworld application for determining, when a student is on probatioa. These can be specified declaratively as rules, wiich when compiled and maintained by the DBMS can determine all students on probation. A trigger is a form of a rule activated by updates to the table, which results in performing some additional operations to some other tables, sending messages, and so on. More involved procedures to enforce rules are popularly called stored procedure 1.3.10 Additional Implications of Using the Database Approach > Potential for Enforcing Standards: The database approach permits the DBA to define and enforce standards among database users in a large organization. > Reduced Application Development Time: Once a database is up and running, substantially less time is generally required to create new applications using DBMS. facilities. > Flexibility: Modern DBMSs allow certain types of evolutionary changes to the structure of the database without affecting the stored data and the existing application programs. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore> Availability of Up-to-Date Information: As soon as one user’s update is applied to the database, all other users can immediately see this update. This availability of up-to-date information is essential for many transaction-processing applications, such as reservation systems or banking databases, and it is made possible by the concurrency control and recovery subsystems of a DBMS > Economies of Scale: The DBMS approach petmits consolidation of data and applications, thus reducing the amount of wastefill overlap between activities of data-processing personnel in different projects or departments ¢s well as redundancies among applications. 1.4 History of Database Application: Early Database Systems: © The Hierarchical and Network Models were introduced in mid 1960s and dominated during, the 70's. «Utilized hierarchical, network, and inverted file systems. + Faced issues with mixing conceptual relationships with physical storage. + Provided only programming languoge interfaces, making query and transaction implementation laborious, Relational Databases: + Relational model was originally introduced in 1970, was heavily researched and experimented with in IBM Research and several universities. + Introduced data abstraction and program-data independence. + Used high-level query languages instead of programming languages. * Initially slow but improved with new indexing and query optimization techniques. ‘Object-Oriented Databases (OODBs): + Object-Oriented Database Management Systems (OODBMSs) were introduced in late 1980s and early 1990s to cater to the need of complex data processing in CAD and other applications. + Emerged to handle complex, structured data. * Included object-oriented features like encapsulation and inheritance. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore+ Limited use due to complexity and lack of standards, leading to object-relational databases (ORDBMSs). XML(eXtended Markup Language) for Web Data: + Many relational DBMSs have incorporated object database concepts, leading to a new category called Object-Relational DBMSs (ORDBMSs) + Extended relational systems add further capabilities (e.g. for multimedia data, XML, and other data types). + Used for data interchange on the Web, combining document and database concepts. + Script programming languages such as PHP and JavaScript allow generation of dynamic ‘Web pages that are partially generated from a database + Facilitated e-commerce by dynamically extracting and displaying data Extending Database Capabilities: + Adapted to specialized applications like scientific research, image and video storage, data mining, spatial data analysis, and time series data. + Required new data types, structures, and operations beyond basic relational models Big Data and NOSQL: + Developed to manage vast amounts of data from social media, e-commerce, web search indexes and cloud storage. + Provided fast search and reliable storage for nontraditional data types. + NOSQL systems is generally interpreted as Not Only SQL that complement SQL databases, addressing specific application needs. Overview of Database Languages and Architectures 1.5 Data Models, Schemas, and Instance: + Data abstraction: It refers to the suppression of details of data organization and storage, and the highlighting of the essential features for an improved understanding of data. One of the main charscteristics of the database approach is to support data abstraction so that different users can perceive data at their preferred level of detail 15.1 Data models: A data model is a collection of concepts that can be used to describe the structure of a database such as data types, relationships, and constraints that provides the necessary means to achieve the data abstraction. Most data models also include a set of basic operations for specifying retrievals and upéates on the database. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreCategories of Data Models It is categorized according to the types of concepts they use to deseribe the database structure, > Highevel or Conceptual data models > Low-level or Physical data models > Representational (Implementation or Record-based) data models. High-level or conceptual data models provide concepts that are close to the way many users perceive data. Conceptual data models use concepts such as entities, attributes, and relationships. An entity represents a real-world object or concept, such as an employee or a project from the miniworld that is described in the database. An attribute represents some property of interest that further describes an entity, such. as the employee's name or salary. A relationship among two or more entities represents an association among the entities, for example, a works-on relationship between an employee and a project Low-level or physical data models provide concepts that describe the details of how data is stored on the computer storage media, typically magnetic disks. Concepts provided by physical data models are generally meant for computer specialists, not for end users. Physical data models deseribe how data is stored as files in the computer by representing, information such as record formats, record orderings, and access paths ‘An access path is a search structure that makes the search for database records efficient, such as indexing or hashing. An index is an example of an access path that allows direct access to data using an index term or a keyword, Between these two extremes is a class of representational (or Implementation) data models, which provide concepts that may be easily understood by end users but that are not too far removed from the way data is organized in computer storage. Representational data models hide many details of data storage on disk but can be implemented on a computer system directly. This model represent data by using record structures and hence are sometimes called record-based data models. 15.2. Schemas and Instances: In a data model, it is important to distinguish between the description of the database and the database itself. The description of a database is called the database schema, which is specified during database design and is not expected to change frequently. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore‘STUDENT Name [Student number | Class | Major COURSE (Course_name | Course_number | Gredi_hours | Deparment PREREQUISITE (Course number | Prerequsite_ number SECTION Section identifier] Course_number | Semester | Yoar_| Instructor GRADE REPORT ‘Student number | Section identifer] Grade Figure 1.3: Schema diagram A sehoma diagram displays only some aspects of a schema, such as the names of record types and data items, and some types of constraints, The data in the database at a particular moment in time is called a database state or snapshot. It is also called the current set of occurrences or instances in the database. The DBMS is partly responsible for ensuring that every state of the database is a valid state that is, a state that satisfies the structure and constraints specified in the schema. The DBMS stores the descriptions of the schema constructs and constraints—also called the meta-data in the DBMS catalog so that DBMS software can refer to the schema whenever it needs to, The schema is sometimes called the intension, and a database state is called an extension of the schema Although, the schema is not supposed to change frequently, it is not uncommon that changes occasiorally need to be applied to the schema as the application requirements change. This is known as schema evolution 1.6 Three-Schema Architecture The goal of the three-schema architecture, illustrated in Figure 1.4, is to separate the user applications from the physical database. In this architecture, schemas ean be defined at the following three levels: > Internal schema > Conceptual schema > External or View schema Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangaloreoa enauses $ Extomal Level Sao a View View Extornal/Concoptual Mapping Conceptual Level ‘Conceptual Schema: Conceptualntaral Mapping Internal Level Internal Schema ‘Stored Database Figure 1.4: The three-schema architecture. Internal level: It describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database. ‘Conceptual level: It describes the logical structure of the entire database as seen by the DBA. The conceptual schema hides the details of physical storage structures and concentrates on describing cntities, data types, relationships, user operations, and constraints. External level: It describes the user views of the database. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database trom that user group. 1.7 Data independence: It is defined as the capacity to change the schema at one level of a database system without ‘having to change the schema at the next higher level. There are two types of data independence: > Logical data independence > Physical data independence Logical data independence is the capacity to change the conceptual schema without having to change external schemas or application programs. We may change the conceptual schema to expand the database (by adding a record type or data item), to change constraints, or to reduce the database (by removing a record type or data item) Physical data independence is the capacity to change the intemal schema without having to change the conceptual schema. Generally, physical data independence exists in most databases and Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalorefile environments where physical details, such as the exact location of data on disk, and hardware details of storage encoding, placement, compression, splitting, merging of records, and so on are hidden from the user. In general, Data independence occurs because when the schema is changed at some level, the schema at the next higher level remains unchanged; only the mapping between the two levels is changed. 1.8 Database Languages and Interfaces: 1.8.1 Database Languages: Data Definition Language (DDL): + In DBMSs where a clear separation is maintained between the conceptusl and internal levels, the DDL is used to specify the conceptual schema only. ‘Storage Definition Language (SDL): + Specifies internal schema in systems with clear schema separation. The mappings between the two schemas may be specified either by DDL or SDL. View Definition Language (VDL): + Specifies user views and their mapping to the conceptual schema, * Inpractice, relational DBMSs use DDL and SQL for this role. Data Manipulation Language (DML): ‘Typical. The DBMS provides a set of operations or a language called the data manipulation language (DML) for manipulations purpose that includes retrieval, insertion, deletion, and modification of the data, There are two main types of DMLs. «High-Level DML: Non-procedural, Declarative, Set-at-a-time, Set-oriented, used for complex queries. Example: SQL. + Low-Level DML: Procedural, Record-at-e-time, embedded in programming languages. Structured Query Language (SQL): DBMS packages provide an integrated feature of above languages (DDL, VDL, and DML) into a single language called Structured Query Language. Query Languages: + High-level DMLs used interactively are termed query languages. + Designed for casual and end users to specify data requests easily. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreHost language and Data sublanguage: Whenever DML commands, whether high level or low level, are embedded in a general purpose programming language called the host language and the DML is called the data sublanguage. 1.8.2 Interfaces: | Menu-Based Interfaces for Web Clients or Browsing: + Provide lists of options (menus) for query formulation. ‘+ Popular in web and browsing interfaces. «Simplify interaction by eliminating the need to memorize commands. 1 Mobile Apps: * Offer access to data through mobile devices. + Forexample, banking, reservations, and insurance companies, among many others, provide apps that allow users to access their data through a mobile phone or mobile device. ‘1Forms-Based Interfaces: ‘+ Users fill out forms to insert or retrieve data. + Designed for naive users, often utilizing forms specification languages. + Examples include SQL*Forms and Oracle Forms. 11 Graphical User Interfaces (GUIs): + Display schema diagrams in diagrammatic form for query specification. + Combine menus and forms for user interaction, ) Natural Language Interfaces: + Allow queries in natural language (e.g., English). + Use a schema and dictionary to interpret and process queries. + Engage in dialogue for clarifications if needed. ] Keyword-Based Search: + These are somewhat similar to Web search engines, which accept strings of natural language (like English or Spanish) words and match them with documents at specific sites, (Gor local search engines) or Web pages on the Web at large (for engines like Google or Ask). Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore+ They use predefined indexes on words and use ranking functions to retrieve resulting documents, + Emerging in relational databases for "free form” textual queries. 1 Speech Input and Output: ‘+ Applications with limited vocabularies, such as inquiries for telephone directory, flight arrival/departure, and credit card account information, allow speech for input and output to enable customers to access the information, + Utilize speech for both input and output in specific applications. + Converts text to speech for responses and speech to text for queries. | Parametric User Interfaces: + Designed for users with repetitive tasks (e.g,, bank tellers). + Parametric users, such as bank tellers, often have a small set of operations that they must perform repeatedly. Usually, a small set of abbreviated commands is included, with the goal of minimizing the number of keystrokes required for each request. 11 _DBA Interfaces + Contain privileged commands for database administration. + These include commands for creating accounts, setting system parameters, granting account authorization, changing a schema, and reorganizing the storage structures of a database. 1.9 The Database System Environment: ‘The top part of Figure 1.5 refers to the various users of the database environment and their interfaces. The lower part shows the internal modules of the DBMS responsible for storage of data and processing of transactions. The database and the DBMS catalog are usually stored on disk. Access to the disk is controlled primarily by the operating system (OS), which schedules disk read/write. A higher-level stored data manager module of the DBMS controls access to DBMS information that is stored on disk, whether itis part of the database or the catalog, 1.9.1 DBMS Component Modules: Top-Level Interfaces: DBA Interfaces: The DBA staff works on defining the database and tuning it by making changes to its definition using the DDL and other privileged commands. The DDL compiler processes schema definitions, specified in the DDL, and stores descriptions of the schemas (meta-data) in the DBMS catalog. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore‘The catalog includes information such as the names and sizes of files, names and data types of data items, storage details of each file, mapping information among schemas, and constraints. Interactive Query Interfaces: Used for casual users to formulate and submit queries. The Queries are parsed and validated for correctness by a query campiler. The query optimizer is concemed with the reamangement and possible reordering of operations, elimination of redundancies, and use of efficient search algorithms during execution, Application Programmers: Write programs using host languages (¢.g., Java, C++) and compile them to inclade DML commands. Parametric Users: Canned transactions are executed repeatedly by parametric users via PCs or mobile apps; these users simply supply the parameters to the transactions. Each execution is a separate transaction. An example is a bank payment transaction where the account number, payee, and amount may be supplied as parameters. DBA Staff Casual Users ——_Appeation Parametric Users cone + rag) (ese) (ee [at YO Come a Figure 1.5: Component modules of a DBMS and their interactions Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreInternal DBMS Modules: Stored Data Manager: Manages access to data stored on disk, interfacing with the operating system for disk VO. Runtime Database Processor: Executes queries, transactions, and commands. Works with the system catalog and manages buffers. Query Compiler: Parses and compiles queries into an internal form. Query Optimizer: Optimizes query execution plans by rearranging operations and eliminating redundancies. Buffer Management: Manages memory buffers, sometimes handled by the DBMS or the OS. 1.9.2 Database System Utilities Loading Utility: Loads data from external files into the database, reformats as needed. Backup Utility: Creates backup copies of the database for recovery purposes, including incremental backups. Database Storage Reorganization: Reorganizes files and creates new access paths to improve performance. Performance Monitoring: Monitors database usage and provides statistics for performance tuning. 1.9.3 Tools, Application Environments, and Communications Facilities CASE Tools: Used for database design and other development tasks. Data Dictionary: Stores detailed schema and application information, accessible by users and DBAs. Application Development Environments: ‘Tools like PowerBuilder and JBuilder support database application development, including design and GUI creation. Communications Software: Facilitates remote access to the database through various networks and communication devices. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreDB/DC Systems: Integrated DBMS and data communications systems for connecting remote users and distributed databases. 1.9.4 Client-Server Architecture: Client Computer: Runs DBMS client software. Application Server: May mediate between the client and database server. Database Server: Hosts the DBMS and database, handles data storage and processing. Diskiess Clint Sener Client withDisk_ Server and Client ‘Sener — T Gient ‘Ghent Chest Stet ‘Site? Stes Sito =) Figure 1.6: Physical two-tier client/server architecture. Groat Croat Gin Webwork Print Fie DENS Server Server Senet Figure 1.7; Logical two-tier client/server architecture. Conceptual Data Modeling using Entities and Relationships 1.10 Using High-Level Conceptual Data Models for Database Design Figure 1.8 shows a simplified overview of the database design process. The database design process begins with Requirements Collection and Analysis, where user éata and functional needs are collected and analyzed. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreNext, in Conceptual Design, these requirements are translated into a conceptual schema using a high-level data model like the ER model, which outlines entities, relationships, and constraints without implementation details. ‘The Logical Design phase then converts the conceptual schema into a logical schema compatible with a specific DBMS, defining how data is structured within the system. Physical Design follows, focusing on storage structures, indexes, and file organization to optimize database performance Finally, Transaction Implementation involves creating application programs and transactions that execute the database operations, ensuring they align with the functional requirements and the physical design. Each step is crucial for developing an efficient, user-centered database REQUIREMENTS COLLECTION AND ——— | ‘ANALYSIS. y Furctoral Regurements Data Requremerts FUNCTIONAL ANAS. ‘CONGEPTUAL DESIGN High-Level Tansaction Concoptal Schama ‘Specticabon {ina highivel data mode LOGICAL DESIGN (OATAMODEL MAPPING) Logical (Conceptual Schema "APPLICATION PROGRAM {nthe data model of a specibe DBMS) DESIGN 1 | PHYSIGAL DESIGN, TRANSAGTION | _______ yornal Schema IMPLEMENTATION ‘Appcation Programe Figure 1.8: A simplified diagram of database design Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore1.11 Entity Types, Entity Sets, Relationship Entity: It is a thing or object in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) or it may be an object with a conceptual existence (for instance, a company, a job, or a university course). An object that exists and is distinguishable from other objects is entity. Eg: EMPLOYEE. Attributes: The particular properties that describe entity is attribute. For example, an EMPLOYEE entity may be described by the employee's name, age, DOB, salary, and phone number, Several types of attributes occur in the ER model: simple versus composite, single-valued versus multivalued, and stored versus derived. > v - ‘Composite versus Simple (Atomic) Attributes. Composite attributes can be divided into smaller subparts, which represent more basic attributes with independent meanings. For example, the Name attribute of the EMPLOYEE entity can be subdivided into Last Name, Middle Name, First Name. Attributes that are not divisible are called simple or atomic attributes. Composite Figure 1.9: Different types of Attributes Single-Valued versus Multivalued Attributes. Most attributes have a single value for a particular entity; such attributes are called single-valued. For example, Age is a single- valued attribute ofa person. Similarly, one person may have only one phone number, and another person may have two or more phone numbers, therefore, different people can have different numbers of values for the phone number attribute. Such attributes are called multivalued Stored versus Derived Attributes. In some cases, two (or more) attribute values are related—for example, the Age and Birth_date attributes of a person. For a particular person entity, the value of Age can be determined from the current (today’s) date and the value of Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalorethat person's Birth_date. The Age attribute is hence called a derived attribute and is said to be derivable from the Birth date attribute, which is called a stored attribute. NULL attribute: In database systems, NULL values are used to represent missing or inapplicable information for an attribute. NULL also represents unknown information. This can happen in two cases: v 1. When it is known that the attribute value exists but is missing (e.g., a person's height is known but not provided), 2. When it is not known whether the attribute value exists at all c.g., itis unclear ifa person hhas a home phone number). ove Complex attributes: It can be composite or multivalued, allowing for nested structures like addresses with multiple phone numbers, Entity type: It defines a collection (or set) of entities that have the same attributes. Entity Type Name: EMPLOYEE Company Name, Age, Salary Name, Headquarters, President (Sunco O, Houston, John Sith) ae Entity Set: - (Fast Computer, Datas, Bob King) (Extension) —_| (Fred Brown, 40, 3010 or udy Clark, 25, 201) Figure 1.10: Two entity types, EMPLOYEE and COMPANY, and some member entities Entity set: The collection of all entities of a particular entity type in the database at any point in time is called an entity set or entity collection. > Key Attributes of an Entity Type: An entity type usually has one or more attributes whose values are distinct for each individual entity in the entity set. Such an attribute is called a key aitribute, and its values can be used to identify each entity uniquely. Each key attribute has its name underlined, > Value Sets Domains) of Attributes: Each simple attribute of an entity type is associated with a value set (or domain of values), which specifics the set of values that may be assigned to that attribute for each individual entity. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreRelationship: > Degree of a Relationship Type or Cardinality: The degree of a relationship type is the number of participating entity types. A relationship type of degree two is called binary, and one of degree three is called teary > Role name: The role name signifies the role that a participating entity from the entity type plays in each relationship instance, and it helps to explain what the relationship ‘means. Recursive Relationships or Self-Referencing Relationships: The same entity type may participate more than once in a relationship type in different roles. Such relationship types. are called recursive relationships or self-referencing relationships. * 1.12 Structural constraints: Ina database, binary relationships between entities often have constraints that define how entities can be related. Two key constraints are > Cardinality Ratio or Maximum cardinality constraint > Participation or Minimum cardinality constraint Cardinality Ratios or Maximum cardinality constraint for Binary Relationships: The cartinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in. It can be of 4 types, > 1:1 One to One) > 1:M (One to Many) > MN (Many to Many) > Mil (Many to One) An example of a 1:1 binary relationship is MANAGES (Figure 1.11), which relates a department entity to the employee who manages that department EMPLOYEE MANAGES DEPARTMENT Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreFigure 1.11: 1:1 relationship, MANAGES. In the WORKS FOR binary relationship type, DEPARTMENT:EMPLOYEE is of cardinality ratio I:N, mesning that each department can be related to (that is, employs) any number ‘of employees (N),9 but an employee can be related to (work for) at most one department (1) ‘The relationship type WORKS_ON in Figure 1.12 is of cardinality ratio M:N, because the miniworld rule is that an employee can work on several projects and a project can have several ‘employees. Figure 1.12; An M:N relationship, WORKS_ON. Another example for various types of cardinality ratio is shown in Figure 1.13 that defines the nature of the connection between the entities. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangalorePERSON DRIVING_LICENSE Pr P2 Ps Pe BOOK PUBLISHER AUTHOR BOOK Figure 1:13: Types of Cardinality Ratio ‘One-to-One (1:1) Relationship: Fach PERSON entity is related to exactly one DRIVING LICENSE entity, and each DRIVING _LICENSE entity is related to exactly one PERSON. Example: Each person has one unique driving license, and each driving license is assigned to one person. ‘One-to-Many (1:M) Relationship: Each PUBLISHER entity can be related to many BOOK entities, but each BOOK is related to only one PUBLISHER. Example: A publisher can publish multiple books, but each book is published by only one publisher. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore‘Many-to-One (M:1) Relationship: Each BOOK entity is related to one PUBLISHER entity, but each PUBLISHER can have many BOOK entities related to it This is essentially the same relationship as the One-to-Many but viewed from the other entity's perspective. “Many-to-Many (M:N) Relationship: Each AUTHOR can be related to nultiple BOOK entities, and each BOOK can be related to multiple AUTHOR entities. Example: An author can write nmultiple books, and a book can have multiple authors Participation Constraints or Minimum cardinality constraint: The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. This constraint specifies the minimum number of relationship instances that each entity can participate in and is sometimes called the minimum cardinality constraint. There are two types of participation constraints total and partial participation which is shown in Figure 1:14 Total Participation or Existence Dependen Total participation means that every entity in the entity set is involved in the relationship, e.g., each student must be guided by a professor (there are no students who are not guided by any professor), In the Chen notation, this kind of relation is depicted as a double line. Partial Participation: Partial participation means that Some or Part of the entity set are involved in the relationship, e.g., not every professor guides a student (there are professors who don’t). In the Chen notation, a partial participation is represented by a single line. partial participation total participation Figure 1:14 Types of Participation Constraint Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore1.13 Weak Entity Types: ‘Weak entity types: Entity types that do not have key attributes of their own are called weak entity types. A weak entity type normally has a partial key, which is the attribute that can uniquely identify weak entities that are related to the same owner entity. Strong entity types: In contrast, regular entity types that do have a key attribute which include all the examples discussed so far are called strong entity types. Identifying or Owner entity type: Entities belonging to a weak entity type are identified by being, related to specific entities from another entity type in combination with one of their attribute values. Identifying relationship: The relationship type that relates a weak entity type to its owner is called the identifying relationship of the weak entity type. A weak entity type always has a total patticipation constraint (existence dependency) with respect to its identifying relationship because a weak entity cannot be identified without an owner entity. 1.14 ER Diagrams: ER diagram is Entity Relationship diagram which is a graphical representation of entities and their relationships to each other. It is a high-level conceptual data model used in database design. It helps to organize and structure data by defining entities, their attributes (properties), and the relationships that connect them. Symbols of ER diagram are shown in Figure 1.15. U Entity (Rectangle): + Represents an entity, which is @ real-world object or concept with a distinct exist ence in the database, + Example: Student, Employee, Book. ) Weak Entity (Rectangle with double borders): = Represents a weak entity, which depends on another entity (usually has a partial key) and cannot exist without a related strong entity. + Example: Dependent entity in an employee database. (Relationship (Diamond): + Represents a relationship between two or more entities, showing how they are connected. + Example: Enrolled relationship between Student and Course. 1) Mentifying Relationship (Diamond with double borders): + Represents an identifying relationship, which connects a weak entity to its identifying or owner entity. + Example: Relationship between Dependent and Employee. ute (Oval): + Represents an attribute, which is a property or characteristic of aa entity Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore+ Example: Name, Age, Address. y Attribute (Oval with underline): + Represents a key attribute, which uniquely identifies an entity within an entity set. + Example: Student_ID, SSN. Multivalued Attribute (Double oval): + Represents an attribute that can have multiple values for a single entity. + Example: Phone Numbers for a person. 1 Composite Attribute (Oval with connected sub-ovals): + Represents an attribute composed of multiple components, each representing a sub- pact of the attribute. + Example: Full Name composed of First Name, Middle Name, Last Name. Derived Attribute (Dashed oval): «Represents an attribute whose value can be derived from other attributes in the database. © Example: Age derived from Date of Birth. © Total Participation (Double line connecting entity to relationship): + Indicates that every instance of the entity must participate in at least one instance of the relationship © Example: Every Employee must be assigned to a Department, Cardinality Ratio 1 (Numbers 1 and N near the relationship): + Represents the cardinality ratio, indicating that one entity can relate to multiple instances of another entity, but not vice versa. + Example: A Depertinent (1) has many Employees (N). Structural Constraint (min, max) on Participation (Numbers in parentheses near the relationship): + Represents constraints on the minimum and maximum number of relationship instances that an entity can participate in. + Example: A project may require a minimum of | and ¢ maximum of 4 employees. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangaloreis g j eatensne oct Raters Kay Aaa Waaoulves Atibate Composite Atebute Deve Atibte Total Paricpation of ER Coninalty Rat 1: Mfr En ‘Scr Con min mid cnPatcipaton d Ein R Figure 1.15: Notation for ER diagrams Example of ER diagram of Company database: Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangaloreiene a Figure 1.16: ER diagrams for the company schema Entities and attributes: (1) EMPLOYEE: Represents the employees in the company. It has the following attributes: © Phame (First Name) + Minit (Middle Initial) + Lname (Last Name) + Baate (Birth Date) © Address + Salary © Sex + Ssn (Social Security Number) 1 DEPARTMENT: Represents the company's departments, with the following attributes: + Name + Number + Locations (multivalued attribute) | PROJECT: Represents the projects within the company, with the attributes: + Name Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore+ Number Location (1 DEPENDENT: Represents the dependents of the employees, with attributes: + Name + Sex + Birth date + Relationship (the relationship of the dependent to the employee) Relationships: 1. WORKS_FOR: Connects EMPLOYEE to DEPARTMENT. Description: Each employee works fora department Cardinality Ratio: (N:1) - Many employees (N) can work for one department (1), but cach employee is associated with only one department. Participation: ‘Total for Employee: Every employee must be associated with a department. Partial for Department: Not every department is required to have employees. Attributes: Start_date, Number_of employees. 2. MANAGES: Connects EMPLOYEE to DEPARTMENT. Description: An employee may manage a department. Cardinality Ratio: (1:1) - One employee manages one department, and one department is managed by one employee 1 for Employee: Not all employees manage a department. Total for Department: Every department must have a manager. Attribute: Start_date. 3. SUPERVISION: A recursive relationship where EMPLOYEE supervises other employees. Description: An employee may supervise other employees. Cardinality Ratio: (1:M)) - Onc employee (supervisor) can supervise many employees (@N), but each employee is supervised by one supervisor. Participation: Partial for Supervisor: Not every employee supervises others. Partial for Supervisee: Not every employee is supervised 4. WORKS_ON: Connects EMPLOYEE to PROJECT. Description: An employee can work on multiple projects, and a project ean have multiple employees working on it Cardinality Ratio: (M:1) - Many employees can work on many projects, and many projects can have many employees. Participation: Partial for Employee: Not every employee works on projects. Partial for Project: Not every project has employees working on it Attribute: Hours (indicating the number of hours worked by an employee on a project). 5. CONTROLS: Connects DEPARTMENT to PROJECT. Description: A department controls multiple projects Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreCardinality Ratio: (1:N) - One department controls many projects (N), but each, project is controlled by one department. Participation: Partial for Department: Not all departments control projects. Total for Project: Every project must be controlled by a department. 6. DEPENDENTS OF: Connects EMPLOYEE to DEPENDENT. Description: An employee can have multiple dependents, but each dependent is associated with only one employee Cardinality Ratio: (1:N) - One employee can have many dependents (N), but each dependent is linked to one employee. Participation: Partial for Employee: Not every employee has dependents. Total for Dependent: Every dependent must be associated with an employee. Example of Bank Management system : ER diagram of Bank has the following description: + Bank have Customer. + Banks are identified by a name, code, address of main office. + Banks have branches. + Branches are identified by a branch_no., branch_name, address, + Customers are identified by name, eust-id, phone number, address. + Customer can have one or more accounts. + Accounts are identified by account_no., ace_type, balance. Customer can avail loans. + Loans are identified by loan_id, loan_type and amount. Account and loans are related to bank’s branch, This bank ER diagram in Figure 1.17 illustrates key information about bank, including entities such as branches, customers, accounts, and loans. It allows us to understand the relationships between entities. Entities and Attributes: Bank Entity : Attributes of Bank Entity are Bank Name, Code and Address. Code is Primary Key for Bank Entity. Customer Entity : Attributes of Customer Entity are Customer_id, Name, Phone Number and Address. Customer_id is Primary Key for Customer Entity Branch Entity : Attributes of Branch Entity are Branch id, Name and Address. Branch_id is Primary Key for Branch Entity. Account Entity : Attributes of Account Entity are Account_number, Account_Type and Balance, Account_number is Primary Key for Account Entity. Loan Entity Attributes of Loan Entity are Loan_id, Loan_Type and Amount. Loan_id is Primary Key for Loan Ent Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreRelationships Bank has Branches =>1:N One Bank can have many Branches but one Branch can not belong to many Banks, so the relationship between Bank and Branch is one to many relationship. Branch maintain Accounts=>1:N One Branch can have meny Accounts but one Account can not belong to many Branches, so the relationship between Branch and Account is one to many relationship. Branch offer Loans=>1:N One Branch can have many Loans but one Loan can not belong to many Branches, so the relationship between Branch and Loan is one to many relationship, Account held by Customers=>M:N One Customer can have more than one Accounts and also One Account can be held by ‘one or more Customers, so the relationship between Account and Customers is many to many relationship Loan availed by Customer=>M:N (Assume loan can be jointly held by many Customers). One Customer can have more than one Loans and also Oue Loan can be availed by one or more Customers, so the relationship between Loan and Customers is many to many relationship. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreName). Code (Address Loan_id Account No) Gane9—] Loan ‘Account le Ca> “ M a amount Balance \vailed by woON ER Diagram of a Bank |Customer| Figure 1.17: ER diagram of Bank Management system Example of Library Management system: ‘The Library Management System database keeps track of readers with the following considerations — The system keeps track of the staff with a single point authentication system comprising login Id and password, Staff maintains the book catalog with its ISBN, Book title, price(in INR), category(novel, general, story), edition, author Number and details. A publisher has publisher Id, Year when the book was published, and name of the book. Readers are registered with their user_id, email, name (first name, last name), Phone no (multiple entries allowed), communication address. ‘The staff keeps track of readers. Readers can returtvreserve books that stamps with issue date and return date. [fnot returned within the prescribed time period, it may have a due date too, Staff also generate reports that has readers id, registration no of report, book no and returnissue info. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreThis Library ER diagram in Figure 1.18 illustrates key information about the Library, including entities such as staff, readers, books, publishers, reports, and authentication system. It allows for understanding the relationships between entities. Entities and their Attributes — Book Entity :It has authno, isbn number, title, edition, category, price. ISBN is the Primary Key for Book Entity. Reader Entity :It has Userld, Email, address, phone no, name. Name is composite attribute of firstname and lastname. Phone no is multi valued attribute. Userld is the Primary Key for Readers entity. Publisher Entity : It has Publisherld, Year of publication, name. PublisherID is the Primary Key. Authentication System Entity : It has Loginld and password with LoginID as Primary Key. Reports Entity : It has Userld, Reg_no, Book_no, Issue/Retum date. Reg_no is the Primary Key of reports entity. Staff Entity : It has name and staff id with staff_id as Primary Key. Reserve/Return Relationship Set : It has three attributes: Reserve date, Due date, Return date. Relationships between Entities ~ ‘A reader can reserve N books but one book can be reserved by only one reader. The relationship 1:N. A publisher can publish many books but a book is published by only one publisher. The relationship 1:N. Staff keeps track of readers. The relationship is MIN. Staff maintains multiple reports. The relationship 1: Staff maintains multiple Books. The relationship 1:N. Authentication system provides login to multiple staffs. The relation is 1:N. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreFigure 1.18: ER diagram of Library Management system Example of University Database: Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreFigure 1.19: ER diagram of University database Entities and Attributes: College Attributes: CName (unique name), COifice (main office location), CPhone (contact number), Dean (faculty member who is the dean) Department Altributes: DName (unique name), DCode (unique code number), DOffice (office location), DPhone (contact number), Chair (faculty member who chairs the department), ‘CStartDate (start date of the chair’s tenure). Course Attributes: CoName (unique name), CCode (unique code number), Level (course level), Credits (credit hours), CDese (course description). Instructor Attributes: Id (unique identifier), [Name (name), IOffice (office location), IPhone (contact number), Rank (academic rank), DepartmentId (department associated with). Student Attributes: Sid (unique student ID), SName (composed of FName, MName, LName), ‘Adar (address), Phone (contact number), Major (major code), DoB (date of birth), DepartmentId (department assigned to) Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreSection Attributes: Secld (unique section identifier), SecNo (section number), Sem (semester), ‘Year (academic year), CRoom (classroom), DaysTime (class meeting times). Relationships and Constraints: Administers -College to Department ‘Type: One-to-Many Description: A College administers multiple Departments. Each Department is associated with exactly one College. Offers Department to Course ‘Type: One-to-Many Description: A Department offers multiple Courses. Each Course is offered by exactly one Department. Employs Department to Instructor ‘Type: One-to-Many Description: A Department employs multiple Instructors. Each Instructor is associated with exactly one Department. ‘Teaches Instructor to Section ‘Type: One-to-Many Description: An Instructor can teach multiple Sections. Each Section is taught by exactly one Instructor. Constraints: In # given semester and year, an Instructor cannot teach two sections at the same time. Includes -Course to Section ‘Type: One-to-Many Description: A Course can have multiple Sections. Each Section is related to exactly one Course, Constraints: The combination of (SecNo, Sem, Year, CCode) must be unique to prevent duplication of section numbers within the same semester and year for the same course. Enrolls -Student to Section ‘Type: Many-to-Many Description: Students can enroll in multiple Sections, and each Section can have multiple Students. ‘Constraints; Each Section must have at least five Students. Grades are recorded for each student in each section. Additional Constraints for Sections: Uniqueness: The combination of (Sem, Year, CRoom, DaysTime) must be unique to avoid scheduling conflicts in the same classroom at the same time. Instructor Constraint: The combination of (Sem, Year, DaysTime, Id) must be unique to ensure that an instructor does not have overlapping teaching schedules. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreSpecialization and Generalization(Enhanced ER model: Specialization: The process of defining a set of subclasses of an entity type. In specialization, an entity is divided into sub-entities based on its characteristies. It is a top-down approach where the higher-level entity is specialized into two or more lower-level entities. For Example, an EMPLOYEE entily in an Employee management system can be specialized into DEVELOPER, TESTER, etc. as shown in Figure 2. In this case, commonattributes like E. NAME, E_SAL, etc. become part of a higher entity (EMPLOYEE), and specialized attributes like TES_TYPE become part of a specialized entity (TESTER). Specialization is also called as” Top- Down approch” Vv Figure 1.20 Specialization Generalization: The process of defining a generalized entity type from a set of entity types. Generalization is the process of extracting common properties from a set of entities and creating a generalized entity from it, It is a bottom-up approach in which two or more entities can be generalized to a higher-level entity if they have some attributes in common. For Example, STUDENT and FACULTY can be generalized to a higher-level entity called PERSON as shown, in Figure 1. In this case, common attributes like P NAME, and P ADD become part of a higher entity (PERSON), and specialized attributes like S_FEE become part of a specialized entity (STUDENT). Generalization is also called as * Bottom-up approach”. Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, BangaloreFigure 1.21 Generalization + Used to model inheritance in databases, where a subclass inherits attributes and relationships of its superclass. Inheritance: It is an important feature of generalization and specialization Attribute inheritance: I: allows lower level entities to inherit the attributes of higher level entities and vice versa In diagram: Car entity is an inheritance of Vehicle entity So Car can acquire attributes of Vehicle example:car can acquire Model attribute of Vehicle, Participation inheritance: In participation inheritance, relationships involving higher level entity set also inherited by lower level entity and vice versa. In diagram: Vehicle entity has an relationship with Cycle entity, So Cycle entity can acquire attributes of lower level entities i.e Car and Bus since itis inheritance of Vehicle. SS Figure 1.22 Inheritance Dr. Ciyamala Kushbu S, AP/ISE, CMRIT, Bangalore