Homework Four – Case Study

Overview

This project will give you hands-on practice in working with MySQL Workbench (or similar tool) to create
a key-based, fully attributed, 3 NF data model. In this project you will design a database, draw a data
model to represent the design, then create a “physical model” of your design in the format of DDL (table
create statements.)
You may use a “pair programming” approach on this assignment. Project teams are limited to TWO
students per pair. When you turn in the assignment, the document must contain the names of both
students who worked on the project together. Each student must turn in a copy of the team’s final
document.
Objectives
1. Familiarize yourself with an unfamiliar company via the Case Study
2. Gain an understanding of the unfamiliar company’s data as presented in the Case Study
3. Use a data modeling tool to design a database to meet the needs of the client company
4. Use the data modeling tool to generate the DDL to create the database you have designed
Deliverables
1. A key-based, fully-attributed data model depicting your database design using the
information in the case study as your input. Your model should include:
• All tables with primary key attributes defined
• All attributes with data type, length, and constraints defined
• Proper table names, key names and attribute names
• All relationships between tables showing captions both ways, and proper optionality
and cardinality
2. The DDL necessary to create the database you have designed.
CSCI3287 Database Systems
Homework Four – Case Study
CSCI 3287 Database Systems Page 2
3. Documentation of any assumptions you made regarding unclear or missing requirements.
For example:
• If you are creating surrogate keys, name the key and explain why you are creating the
surrogate.
• Note the fact that you are using auto-increment for any created surrogate keys.
Submission
Use the submission link in the Project Assignment section of the Week 8 Moodle Mar 3-8 -- which is the
same place where you got this file.
Your results for this project assignment can be captured in a document (such as a .txt file, MS Word or
similar tool.) Please then save your final deliverable document as a PDF for submission. The final
deliverable document you submit for this project must consist of three sections:
The first section is a picture (screen shot) of your complete data model. The second section is text
containing all DDL generated by your data modeling software tool necessary to create the database you
have designed. You can copy the DDL as text from MySQL workbench, and paste it into your document.
The DDL must include create statements for all tables in your database (including definition of all data
columns.) Primary and foreign keys must be defined. DDL must include all constraints, including foreign
key references. Third is list (bullet points) of any assumptions you found necessary to support decisions
you made about the process and/or database design.
Grading
We will conduct “Interview Grading” to grade this project. Each student will be scheduled for a brief
(less than 10 minutes) chat with the instructor or one of the graders on April 3-4-5 or April 8-9-10. Each
student will be asked to walk through their database design and show how the database design supports
Eden’s business processes.
Case Study Scenario
For this assignment, you must read and analyze the Eden Landscaping Case Study. As you read the Case
Study, you must pay close attention to every reference to the data that is collected and used by Eden
Landscaping.
CSCI3287 Database Systems
Homework Four – Case Study
CSCI 3287 Database Systems Page 3
For this assignment, you must play the role of a consultant who has been hired by the client (Eden
Landscaping) to design and create a database for them.
The SCOPE of this assignment includes your database design ONLY for the inventory kept by Eden for
their LIVE PLANT STOCK and LANDSCAPING MATERIALS.
• The scope of this assignment specifically EXCLUDES the tracking of any items kept in
inventory for the purpose of retail sales through Eden’s retail store.
• The scope of the assignment EXCLUDES any inventory of tools and equipment used by
Eden’s crews as they go out to customers’ locations and do landscaping jobs.
Your scope for this project includes
• plants and landscaping materials that are sold to customers who come to the store,
make a purchase, and pick up and carry their purchased items
• plants and landscaping materials that are sold to customers who come to the store,
make a purchase, and have Eden deliver their purchased items to their home
• plants and landscaping materials that are sold to customers as part of a landscaping
service contract (“job”), where Eden loads up a truck and sends a crew to the
customer’s home to do a landscaping project
The case study includes in-depth descriptions of the business processes that center around the keeping
of inventory, including business activities that ADD items into inventory (such as Orders and receiving
Shipments) and the business activities that SUBTRACT items from inventory (such as sales, landscaping
jobs, and deliveries of purchased items to customers.)
Your database design must support the following business processes at Eden
• the ordering of plants and landscaping materials from suppliers
• taking physical inventory to see what’s in stock
• receiving incoming shipments of plants and landscaping materials from suppliers
• fulfilling customer orders and purchases, including landscaping jobs
Step-By-Step Instructions
• Read the entire Case Study
• As you read the case study, you must document the ENTITIES and ATTRIBUTES you observe as
you learn about how Eden keeps track of its inventory of LIVE STOCK and LANDSCAPING
MATERIALS.
CSCI3287 Database Systems
Homework Four – Case Study
CSCI 3287 Database Systems Page 4
• As you read the case study, you must consider each business process that affects inventory.
That is, consider the processes that ADD plants and materials into inventory. And, consider that
processes that REMOVE plants and materials from inventory.
• As you finish identifying entities and attributes, then you can begin to design the Inventory
Database that will become the foundation of the Inventory Tracking System that your consulting
firm will build for Eden in the next phase of this project.
• Draw a data model that includes all entities and attributes.
• Walk through the data model to ensure that it contains all entities and attributes necessary to
support he business processes described by the staff at Eden.
Your database design deliverables must include
• A Fully Attributed Logical Data Model
o all entities must be properly named,
o all relationships must be properly named (at least one way),
o all cardinality and optionality must be defined
o all primary and foreign keys defined
o all many-to-many relationships must be resolved
o entities/attributes must be in third normal form
• A Physical Data Model (SQL create statements) showing all tables, column names, data types,
lengths, along with primary and foreign key constraints.