DBMS Module 1 PDF

# B.C.A. Semester-l CBCGS-HME 2024-25 ## Module 1 ### Introduction to Database System #### 1.1 Motivation: The motivation for studying the evolution of databases and DBMS lies in understanding the growing need for effective data management. It highlights the transition from file systems to database systems, emphasizing the advantages of data models, DBMS architecture, and data independence. #### 1.2 Syllabus: | Lecture No | Syllabus | Duration | Self-Study | |---|---|---|---| | 1 | Data - Database Applications - Evolution of DB & DBMS | 1 hr | 1 hr | | 2 | Need for data management, Introduction and applications of DBMS | 1 hr | 2 hr | | 3 | File systems versus Database systems, | 1 hr | 1 hr | | 4 | Data Models, DBMS Architecture | 1 hr | 1 hr | | 5 | Data Independence | 1 hr | 1 hr | | 6 | Data Modelling using Entity-Relationship Model | 1 hr | 1 hr | | 7 | Enhanced ER Modelling. | 2hr | 2hr | #### 1.2.1 Summative Assessment weightage: 10 to 15 Marks #### 1.2.2 Prerequisite: Basic knowledge of Database Management Systems. #### 1.3 Learning Objectives: Students shall be able to, 1. Identify the key components and processes involved in data management. 2. Explain the purpose and functions of a DBMS 3. Discuss real-world applications of DBMS in various industries. 4. Describe the overall architecture of a DBMS. 5. Define and explain the components of a file system 6. Define data independence and its importance in DBMS #### 1.3.1 Learning Outcomes: Students will be able to: 1. Identify different applications and uses of databases in various industries. 2. Comprehend the advancements in DBMS technologies over time. 3. Recognize the challenge associated with managing large volumes of data. 4. Understand the limitations of file systems and the advantages offered by database systems 5. Understand the architecture of a DBMS, including its components and their interactions 6. Design ER diagrams to represent data relationships and structures. 7. Explore advanced concepts in ER modelling, including specialization, generalization, and aggregation. #### 1.4 Theoretical Background A Database Management System (DBMS) is software designed to store, retrieve, define, and manage data within a database. Unlike file systems, which handle data through simple files and directories, a DBMS offers advanced functionalities for managing and organizing data. Data models are abstract frameworks that depict the structures and relationships of data within a database. DBMS architecture typically consists of three layers: the internal layer, which deals with storage and physical database design; the conceptual layer, which addresses logical database design; and the external layer, which focuses on user interfaces and interactions. The Entity-Relationship (ER) model is a conceptual tool used to design and represent the data structures and relationships within a database. The Enhanced ER (EER) model builds upon the ER model by integrating more complex concepts, such as specialization, generalization, and aggregation. #### 1.4.1 Scope Explore different types of data and their importance. Review the evolution of database systems, from early file-based systems to contemporary DBMS, highlighting key milestones and innovations in the field. Study various data models—hierarchical, network, relational, and object-oriented—and their roles in representing data structures. Understand how these models affect database design and implementation. #### 1.5 Key notations, Keywords: 1. **Data** - Data refers to a collection of facts, which can include values, measurements, observations, or descriptions. It encompasses numbers, words, measurements, and any other types of information that can be recorded and analyzed. 2. **Database** - A database is an organized collection of data that allows for easy access, management, and updating. 3. **Database management system**- A database management system (DBMS) is essentially a computerized system for managing data. It is a software application that facilitates the creation and management of databases. 4. **Data Independence** - Data independence is a concept in database management that ensures data is kept separate from the programs that use it, thereby limiting access to the underlying storage structure by user applications. 5. **Database Administrator**- A database administrator (DBA) is an individual who has primary control over the database system. ### 1.6 Definition #### Lecture 1 Objective: To define and understand the concepts of Database Management Studies #### 1.6.1 Database Applications Database Management Systems (DBMS) have evolved significantly over the years, with various applications across different domains. Here's an in-depth look at the applications of DBMS, including some recent insights **Applications of Database Management System** * **Transactional Systems:** These systems manage transactions such as purchases, withdrawals, and deposits. They ensure data consistency and integrity using ACID properties (Atomicity, Consistency, Isolation, Durability). * **Application:** Used in banking, retail, and e-commerce for handling day-to-day operations. * **New Insights:** Modern transactional systems incorporate advanced techniques like distributed transactions and blockchain technology to enhance security and transparency. * **Data Warehousing and Business Intelligence (BI):** * **Application:** Used for storing and analysing large volumes of data from different sources. * **Details:** Data warehouses aggregate data from multiple sources, enabling complex queries and analytics. BI tools provide insights and visualization for decision-making. * **New Insights:** The integration of Al and machine learning in BI tools is enabling predictive analytics and automated data-driven decision-making. * **Customer Relationship Management (CRM) Systems:** * **Application:** Used by businesses to manage interactions with customers and streamline processes. * **Details:** CRM systems store customer data, track interactions, and manage sales pipelines. * **New Insights:** Al-driven CRM systems offer personalized customer experiences, predictive analytics for sales forecasting, and automated customer service solutions. * **Enterprise Resource Planning (ERP) Systems:** * **Application:** Integrates various business processes like finance, HR, and supply chain into a single system. * **Details:** ERP systems facilitate data sharing across departments, improving efficiency and coordination. * **New Insights:** Cloud-based ERP systems are becoming popular, providing scalability and flexibility, and incorporating IoT for real-time data collection and analysis. * **Content Management Systems (CMS):** * **Application:** Used for creating, managing, and modifying digital content on websites. * **Details:** CMSs handle content storage, retrieval, and presentation, making it easier to manage websites without technical expertise. * **New Insights:** Headless CMSs are gaining traction, allowing for greater flexibility in delivering content across various platforms and devices. * **Healthcare Information Systems:** * **Application:** Manage patient records, treatment plans, and medical history. * **Details:** These systems ensure accurate and timely access to patient data, supporting clinical decision-making and administrative functions. * **New Insights:** The use of Al for diagnostic support and predictive analytics is improving patient outcomes and operational efficiency. * **Supply Chain Management (SCM):** * **Application:** Manages the flow of goods and services from suppliers to customers. * **Details:** SCM systems track inventory levels, order processing, and supplier relationships. * **New Insights:** Integration with IoT and blockchain is enhancing transparency and traceability in supply chains. * **Geographic Information Systems (GIS):** * **Application:** Analyses spatial and geographic data for urban planning, environmental management, and logistics. * **Details:** GIS systems store, manipulate, and visualize data related to geographic locations. * **New Insights:** Advances in spatial data analytics and integration with real-time data sources are enhancing decision-making in urban planning and disaster management. * **Educational Systems:** * **Application:** Manage student records, course materials, and academic progress. * **Details:** Educational systems facilitate administration, learning management, and assessment. * **New Insights:** Adaptive learning technologies and Al-driven tutoring systems are personalizing education and improving learning outcomes. * **Government and Public Sector:** * **Application:** Used for managing public records, census data, and other governmental functions. * **Details:** These systems ensure efficient data management for public services and policy-making. * **New Insights:** The use of data analytics and smart city technologies is improving public services and infrastructure management. * **Emerging Trends:** * **Multi-Model Databases:** Supporting various types of data models (e.g., relational, document, graph) within a single database. * **Serverless Databases:** Offering auto-scaling and on-demand capacity without the need for traditional server management. * **Graph Databases:** Gaining traction for applications requiring complex relationships and network analysis. These insights reflect the ongoing innovations in DBMS applications and how they are being leveraged to address modern challenges and opportunities. #### 1.6.2 Evolution of Database and DBMS | | | |---|---| | **Early File Systems (1950s-1960s):** | Data was stored in flat files with no structured way to query or manage data. This approach was limited in scalability and efficiency. | | **Hierarchical Databases (1960s-1970s):** | The hierarchical model, popularized by IBM's Information Management System (IMS), organized data in a tree-like structure. This model was good for representing one-to-many relationships but was rigid and not suitable for complex queries. | | **Network Databases (1960s-1970s):** | Network databases, exemplified by the Integrated Data Store (IDS), allowed more flexible relationships between data. Data could be organized in a graph-like structure, which supported more complex queries but was still complex to manage. | | **Relational Databases (1970s-present):** | Introduced by Edgar F. Codd, the relational model organizes data into tables (relations) that can be queried using SQL (Structured Query Language). This model offers ease of use, flexibility, and the ability to handle complex queries and relationships. Major relational databases include Oracle, MySQL, PostgreSQL, and SQL Server. | | **Object-Oriented Databases (1980s-1990s):** | Object-oriented databases, such as Objects tore, integrated object-oriented programming with databases, allowing data to be stored as objects. This model aimed to better handle complex data types and relationships found in modern applications. | | **Distributed Databases (2000s-present):** | Distributed databases manage data across multiple physical locations. Technologies like Apache Hadoop and Apache Kafka focus on distributed storage and processing, allowing for high availability, fault tolerance, and scalability. | | **NoSQL Databases (2000s-present):** | Designed to handle unstructured data and scale horizontally, NoSQL databases like MongoDB, Cassandra, and Redis provide flexible schema designs and support various data models (document, key-value, column-family, graph). They are particularly useful for big data and real-time web applications. | | **NewSQL Databases (2010s-present):** | NewSQL databases, such as Google Spanner and Cockroach DB, aim to provide the scalability and performance of NoSQL systems while retaining the strong consistency and ACID (Atomicity, Consistency, Isolation, Durability) properties of traditional relational databases. | | **Cloud Databases (2010s-present):** | Cloud-based databases, like Amazon RDS, Azure SQL Database, and Google Cloud SQL, offer scalable and managed database services that run on cloud infrastructure. They simplify database management tasks and offer high availability and scalability. | #### File System * **Data Storage:** Files and directories * **Data Organization:** Hierarchical, unstructured * **Data Retrieval:** Basic file operations * **Data Integrity:** Minimal, application-new features * **Concurrency:** Limited, managed * **Data Security:** Basic file permissions * **Backup & Recovery:** Manual or external tools * **Scalability:** Limited * **Data Redundancy:** Higher risk, manual management #### Database System * **Data Storage:** Structured tables and records * **Data Organization:** Relational, structured, schema-based * **Data Retrieval:** Advanced querying with SQL * **Data Integrity:** Enforced through constraints and rules * **Concurrency:** Managed with transactions and locks * **Data Security:** Advanced, role-based, encryption * **Backup & Recovery:** Built-in, automated, point-in-time * **Scalability:** High, supports large data volumes * **Data Redundancy:** Minimized, managed through design #### 1.6.3 Purpose of Database Management System. * **Data redundancy and inconsistency:** Since different programmers create the files and application programs over a long period, the various files are likely to have different structures and the programs may be written in several programming languages Therefore, the same information may be duplicated in several places (files). This redundancy leads to higher storage and access costs. In addition, it may lead to data inconsistency; that is, the various copies of the same data may no longer agree. * **Difficulty in accessing data:** The conventional file-processing environments do not allow needed data to be retrieved conveniently and efficiently. For Example, consider that one of the university clerks needs to find out the names of all students who live within a particular postal code area. The clerk asks the data-processing department to generate such a list. Because the designers of the original system did not anticipate this request, there is no application program on hand to meet it. There is, however, an application program to generate the list of all students. The university clerk has now two choices: either obtain the list of all students and extract the needed information manually or ask a programmer to write the necessary application program. Both alternatives are unsatisfactory. Therefore, extraction of the required data is difficulty * **Data Isolation:** Because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult. * **Integrity problems:** The data values stored in the database must satisfy certain types of consistency constraints. Suppose the university maintains an account for each department, and records the balance amount in each account. Suppose also that the university requires that the account balance of a department may never fall below zero. Developers enforce these constraints in the system by adding appropriate code in the various application programs. The problem is compounded when constraints involve several data items from different files. * **Atomicity problems:** A computer system, like any other device, is subject to failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed before the failure. It is difficult to ensure atomicity in a conventional file-processing system. * **Concurrent-access anomalies:** To increase the overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates is possible and may result in inconsistent data. Security problems Not every user of the database system should be able to access all the data. #### 1.6.4 Learning Objective: In this lecture, students will be able to learn DBMS, file systems, and data models #### 1.6.5 Advantages of Database Management System1 * **Improved data sharing:** The DBMS helps create an environment where users have better access to more and better-managed data. Such access makes it possible for end users to respond quickly to changes in their environment * **Improved data security:** The more users access the data, the greater the risks of data security breaches. Corporations invest considerable amounts of time, effort, and money to ensure that corporate data are used properly. DBMS provides a framework for better enforcement of data privacy and security policies. * **Better data integration:** Wider access to well-managed data promotes an integrated view of the organization's operations and a clearer view of the big picture. It becomes much easier to see how actions in one segment of the company affect other segments. * **Minimized data inconsistency:** Data inconsistency exists when different versions of the same data appear in different places. The probability of data inconsistency is greatly reduced in a properly designed database. * **Improved data access:** The DBMS makes it possible to produce quick answers to queries. From a database perspective, a query is a specific request issued to the DBMS for data manipulation—for example, to read or update the data. Simply put, a query is a question, and an ad hoc query is a spur-of-the-moment question. * **Improved decision making:** Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based. The quality of the information generated depends on the quality of the underlying data. Data quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of the data. While the DBMS does not guarantee data quality, it provides a framework to facilitate data quality initiatives. #### 1.6.6 Disadvantages of Database Management System * **Increased costs:** Database systems require sophisticated hardware and software and highly skilled personnel. The cost of maintaining the hardware, software, and personnel required to operate and manage a database system can be substantial. Training, licensing, and regulation compliance costs are often overlooked when database systems are implemented. * **Management complexity:** Database systems interface with many different technologies and significantly impact a company's resources and culture. The changes introduced by the adoption of a database system must be properly managed to ensure that they help advance the company's objectives. Given the fact that database systems hold crucial company data that are accessed from multiple sources, security issues must be assessed constantly. * **Maintaining currency:** To maximize the efficiency of the database system, you must keep your system current. Therefore, you must perform frequent updates and apply the latest patches and security measures to all components. Because database technology advances rapidly, personnel training costs tend to be significant. Vendor dependence. Given the heavy investment in technology and personnel training companies might be reluctant to change database vendors. As a consequence, vendors are less likely to offer pricing point advantages to existing customers, and those customers might be limited in their choice of database system components. * **Frequent upgrade/replacement cycles:** DBMS vendors frequently upgrade their products by adding new functionality. Such new features often come bundled in new upgraded versions of the software. Some of these versions require hardware upgrades. Not only do the upgrades themselves cost money, but it also costs money to train database users and administrators to properly use and manage the new features. #### File systems versus Database system | **Features** | **File System** | **Database System** | |---|---|---| | **Data Storage** | Files and directories | Structured tables and records | | **Data Organization** | Hierarchical, unstructured | Relational, structured, schema-based | | **Data Retrieval** | Basic file operations | Advanced querying with SQL | | **Data Integrity** | Minimal, application-new features | Enforced through constraints and rules | | **Concurrency** | Limited, managed | Managed with transactions and locks | | **Data Security** | Basic file permissions | Advanced, role-based, encryption | | **Backup & Recovery** | Manual or external tools | Built-in, automated, point-in-time | | **Scalability** | Limited | High, supports large data volumes | | **Data Redundancy** | Higher risk, manual management | Minimized, managed through design | #### 1.6.7 Data Models A data model is a conceptual framework used in Database Management Systems (DBMS) to organize and structure data. It specifies how data is related, stored, accessed, and modified. Various data models are appropriate for various application kinds and have unique properties. The following are a few of the main * **Hierarchical Data Model:** * **Structure:** Data is organized in a tree-like structure with a single root and multiple levels of related records. * **Characteristics:** Each child node has only one parent, but a parent can have multiple children. * **Usage:** Used in early database systems and in applications where relationships are naturally hierarchical (e.g., organizational structures) * **Network Data Model:** <start_of_image> Schematic of Network Data Model * **Relational Data Model:** * **Structure:** tables are the fundamental building blocks of the relational model. Each table is a collection of related data organized in rows and columns. * **Characteristics:** Ensures that each table row is uniquely identifiable via its primary key. * **Usage:** Relational databases store data in a structured format that is easy to retrieve and manipulate * **Object-Oriented Data Model:** * **Structure:** Data is represented as objects, similar to object-oriented programming. * **Characteristics:** Encapsulates data and behaviour (methods); supports inheritance, polymorphism, and encapsulation. * **Usage:** Used in applications requiring complex data representations and object manipulation (e.g., CAD/CAM systems). * **Entity-Relationship (ER) Model:** * **Structure:** Data is represented as entities and relationships between them. * **Characteristics:** Used primarily for database design; helps in visualizing and organizing data. * **Usage:** Serves as a blueprint for creating relational databases. #### Lecture 2 #### 1.6.4 Learning Objective: In this lecture, students will be able to learn DBMS, file systems, and data models #### 1.6.5 Advantages of Database Management System * **Improved data sharing:** The DBMS helps create an environment where users have better access to more and better-managed data. Such access makes it possible for end users to respond quickly to changes in their environment. * **Improved data security:** The more users access the data, the greater the risks of data security breaches. Corporations invest considerable amounts of time, effort, and money to ensure that corporate data are used properly. DBMS provides a framework for better enforcement of data privacy and security policies. * **Better data integration:** Wider access to well-managed data promotes an integrated view of the organization 's operations and a clearer view of the big picture. It becomes much easier to see how actions in one segment of the company affect other segments. * **Minimized data inconsistency:** Data inconsistency exists when different versions of the same data appear in different places. The probability of data inconsistency is greatly reduced in a properly designed database. * **Improved data access:** The DBMS makes it possible to produce quick answers to queries. From a database perspective, a query is a specific request issued to the DBMS for data manipulation—for example, to read or update the data. Simply put, a query is a question, and an ad hoc query is a spur-of-the-moment question. * **Improved decision making:** Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based. The quality of the information generated depends on the quality of the underlying data. Data quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of the data. While the DBMS does not guarantee data quality, it provides a framework to facilitate data quality initiatives. #### 1.6.6 Disadvantages of Database Management System * **Increased costs:** Database systems require sophisticated hardware and software and highly skilled personnel. The cost of maintaining the hardware, software, and personnel required to operate and manage a database system can be substantial. Training, licensing, and regulation compliance costs are often overlooked when database systems are implemented. * **Management complexity:** Database systems interface with many different technologies and significantly impact a company's resources and culture. The changes introduced by the adoption of a database system must be properly managed to ensure that they help advance the company's objectives. Given the fact that database systems hold crucial company data that are accessed from multiple sources, security issues must be assessed constantly. * **Maintaining currency:** To maximize the efficiency of the database system, you must keep your system current. Therefore, you must perform frequent updates and apply the latest patches and security measures to all components. Because database technology advances rapidly, personnel training costs tend to be significant. Vendor dependence. Given the heavy investment in technology and personnel training companies might be reluctant to change database vendors. As a consequence, vendors are less likely to offer pricing point advantages to existing customers, and those customers might be limited in their choice of database system components. * **Frequent upgrade/replacement cycles:** DBMS vendors frequently upgrade their products by adding new functionality. Such new features often come bundled in new upgraded versions of the software. Some of these versions require hardware upgrades. Not only do the upgrades themselves cost money, but it also costs money to train database users and administrators to properly use and manage the new features. #### Lecture 3 #### Learning Objective: In this lecture, students will be able to learn database architecture #### 1.10 Database Architecture: Database architecture refers to a database system's structural design and methodology, which forms the core of a Database Management System (DBMS). This architecture dictates how data is stored, organized, and retrieved, playing a crucial role in the efficiency and effectiveness of data management. Schematic of Database Architecture #### 1.10.2 Storage Manager: The storage manager is the component of a database system that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system provided by the operating system. The storage manager translates the various DML statements into low-level file-system commands. #### 1.10.3 Data Storage and Querying: The storage manager components include: 1. **Authorization and integrity manager:** which tests for the satisfaction of integrity constraints and checks the authority of users to access data. 2. **Transaction manager:** which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting. 3. **File manager:** which manages the allocation of space on disk storage and the data structures used to represent information stored on disk. 4. **Buffer manager:** which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory. The storage manager implements several data structures as part of the physical system implementation: * **Data files:** which store the database itself. * **Data dictionary:** which stores metadata about the structure of the database, in particular the schema of the database. * **Indices:** which can provide fast access to data items. Like the index in this textbook a database index provides pointers to those data items that hold a particular value. For example, we could use an index to find the instructor record with a particular ID, or all instructor records with a particular name. Hashing is an alternative to indexing that is faster in some but not all cases. #### The Query Processor The query processor components include: * **DDL interpreter:** which interprets DDL statements and records the definitions in the data dictionary. * **DML compiler:** which translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. A query can usually be translated into any of a number of alternative evaluation plans that all give the same result. The DML compiler also performs query optimization; that is, it picks the lowest-cost evaluation plan from among the alternatives. * **Query evaluation engine:** which executes low-level instructions generated by the DML compiler #### 1.10.4 Database Users Users are differentiated by the way they expect to interact with the system. * **Application programmers** interact with system through DML calls. * **Sophisticated users** form requests in a database query language. * **Specialized users** write specialized database applications that do not fit into the traditional data processing framework. * **Native users** invoke one of the permanent application programs that have been written previously. Examples, people accessing database over the web, bank tellers, clerical staff Coordinates all the activities of the database system; the database administrator has a good understanding of the enterprise information resources and needs. Database administrator's duties include: * **Schema definition** * **Storage structure and access method definition** * **Schema and physical organization modification** * **Granting user authority to access the database** * **Specifying integrity constraints** * **Acting as liaison with users** * **Monitoring performance and responding to changes in requirements** #### Lecture 4 #### Learning Objective: In this lecture, students will be able to learn Data independence #### 1.11 Three-Schema Architecture: The goal of the three-schema architecture is to separate the user applications from the physical database. In this architecture, schemas can be defined at the following three levels: 1. **The internal level:** has an internal schema, which 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. 2. **The conceptual level:** has a conceptual schema, which describes the structure of the whole database for a community of users. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints. 3. **The external or view level:** includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. As in the previous level, each external schema is typically implemented using a representational data model, possibly based on an external schema design in a high-level data model. #### 1.12 Data Independence: The ability to modify a scheme definition in one level without affecting a scheme definition in a higher level is called data independence. * **Physical data independence:** The ability to modify the physical scheme without causing application programs to be rewritten. Modifications at this level are usually to improve performance. * **Logical data independence:** The ability to modify the conceptual scheme without causing application programs to be rewritten. Usually done when logical structure of database is altered. #### Lecture 5 #### Learning Objective: In this lecture, students will be able to learn Data Modelling using the Entity-Relationship Model #### 1.13 Data Modelling using Entity-Relationship Model **Step 1: Requirements Collection and Analysis** Prospective users are interviewed to understand and document data requirements this step results in a concise set of user requirements, which should be detailed and complete. The functional requirements should be specified, as well as the data requirements. Functional requirements consist of user operations that will be applied to the database, including retrievals and updates. Functional requirements can be documented using diagrams such as sequence diagrams, data flow diagrams, scenarios, etc. **Step 2: Conceptual Design** Once the requirements are collected and analysed, the designers go about creating the conceptual schema. Conceptual schema: concise description of data requirements of the users, and includes detailed description of the entity types, relationships and constraints. The concepts do not include implementation details; therefore, the end users easily understand them, and they can be used as a communication tool. The conceptual schema is used to ensure all user requirements are met, and they do not conflict. **Step 3: Database Implementation** Many DBMS systems use an implementation data model, so the conceptual schema is transformed from the high-level data model into the implementation data model. This step is called logical design or data model mapping, which results in the implementation data model of the DBMS. **Step 4: Physical Design** Internal storage structures, indexes, access paths and file organizations are specified. Application programs are designed and implemented ER Model. In software Engineering, an entity relational model is an abstract and conceptual representation of data. Entity-relationship modelling is a database modelling method, used to produce a type of conceptual schema or semantic data model of a system, often A relational database and its requirements in a top-down fashion. Diagrams created by this process are called entity-relationship diagrams. #### 1.13.1 ER Diagram * A database can be modelled as a collection of entities. * Relationship among the entities an entity is a real-world object that exists and it is distinguishable from other entities. * Example: Person, company, event, plants. * All the entities in the data model have attributes as known as properties of an entities. **Example:** set of all persons, companies, trees, holidays * Schematic of ER Diagram * **Rectangles:** entity sets * **Diamonds:** relationship sets * **Lines:** link attributes to entity sets and entity sets to relationship sets * **Underline:** indicates primary key attributes * **Ellipses:** represent an attribute Double * **Lines:** represent the total participation of an entity in a relationship set * **Double rectangle:** represents a weak entity sets **1.13.2 Strong Entity type** * In the Case of the Client entity. Client_no is the primary key of Client entity which is used to uniquely identify among the Client_s entity set. In the case of Customer Entity, Customer_id is the primary key of Customer Entity which is used to uniquely identified among the Customer ‘s entity set. **Example:** * In the Case of Client entity Client_no is the primary key of Client entity which is used to uniquely identified among the Clients entity set. * In the case of Customer Entity, Customer_id is the primary key of Customer Entity which is used to uniquely identified among the Customer's entity set. **Strong Entity type** is represented by rectangle Symbol. **A weak entity type** is dependent on a strong entity and cannot exist on its own. It does not have a unique identifier that has a partial identifier. Partial identifier is represented by a double line. Some weak entities assign partial identifiers and such partial identifiers of a weak entity called as discriminators. The weak entity type is represented by a double rectangle. Identify relationships. The strong entity type is linked with the weak entity type. #### Lecture 6 #### Learning Objective: In this lecture, students will be able to learn about the types of attributes. #### 1.14 Attributes * Properties of an entity or relationship type is called as attribute. Example Staff no, staff name, staff designation is describing an entity Staff. Value of an attribute plays a major role of data stored in database Each entity will have the value which is assigned to its attributes. #### Attribute domains * The set of allowable values which is assigned to one or more attribute is known as Attribute domains. There are types of attributes has been classified such as simple and Composite type, single valued and multi valued attributes. Stored and derived attributes, null attributes, and Key Attributes. **Simple Attributes** * Simple attributes is an attribute that can further divided into two parts. An Attribute composed of single components with an independent existence for example: The designation of an staff and the Salary of a staff **Composite Attribute** * Composite Attribute is an attribute that is further divided into many parts. An attribute composed of multiple components each component has its independent existence. **Example:** Address attributes of a Branch entity that can be further divided in to sub parts i.e. street, city and postal code as an attribute. **Single valued and Multi Valued attributes** * Single valued attribute is an attribute which has single value(atomic) for each entity. An attribute that holds a single value for each occurrence of an entity type. **Example:** Each branch has only single valued attributes is known as branch_no. * **Multivalued attributes** * The multivalued attribute is an attribute which has as many values for each entity. An attribute that holds multiple values for each occurrence of an entity type. * **Example:** Each staff member has multiple mobile numbers. **Stored and Derived attributes** * Stored attributes is an attribute which is used supplied a value to the related attribute. Example Date_of_birth of an staff is a stored attributes. * **Derived attributes** * The value from the derived attribute is derived from the stored attribute for an example Date_of_birth is a stored attribute for each staff member. The value for an Age can be derived from the Date_of_birth attributes i.e. by subtracting the Date_of_birth from the Current date, therefore the Stored attributes is usedSupplied a value to the related attributes. **Null attribute** * The attribute which takes NULL value when entity does not have the value to it. The Null attribute is an attribute their value is unknown, unassigned and missing information **Key Attributes** * This attribute has the unique value for an entity which is used to identify a given row in the table and is called as key attribute of an entity. * **Example**: Staff_no is a key attribute which has a unique value that is used to identify a given row in the table. #### Lecture 7 #### Learning Objective: In this lecture, students will be able to learn about relationships and cardinalities #### 1.15 Relationships A set of meaningful relationships among several entities. We used to indicate the diamond symbol for Relationships among the several entities, it could read from left to right. **Example:** The Branch has a staff *** * Schematic of Relationship * **Degree of relationship:** It is the number of entities participating in a particular relational model. There are two types of degree of relationship. * **Binary relationship:** A Relationship of degree two is called a binary relationship. * **Ternary Relationship:** A relationship of degree three is called a Ternary relationship. **Example:** * Schematic of Ternary Relationship **1.15.1 Relationship set:** The collection of similar relationship is known as a Relationship set. **Constraints on relationship:** 1. **Mapping Constraints / Cardinalities:** The number (or range) of a possible entity type that is associated with another entity type through a particular entity. Cardinalities indicate a specific number of entity occurrences of related entities. * **Type of Mapping Constraints:** * **One-to-one (1:1)** * **One-to

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue