Travaux pratiques (TP) - Bases de données
23 documents à télécharger gratuitement
Travaux pratiques (TP) de bases de données partagés par des étudiants et des enseignants. Thèmes couverts : SQL, MySQL, Oracle, modèle relationnel, SGBD, MERISE.
This document outlines the steps for preparing a virtual environment to study NoSQL databases, incorporating the setup of VirtualBox and a virtual Ubuntu 20 machine. It describes VirtualBox's networking modes, guides users through obtaining an Ubuntu ISO, creating and running a virtual machine, and provides essential terminal commands for updating packages and setting up network tools. The document is designed as a hands-on reference for students or practitioners in virtual environment preparation within database contexts.
This document provides a comprehensive guide to SQL as a Data Manipulation Language (DML). It covers the essential SQL clauses such as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY, along with their syntax, application, and use cases in querying relational databases. Examples demonstrate data extraction, aggregation functions, filters, logical operators, and table joins to facilitate understanding. The material also emphasizes conditions, NULL values, and advanced techniques like concatenation and reflexive joins while illustrating real-life database queries using an employee database.
This document outlines the correction for Lab TP2 regarding data dictionaries in Oracle. It includes steps to connect and manage database instances, roles of various system tables, and SQL commands to describe data structures. Additionally, it provides instructions for creating storage spaces and retrieving instance information.
This document outlines the creation of SQL tables for a cycling event database, including teams, countries, riders, stages, and timing. It provides SQL commands for inserting data into these tables as well as queries for data retrieval. The focus is on relational database design and querying techniques in SQL.
Ce TP a pour objectif de permettre aux étudiants d'écrire des requêtes SQL avancées en utilisant des constructions complexes telles que GROUP BY, HAVING, et des sous-requêtes. Les étudiants travailleront avec une base de données importée pour répondre à diverses questions concernant des coureurs et des étapes. Les exercices incluent des fonctions d’agrégation et des requêtes imbriquées.
This document provides a solution and analysis for various PL/SQL exercises related to triggers and queries. It includes explanations of trigger functionality and sample SQL scripts for calculating averages and processing data. Additionally, it addresses updates and inserts in database tables.
This document outlines the step-by-step process for implementing incremental data extraction and change data capture (CDC) using SQL Server and SSIS. The lab first establishes a setup with predefined tasks, guides the user through configuring SSIS packages for tracking modified data using high water marks, and verifies successful execution. It also introduces CDC in SQL, demonstrating change tracking with timestamp ranges and stored procedures for extracting modifications in datasets efficiently.
This document focuses on optimizing data extraction processes by configuring SSIS packages to perform incremental data extraction, thereby reducing network and system resource usage. It explores leveraging datetime columns, Change Data Capture (CDC), and Change Tracking to identify and extract only modified rows. Practical exercises include configuring SSIS packages, utilizing expressions and variables for incremental extraction, CDC setup and usage, stored procedure creation to identify data changes, and integrating these techniques into ETL workflows via SSIS and Visual Studio debugging....
This lab focuses on modifying SSIS packages to address the inefficiencies of extracting unnecessary data in ETL processes. It guides participants through using datetime columns and Change Data Capture to ensure only modified records are extracted. By the end of the lab, participants will have practical experience in implementing incremental data extraction techniques.
The document outlines a programming assignment requiring the use of the Map-Reduce and MongoDB Aggregation frameworks for data processing. It involves importing a dataset, performing counting operations using Map-Reduce, analyzing ordering issues, and generating derived collections based on specific keys. The assignment also requires joining the resulting collections into a final document format. Detailed references are provided to guide the implementation of the tasks.
This document outlines a lab assignment focused on Map-Reduce and aggregation frameworks in MongoDB. Students are required to import JSON data, perform data counting using Map-Reduce, generate new collections based on specified criteria, and execute a join operation. The tasks include analyzing data structure and problem-solving in a practical context.
This lab focuses on developing a data warehouse schema for Adventure Works Cycles, enabling analytical reporting and data analysis. Participants will implement both star and snowflake schemas to support the company's reporting requirements. Key exercises include preparing the lab environment, creating dimension and fact tables, and modifying database diagrams.
This lab focuses on writing SQL queries to extract and manipulate data related to restaurants and their offerings. It includes various tasks ranging from displaying restaurant information to calculating averages and ordering results. Students will demonstrate their SQL skills through practical applications in a database context.
This document presents a lab assignment focused on data manipulation in SQL within a database system. It includes instructions for inserting data into multiple tables related to restaurants, dishes, orders, and delivery personnel. The assignment also outlines tasks to update and manipulate ratings, quantities, and other attributes based on business logic.
This document provides an overview of cursor manipulation in PL/SQL, detailing the types of cursors, their attributes, and simplified usage. It includes practical examples of declaring, opening, executing, and closing cursors. The aim is to enhance understanding of how to effectively handle SQL query results in PL/SQL programming.
This document provides a comprehensive exercise in designing and working with relational databases, specifically focusing on schemas, constraints, and advanced SQL queries. It introduces a database involving tennis players, tournaments, sponsors, and financial records. Participants are tasked with creating the schema, inserting data, and writing SQL queries to extract insights based on specific conditions, such as retrieving player performance statistics, financial earnings, and tournament participation. The lab emphasizes practical SQL proficiency and demonstrates the application of concep...
This project involves the construction and querying of a relational database schema for tennis tournaments. The schema includes entities such as players, tournaments, earnings, and sponsors, with specified constraints and keys. The practical tasks include creating the schema, populating it with provided data scripts, and answering complex SQL-based queries concerning player participation, sponsorship, and performance in tournaments. The queries test various conditions and use cases, such as multi-year performance analysis, sponsor engagement, and match outcomes.
The document presents an in-depth series of exercises focused on PL/SQL programming within the context of databases. It guides the creation and manipulation of tables, variables, and records, employing conditional logic and loops for dynamic operations like updating, deleting, or calculating data attributes. Methodologies address real-world database management scenarios, including type and record definitions, conditional data modifications, and iterative processing. Findings emphasize practical implementations to teach advanced PL/SQL techniques through structured, hands-on activities.
The document provides an introduction to interfacing PHP with MySQL databases using various methods such as the mysql_, mysqli_, and PDO extensions. It outlines the core steps for connecting, querying, and managing a MySQL database, including executing SQL queries to retrieve, insert, update, and delete data. Error handling mechanisms like try-catch blocks and methods to ensure robust database operations are also covered. Additionally, the document compares the PDO and MySQLi extensions, highlighting their features and functionalities for database interactions in PHP.
This document provides a series of SQL queries and operations that aim to manage, query, and manipulate data within a relational database system relevant to a scenario involving members and competitions. The queries demonstrate the use of data insertion, filtering with conditions, data aggregation, the MINUS operation, and subqueries to derive meaningful insights. It illustrates practical SQL functions such as counting, averaging, grouping, and joining tables for relational operations. This exercise helps familiarize readers with advanced SQL methodologies involving constraints and comparat...
This document provides a practical SQL structured exercise focused on a simplified database schema related to aviation, covering tasks such as data manipulation, analysis, and creation of views. It explores operations like filtering, aggregation, altering table structures, and creating new entries or views. Additionally, advanced SQL queries analyze pilots, aircrafts, and destinations, emphasizing sorting, conditions, and capacity calculations. The document concludes with administrative tasks for user access control.
The document introduces SQL as a language to define, manipulate, and control data within relational databases. A detailed explanation of SQL commands is provided, covering data definitions, data manipulations, and access controls. The document elaborates on data types, integrity constraints, and table creation/modification commands, complemented by a practical implementation section with relational schemas. The practical section demonstrates SQL in action with table creation, constraint management, and attribute modifications.
The document provides a comprehensive overview of SQL, detailing its primary commands and structured subsets including DDL for data definition, DML for data manipulation, and DCL for access control. It further elaborates on SQL’s data types, integrity constraints, and table management operations such as creation, modification, and deletion. Practical exercises are included to reinforce theoretical understanding by guiding the user through schema creation, attribute manipulation, and constraint application. The document concludes with a relational schema example and hands-on steps for applyi...