Bases de données
121 documents à télécharger gratuitement
Cours, examens, TD, TP et exercices de bases de données. Thèmes couverts : SQL, MySQL, Oracle, modèle relationnel, SGBD, MERISE.
This document provides an in-depth overview of SQL as a language used for relational Database Management Systems (DBMS) and covers its historical origin, purpose, and universal application across platforms. It explains the three types of SQL commands: Data Definition Language (DDL) for managing database structure, Data Manipulation Language (DML) for handling data operations, and Data Control Language (DCL) for transactions and permissions. Detailed syntax and examples are provided for creating, altering, and maintaining database objects such as tables, keys, and constraints. Best practices...
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 document provides a comprehensive overview of PL/SQL, an extension of SQL incorporating procedural elements such as variables, loops, and error handling. It presents structures for organizing code using blocks, procedures, and functions, as well as mechanisms for database interaction via cursors and exception handling. Practical examples and exercises emphasize learning through hands-on programming, including the use of structured control flows to interface with databases efficiently and manage data operations securely.
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.
This document outlines a tutorial on SQL queries aimed at managing information systems related to student course schedules. It includes a relational schema for student, course, and session databases along with various SQL tasks such as inserting data, retrieving information, and creating database tables. The tutorial is part of a course provided by Mines de Nancy and includes both simple and nested SQL query exercises.
This document contains corrections and exercises related to SQL data types, syntax errors, table creation, data manipulation, and data access queries. The content includes definitions and appropriate data types for various entities, incorrect syntax examples, and SQL queries for managing a film database.
This document meticulously describes components of Oracle Database architecture, including memory management structures like the Database Buffer Cache and Redo Log Buffer, alongside their operational logic governed by specific parameters (e.g., DB_CACHE_SIZE, LOG_BUFFER). It also delves into the structural organization of Oracle's physical storage layers like tablespaces, control files, and data files, emphasizing their purpose and interrelation. Additionally, it explains the roles of user and server processes, background processes like DBWn and LGWR, and their significance in maintaining d...
This document introduces the fundamental concepts of relational databases, their advantages, and methodologies of data modeling. It covers topics including relational algebra, SQL language, transaction management, and database programming with tools like VBA and Access interfaces. Historical evolution and key database models such as hierarchical, network, and relational are analyzed alongside practical examples of entity-relationship diagrams and normalization processes.
This document provides a detailed explanation of Oracle's memory and storage components, including data caching, logging, and transaction management. Techniques like the Least Recently Used (LRU) algorithm and parameterized configurations such as DB_CACHE_SIZE, LOG_BUFFER, and SHARED_POOL_SIZE are central to optimizing operations. Key storage structures like tablespaces, data files, control files, and redo log files are discussed, highlighting their roles in ensuring data integrity and recovery. The document also describes core Oracle processes, including DBWn and LGWR, and their critical i...
Oracle Warehouse Builder (OWB) is introduced as an integrated tool for data warehousing and business intelligence that handles schema design, ETL processes, and metadata management via a unified interface. It offers functionalities such as automated code generation, metadata reporting, and integration with Oracle systems and third-party sources. OWB promotes productivity, accuracy, and cost-efficiency while providing advanced options for data integration, quality management, and lifecycle management. The document also covers OWB’s architectural details and its practical applications in BI s...
This document provides a comprehensive introduction to PL/SQL, Oracle's procedural extension for SQL. It explains the limitations of SQL as a purely declarative language and introduces the capabilities of PL/SQL, including procedural features like loops, conditional structures, and exception handling. The methodology focuses on the structuring of PL/SQL code into logical 'blocks,' improving performance and readability. Key processes such as variable declarations, data type conversions, cursors, and the creation of triggers, procedures, and functions are covered in detail.
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.
This document contains three practical exercises in PL/SQL. The first exercise involves creating a program to display the system catalog of tables and views of the database account using the system view `TAB`. The second exercise focuses on updating and transferring employee data due to a company's relocation, including currency conversion and salary increase. The third exercise involves transforming product data from an outdated database, converting prices from Francs to Euros, handling special cases such as empty tables and null values, and applying advanced SQL functions like UPPER, ROUN...
This document introduces SQL, a fourth-generation programming language developed by IBM in the 1970s and standardized as SQL-92 in 1992, laying the foundation for relational database queries. It outlines four key subsets of SQL: Data Query Language (DQL) for data retrieval, Data Manipulation Language (DML) for data updates, Data Definition Language (DDL) for structuring databases, and Data Control Language (DCL) for managing access control. The document then details core SQL operations such as SELECT commands for projections and restrictions, syntax for WHERE clauses with logical operators,...
This document provides solutions to SQL queries and database management problems. It first establishes a relational database schema for a car sales company using an Entity/Relationship diagram and schema definition, followed by SQL query examples for specific operations such as retrieving sales data and calculating profits. The document also includes schema and SQL queries for a cinema database, addressing problems such as actor-payroll calculations and film retrieval based on various attributes. Additionally, relational algebra expressions and query optimizations are explored for several s...
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.
This document introduces Oracle Database, its history, and core functionalities, including data manipulation, security, and backup features. It provides a step-by-step guide to the installation and configuration of Oracle Database Express Edition 11g. It details each phase, including software prerequisites, installation process, and post-installation setup for administrative access. The document concludes with instructions on connecting to the database and ensuring functionality, with tips for remote access and uninstallation.
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...
This document serves as an introduction to PL/SQL, a procedural language that extends SQL, specifically within the Oracle environment. It covers essential features, programming conventions, block structures, syntax rules, and various programming structures within PL/SQL. The document is aimed at helping learners understand the practical aspect of PL/SQL programming.
This document provides an overview of database views, indexes, and sequences. It explains the definitions, creation, usage, and limitations of each element in SQL database management. The document contains practical examples and syntax for implementing these concepts.
This document provides a detailed explanation of the relational model in database systems, originally defined by Ted Codd in 1970 and widely adopted in commercial database systems like Oracle, DB2, and SQL Server. It explains the foundational components of the relational model, including relations (tables), attributes (columns), and tuples (rows). The document also details the translation process from the Entity-Attribute model to the relational model, describing how entities, associations, and attributes are mapped. Integrity constraints such as primary keys, foreign keys, referential inte...
This document outlines various domains, predicates, and clauses related to identity and food items. The first section details personal identification predicates like name, age, and city of residence. The second section lists menu items categorized by type along with their prices.
This course provides an introduction to PL/SQL, a procedural language used in Oracle. It covers the structure of PL/SQL blocks, variable declarations, SQL integration, and advanced features such as exceptions and cursors. Students will learn how to write and execute PL/SQL code effectively.
This document outlines the specifications for a database exam focused on educational data. It includes details for creating tables and constraints in a SQL database, as well as specific queries to retrieve information about students, schools, and teachers. Tasks include table creation, data insertion, and various querying scenarios.