CpS 301 Intro to Database

Database Implementation

Estimated time to complete: 7-9 hours

GitHub Submission Repo

Overview

In this phase of the project, you implement the official design solution by creating tables and putting data into them.

  1. Your implementation must be based on the official design solution provided by the instructor.

  2. Create a new MySQL database named wso (worship service organizer).

  3. Write a script named wsoschema.sql that uses standard SQL CREATE TABLE statements to create tables to implement the official design. Define foreign keys. Also, define unique constraints to enforce uniqueness on columns which are candidate keys. This script should be compatible with both SQLite and MySQL (no MySQL-specific code); it should run without error in both databases in db-fiddle, and create tables without any data.

    You will be graded on how closely your implementation follows the official design solution. An automated utility will be used to help score your solution, so name your tables and columns carefully according to the following.

    • Table and column names should be identical to those in the diagram.
    • Foreign keys must be named the same as the corresponding primary key.
    • Name any association tables you may need to create using the verb phrase on the relationship line, with an underscore between words.

    Do not add any additional tables or columns beyond what is necessary to implement the model as specified.

  4. Use Access or MySQL Workbench to insert sample data into the tables. Use the data provided on the sample service orders. Put in enough data to generate the complete Sunday a.m. Service order (October 3, 2019), and the first few items on the Sunday pm service order (up through the Prayer/Announcements). Use 10:30 for the a.m. service time and 17:30 for the p.m. time (SQL uses 24-hour time).

    Use the information in the official project data dictionary to determine values for the code-type fields (ex. the Confirmed field).

Use the following command to export your MySQL database to a SQL script (do this from a command prompt, not powershell):

mysqldump -u root -ppassw0rd -R wso >wso.sql

Replace passw0rd in the above command with your database password (note there is no space between the -p and the password).

Review wso.sql to ensure that it contains the create table statements and insert statements that you expect.

Create a Database Diagram

In MySQL Workbench, do the following:

  1. Choose Database > Reverse Engineer. Follow the wizard steps to connect to your local database, select the wso schema, and accept the default options to import MySQL Table Objects and place the imported objects on a diagram.

  2. Arrange the tables so that they all fit on at most two pages (choose Model > Diagram Properties and Size to set the number of pages). Eliminate overlapping of tables, and work to minimize bending or crossing of relationship lines, and use File > Print Preview to ensure that tables will be visible when printed.

Submission

Submit the following to your github repo:

  • wso.sql (generated as shown above)
  • wsoschema.sql
  • report.pdf (a report with standard cover sheet containing a MySQL-generated database diagram)

In order for me to grade your sample data, your database structure must pass basic correctness checks (table and column names and foreign keys must be all correctly named and defined). The first three uploads are free. Subsequent uploads will cost you 2 points each. The final upload with report counts against your uploads.

Note: Submissions where wsoschema.sql fails to process without error on both MySQL and SQLite will be penalized 4 points.