联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp2

您当前位置:首页 >> Database作业Database作业

日期:2020-07-04 10:53

2020/7/1 COMP9311 20T2 - Assignment 1

Assignment 1

Stage 2 (ER to Relational Mapping)

Introduction

This document contains the standard ER design for Stage 2 of Assignment 1. You must convert this design

into a PostgreSQL relational schema (a collection of create table statements) and submit it via the

Assignments link on the course web site. In performing the conversion from the ER design to a relational

schema, you should follow the approach given in the lecture notes on "ER to Relational Mapping".

Submission

Submission : Through WebCMS3 submission link. You will submit a single SQL file called ass1.sql. Only

one person in your group need to make a submission. As long as your group registration is correctly done in

WebCMS3, one submission will record submissions for both members.

Deadline : Friday 3 July (6pm)

Late Penalty: Within 24 hours, 15% of the assessed mark, Within 48 hours, 30% of the assessed mark,

Within 62 hours, 40% of the assessed mark. No late submission accepted after 3 days.

PostgreSQL version 12 online documentation

The main index page to PostgreSQL v. 12 Documentation

Requirements on your Submission

The schema you submit will be marked first by a program (auto-marked), then a tutor. In order for the

program to recognise what you've done as being correct, your SQL must adhere to the following

requirements:

all tables must have an appropriate primary key defined; all foreign keys must be identified

use appropriate domains for each attribute (e.g. a birthdate would be done as an SQL date, a

counter would be done as an SQL integer constrained to be ≥ 0)

if an attribute is a string, and no maximum length is specified, use PostgreSQL's (non-standard) text

type; otherwise, use an appropriate varchar(N) type or one of the supplied domain types

if an attribute is a boolean value, use the SQL boolean type

wherever possible, not-null, unique and domain constraints must be used to enforce constraints

implied by the ER design

derived (computed) attributes should not be included in the SQL schema

wherever possible, participation constraints should be implemented using the appropriate SQL

constructs

map all of the entity class hierarchies in the ER design using the ER-style mapping (i.e. one table for

each entity class).

all relationships should be mapped using the approaches described in the lecture notes; in particular,

you should avoid "over-generalising" your SQL schema relative to the ER design (e.g. a 1:n

relationship should not be mapped in such a way that it can actually be used to form an n:m

relationship)

Since the assignment is going to be auto-checked first, it is very helpful if you use the names that the automarker

expects. Please follow as much as possible the following naming conventions:

each table that represents an entity should be given a name which is the "pluralised" version of the

entity name (e.g. entity Person is mapped to a table called People and entity Event is mapped to a

table called Events)

each table that represents a relationship can be given the same name as the relationship in the ER

diagram

each data attribute in the SQL schema should be given the same name as the corresponding

attributes in the ER

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 2/9

if an attribute in the SQL schema is derived from a relationship in the ER diagram, name it after the

relationship (suitably modified to make sense, e.g. if the relationship is owns and the attributes is in

the table for the entity that is being owned, then you would changed the name to ownedBy)

when mapping multi-valued attributes, name the new table by concatenating the entity and attribute

names

when mapping composite attributes, use the names of the "leaf" attributes

if names in the ER diagram contain multiple words, concatenate them into a single word in

camelCase in the SQL schema

Note: if the name you want to use clashes with a PostgreSQL keyword (e.g. user), you will need to write

the name in double-quotes (i.e. "user") and in all lower-case

Place the schema in a file called ass1.sql and submit it via WebCMS3 (see above) before the deadline.

To give you a head-start, a template for the schema is available, which has (parts of) some of the required

tables already defined. Note that you will need to add more tables, as well as filling out the attributes in the

supplied tables. Your submission must follow this format, so save a copy of this and edit it to produce your

submittable ass1.sql file.

The reason for insisting on strict conformance to the above is that your submission will be auto-checked as

follows:

we will create an initially empty database (no tables, etc.)

we will load your schema into this database

we will use a script to compare your schema with the expected schema

The comparison will make use of the meta-data which has been added to the database by loading your

schema. Needless to say, if you schema has load-time errors, then it's not going to be possible to compare it

against the correct version. Therefore it is essential that you check that your schema can load into an initially

empty database before you submit it.

Following the instructions above is considered to be a requirement of this assignment. If you stray from the

