Assessment 3, MIS201

Case Study: Plant Collection and Care Application Database

In this scenario, we will delve into the design of a database for a distinctive application tailored to plant enthusiasts and hobbyists. The application is built to provide users with a platform to curate their personal plant collections, access comprehensive care instructions, and foster connections with fellow plant lovers. While plant collection databases are less common  compared  to  traditional  business-centric  databases,  they  cater  to  a  growing community passionate about cultivating and caring for various plant species.

Application Overview

The plant collection and care application serve as a comprehensive hub for users to manage their botanical treasures, receive expert care advice, and engage with a community that shares their green-thumb interests. Users can create personalized collections, add plants to them, and document acquisition dates and notes. The application also enables users to rate and review plants based on their experiences, fostering a collaborative environment.

Key Features:

Plant Information: Users can access a database of plants,  each accompanied by detailed data  such  as  common names,  scientific names,  descriptions,  light requirements, watering instructions, and humidity preferences.

User Profiles: Individuals  interested  in  plants  can  register  and  create  profiles  with information like usernames, email addresses, locations, and specific plant-related interests.

Collections: Users  can  organize  their  plants  into  collections,  which  might  range  from categories like "Cacti" and "Orchids" to more personal themes like "Office Oasis" or "Sunny Windowsills."

Plant-Centric   Relationships: The   database    maintains   relationships   between    users, collections, and individual plants, allowing for seamless management and retrieval of data.

Acquisition Tracking: Users can record the date they acquired each plant, along with any additional notes they find pertinent.

Reviews and  Ratings: Enthusiasts  can  rate  and  write  reviews  for  plants,  contributing valuable insights and personal anecdotes for the broader community.

Search and Discovery: The  application  facilitates  searches  for  plants  based   on  care requirements, location, or common names, helping users find suitable plants to add to their collections.

Community Engagement: The platform fosters connections among users, creating a space for plant enthusiasts to share experiences, tips, and advice.

Database Design:

This section explores the database design, table structures, and the relationships that facilitate the smooth functioning of this unique plant collection and care application. This scenario underscores  the   adaptability  of  database   concepts  to   cater  to   diverse   interests   and communities beyond conventional business use cases.

Description of Tables Plants

This table stores information about various plants that users may own or be interested in. Each plant is identified by a unique PlantID. Details include the CommonName (a common name for the plant), ScientificName (the scientific name of the plant), Description (a brief description of the plant's characteristics), LightRequirements (the amount of light the plant needs), WateringInstructions (guidance on how to water the plant), and HumidityPreferences (the plant's preferred humidity level).

User

This table holds information about users of the plant database system. Each user is identified by a unique UserID. The table includes the user's Username, Email, Location (where the user is located), and Interests (topics or areas related to plants that interest the user).

Collection

This table represents collections of plants that users can create and manage. Each collection is assigned   a   unique   CollectionID.   Users   can   have   multiple   collections,   each   with   a CollectionName (the name of the collection), and a Description (a brief description of the collection's theme or purpose).

CollectionPlant

This table establishes the relationship between plants and collections. It records which plants are part of which collections. Each record is identified by a unique CollectionPlantID. It links a CollectionID (foreign key referencing the "Collections" table) to a PlantID (foreign key referencing the "Plants" table), indicating that a specific plant is in a particular collection. The table also includes an AcquisitionDate (when the plant was added to the collection) and Notes (additional information about the plant's inclusion).

Review

This table records reviews that users provide for various plants. Each review has a unique ReviewID.  It  is  associated with  a  UserID  (foreign  key referencing the  "Users"  table)  to identify the user providing the review, and a PlantID (foreign key referencing the "Plants" table) to indicate which plant the review is for. The table includes a Rating (numerical rating provided by the user), Comment (textual feedback), and ReviewDate (when the review was submitted).

Logical/Relational Model