Unit 4.1 Fundamental Data Concepts, Database Structures PDF

Unit 4 Data Resource Management and DSS TOPICS Fundamental Data Concepts Database structures Database Development Types of Databases Technical Foundation of Database Management Outcomes Understanding of key data concepts, including data and its fundamental concepts essential for effi...

Unit 4 Data Resource Management and DSS TOPICS Fundamental Data Concepts Database structures Database Development Types of Databases Technical Foundation of Database Management Outcomes Understanding of key data concepts, including data and its fundamental concepts essential for efficient database design and management. Proficiency in various database structures, such as tables, tuple (record), field(attribute), and keys enabling effective organization and retrieval of data. Ability to understand different database models, including hierarchical, network, and relational databases, to meet specific business needs. Fundamental Data Concepts Organizations handle vast amounts of data, which can be efficiently managed using software known as a database management system (DBMS). A DBMS allows for the organization of data within a database, facilitating its definition, manipulation, retrieval, and management. It manages the data itself, including data formats, field names, record structures, and file structures, and sets rules for data validation and interaction. By using a DBMS, users are freed from the need to write programs for data maintenance. Additionally, fourth-generation query languages like SQL are employed alongside DBMS packages to interact with databases. A conceptual framework consisting of multiple levels of data has been developed to distinguish between various logical levels of data. In this framework, data can be logically organized into characters, fields, records, files, and databases, similar to how writing is structured into letters, words, sentences, paragraphs, and document. Fundamental Data Concepts Logical list of data elements are: Characters Fields Records Files Databases Fundamental Data Concepts Characters A character is the most basic logical data element, consisting of a single alphabetic, numeric, or other symbol. While bits or bytes are more fundamental in terms of physical storage, a character represents a byte used to denote a specific symbol. From the user's perspective, a character is the smallest observable and manipulable data unit. Examples of characters include digits, letters, and special symbols like 1, b, $, #, and ?. Fields A field, also known as a column or data item, is the next level of data organization and represents a part of information. It consists of a grouping of related characters, such as a name field composed of alphabetic characters or a sales amount field made up of numeric characters. A data field represents an attribute of an entity, like an employee's salary describing an employee. Fields are typically organized in a logical order, such as last_name, first_name, address, and city. Fundamental Data Concepts Records A record, also known as a row or tuple in RDBMS, is formed by grouping all the fields that describe an entity's attributes, representing a single instance of that entity. For example, a payroll record might include fields for a person's name, Social Security number, and rate of pay. Records can be fixed-length, with a set number of fields of fixed size, or variable-length, with a variable number and size of fields. Each record typically includes a unique identifier called the primary key, such as a student ID number, to distinguish one instance from another. If no natural primary key exists, a unique sequential number can be assigned to ensure each record is uniquely identifiable. Files A file, sometimes referred to as a table or flat file, is a group of related records. When independent of other files, a single table is known as a flat file. A flat file database, in its strictest sense, consists solely of data and delimiters. More broadly, it refers to any database existing in a single file in row- and-column form, without relationships between records and fields. An employee file, for example, contains records of a firm's employees information only. Here, files are classified by their data it holds, such as payroll or inventory files, or by the type of data they hold, like document or image files. Fundamental Data Concepts Databases A database is an integrated collection of logically related data elements, consolidating records from separate files into a common data pool for various applications. The data in a database are independent of the application programs using them and the storage devices on which they are stored. Databases describe entities and their relationships, providing a logical organization and easy access to stored data. For example, social networking sites store pictures, videos, messages, and other data as fields, records, files, or objects in large databases. These databases ensure easy access, data sharing, and protection from unauthorized use. A database can be simple or complex, comprising one or more tables related as a single system, much like files in a flat file system. ❖ Integrated collection of logically related data elements. ❖ Consolidates records into a common pool of data elements. ❖ Data is independent of the application program using them and type of storage device. Limitations of File processing System ▪ It has many limitations as discussed below: Unfriendly interface High data redundancy and inconsistency. Difficulty in accessing the data Concurrent access problem Integrity problems Atomicity problem Security problems Data unshareable Difficulty in recovery of corrupt data Backup difficulty  Applications of Database Banking: customer accounts, transactions, and other financial data. Airlines: flight schedules, reservations, and customer information. Universities: student information, course schedules, and academic records. Sales: customer information, sales data, and other sales-related information. Online retailers: product information, customer orders, and other e-commerce data. Human resources: employee information, payroll data, and other HR-related information.  DBMS ▪ A Database Management System (DBMS) is a software system that allows users to create, modify, and manage databases. ▪ It is a collection of interrelated data and a set of programs to access those data. ▪ It provides a way to store, retrieve, and manipulate data in a structured and organized manner. ▪ The primary goal of a DBMS is to store and retrieve database information in convenient and efficient way.  Example of DBMS ▪ MySQL ▪ Oracle Database ▪ SQL Server ▪ MS Access ▪ SQLite ▪ And many more Components of DBMS Similar to components of Information system Procedure The procedure is a type of general instruction or guidelines for the use of DBMS. This instruction includes how to set up the database, how to install the database, how to log in and log out of the database, how to manage the database, how to take a backup of the database, and how to generate the report of the database. Database (Data) Access Language Database Access Language is a simple language that allows users to write commands to perform the desired operations on the data that is stored in the database. Database Access Language is a language used to write commands to access, insert, and delete data stored in a database.  These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). Functions of DBMS Data Storage, Retrieval, and Update Provides mechanisms to store, retrieve, and update data in the database efficiently. Ensures fast access to large volumes of data through indexing and optimized storage structures. Data Organization and Management Supports the organization of data into tables, schemas, and databases. Maintains metadata (data about data) for better management. Transaction Management Ensures ACID properties: Atomicity: Ensures transactions are all-or-nothing. Consistency: Maintains database integrity before and after a transaction. Isolation: Prevents conflicts in concurrent transactions. Durability: Guarantees transaction results persist even after a failure. Functions of DBMS Concurrency Control Manages simultaneous access to data by multiple users or applications. Prevents issues like deadlocks and inconsistencies using locks or timestamps. Data Integrity Enforces rules to maintain data accuracy and consistency. Implements constraints like: Primary keys Foreign keys Check constraints Data Security Protects data from unauthorized access through: Authentication mechanisms (user IDs, passwords). Authorization controls (permissions and roles). Encryption of sensitive data. Mini Assignment 3.1 : List and explain advantage and disadvantage of DBMS? Submit in Hardcopy. Database structures (model) Database structures refer to the organization and arrangement of data within a database system. These structures dictate how data is stored, accessed, and managed, providing the framework for organizing and manipulating information effectively. Common database structures include hierarchical, network, relational, object- oriented, ER model each offering unique ways to model data and relationships between entities. It basically determines the logical structure of a database. Database structures (model) Database structure refers to both the organization of data and the relationships between different data elements. Why Both Structure and Relationship Are Important: Structure: Ensures data is logically grouped and stored efficiently (e.g., normalization reduces redundancy). Relationships: Enables meaningful connections between different datasets, allowing for advanced queries like joins (e.g., "List all courses taken by a specific student"). Types of Database model DBMS Object- Entity Hierarchical Network Relational Oriented Relationship DBMS Model Model Model Model Hierarchical Model ▪ It is one of the oldest database model developed by IBM for information Management System. ▪ Eg: file system, organizational chart ▪ In a Hierarchical database model data is organized in a tree-like structure. ▪ Data is Stored Hierarchically (top down or bottom up) format. ▪ Data is represented using a parent-child relationship. ▪ In Hierarchical DBMS parent may have many children, but children have only one parent. ▪ Each parent or child is called a node. ▪ Topmost node is called root node. ▪ Lower node is called child node. ▪ Lowest node is called Leaf node. ▪ One parent can have multiple child but a child can have only one parent. Hierarchical Model Hierarchical Model Network Model Network Model ▪ It is the advance version of the hierarchical data model. ▪ Network Model looks like a Hierarchical database model due to which many time it is called as modified version of Hierarchical database. ▪ The network database model allows each child to have multiple parents. ▪ It helps you to address the need to model more complex relationships among the entities. ▪ In this model, entities are organized in a graph which can be accessed through several paths. Network Model Relational model ▪ A relational database is developed by E. F. Codd in 1970. ▪ Data is represented in the form of table only. ▪ Relational DBMS is the most widely used DBMS model because it is one of the easiest. ▪ This model is based on normalizing data in the rows and columns of the tables. ▪ Relational model stored in fixed table structures and manipulated using SQL. Relational model Object-Oriented Model ▪ In Object-oriented Model data stored in the form of objects. ▪ It is a DBMS where data is represented in the form of objects. ▪ Objects stores data as well as methods(functions to operate on data). ▪ An object-oriented database stores complex data as compared to relational database. ▪ Object-oriented databases can handle the different types of data, for example, pictures, voice video, including text, numbers and so on. Object-Oriented Model Object Oriented Data Model = Combination of Object Oriented Programming + Relational database model Object-Oriented Model Object Oriented Data Model = Combination of Object Oriented Programming + Relational database model Object-Oriented Model Entity Relationship Model ▪ An Entity Relationship (ER) Model is a data model that illustrates how “entities” such as people, objects or concepts relate to each other within a system. ▪ ER Diagrams are most often used to design relational databases. ▪ ER model use a defined set of symbols such as rectangles, diamonds, ovals and connecting lines to show entity relationships and their attributes. Entity Relationship Model List down the various entities for the following. ▪ School Management System ▪ Banking System ▪ Library System ▪ E commerce List down the various entities for the following. ▪ School Management System - (Students, subject, teacher, fee) ▪ Banking System - (Account, customer, transaction) ▪ Library System - (Book, member, issue_info, return_info) ▪ E commerce -(Product, order, customers, category) List down the various attributes for the following entities. Student Bank Account Product Event List down the various attributes for the following entities. Student - (roll_no, name, email, gender, address,..) Bank Account - (acc_num, acc_type, customer_name) Product - (product_id, product_name, unit_price, stock) Event – (id, name, type, start_date, end_date) Comparison between models: Model Structure Relationship One-to-many (each child has Tree-like structure with parent- Hierarchical one parent, but a parent can child nodes. have multiple children). Graph structure with Many-to-many (records can Network interconnected records have multiple parents and (nodes). children). Tabular structure (tables with Relationships defined using Relational rows and columns). primary and foreign keys. Object-based structure with Relationships through object Object-Oriented classes and attributes. references and methods. Conceptual relationships Diagrammatic structure with visualized as links between ER Model entities, attributes, and entities (1-to-1, 1-to-many, relationships. many-to-many). Key 1. A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). 1. Keys are also used to establish relationships between the different tables and columns of a relational database. 1. Individual values in a key are called key values. Types of Keys in DBMS 1.Primary Key 2.Candidate Key 3.Super Key 4.Foreign Key 5.Composite Key 6.Alternate Key 7.Unique Key Primary Key o Primary key is a column of a table or a set of columns that helps to identify every record present in that table uniquely. o There can be only one primary Key in a table. o Also, the primary Key cannot have the same values repeating for any row. o Every value of the primary key must be different with no repetitions. o The PRIMARY KEY (PK) constraint put on a column or set of columns will not allow them to have any null values or any duplicates. o Value of primary key cannot be changed o One table can have only one primary key constraint. Any value in the primary key cannot be changed by any foreign keys (explained below) which refer to it. Primary Key ID Name Age Salary 1 Adam 34 13000 2 Alex 28 15000 3 Stuart 20 18000 4 Ross 42 19020 5. Foreign Key Foreign Key is used to establish relationships between two tables. A foreign key will require each value in a column or set of columns to match the Primary Key of the referential table. Foreign keys help to maintain data and referential integrity. Components of ERD ▪ Entity ▪ Relationship ▪ Attributes Entity ▪ An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. ▪ For example, each person in a university is an entity. ▪ An entity has a set of properties, some properties may uniquely identify an entity. Student 🡪 Roll Number Entities are represented by rectangle in ERD. Entity Students Employees Products Orders Projects Customers Countries Common Types of Entity ▪ People: humans who carry out some function Employees, students, customers ▪ Places: sites or locations Cities, offices, routes, countries ▪ Things: tangible physical objects Equipment, products, buildings ▪ Events: things that happen at a given time or in a sequence. Project phases, account payments ▪ Concepts: intangible ideas Projects, accounts, complaints Relationship ▪ A relationship is an association among several entities. ▪ For example, in a university database a faculty has a relationship with a student. (i.e. these two entities are associated in some manner) ▪ A doctor is associated with patient, employee with department and many more. Relationships are represented by diamond in ERD. Relationship Faculty Teach Students Customers Place Orders Employees Work Departments Degree of a relationship ▪ The number of different entity sets participating in a relationship set is called as degree of a relationship set. ▪ Unary Relationship When there is only ONE entity set participating in a relation. A person is married to another person ▪ Binary Relationship When there are TWO entities set participating in a relation. Student is enrolled in Course Ternary relationship When there are 3 entities set participating in a relation N-ary relationship When there are n entities set participating in a relation Entity and Entity Set Cardinality ▪ Cardinality refers to the number of occurrences of one entity that are associated with the number of occurrences of another entity and vice-versa in a relationship. 1. One-to-one 2. One-to-many 3. Many-to-one 4. Many-to-many Cardinality One-to-one. An entity in A is 1 1 Person has Passport associated with at most one entity in B, and an entity in B is associated with at most one entity in A One-to-many. An entity in A is associated with any number (zero or m 1 more) of entities in B. An entity in B, Customer Place Order however, can be associated with at most one entity in A Cardinality Many-to-one. An entity in A is m 1 associated with at most one entity Student Enrolled In Courses in B. An entity in B, however, can be associated with any number (zero or more) of entities in A. Many-to-many. An entity in A is m associated with any number (zero or Book m Written Author more) of entities in B, and an entity By in B is associated with any number (zero or more) of entities in A. Attributes ▪ Attributes are the set of properties that describes an entity. ▪ For example, student’s roll_num and student’s name describes a student in a student entity set. So, roll-num, name, email, phone, address are some of the attributes that describes student entity. Attributes are represented by ellipse in ERD. Attributes Name Roll_num Email Student Address Phone Gender Types of Attributes ▪ Key Attributes ▪ Composite Attributes ▪ Multi-valued Attributes ▪ Derived Attributes Key Attributes ▪ The attribute which uniquely identifies each entity in the entity set is called key attribute. ▪ For example, Roll_No will be unique for each student. ▪ In ER diagram, key attribute is represented by an oval with underlying lines. Composite Attributes ▪ An attribute composed of many other attribute is called as composite attribute. ▪ For example, Address attribute of student Entity type consists of Street, City, State, and Country. ▪ In ER diagram, composite attribute is represented by an oval comprising of ovals. Multi-valued Attributes ▪ An attribute consisting more than one value for a given entity. ▪ For example, Phone_No (can be more than one for a given student). ▪ In ER diagram, multivalued attribute is represented by double oval. Derived Attributes ▪ An attribute which can be derived from other attributes of the entity type is known as derived attribute. ▪ e.g.; Age (can be derived from DOB). ▪ In ER diagram, derived attribute is represented by dashed oval. ▪ The complete entity type Student with its attributes can be represented as: Last Name First Name Assignment x ▪ Draw an ER diagram for Library Management System. ▪ Draw a ER diagram with suitable attributes for the following entities (College, Lecturer and Student). ▪ Draw a ER diagram with suitable attributes for the following entities (Customer, Order AND Products). ▪ Draw an E-R diagram with the help of following information Student (id, name, address, joined_year, faculty) Subject(sid, sname, category) Classroom(room_no, size, no_of_seats) Teacher (tid, tname, department) END