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