Homework 4 Instructions
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 4
CSCI3287(Database(Systems((
Homework(Four(–(Case(Study(
!
CSCI!3287!Database!Systems!Page!1!
!
!
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'
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.!!
!
