” COMP9311 20T2程序 写作、SQL编程设计程序 辅导2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 1/9Assignment 1Stage 2 (ER to Relational Mapping)IntroductionThis document contains the standard ER design for Stage 2 of Assignment 1. You must convert this designinto a PostgreSQL relational schema (a collection of create table statements) and submit it via theAssignments link on the course web site. In performing the conversion from the ER design to a relationalschema, you should follow the approach given in the lecture notes on ER to Relational Mapping.SubmissionSubmission : Through WebCMS3 submission link. You will submit a single SQL file called ass1.sql. Onlyone person in your group need to make a submission. As long as your group registration is correctly done inWebCMS3, 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 documentationThe main index page to PostgreSQL v. 12 DocumentationRequirements on your SubmissionThe schema you submit will be marked first by a program (auto-marked), then a tutor. In order for theprogram to recognise what youve done as being correct, your SQL must adhere to the followingrequirements:all tables must have an appropriate primary key defined; all foreign keys must be identifieduse appropriate domains for each attribute (e.g. a birthdate would be done as an SQL date, acounter would be done as an SQL integer constrained to be 0)if an attribute is a string, and no maximum length is specified, use PostgreSQLs (non-standard) texttype; otherwise, use an appropriate varchar(N) type or one of the supplied domain typesif an attribute is a boolean value, use the SQL boolean typewherever possible, not-null, unique and domain constraints must be used to enforce constraintsimplied by the ER designCOMP9311 20T2作业 写作、SQL编程设计作业 辅导derived (computed) attributes should not be included in the SQL schemawherever possible, participation constraints should be implemented using the appropriate SQLconstructsmap all of the entity class hierarchies in the ER design using the ER-style mapping (i.e. one table foreach 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:nrelationship should not be mapped in such a way that it can actually be used to form an n:mrelationship)Since the assignment is going to be auto-checked first, it is very helpful if you use the names that the automarkerexpects. 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 theentity name (e.g. entity Person is mapped to a table called People and entity Event is mapped to atable called Events)each table that represents a relationship can be given the same name as the relationship in the ERdiagrameach data attribute in the SQL schema should be given the same name as the correspondingattributes in the ER2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 2/9if an attribute in the SQL schema is derived from a relationship in the ER diagram, name it after therelationship (suitably modified to make sense, e.g. if the relationship is owns and the attributes is inthe 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 attributenameswhen mapping composite attributes, use the names of the leaf attributesif names in the ER diagram contain multiple words, concatenate them into a single word incamelCase in the SQL schemaNote: if the name you want to use clashes with a PostgreSQL keyword (e.g. user), you will need to writethe name in double-quotes (i.e. user) and in all lower-casePlace 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 requiredtables already defined. Note that you will need to add more tables, as well as filling out the attributes in thesupplied tables. Your submission must follow this format, so save a copy of this and edit it to produce yoursubmittable ass1.sql file.The reason for insisting on strict conformance to the above is that your submission will be auto-checked asfollows:we will create an initially empty database (no tables, etc.)we will load your schema into this databasewe will use a script to compare your schema with the expected schemaThe comparison will make use of the meta-data which has been added to the database by loading yourschema. Needless to say, if you schema has load-time errors, then its not going to be possible to compare itagainst the correct version. Therefore it is essential that you check that your schema can load into an initiallyempty database before you submit it.Following the instructions above is considered to be a requirement of this assignment. If you stray from theexpected schema, your submission will be marked as incorrect. Our auto-checking scripts have a littleflexibility, but not much, so dont rely on it.Please dont try to second-guess or improve the standard design below. Even if you think its completerubbish, just translate it as given. If you think that its incorrect or that the information supplied isnt enoughto do the mapping unambiguously, post a message on the course forum for clarification Also, if you want togive opinions on the standard schema use the Assignment 1 topic on the course forum. But we wontupdate the standard ER given as the spec for Stage 2.Standard ER DesignThis ER design gives one possible data model for the et.org application introduced in the first stage of thisassignment. The design here is based on the discussions on the MessageBoards, on my experience withEventBrite, and on my interpretation of the more ambiguous aspects of the requirements. This isntnecessarily the design that would be used in practice and may not even follow all of the requirements fromStage 1 precisely. It has been designed to make Stage 2 of the assignment more interesting (i.e. to give youexperience 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 willhave its attributes and class hierarchy defined exactly once. If an entity is used in a later section of thedesign (e.g. to show relationships), it will simply be shown as an unadorned entity box (and you shouldassume all of the attributes and sub/super-classes from its original definition).The development of any significant design requires assumptions. Assumptions specific to particular entitiesand 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, andthus their values need to be copied to many places in the database. Natural keys (such as email) arestrings (typically 40-60 bytes), whereas numeric keys are 4-byte integers, so there is a clear space saving inmaintaining copies of smaller keys. Using numeric keys also makes indexing and various query processingtechniques faster. One disadvantage is adding an extra attribute into each table.2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 3/9Other notational conventions in the ER diagrams:primary key attributes for entities are underlinedtotal participation in a relationship is indicated by a thick linean arrow indicates that at most 1 entity is involved in the relationshipNote that the data here is sufficient to allow the et.org site to be built. Some notions mentioned in theStage 1 requirements are related to the working of the application and do not need to be explicitly modelledhere. Actions (e.g. adding a person to a contact list) typically do not have a presence in the data modeleither, although they clearly affect the data in the database.Data TypesTo make your life simpler, Ive defined some useful data types using the create domain statement. Someof the create domain statements use standard SQL patterns for specifying constraints, while others usePostgreSQL-specific regular expressions for this prupose. The domain definitions are given at the top of thetemplate file.You shouldnt need to use many varchar(N) types in this assignment. The above types ought to besufficient for most of the fields in the database. Use them whereever you think its appropriate.You can ignore the PrivacyValue type; it is not relevant for this assignment.Users and PeopleThe following diagram shows the entities, attributes and relationships that provide the information aboutpeople on the et.org site.Comments:we use a numeric ID as a primary key, since People and Users will be extensively referenced in thedatabasefor every person in the database, we need to know their email and their given-namesinformation which every user is required to provide (as well as their person data): password and billingaddressusers can provide a name for the system to display them as; if none is supplied, et.org will form aname from the family- and given-namesnote 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 mappingnames are typically no longer than 50 chars (for the given- and family-name components) and lessthan 100 chars for full namesa users blog and website are both given as URLs2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 4/9Contact Lists and other People relationshipsThe following diagram shows entitites, attributes and relationships that deal with various groups of people onthe et.org site.Comments:users can build contact lists for easy reference to groups of peopleevery contact list must have a name; contact lists cannot be emptyall members of a contact list must be entered into the database as peoplethe owner of a contact list might want to refer to a list member by a different name to the one in theirPeople entry, and so can define a nickname for them in that listcontact lists can be used to generate invitations to events (doing this will produce a collection ofinvitations, 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.OrganisersThe following diagram shows entities, attributes and relationships relevant to event organisers on the et.orgsite.Comments:organisers are created by users for the purpose of being the front facing contact for eventsevery organiser must have a name; each organiser may also have a logo and some descriptivematerialorganiser 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 PostgreSQLdocumentation for details on bytea)the descriptive material on an organiser can be arbitrary text (most likely HTML)the system generates a page for each organiserevery organiser is required to specify a theme which gives the colour scheme for their pageEvents2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 5/9The following diagram shows entities, attributes and relationships for the events that are managed in theet.org site.The design for Events is complicated by the fact that we have both one-off and repeating events. Repeatingevents effectively define a series of one-off events recurring at regular intervals. Each instance of arepeating event shares most of the same information (e.g. title, details, privacy) as defined for the repeatingevent. To avoid duplication, the common event information is moved to an EventInfo entity and both therepeating event and event instances refer to this. An Event (either a one-off or an instance of a repeatingevent) 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 entitieshave their own identities.Comments on EventInfo:EventInfo entities define the core information for both one-off events and repeating event seriesthis 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 occurEventInfo entities are required to have a title (up to 100 chars), some details (arbitrary text), alocation and a starting timea duration may also be specified to indicate how long the event runs (implement this as a PostgreSQLinterval value; see the PostgreSQL documentation for details);events have a set of boolean flags to indicate how/whether they should be displayed: isPrivate,showLeft, showFeeshowLeft indicates whether the event page will show the number of tickets remaining to be soldshowFee indicate whether the service fee charged to the organiser will be included in the ticket priceshown to purchaserseach flag is required to be set; by default, events are private, do not show remaining tickets and donot show the service fee in the ticket priceeach event may be tagged with some categories to identify what kind of event it is; categories aresimply short text strings like music, food/wine, theatre, festival, etc.2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 6/9ticket types will also be linked to the EventInfo entity, meaning that each instance of a repeatingevent series has the same ticketing structureComments on Events:the Event entity corresponds to a specific happening at a given place and given timethe core information for an event is contained in its associated EventInfo entityan 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 eventthe Event entity serves primarily to hold timing information for the eventevents must also have a starting date and time specifiedfor display purposes only, we might compute an end-date and end-time based on the start-date/timeand the event duration; if no duration is given, we simply display the start-date/time detailsThe 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 organiserthe startTime will be set using the starting time in the associated EventInfothe endTime and endDate will be computed using the startDate and startTime and the durationspecified in the EventInfoif no duration is given, the endDate and endTime will be nullfor an event which is an instance of a repeating event series …the startDate will be calculated according to the repetition informationthe startTime will be determined from the EventInfo starting timethe endDate and endTime will be computed from the EventInfo duration, if suppliedNote that the organiser can override the startTime for a repeating event instance if one event happens tostart at a different time. This is the reason we have startTime in the Event entity, rather than simply usingthe EventInfo starting timeComments on Repeating Events subclasses:a series of similar events is specified via a RepeatingEvent entityvarious common styles of repetition are supported via the subclasses of RepeatingEventboth the lowerDate and upperDate must be supplied, and these give lower and upper bounds on thedays when instances of the repeating event can occurthe first event in the series will occur on or after the lowerDate and the last event in the series willoccur on or before the upperDatefor a daily event, we specify whether it repeats every day, or every two days, etc.; if an event repeatsevery 32 days or more, we may as well make it a monthly eventfor a weekly event, we specify which day of the week the event occurs on and how many weeksbetween recurrences (e.g. every 2nd Tuesday)there are two kinds of monthly repetition: by date or by day+weekfor monthly-by-date (e.g. on the 12th of each month), we simply give the date in the month when theevent occursfor monthly-by-day (e.g. on the third Tuesday of each month), we give which day of the week andwhich week in the month when the even occurs2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 7/9When a RepeatingEvent entity is defined, all of the Event instances determined by the repetition aregenerated. 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 somerepetition cases that cannot be represented in this scheme (e.g. the third Tuesday in every fifth month).TicketsThe 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 (ticketclasses) and ticket sales are represented as a block of tickets of a given typeeach ticket type is associated to an EventInfo entity; several ticket types will typically be associatedwith each EventInfoa ticket type is defined by a type name (e.g. First Class, Standard, Mosh Pit, etc.) and may havean accompanying description (up to 100 chars)for each ticket type we also need to record the total number available, and the maximum number oftickets that can be purchased by a user in a given puchase (if users are greedy they can simply makemultiple purchases)each ticket type has a quoted price, which is specified in a particular currency (although since saleshappen outside et.org, customers can preumably purchase using a different currency)currencies should be given using the standard ISO4217 3-letter currency codesa ticket sale indicates that some tickets of a certain type have been sold to a given person for a giveneventa ticket sale is represented by a record which has a unique ID, is linked to the specific ticket type andhas a quantity of tickets attached to itticket sales are linked to a person; the purchaser would be given the sales ID as part of the URL thatthey can use to examine their purchase, even if theyre not a user who can log in to the systemif the system wants to provide printing, it could use the sales ID plus sequencing numbers to generatea set of unique ticket numbersfor display purposes, we may also wish to compute the number of tickets remaining in each tickettype; this could be derived from the total available tickets and the quantity in the ticket salesPlacesThe following diagram shows entities, attributes and some relationships related to location information withinthe et.org site.2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 8/9Comments:Places are used for a range of location/address scenarioseach place record must have an ID and a name (text string up to 100 chars) and a country name (textstring up to 50 chars)apart from these required fields it may have any other combination of the attributes specified or notspecifiedthis allows us to represent complete postal addresses (with street address, city, state, postcode andcountry) if neededalternatively, 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 heldit is also possible to optionally provide GPS coordinates in the form used by Google maps; if these areprovided, then a map can be displayed with the event location pinpointedevery event has to be associated with a location, but the description of the location can be as vagueor detailed as the organiser wishesbecuase of their flexibility, Place records are also used to handle addresses associated with usersPage ConfigurationThe following diagram shows entities and attributes for page settings that are used to control theappearance of web pages on the et.org site.Comments:a table of page configurations is stored, where each configuration setting specifies the colour settingfor some component of the pagesome of these page colour records can be made publically available as templates and users can linkto them when setting up themes for organisers and eventsusers Can also create their own entries in this table to develop custom settingspage configurations are not treated as templates by defaultas well as having values for all colour settings, page configurations must have a name specified (evenif only used by one user)page configurations are owned by the user who created them, except for system templates which areowned by Nobodyusers can specify their own themes as templates, in which case other users can make use of them2020/7/1 COMP9311 20T2 – Assignment 1file:///Users/daiheng/Desktop/COMP9311 20T2 – Assignment 1.htm 9/9Hint: if you keep the table declaration order given in the template, there will be a forward reference to theUsers table from within the PageColours table. You can deal with this either by re-ordering the tabledeclarations or by using an alter table statement to add the foreign key constraint to the PageColourstable after you have created the Users table.What To Do NowMake sure you read the above description thoroughly, and review the notes and exercises on ER-torelationalmapping. Grab a copy of the ass1.sql template and see whats provided there. If any aspect of thisdesign requires Further clarification, ask for it under topic Assignment 1 (Stage 2) on the courseMessageBoard, 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 oferrors!)如有需要，请加QQ：99515681 或邮箱：99515681@qq.com
“
添加老师微信回复‘’官网 辅导‘’获取专业老师帮助，或点击联系老师1对1在线指导。