expected schema, your submission will be marked as incorrect. Our auto-checking scripts have a little

flexibility, but not much, so don't rely on it.

Please don't try to second-guess or "improve" the standard design below. Even if you think it's complete

rubbish, just translate it as given. If you think that it's incorrect or that the information supplied isn't enough

to do the mapping unambiguously, post a message on the course forum for clarification Also, if you want to

give opinions on the standard schema use the "Assignment 1" topic on the course forum. But we won't

update the standard ER given as the spec for Stage 2.

Standard ER Design

This ER design gives one possible data model for the et.org application introduced in the first stage of this

assignment. The design here is based on the discussions on the MessageBoards, on my experience with

EventBrite, and on my interpretation of the more ambiguous aspects of the requirements. This isn't

necessarily the design that would be used in practice and may not even follow all of the requirements from

Stage 1 precisely. It has been designed to make Stage 2 of the assignment more interesting (i.e. to give you

experience with a range of modelling constructs and translation mechanisms).

To make the presentation clearer, the design is broken into a number of sections. Note that an entity will

have its attributes and class hierarchy defined exactly once. If an entity is used in a later section of the

design (e.g. to show relationships), it will simply be shown as an unadorned entity box (and you should

assume all of the attributes and sub/super-classes from its original definition).

The development of any significant design requires assumptions. Assumptions specific to particular entities

and relationships are presented below each diagram.

A general strategy used in the design is to introduce a numeric primary key called id into all major entities.

This is despite the fact that we could have made a primary key from existing attributes in many cases (e.g.

email). The reason for doing this is that primary keys typically end up as foreign keys in other tables, and

thus their values need to be copied to many places in the database. "Natural" keys (such as email) are

strings (typically 40-60 bytes), whereas numeric keys are 4-byte integers, so there is a clear space saving in

maintaining copies of smaller keys. Using numeric keys also makes indexing and various query processing

techniques faster. One disadvantage is adding an "extra" attribute into each table.

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 3/9

Other notational conventions in the ER diagrams:

primary key attributes for entities are underlined

total participation in a relationship is indicated by a thick line

an arrow indicates that at most 1 entity is involved in the relationship

Note that the data here is sufficient to allow the et.org site to be built. Some notions mentioned in the

Stage 1 requirements are related to the working of the application and do not need to be explicitly modelled

here. Actions (e.g. adding a person to a contact list) typically do not have a presence in the data model

either, although they clearly affect the data in the database.

Data Types

To make your life simpler, I've defined some useful data types using the create domain statement. Some

of the create domain statements use standard SQL patterns for specifying constraints, while others use

PostgreSQL-specific regular expressions for this prupose. The domain definitions are given at the top of the

template file.

You shouldn't need to use many varchar(N) types in this assignment. The above types ought to be

sufficient for most of the fields in the database. Use them whereever you think it's appropriate.

You can ignore the PrivacyValue type; it is not relevant for this assignment.

Users and People

The following diagram shows the entities, attributes and relationships that provide the information about

people on the et.org site.

Comments:

we use a numeric ID as a primary key, since People and Users will be extensively referenced in the

database

for every person in the database, we need to know their email and their given-names

information which every user is required to provide (as well as their person data): password and billing

address

users can provide a name for the system to display them as; if none is supplied, et.org will form a

name from the family- and given-names

note the use of isA in a circle to indicate that the Person entity has only a single sub-class (User);

remember that you must implement this (very small) class hierarchy via the ER-style mapping

names are typically no longer than 50 chars (for the given- and family-name components) and less

than 100 chars for full names

a user's blog and website are both given as URLs

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 4/9

Contact Lists and other People relationships

The following diagram shows entitites, attributes and relationships that deal with various groups of people on

the et.org site.

Comments:

users can build contact lists for easy reference to groups of people

every contact list must have a name; contact lists cannot be empty

all members of a contact list must be entered into the database as people

the owner of a contact list might want to refer to a list member by a different name to the one in their

People entry, and so can define a nickname for them in that list

contact lists can be used to generate invitations to events (doing this will produce a collection of

invitations, one for each person on the list)

we can also record attendees at events, but only for people who are already known to the system.

Organisers

The following diagram shows entities, attributes and relationships relevant to event organisers on the et.org

site.

Comments:

organisers are created by users for the purpose of being the "front facing" contact for events

