Entities and Basic Information:

1. Product: Contains details like ProductID, Name, Type (e.g., dress, shirt), Brand, Price, and Stock.

2. Designer: Personal details, Brand Affiliation, and their Exclusive Collections.

3. Store: Location, Address, Opening Hours, and Staff details.

4. SalesTransaction: Transaction details, Date, Products Sold, and the corresponding store.

5. Collection: Details of specific clothing collections, often season-based or collaborationbased.

6. Brand: Brand name, Headquarters, Logo link, Founding year

7. Staff: First name, Last name, Store ID, contact information, Hire date

8. Customer: First name, Last name, E-mail, Password hash, Sign up date, Sign up address, wishlist, Previous purchases

Key Business Rules to Consider:

1. Each product belongs to a single brand but can be part of multiple collections over time.

2. Each brand has a designated Brand Manager, and this role is exclusive, meaning a staff member designated as the Brand Manager for one brand cannot hold the same position for another brand at the same time.

3. Brands can collaborate with multiple designers for exclusive collections.

4. Brands can operate in different regions or stores, and their products may vary across different store locations.

5. Each customer query/complaint is assigned to one and only one staff member.

6. Staff members are employed at specific store locations and have unique staff IDs.

7. Customers can make purchases through sales transactions at various store locations.

8. Each staff member has a designated role within the store (e.g., cashier, sales associate, manager).

9. Customers who make purchases above a certain threshold amount within a calendar year are recognized as "Premium Customers" This status entitles them to special discounts and early access to new collections. If a customer's total purchases in a year exceed a set amount (e.g., $2000), their account is tagged as "Premium" for the subsequent year.

10. Stores carry a subset of products based on their location and size.

11. Sales transactions are linked to a store and capture all the products sold in that transaction.

12. Collections are time-bound and can either be seasonal (e.g., Spring, winter, summer Collection) or collaboration-based (e.g., Designer X, Designer Y for StyleHub).

Tasks:

1. SQL script to create the complete database.
2. SQL script to insert records into the tables (each table should have at least 10 rows of data).
3. SQL queries for generating reports (minimum 2 reports from each team member should be created).