代写PostgreSQL数据库作业，根据数据库Schema编写SQL语句。
Introduction
This document contains the completed ER design for Stage 2 of Assignment 1.
You must convert this design into a PostgreSQL relational schema (i.e., a
collection of create tablestatements) and submit it via the give command. When
converting from the ER design to a relational schema, you should follow the
approach given in the lecture notes on ER to Relational Mapping. You may need
to investigate and/or derive mappings for the constructs that have not been
discussed in the lecture.
Submission
Submission: Login to a CSE Linux machine such as wagner and use the give
command below to submit the assignment (note that the give command does not
work on grieg):
give cs9311 a1 a1.sql
Late Penalty: Late submissions will attract a 10% penalty for each day.
Notes: For fairness to all students in the class, no special considerations
will be given to those:
- who claim to have submitted their assignment but the assignment has not been received by the give system above (use the “classrun” command to check if your assignment has been submitted, e.g., “9311 classrun -check a1”);
- who submit their assignment a few minutes late and request to be considered as non-late submissions (please do submit your assignment early in case of your network connection problem, computer breakdown, etc);
- who claim that their assignments worked perfectly on their home computers but somehow did not work on CSE Linux machines (we will only test and mark your assignments on CSE Linux machines, and will not consider the results on your own machines. Hence, please test your assignments on CSE Linux machines before submission).
Submission Requirements
The schema that you will submit will be marked by a program (auto-marked). In
order for the program to recognise what you have done as being correct, your
SQL must adhere to the following requirements:
- all tables must have an appropriate primary key defined and all foreign keys must be identified
- use appropriate domains for each attribute (e.g., date should be defined using 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 type such as varchar(N) type or one of the supplied domain types; if appropriate, you can also create new 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-marked, you must use the names that
the auto-marker expects (i.e., the same names that are used in the provided ER
design. Please also follow the following naming conventions: - when mapping multi-valued attributes, the new table’s name is the concatenation of 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 (just in case, though I believe that I have concatenated them already), 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 a1.sql and submit it via the give command
(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 own a1.sql file.
The reason for insisting on strict conformance to the above is that your
submission will be auto-marked 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 program 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 your schema has load-time
errors, then it is 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. Manual checking is to examine specific
implementations that are difficult to be auto-checked, and it is not an
alternative to override the results from auto-marking.
Please don’t try to second-guess or improve the given ER design below. Even if
you think it can be further improved, just translate it as given. If you still
think that it is incorrect or that the information supplied is not enough to
do the mapping unambiguously, either send me an email or post a message on the
course website Forums (section: Assignment 1 (Stage 2)).
The ER Design
This ER design gives one possible data model for the OzCars application
introduced in the first stage of this assignment. The design presented here is
based on the discussions during Weeks 2 and 3 lectures.
To make the presentation clearer, the design is broken into a number of
sections.
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
Data Types
To make things easier, I have 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 purpose. The domain definitions are given at the
top of the template file.
Employee
The following diagram shows the entities, attributes and relationships that
provide the information about the employee entity for OzCars.
Details
- we use a system-generated, numeric EID as a primary key, since Salesman and Mechanic will be extensively referenced in the database.
- the database should store every employee’s name (consists of first name and last name), TFN and salary.
- both the salary and commission rate are integers, and must be larger than 0. Also refer to the assignment spec for further constraints on the commission rate (i.e., each salesman will have a negotiated commission rate ranging from 5% to max 20%).
- we assume that both first name and last name are not longer than 50 characters.
- TFN has exactly 9 digits.
- the mechanic’s license is alphanumeric, with exactly 8 characters.
- all attributes in the above ER diagram cannot have a null value.
Client and Car
The following diagram shows entities, attributes and relationships for clients
and cars.
Details (Car):
- We assume that VIN is exactly 17-character long and does not include the letters I, O, or Q (to avoid confusion with numerals 1 and 0)
- Year is between 1970-2099 inclusive.
- Model and Manufacturer are of maximum 40 characters each.
- Car license is of maximum 6 alphanumeric characters.
- Except for the list of options, all other information as specified by their corresponding attributes are compulsory.
- Use the defined domain OptionType for a list of possible options.
Details (Client): - we assume that name is no longer than 100 characters.
- we use a system-generated, numeric CID as a primary key.
- address is maximum 200 characters.
- Phone number has exactly 10 digits (mobile or landline with area code).
- if a client does not have an ABN (i.e., it is not a company), all information is compulsory except the email address. The database should check if the input email is of a proper email format (using the defined EmailType domain).
- ABN has exactly 11 digits.
- If an ABN is provided (i.e., it is a company), an optional Web address of the company is stored. A URL should start with http://…
Buy, Sell and Repair
The following diagram shows entities, attributes and relationships for the
rest of the design for OzCars.
Details:
- Any monetary amounts should be defined using the type numeric with 2 decimal digits and they should all be positive numbers. All monetary amounts will not exceed 6 integral digits.
- Description has a maximum of 250 characters.
- RepairJob Number is a number between 1 and 999 inclusive.
- Phone number has exactly 10 digits (mobile or landline with area code).
- As specified in the requirements, the clients involved in a car transaction are the owners of the car.
What To Do Now
Make sure you read the above description thoroughly, and review the notes and
exercises on ER-to-Relational mapping. Get a copy of the a1.sql template and
see what is provided there. If any aspect of this design requires further
clarification, ask a question under topic Assignment 1 (Stage 2) on the course
website Forums.
Reminder: before you submit, ensure that your schema will load without error
if used as follows on grieg:
% dropdb a1
% createdb a1
% psql a1 -f a1.sql
… will produce notices, but should have no errors …
% psql a1
… can start using the complete database …
Penalty: If we have to fix errors in your schema before it will load, you will
incur 3 (out of a total of 10) marks “penalty”.
Plagiarism
The work you submit must be your own work. Submission of work partially or
completely derived from any other person or jointly written with any other
person is not permitted. The penalties for such an offence may include
negative marks, automatic failure of the course and possibly other academic
discipline. Assignment submissions will be examined both automatically and
manually for such submissions.
Relevant scholarship authorities will be informed if students holding
scholarships are involved in an incident of plagiarism or other misconduct.
Do not provide or show your assignment work to any other person - apart from
the teaching staff of this subject. If you knowingly provide or show your
assignment work to another person for any reason, and work derived from it is
submitted you may be penalized, even if the work was submitted without your
knowledge or consent. This may apply even if your work is submitted by a third
party unknown to you.