every organiser must have a name; each organiser may also have a logo and some descriptive

material

organiser names may be moderately long (up to 100 characters)

logos must be JPEG images and are stored in the database as bytea values (see the PostgreSQL

documentation for details on bytea)

the descriptive material on an organiser can be arbitrary text (most likely HTML)

the system generates a page for each organiser

every organiser is required to specify a theme which gives the colour scheme for their page

Events

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 5/9

The following diagram shows entities, attributes and relationships for the events that are managed in the

et.org site.

The design for Events is complicated by the fact that we have both one-off and repeating events. Repeating

events effectively define a series of one-off events recurring at regular intervals. Each instance of a

repeating event shares most of the same information (e.g. title, details, privacy) as defined for the repeating

event. To avoid duplication, the common event information is moved to an EventInfo entity and both the

repeating event and event instances refer to this. An Event (either a one-off or an instance of a repeating

event) thus has some of its information in the Event entity and some of its information in the EventInfo entity.

Note that this is not a class hierarchy, because EventInfo entities, Event entities and RepeatingEvent entities

have their own identities.

Comments on EventInfo:

EventInfo entities define the core information for both one-off events and repeating event series

this means that all events in the series will have the same common data (e.g. title, details, location);

the only thing that may vary is the actual time that the events occur

EventInfo entities are required to have a title (up to 100 chars), some details (arbitrary text), a

location and a starting time

a duration may also be specified to indicate how long the event runs (implement this as a PostgreSQL

interval value; see the PostgreSQL documentation for details);

events have a set of boolean flags to indicate how/whether they should be displayed: isPrivate,

showLeft, showFee

showLeft indicates whether the event page will show the number of tickets remaining to be sold

showFee indicate whether the service fee charged to the organiser will be included in the ticket price

shown to purchasers

each flag is required to be set; by default, events are private, do not show remaining tickets and do

not show the service fee in the ticket price

each event may be tagged with some categories to identify what kind of event it is; categories are

simply short text strings like "music", "food/wine", "theatre", "festival", etc.

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 6/9

ticket types will also be linked to the EventInfo entity, meaning that each instance of a repeating

event series has the same ticketing structure

Comments on Events:

the Event entity corresponds to a specific happening at a given place and given time

the core information for an event is contained in its associated EventInfo entity

an Event which is an instance of a repeating event will be associated to a RepeatingEvent entity;

a one-off event will not refer to any repeating event

the Event entity serves primarily to hold timing information for the event

events must also have a starting date and time specified

for display purposes only, we might compute an end-date and end-time based on the start-date/time

and the event duration; if no duration is given, we simply display the start-date/time details

The date/time information in an Event is determined as follows:

for an event which is a genuine one-off event ...

the startDate will be provided by the organiser

the startTime will be set using the starting time in the associated EventInfo

the endTime and endDate will be computed using the startDate and startTime and the duration

specified in the EventInfo

if no duration is given, the endDate and endTime will be null

for an event which is an instance of a repeating event series ...

the startDate will be calculated according to the repetition information

the startTime will be determined from the EventInfo starting time

the endDate and endTime will be computed from the EventInfo duration, if supplied

Note that the organiser can override the startTime for a repeating event instance if one event happens to

start at a different time. This is the reason we have startTime in the Event entity, rather than simply using

the EventInfo starting time

Comments on Repeating Events subclasses:

a series of similar events is specified via a RepeatingEvent entity

various common styles of repetition are supported via the subclasses of RepeatingEvent

both the lowerDate and upperDate must be supplied, and these give lower and upper bounds on the

days when instances of the repeating event can occur

the first event in the series will occur on or after the lowerDate and the last event in the series will

occur on or before the upperDate

for a daily event, we specify whether it repeats every day, or every two days, etc.; if an event repeats

every 32 days or more, we may as well make it a monthly event

for a weekly event, we specify which day of the week the event occurs on and how many weeks

between recurrences (e.g. "every 2nd Tuesday")

there are two kinds of monthly repetition: by date or by day+week

for monthly-by-date (e.g. "on the 12th of each month"), we simply give the date in the month when the

event occurs

for monthly-by-day (e.g. "on the third Tuesday of each month"), we give which day of the week and

which week in the month when the even occurs

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 7/9

When a RepeatingEvent entity is defined, all of the Event instances determined by the repetition are

