Relational Databases PDF

RELATIONAL DATABASES Type of database that stores and provides access to data points that are related to one another. Data is organized in tables, which consist of rows and columns. Each table represents a specific entity and contains records (rows) that are composed of fields (columns). Table: the...

RELATIONAL DATABASES Type of database that stores and provides access to data points that are related to one another. Data is organized in tables, which consist of rows and columns. Each table represents a specific entity and contains records (rows) that are composed of fields (columns). Table: the fundamental structure to store data in a relational database. Each table has a unique name and contains rows (records) and columns (fields). Columns define the attributes, and rows contain the data instances. Relationships: they are essential in a relational database for linking tables to enable complex queries and to ensure data integrity. Relationships are established through keys: - PRIMARY KEYS: column/set of columns in a table that uniquely identifies each row in that table. - FOREIGN KEYS: column/set of columns in one table that uniquely identifies a row of another table. It establishes a link between the data in the 2 tables. WHY RELATIONSHIPS MATTER? Data integrity: ensures consistency and accuracy of the data. Foreign key constraints prevent invalid data from being inserted. EX: preventing an order from referencing a non-existent customer. Efficient data retrieval: allows complex queries to retrieve related data across multiple tables. EX: joining Customers and Orders tables to find out which customer placed which order. Reduced data redundancy: eliminates the need to duplicate data by storing related data in separate tables. EX: storing customer details once in the Customers table and referencing it in the Orders table. RELATIONAL DB: - Entity identification: ER diagrams assist in identifying entities, attributes of these entities and the relationships between entities. - Relationship definition: they define the relationships between entities. It´s essential for understanding how different entities interact with each other. It refers to the number of instances of one entity that can be associated with instances of another entity through a relationship. o ONE TO ONE: one person has one DNI  o ONE TO MANY: one school has many students o MANY TO ONE: many employees work in one store o MANY TO MANY: many employees can enroll in many trainings, and many trainings can have many employees enrolled → use of intermediate table - Normalization: it reduces data redundancy and improves data integrity by organizing fields and table of a database. - Implementation: ER diagrams guide the creation of tables, specifying primary keys, foreign keys, and other constraints based on the relationships defined in the ER diagram. STEPS FOR ER MODEL CREATION: 1. Identify entities 2. Define relationships 3. Determine attributes 4. Choose keys 5. Draw the diagram → use standard ER diagram symbols: ✓ ENTITY: represented by a rectangle ✓ RELATIONSHIP: represented by a diamond ✓ ATTRIBUTE: represented by an oval ✓ PRIMARY KEY: represented by an underlined attribute ✓ FOREIGN KEY: represented by a dashed underline or noted in the entity´s attributes NORMALIZATION: Organize and structure the data in an efficient way to avoid redundancies and to ensure accuracy and data integrity. - Eliminate data redundancy - Use primary and foreign keys - Reduce transitive dependencies 1ST NORMAL FORM: each column contains only atomic values (each word/number in a single column) 2ND NORMAL FORM: eliminate partial dependencies. All non-key attributes are fully dependent on the primary key. What is the primary key? Are all non-key attributes depending on the complete primary key? 3RD NORMAL FORM: eliminate non-key attributes depending on another non-key attribute. DATABASE DESIGN: The process of defining the structure, storage and retrieval mechanisms of data in a database system. It involves creating a detailed blueprint of how data will be stored, accessed and managed in a database. Scheme definition: specifies the tables, fields, data types and relationships in the database Normalization: ensures the database structure minimizes redundancy and optimizes data integrity Physical implementation: determines how the logical schema will be physically stored and accessed in the database management system Performance optimization: includes indexing, partitioning and query optimization to enhance database performance