Master of Science / Department of Computer Science / Doina Caragea / An Introduction to Databases course involves learning the concepts of data storage, manipulation, and retrieval. Relational databases provide an ideal learning path for understanding database concepts. The Structured Query Language (SQL) is a standard language for interacting with relational database. Each database vendor implements a variation of the SQL standard. Furthermore, a particular question that asks for some data can be written in many ways, using somewhat similar or structurally different SQL queries. Evaluation of SQL queries for correctness involves the verification of the SQL syntax and semantics, as well as verification of the output of queries and the usage of correct clauses. An evaluation tool should be independent of the specific database queried, and of the nature of the queries, and should allow multiple ways of providing input and retrieving the output. In this report, we have developed an evaluation tool for SQL queries, which checks for correctness of MySQL and PostgreSQL queries with the help of a parser that can identify SQL clauses. The tool developed will act as a portal for students to test and improve their queries, and finally to submit the queries for grading. The tool minimizes the manual effort required while grading, by taking advantage of the SQL parser to check queries for correctness, provide feedback, and allow submission.

Feature engineering at scale is always critical and challenging in the machine learning pipeline. Modern data warehouses enable data analysts to do feature engineering by transforming, validating and aggregating data in Structured Query Language (SQL). To help data analysts do this work, Data Build Tool (DBT), an open-source tool, was proposed to build and orchestrate SQL pipelines. Hopsworks, an open-source scalable feature store, would like to add support for DBT so that data scientists can do feature engineering in Python, Spark, Flink, and SQL in a single platform. This project aims to create a concept about how to build this support and then implement it. The project checks the feasibility of the solution using a sample DBT project. According to measurements, this working solution needs around 800 MB of space in the server and it takes more time than executing DBT commands locally. However, it persistently stores the results of each execution in HopsFS, which are available to users. By adding this novel support for SQL using DBT, Hopsworks might be one of the completest platforms for feature engineering so far. / Att utveckla funktioner i stor skala är alltid kritiskt och utmanande i pipeline för maskininlärning. Moderna datalager gör det möjligt för dataanalytiker att göra feature engineering genom att omvandla, validera och aggregera data i Structured Query Language (SQL). För att hjälpa dataanalytiker att utföra detta arbete föreslogs Data Build Tool (DBT), ett verktyg med öppen källkod, för att bygga och organisera SQL-pipelines. Hopsworks, ett skalbart funktionslager med öppen källkod, vill lägga till stöd för DBT så att datavetare kan göra funktionsutveckling i Python, Spark, Flink och SQL på en enda plattform. Det här projektet syftar till att skapa ett koncept för hur man bygger detta stöd och sedan genomföra det. Projektet kontrollerar lösningens genomförbarhet med hjälp av ett exempel på DBT-projekt. Enligt mätningar behöver denna fungerande lösning cirka 800 MB utrymme på servern och det tar mer tid än att utföra DBT-kommandon lokalt. Den lagrar dock permanent resultaten av varje körning i HopsFS, vilka är tillgängliga för användarna. Genom att lägga till detta nya stöd för SQL med DBT kan Hopsworks vara en av de mest kompletta plattformarna för funktionsutveckling hittills.

Masters of Science / Coders of databases repeatedly face the problem of checking their Structured Query Language (SQL) code. Instructors face the difficulty of checking student projects and lab assignments in database courses. We collect and categorize common MySQL programming errors into three groups: data definition errors, data manipulation errors, and transaction control errors. We build these into a comprehensive list of MySQL errors, which novices are inclined make during database programming. We collected our list of common MySQL errors both from the technical literature and directly by noting errors made in assignments handed in by students. In the results section of this research, we check and summarize occurrences of these errors based on three characteristics as semantics, syntax, and logic. These data form the basis of a future static MySQL checker that will eventually assist database coders to correct their code automatically. These errors also form a useful checklist to guide students away from the mistakes that they are prone to make.

