Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
COMP3311 23T1
Assignment 1
Data Models for BeerDB
Database Systems
Last updated: Monday 27th February 10:28pm
Most recent changes are shown in red ... older changes are shown in brown.
[Assignment Spec] [Database
Design] [Examples] [Testing] [Submitting] [Fixes+Updates]
Introduction
This gives both an overview and a detailed description of the beer database for this
assignment. The overview is expressed as an ER diagram; the detail is give via an
annotated SQL schema.
ER Model of BeerDB
Most entities have an ID field as the primary key. We wouldn't normally do this at the ER
level, but none of the entities seemed to have obvious and compact primary keys.
Relationships between entities
Notes:
• every beer is brewed by some brewery
• several breweries may collaborate on one beer
• every beer is associated to a style
• the ingredients class hierarchy as a bit contrived, but would be useful if we wanted
different information for each of the different type of ingredient (e.g. colour for
grains)
• sometimes we may not know the ingredients in a beer
• we know at least the country where each brewery is located
• we did not put all the attributes for Beer in the ER design
the extra attributes are shown at the bottom of the diagram
SQL Schema for BeerDB
Notes:
• n:m relationships are implemented by a new table
• 1:n relationships are implemented by a FK attribute
• the Ingredients class hierarchy is implemented by the single-table mapping
• new types and domains aim to provide more readable table definitions
schema.sql
-- COMP3311 23T1 Assignment 1
--
-- BeerDB Schema
-- Original version: John Shepherd (Sept 2021)
-- Current version: John Shepherd (Feb 2023)
--
-- To keep the schema a little shorter, I have ignored my usual
-- convention of putting foreign key definitions at the end of
-- the table definition.
--
-- Some general naming principles:
-- max 10 chars in field names
-- all entity tables are named using plural nouns
-- for tables with unique numeric identifier, always call the field "id"
-- for cases where there's a long name and a short name for something,
-- use "name" for the short version of the name (typically for display),
-- and use "longname" for the complete version of the name (which might
-- typically be used in lists of items)
-- for foreign keys referring to an "id" field in the foreign relation,
-- use the singular-noun name of the relation as the field name
-- OR use the name of the relationship being represented
--
-- Null values:
-- for each relation, a collection of fields is identified as being
-- compulsory (i.e. without them the data isn't really usable) and
-- they are all defined as NOT NULL
-- reminder: all of the primary keys (e.g. "id") are non-NULL
-- note also that fields that are allowed to be NULL will need to be
-- handled specially whenever they are displayed e.g. in a web-based
-- interface to this schema