Design A Relational Database For A Provided Scenario

Design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation.

The purpose of this task is to develop students' skills in designing and implementing a relational database for a given case study involving Alcool Holdings, an online retail beverages sales company that manages inventory, manufacturers, wholesalers, customers, and sales. The database should facilitate various reports, such as a list of customers by suburb and inventory by price or category, and incorporate discount features based on inventory type, alcohol content, VIP status, and bulk orders. The initial scenario includes unnormalized entities with many-to-many relationships that need to be resolved to ensure proper database design.

Your submission must include an ER diagram showing all entity names, attribute names, primary keys, foreign keys, relationships, cardinality, and participation. All many-to-many relationships must be resolved. Additionally, you should provide a discussion on normalization, detailing the normal form of each entity, how normalization was achieved, and justifications for the level of normalization, ideally in 3NF.

A list of relationships must specify all table names, attributes, primary and foreign keys, following naming conventions (entity/table names in capitals, attributes in proper nouns, primary keys underlined, foreign keys italicized). A detailed database schema should also be included, describing the purpose and data type of each attribute.

The report must be well-presented, properly referenced using APA style, and adhere to academic writing standards. Submission is via the Moodle assessment portal, and the grading criteria include presentation, ER diagram accuracy, normalization level, and schema correctness.

Paper For Above instruction

Design a relational database for a provided scenario utilising tools and techniques including ER diagrams relation models and normalisation

Introduction

The development of a relational database for Alcool Holdings presents a complex design challenge, requiring careful consideration of entities, relationships, and normalization procedures. This paper outlines the process of constructing an ER diagram, resolving many-to-many relationships, and normalizing the schema to achieve third normal form (3NF).

Understanding the Scenario

Alcool Holdings is an online retailer specializing in wines and beers. The company needs a robust database to track its inventory, which includes attributes like inventory ID, title, type, price, stock levels, wholesaler information, and alcohol content. Additional data include details about wineries and breweries, their awards, and contact information. The system must also accommodate customer data, including discounts, and facilitate report generation on customer locations and inventory categorization.

Entities and Attributes

Key entities identified include Inventory, Wine, Beer, Winery, Brewery, Award, Wholesaler, and Customer. Each entity has specific attributes:

  • Inventory: Inventory ID, Title, Type, Retail Price, In Stock, Wholesaler, Alcohol Content
  • Wine: Year of Production, Wineries, Wholesalers, Body, Style, Tannin, Markup, Discount
  • Beer: Year of Production, Breweries, Wholesalers, Aroma, Appearance, Flavour, Markup, Discount
  • Winery: Name, Address, Contact, Phone, Fax, Email, Year of Origin, Specialization, Awards
  • Brewery: Similar to Winery
  • Award: Type, Name, Year
  • Wholesaler: Name, Address, Contact, Phone, Fax, Email
  • Customer: Name, Address, Phone, Email, Discount

ER Diagram Construction

The ER diagram visually represents entities, attributes, and relationships, with primary keys underlined and foreign keys italicized. Relationships like 'manufactures', 'supplies', and 'awards' connect entities, with many-to-many relationships resolved through associative entities such as 'Inventory_Warehouse' and 'Product_Manufacturer'. For example, an inventory item may have multiple wineries or breweries, necessitating associative entities to resolve N:M relationships.

Normalization Process

Entities are examined to achieve 3NF by ensuring non-key attributes depend solely on primary keys. For instance, in the 'Wine' entity, attributes like 'Body' and 'Tannin' depend directly on the primary key. Redundant data, like winery addresses stored multiple times, are placed into separate entities. This normalization reduces data anomalies and improves data integrity.

Schema Translation and Relationships

The relational schema lists all tables with their primary keys (underlined) and foreign keys (italicized). For example:

INVENTORY (Inventory_ID, Title, Type, Retail_Price, In_Stock, Wholesaler_ID, Alcohol_Content)


WINE (Inventory_ID, Year_Produced, Body, Style, Tannin, Markup, Discount)


BREW (Inventory_ID, Year_Produced, Aroma, Appearance, Flavour, Markup, Discount)


WINERY (Winery_ID, Name, Address, Contact, Phone, Fax, Email, Year_Of_Origin, Specialization, Awards_ID)


BREWERY (Brewery_ID, similar structure)


AWARD (Award_ID, Type, Name, Year)


WHOLESALER (Wholesaler_ID, Name, Address, Contact, Phone, Fax, Email)


CUSTOMER (Customer_ID, Name, Address, Phone, Email, Discount)

Foreign keys link related entities, such as 'Wholesaler_ID' in INVENTORY referencing WHOLESALER.

Conclusion

The designed schema ensures data integrity, eliminates redundancy, and supports efficient reporting and querying. Normalization to 3NF optimizes the database for future scalability and maintenance, aligning with best practices outlined in database design literature.

References

  • Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments