Homework Four Case Study Solution

Overview




This project is worth 180 points (out of 1000) toward your final grade. It is due on Tuesday, April 2, at 11:59 p.m. After that time, no late work will be accepted. Your submission should be a PDF document submitted as a file via the link found in the Project Assignment section of the Week 8 Moodle Mar 3-8 which is the same place where you got this file.




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




Familiarize yourself with an unfamiliar company via the Case Study



Gain an understanding of the unfamiliar company’s data as presented in the Case Study



Use a data modeling tool to design a database to meet the needs of the client company



Use the data modeling tool to generate the DDL to create the database you have designed



Deliverables




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



The DDL necessary to create the database you have designed.






CSCI3287 Database Systems













Homework Four – Case Study







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







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







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
all entities must be properly named,



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

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.