Implementation Phase

Introduction

In this phase of the database project, you are to implement a relational database that is a subset of the database model you designed. You will implement the relational database as a MySQL database. It is your reponsibility to determine the data that you will use to load the tables with rows. Be sure to use realistic values for the data; to this end, you may use information found on the Internet -- as always, be sure to keep a list of references used and cite these where appropriate.

Database to be implemented

Each group will be given instructions on which part of the database model to implement. You may need to make changes to the implementation so that you are implementing between 10 and 15 tables. Be sure that you implement parts of the model that are essential and interesting aspects of the problem you proposed for the project.

Activities

  1. Design the Relational Database Schema.  Translate the UML Class diagram to the relational database model. Be sure to identify all candidate keys for every relation. You are to use all relational database design techniques we have learned in this class. In particular, you should carefully plan the use of surrogate and substitute PK's.
  2. Determine the SQL DDL to create the relations. The DDL must include constraints for uniqueness of primary keys, uniqueness of any candidate keys, enforcement of referential integrity, and appropriate data constraints on attributes (e.g.: CHECK constraints).
  3. Load data into your database.  You should have sufficient data in each table to demonstrate the functionality of your database and to return meaningful results for the SQL to be implemented (see below). Use your own judgement as to the number of rows in each table. You need to have enough data to demonstrate the correctness of your model and of the queries you write.
  4. To complete the implementation of the database, you are to provide 10 SQL queries that will be used to create reports demonstrating the correctness, consistency, and completeness of your relational database. A description of the information being retrieved must be provided with each of the SQL queries you create. These descriptions are to be aimed at users of the database (not someone who is knowledgeable of SQL), so make them as clear as possible. You must write queries that make use of the following concepts we have learned:
    1. All queries must join multiple tables and should rename columns when necessary so that they are more descriptive than the name of the column in the table (when the column name is not descriptive).
    2. Outer join of three or more tables
    3. Aggregation of values for information grouped according to some attribute(s).
    4. Combination of aggregation of information that is gathered with the outer join of tables. Clarification: you must apply one or more aggregate functions to information gathered with the use of outer joins. You must aggregate at least one column that has NULL values as a result of the outer joins. Be sure to explain this query clearly.
    5. Three queries each demonstrating a different aspects of nested queries (e.g. one to find the entity that has the minimum (or maximum) value of a certain property, another to find the entity that satisfies all of a certain criteria -- e.g. Universal quantifier, etc.)

Implementation phase report

A report is no longer required, instead a collection of files is expected which will contain the required informataion. All these files are to be submitted as attachments via e-mail -- be sure to follow e-mail communication instructions.

  1. The Relational Database Scheme Diagram
    Attach a file for the diagram showing the relational database scheme that implements the database model. Be sure to include the part of the UML class diagram that you are implementing so that I am able to grade the relational database scheme for correctness. You may also include, if necessary, documentation regarding decisions you have made with respect to surrogate and/or substitute PK's, domains, data types, etc.
  2. The MySQL Database SQL DDL to create the database.
    This script should be similar to MySQL DDL script for the ToyBank database. You may find it more manageable to separate this script into two, one to create all the tables and another to load the tables with data. Be sure to format all of your SQL statements to improve readability. NOTE: I will create your database using these scripts, so they need to work flawlessly.
  3. SQL statements and descriptions.
    In this file you are to provide the SQL statements that can provide the data necessary for each of the queries you are implementing. Each statement is to be documented (use SQL comments preceding each of the SELECT statements) by a desription of the information that it is meant to retrieve. This description is meant for people who understand the information your database stores but have no background in SQL. Again, be sure to format your SQL statements properly. NOTE: I will run each of your statements on the database that I create with the script that you provide to me (see above), so again these statements must function flawlessly.
  4. Work distribution
    A final report of contributions made by each individual member of the group. Here you are to explain specifically what each student in the group was responsible for in the entire implementation phase. You must also provide an indication of the amount of time each student spent on the assignment.

Database Resources