Home‎ > ‎CIS 54‎ > ‎

Lab 11: Project Implementation

In this lab you'll advance your project by creating a fully functional schema, populating your DB with some data and defining transactions. 

Please take a look at my reference implementation

Introduction

Your specification is a starting point. Using the feedback you get from me and your classmates update your schema to correct any problems. Also, fill in your schema will keys (surrogate and otherwise). Since you're working in MySQL Workbench you will be able to export your completed drawing directly into a schema. 

Note any changes you made to your schema.

Instantiate Your Schema

After updating your drawing you have two options: manually create your schema using CREATE statements or have MySQL do it for you. Since I hate busy work here are instructions to get MySQL workbench do it for you: 
  • Create a connection to Killgrave and test that it works.
  • Name your schema. You schema must be named according to your username. Right click on the name where it's shown in the picture below:
  • Select Database -> Forward Engineer... 
  • On the first screen select the connection settings for Killgrave. 
  • Click "Next" through all subsequent screens. 
Check for errors. If there were none you should have a schema just as you designed it. 

Insert Data

In the subsequent parts you'll make queries and transactions for your database. It'll help if you have some data to look at. Follow these guidelines for inserted data:
  1. For each table that contains an entity insert at least five rows. 
  2. For each table that contains a relationship insert at least three rows. 
Of course, you may insert as much data as you wish. Once you have all of your data inserted export your database into an SQL file. I will test your queries against the SQL file you give me.

Define Procedures

Now you will define how your schema is to be used by relating real-world activities to SQL select, insert and delete statements. Most procedures are simple. Start with those: 
  • For each entity in your database describe what it means to create, update and delete them. 
  • For each relationship in your database describe what it means to create and delete the relationship. 
CRUD operations are simple. You should also consider more complicated operations on your schema. 
  • Describe a procedure and write a query that summarizes your data in a useful way (e.g. determines an average)
  • Describe a procedure and write a query that groups entities.
Last, define a procedure that must execute in a transaction because it makes multiple, related changes to your database. Write SQL for your transaction. 

Turn In
  1. Your MySQL model file (the *.mwb file)
  2. Your exported schema in a SQL file. 
  3. The description of your procedures and SQL (preferably in a single file)
Submit your homework on canvas.

Grading

  • 10 points for updating your schema and inserting data
  • 10 points for your procedures
Subpages (1): Example Implementation
Comments