This term project is about analyses and design of information system for  administration and managing business firm.System is designed as module system with unlimithed count of modules that coact or  are connected with another modules. Each module has its own data tables in database, own  Classes which make the middle layer of aplication  and graphical interface, but modules are not independent (one module can not work as a system).Iner communication among modules is based on database servers.Part of the application is its own database. To accomplish this project I had to familiarize myself with a problemathic of programming in  programming language C# and with database language PostgreSQL

Although approximate query processing is a prominent way to cope with the requirements of data analysis applications, current database systems do not provide integrated and comprehensive support for these techniques. To improve this situation, we propose an SQL extension---called SQL/S---for approximate query answering using random samples, and present a prototypical implementation within the engine of the open-source database system Derby---called Derby/S. Our approach significantly reduces the required expert knowledge by enabling the definition of samples in a declarative way; the choice of the specific sampling scheme and its parametrization is left to the system. SQL/S introduces new DDL commands to easily define and administrate random samples subject to a given set of optimization criteria. Derby/S automatically takes care of sample maintenance if the underlying dataset changes. Finally, samples are transparently used during query processing, and error bounds are provided. Our extensions do not affect traditional queries and provide the means to integrate sampling as a first-class citizen into a DBMS.

Dans le domaine des transactions et du paiement électronique avec une carte à puce, le standard de communication en champ proche « Near Field Communication » (NFC) s’est imposé comme la technologie des transactions sans contact mobiles sécurisées pour le paiement, le contrôle d’accès, ou l’authentification. Les services sans contact mobiles sécurisés sont basés sur le mode émulation de carte du standard NFC qui implique une composante matérielle à accès restreint de type carte à puce appelée « Secure Element » (SE) dans laquelle sont stockées les données confidentielles et les fonctions sensibles. Malgré les efforts de standardisation de l'écosystème, les modèles proposés pour la mise en œuvre du SE sont complexes et souffrent du manque de généricité, à la fois pour offrir des mécanismes d’abstraction, pour le développement d’applications de haut niveau, et pour la mise en œuvre et la vérification des contraintes de sécurité des applications.L’objectif de la thèse est de concevoir et réaliser un environnement logiciel basé sur un modèle générique compatible avec les standards établis et peu sensible aux évolutions technologiques. Cet environnement devrait permettre à des non-experts de développer des applications multiplateformes, multimodes, multi-facteurs de forme, qui s’interfacent avec le SE dans un smartphone NFC. / In the field of electronic transactions and payment with a smart card, the Near Field Communication (NFC) standard has stood out against other candidate technologies for secure mobile contactless transactions for payment, access control, or authentication. Secure mobile contactless services are based on the card emulation mode of the NFC standard which involves a smart card type component with restricted access called "Secure Element" (SE) in which sensitive data and sensitive functions are securely stored and run. Despite considerable standardization efforts around the SE ecosystem, the proposed models for the implementation of SE are complex and suffer from the lack of genericity, both to offer abstraction mechanisms, for the development of high-level applications, and for the implementation and verification of applications security constraints.The objective of the thesis is to design and realize a software environment based on a generic model that complies with established standards and which is not very sensitive to technological evolutions. This environment should enable non-experts to develop multi-platform, multi-mode, multi-factor SE-based applications running into the NFC smartphone.

Model View Controller (MVC) software architecture is widely spread and commonly used in application’s development. Therefore generation of data layer for the database model is able to reduce cost and time. After research on current Object Relational Mapping (ORM) tools, it was discovered that there are generating tools like Data Access Object (DAO) and Hibernate, however their usage causes problems like inefficiency and slow performance due to many connections with database and set up time. Most of these tools are trying to solve specific problems rather than generating a data layer which is an important component and the bottom layer of database centred applications. The proposed solution to the problem is an engineering approach where we have designed a tool named Generated Intelligent Data Layer (GIDL). GIDL tool generates small models which create the main data layer of the system according to the Database Model. The goal of this tool is to enable and allow software developers to work only with object without deep knowledge in SQL. The problem of transaction and commit is solved by the tool. Also filter objects are constructed for filtering the database. GIDL tool reduced the number of connections and also have a cache where to store object lists and modify them. The tool is compared under the same environment with Hibernate and showed a better performance in terms of time evaluations for the same functions. GIDL tool is beneficial for software developers, because it generates the entire data layer.