This repository contains the work completed for the Database systems course assignment, which was divided into two main parts: Database Design and Implementation & Queries. The project focuses on designing a relational database and executing various SQL queries in MySQL.
This phase involved the conceptualization and documentation of the database schema. The following steps were taken:
- Choosing a Database Theme: Selecting a theme with sufficient entities and relationships.
- Conceptual Schema Design: Documenting the schema based on chosen entities and their relationships.
- Entity-Relationship (ER) Diagram: Creating a reduced ER diagram using Chen notation, covering:
- Entities
- Relationships
- Cardinalities of relationships
- Accompanying Text: Defining attributes for each entity or relationship, and specifying the primary key for each entity.
- Relational Schema Conversion: Converting the conceptual schema into a relational schema.
- Normalization: Applying normalization principles to ensure database integrity.
This phase focused on the implementation of the designed database and performing SQL queries using MySQL. An internal application was used for accessing personal MySQL databases, providing the necessary permissions for creating and updating tables.
- Table Creation: Designing and populating the tables with sufficient data rows.
- Query Creation: Developing four meaningful SQL queries that involve multiple tables. Some of the queries had to include grouping or be nested.
- Query Documentation: Detailed descriptions of each query, including:
- What the query returns: Explanation of the result set (output table).
- How the query works: Step-by-step description of the query's logic and execution process.
For the second part of this assignment, only the SQL queries and their corresponding explanations are included in the repository. The commands for creating and populating the tables are not part of the deliverables.
The queries are stored in a PDF file.