Tasks and Duties
Objective
This task is designed to engage you in creating a solid foundation for a relational database. You will choose a hypothetical business scenario, define its requirements, and design a comprehensive database schema. The main goal is to understand how to translate business requirements into a coherent data model and to design an efficient relational structure.
Expected Deliverables
- A DOC file containing a detailed explanation of your business scenario and its needs.
- A complete Entity-Relationship (ER) diagram created using any tool of your choice.
- An explanation of normalization steps (up to third normal form) with reasons for each decision.
Key Steps
- Scenario Selection: Choose a publicly available business domain (e.g., an online bookstore, a logistics company, or a restaurant chain). Define key business objectives.
- Requirement Analysis: List down entities, relationships, key attributes, and constraints based on the scenario.
- Data Modeling: Construct an ER diagram that highlights these entities, relationships, and cardinalities.
- Normalization Process: Explain your normalization process and justify splitting tables in the DOC file submission.
- Documentation: Provide a detailed narrative outlining each step, rationale behind design decisions, and potential challenges.
Evaluation Criteria
Your submission will be evaluated on the clarity, depth, and professionalism of the documentation, correctness and creativity of the ER diagram, as well as the logical consistency of the normalization process. The ability to translate business requirements into a robust database design is key.
Objective
This task focuses on the practical application of SQL skills through the development of advanced SQL queries. You are expected to create a series of complex queries that demonstrate a deep understanding of SQL functionalities such as joins, subqueries, set operations, and aggregate functions. The purpose is to enable you to extract complex insights from a database based on hypothetical scenarios derived from your chosen business domain from Week 1 or any public dataset.
Expected Deliverables
- A DOC file that outlines a series of complex SQL queries, including descriptions of their objectives and expected outcomes.
- Each query should be accompanied by comments, explanation of join types, and rationale behind using subqueries or aggregates.
- Screenshot copies of query results (if applicable) or detailed descriptions of the anticipated outputs.
Key Steps
- Query Planning: Draft a list of SQL problems or analytical questions relevant to your selected scenario.
- Query Construction: Write and annotate each SQL query, covering various SQL functionalities such as inner joins, outer joins, subqueries, window functions, and grouping techniques.
- Error Handling: Describe any challenges encountered with query performance and possible improvements or alternatives.
- Documentation: Provide step-by-step explanations in your DOC file detailing the approach, logic, and key SQL syntax used.
Evaluation Criteria
Submissions will be evaluated based on the complexity and correctness of SQL queries, clarity of documentation, demonstration of advanced SQL techniques, and the ability to explain performance considerations. Originality and professional presentation in the DOC file are also important.
Objective
This task emphasizes the importance of database performance optimization. You are expected to analyze a series of SQL queries (or a hypothetical workload scenario) to identify potential bottlenecks and inefficiencies. The purpose is to apply indexing strategies, query rewriting techniques, and other performance tuning methods to improve query execution times and overall database performance.
Expected Deliverables
- A DOC file that includes a detailed evaluation report of at least three different SQL queries or operations.
- An explanation of the identified performance issues and the potential indexing or query modification strategies applied.
- Comparative analysis showing before-and-after scenarios with potential execution plan snapshots (screenshots or detailed hypothetical output descriptions) when possible.
Key Steps
- Hypothetical Workload Analysis: Define a set of SQL operations which mimic real-world scenarios with performance challenges.
- Performance Identification: Discuss how you would identify performance issues using query execution plans and metrics.
- Optimization Techniques: Detail specific tuning approaches such as indexing, rewriting queries, or partitioning tables. Explain why these are appropriate for each case.
- Comparative Analysis: Describe your methodology for assessing improvements, including any assumptions made if actual performance data isn’t available.
- Documentation: Your DOC file should contain a structured report with an introduction, analysis, techniques, results, and conclusion sections.
Evaluation Criteria
Submissions will be evaluated on your analytical skills, understanding of performance optimization, clarity in describing techniques, and the professional quality of the documentation. Innovative approaches and comprehensive comparative analysis will be valued.
Objective
This task is aimed at consolidating your skills in automating data workflows and integrating multiple data components. You will develop an approach to automate routine SQL tasks such as data extraction, transformation, and loading (ETL) processes, while also creating comprehensive documentation. This practice simulates real-world scenarios where automation is key to database management and operations, ensuring efficiency and reliability in data processing.
Expected Deliverables
- A DOC file that outlines your complete strategy for data integration and automation, including a flowchart or diagram to illustrate the process.
- Detailed explanation of SQL scripts, triggers, or stored procedures created to facilitate automation.
- A description of how error handling, scheduling, and logging are incorporated into the automation process.
Key Steps
- Process Design: Outline a hypothetical ETL process for a selected public dataset or a business scenario. Describe each phase in detail.
- Automation Strategy: Specify SQL scripts and procedures you would use for automating data import/export or transformation tasks. Include pseudo-code or workflow diagrams if needed.
- Error Handling and Logging: Document how you would manage errors, perform logging, and ensure reliability in an automated workflow.
- Final Documentation: Create a DOC file that presents the integration strategy, including sections on objectives, methodology, scripts, tools and techniques, and expected benefits. Ensure that your description is thorough and detailed, covering all aspects of the process.
Evaluation Criteria
Your submission will be evaluated on the completeness and clarity of your automation plan, the creativity in integrating multiple SQL components, quality of the troubleshooting and error handling strategies, and the overall quality of documentation. Correct usage of SQL-based tools and detailed explanations in the DOC file will be key in the assessment.