generated. If the RepeatingEvent entity is subsequently modified, the Events will be regenerated

(possibly resulting in sold tickets for any deleted events needing to be refunded). Note that there are some

repetition cases that cannot be represented in this scheme (e.g. "the third Tuesday in every fifth month").

Tickets

The following diagram shows entities, attributes and relationships on tickets in et.org.

Comments:

individual tickets are not directly represented in et.org, but rather we represent ticket types (ticket

classes) and ticket sales are represented as a block of tickets of a given type

each ticket type is associated to an EventInfo entity; several ticket types will typically be associated

with each EventInfo

a ticket type is defined by a type name (e.g. "First Class", "Standard", "Mosh Pit", etc.) and may have

an accompanying description (up to 100 chars)

for each ticket type we also need to record the total number available, and the maximum number of

tickets that can be purchased by a user in a given puchase (if users are greedy they can simply make

multiple purchases)

each ticket type has a quoted price, which is specified in a particular currency (although since sales

happen outside et.org, customers can preumably purchase using a different currency)

currencies should be given using the standard ISO4217 3-letter currency codes

a ticket sale indicates that some tickets of a certain type have been sold to a given person for a given

event

a ticket sale is represented by a record which has a unique ID, is linked to the specific ticket type and

has a quantity of tickets attached to it

ticket sales are linked to a person; the purchaser would be given the sales ID as part of the URL that

they can use to examine their purchase, even if they're not a user who can log in to the system

if the system wants to provide printing, it could use the sales ID plus sequencing numbers to generate

a set of unique ticket numbers

for display purposes, we may also wish to compute the number of tickets remaining in each ticket

type; this could be derived from the total available tickets and the quantity in the ticket sales

Places

The following diagram shows entities, attributes and some relationships related to location information within

the et.org site.

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 8/9

Comments:

Places are used for a range of location/address scenarios

each place record must have an ID and a name (text string up to 100 chars) and a country name (text

string up to 50 chars)

apart from these required fields it may have any other combination of the attributes specified or not

specified

this allows us to represent complete postal addresses (with street address, city, state, postcode and

country) if needed

alternatively, an organiser might just specify a name (e.g. "Acer Arena") and a city (e.g. "Sydney")

(and, of course, a country) to identify where the event is held

it is also possible to optionally provide GPS coordinates in the form used by Google maps; if these are

provided, then a map can be displayed with the event location pinpointed

every event has to be associated with a location, but the description of the location can be as vague

or detailed as the organiser wishes

becuase of their flexibility, Place records are also used to handle addresses associated with users

Page Configuration

The following diagram shows entities and attributes for page settings that are used to control the

appearance of web pages on the et.org site.

Comments:

a table of page configurations is stored, where each configuration setting specifies the colour setting

for some component of the page

some of these page colour records can be made publically available as templates and users can link

to them when setting up themes for organisers and events

users can also create their own entries in this table to develop custom settings

page configurations are not treated as templates by default

as well as having values for all colour settings, page configurations must have a name specified (even

if only used by one user)

page configurations are owned by the user who created them, except for system templates which are

owned by nobody

users can specify their own themes as templates, in which case other users can make use of them

2020/7/1 COMP9311 20T2 - Assignment 1

file:///Users/daiheng/Desktop/COMP9311 20T2 - Assignment 1.htm 9/9

Hint: if you keep the table declaration order given in the template, there will be a forward reference to the

Users table from within the PageColours table. You can deal with this either by re-ordering the table

declarations or by using an alter table statement to add the foreign key constraint to the PageColours

table after you have created the Users table.

What To Do Now

Make sure you read the above description thoroughly, and review the notes and exercises on ER-torelational

mapping. Grab a copy of the ass1.sql template and see what's provided there. If any aspect of this

design requires further clarification, ask for it under topic "Assignment 1 (Stage 2)" on the course

MessageBoard, or email me personally if it might give away some of the solution.

Reminder: before you submit, ensure that your schema will load without error if used as follows:

% dropdb ass1

% createdb ass1

% psql ass1 -f ass1.sql

... will produce notices, but should have no errors ...

% psql ass1

... can start using the complete database ...

If I have to fix errors in your schema before it will load, you will incur a penalty (bigger penalty if a lot of

errors!)


版权所有:留学生编程辅导网 2018 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。