Database System (2024-2025) PDF

Database System Department Of Cybersecurity and Cloud Computing Technical Engineering Prepared By Assistant Lecturer: Donya Yasir 2024-2025 Uruk University Department of Cybersecurity and Cloud Computing Technical Eng...

Database System Department Of Cybersecurity and Cloud Computing Technical Engineering Prepared By Assistant Lecturer: Donya Yasir 2024-2025 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering 1. Introduction Databases and database technology are having a major impact on the growing use of computers. It is fair to say that databases play a critical role in almost all areas where computers are used, including business, engineering, medicine, law, education, and library science, to name a few. The word database is in such common use that we must begin by defining a database. Our initial definition is quite general. In other words, a database has some source from which data are derived, some degree of interaction with events in the real world, and an audience that is actively interested in the contents of the database. 2. Definitions A database is a collection of related data. By data, we mean known facts that can be recorded and that have implicit meaning. For example, 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 diskette, using a personal computer and software such as DBASE IV or Microsoft ACCESS, or EXCEL. This is a collection of related data with an implicit meaning and hence is a database. A database management system (DBMS) is a collection of programs that enables users to create and maintain a database. The DBMS is hence a general-purpose software system that facilitates the processes of defining, constructing, and manipulating databases for various applications. Defining a database involves specifying the data types, structures, and constraints for the data to be stored in the database. Constructing the database is the process of storing the data itself on some storage medium that is controlled by the DBMS. Manipulating a database includes such functions as querying the database to retrieve specific data, updating the database to reflect changes in the miniworld, and generating reports from the data. 1 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering 3. Database Advantages 1. Reduction in data redundancy. 2. The ability to operate on deferent data structure. 3. Independent of data from the program. 4. High speed of retrieval and fast on line. 5. High degree of flexibility in handling data format. 6. Minimum cost. 7. Inconsistent can be avoided. 8. Integrity can be maintained. 9. Standard parameter can be enforced. 10. Security restriction can be applied. 4. What is the Relational Model? The relational model represents how data is stored in Relational Databases. A relational database consists of a collection of tables, each of which is assigned a unique name. Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the below table. A relational schema is a data model for logically representing the structure of a database. A relational schema is a type of database schema specifically designed to represent relational databases. 2 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering Relational schemas can have a name and consists of the three main components below: Relations: In a relational schema, they are called tables. Attributes: Each attribute has a domain that specifies the type of data or limits the value an attribute can take. Each table can have many attributes. In a relational schema, they are the columns of a table. Tuples: They can be seen as instances of an entity. Each table can have several instances. In a relational schema, they are the rows contained in a table. A relational database definition is a collection of named relations, each with its attributes and tuples. In a relational schema, an attribute can also be a key: A primary key represents the identifying attribute of each instance of a table. It cannot be NULL or accept duplicate values. A foreign key links two tables in a relationship. It contains a value that references the primary key of another table. It can be a single attribute or a set of attributes. 5. What is SQL? SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to perform various operations on the data stored in these databases. Basic SQL Components: Tables: Data is stored in tables, which are made up of rows and columns. Queries: SQL queries are used to interact with the data. The most common query is SELECT. ❖ Intermediate SQL data analysis Intermediate SQL Analysis involves using SQL for data analysis using GROUP BY, HAVING, aggregate function, Date and Time functions and String functions. ❖ Advanced SQL data analysis Advanced SQL refers to concepts and techniques beyond the foundational skills of querying data from relational databases. While basic SQL equips you with standard operations like filtering, sorting, and altering tables. 3 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering 6. Relational Query Languages Relational Database systems are expected to be equipped with a query language that assists users to query the database. Relational Query Language is used by the user to communicate with the database user requests for the information from the database. Relational algebra breaks the user requests and instructs the DBMS to execute the requests. It is the language by which the user communicates with the database. There are two types of relational query language: 1. Procedural Query Language In Procedural Language, the user instructs the system to perform a series of operations on the database to produce the desired results. Users tell what data to be retrieved from the database and how to retrieve it. Procedural Query Language performs a set of queries instructing the DBMS to perform various transactions in sequence to meet user requests. Relational Algebra: is a Procedural Query Language, defined as the set of operations on relations. There are a few operators that are used in relational algebra: 1. Select (sigma): Returns rows of the input relation that satisfy the provided predicate. It is unary Operator means requires only one operand. 2. Projection (ℼ): Show the list of those attribute which we desire to appear and rest other attributes are eliminated from the table. It separates the table vertically. 3. Set Difference (-): It returns the difference between two relations. If we have two relations R and S them R-S will return all the tuples (row) which are in relation R but not in Relation S, It is binary operator. 4. Cartesian Product (X): Combines every tuple (row) of one table with every tuple (row) in other table, also referred as cross Product. It is a binary operator. 5. Union (U): Outputs the union of tuples from both the relations. Duplicate tuples are eliminated automatically. It is a binary operator means it require two operands. 4 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering 2. Non-Procedural Language In Non-Procedural Language user outlines the desired information without giving a specific procedure or without telling the steps by step process for attaining the information. It only gives a single Query on one or more tables to get. The user tells what is to be retrieved from the database but does not tell how to accomplish it. For Example: get the name and the contact number of the student with a Particular ID will have a single query on STUDENT table. Relational Calculus: is a Non Procedural Language, exists in two forms: Tuple Relational Calculus (TRC): Tuple Relational Calculus is a non procedural query language, It is used for selecting the tuples that satisfy the given condition or predicate. The result of the relation can have one or more tuples (row). Domain Relational Calculus (DRC): Domain Relational Calculus is a Non Procedural Query Language, the records are filtered based on the domains, DRC uses the list of attributes to be selected from relational based on the condition. 7. Database design Database design is the process of defining the structure, organization, and relationships of data within a database. It involves creating a blueprint that outlines how data will be stored, accessed, and managed. Key components of database design include: 1. Data Modeling: Identifying the entities (e.g., customers, orders) and their relationships. 2. Schema Definition: Establishing tables, fields, data types, and constraints. 3. Normalization: Organizing data to minimize redundancy and improve integrity. 4. Indexing: Creating indexes to enhance query performance. 5. Security Considerations: Implementing access controls and data protection measures. The Entity-Relationship (ER) approach is a foundational method for database design that visually represents the data and its relationships. 5 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering Here are the key components and concepts: 1. Entities Definition: Objects or things in the database that have a distinct existence, each table is associated with an ER entity. Examples: Customers, Products, Orders. Representation: Typically represented as rectangles in an ER diagram. 2. Attributes Definition: Characteristics or properties of entities, they correspond to the columns on a table. Examples: For a Customer entity, attributes might include CustomerID, Name, and Email. Types: Simple: Indivisible (e.g., Name). Composite: Can be divided into smaller parts (e.g., Address). Derived: Can be calculated from other attributes (e.g., Age from Date of Birth). 3. Relationships Definition: Connections between two or more entities. Types: One-to-One (1:1): One entity instance is related to one instance of another entity. One-to-Many (1): One entity instance is related to multiple instances of another entity. Many-to-Many (M): Multiple instances of one entity relate to multiple instances of another entity. Representation: Shown as diamonds connecting entities in an ER diagram. 6 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering ER Diagrams Purpose: Visual representation of entities, attributes, and relationships. Components as shown in the below figure: Normalization Involves organizing the attributes and tables of a database to reduce redundancy and improve data integrity. Benefits of the ER Approach Provides a clear and understandable visualization of the data structure. Facilitates communication between stakeholders. Helps identify potential issues in data relationships before implementation. 7 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering Example of ER diagram ER Model Vs ER Diagram ER Model (Entity-Relationship Model) Definition: The ER Model is a conceptual framework used to describe the data structure of a database. It focuses on the entities within the system and the relationships between them. Components: It includes entities (objects or things in the database), attributes (properties of entities), and relationships (how entities interact with each other). Purpose: The model serves as a blueprint for designing a database and helps to understand the data requirements and constraints of the system. ER Diagram (Entity-Relationship Diagram) Definition: An ER Diagram is a visual representation of the ER Model. It uses standardized symbols to depict entities, attributes, and relationships graphically. Components: In an ER Diagram, entities are represented as rectangles, attributes as ovals, and relationships as diamonds. Lines connect these elements to illustrate how they interact. Purpose: The diagram provides a clear and intuitive way to communicate the data structure and relationships, making it easier for stakeholders to understand the design. Summary ER Model: The theoretical framework describing the structure of a database. ER Diagram: The visual representation of that framework, used for communication and design purposes. 8 Uruk University Department of Cybersecurity and Cloud Computing Technical Engineering Collage of Technical Engineering Q.1: What is the difference between Relational Calculus and Relational Algebra? Answer: Relational Algebra is a Procedural Query Language whereas Relational Calculus is a Non Procedural Query Language. Relational Algebra targets on how to get the result whereas Relational Calculus focuses on what result is to be obtained. Q.2: What is Relational Algebra? Answer: Relational Algebra is a Procedural Query Language that targets on how to get the results, It take instances of relations as input and then yields instances of relations as output, it is performed recursively on a relation. Q.3: What is the use of Relational Query Language? Answer: Relational Query Language is used by users to communicate with the database, It uses relational Algebra to break the user requests and instructs the DBMS to execute those requests. Relational Query Language can be Procedural or Non Procedural. Q.4: What are the types of Relational Calculus? Answer: There are two types of Relational Calculus Tuple Relational Calculus and Domain Relational Calculus. 9