31061/32606 Assignment Spring (“High Distinction Assignment”)
Due: Due before the lecture session 23 May. Students will make oral presentations in the lecture and lab classes of 23 May, 30th May & 6 June. The USB soft copy of the assignment should be given on the 23th May BEFORE THE LECTURE.
The code you will write to implement your HD assignment must be put in a USB device. The USB device must be clearly labeled. You can also put in your softcopy of your presentation in the USB. Your USB devices may not be returned, I will ask my tutors to check code manually. Don’t give expensive USB drives just a cheap one will do.
The following is extracted from the Subject Outline:
Assessment Item: The High Distinction Assignment
Objective(s): 1 – 5.
When: Hard and softcopy due before the lecture in 23 may 2018
For the marks for this assessment item to count toward a student’s final grade.
Students will create a database of their own choosing, subject to minimum necessary conditions, such as minimum number of tables, and minimum number of relationship types, that will be specified in a separate document. Students will also write SQL queries for their database. This assignment addresses objectives 1-5. PowerPoint overheads will need to be produced.
The High Distinction assignment is individual work. Students may NOT work in groups.
Students are required to give a satisfactory presentation about their High Distinction assignment. The presentations will be marked simply as satisfactory or unsatisfactory.. Prior to giving their respective presentations, each student will hand to the subject coordinator a brief document summarizing their chosen database project.
A hard-copy of your assignment must be submitted to the lecturer prior to you making your oral presentation. The hardcopy of your assignment will not normally be returned. Your mark will be posted to UTSOnline. If you have any issues with that mark, you should contact the subject coordinator. Also email the soft copy of your presentation and your assignment to Srinivas.Madhisetty@uts.edu.au
Late assignment submissions will incur a penalty of 1 mark for each HOUR the assignment is overdue. That is, an assignment submitted 1 minute past the due date and time will lose a mark, as would an assignment submitted 59 minutes late. An assignment submitted 60 minutes late will lose 2 marks, and so on. Thus High Distinction assignments may only be submitted up to one day late. Special consideration, for late submission, must be arranged before the due date with the subject coordinator.
A very high standard of work is expected from High Distinction students. Students are warned that a token effort on this assignment will attract zero marks. The coordinator anticipates that any reasonable assignment submission will not score a mark that is greater than zero but less than half the available marks.
Expected numbers of hours to do the assignment
Students are expected to spend approximately 16-25 hours on this assignment.
Academic Misconduct
Students are reminded of the principles laid down in the Faculty’s Statement of Academic Integrity – Good Practice and Ethics in Informal Assessment found at <start.it.uts.edu.au/w/doc/student/integrity.html>, and in particular that:
- Any collaboration with another person on this assignment should be limited to those described in the “Acceptable Behaviour” section of the Statement of Academic Integrity. Similarly, any group work should be the result of collaboration only within the group.
- The Faculty penalty for proven and serial misconduct of this nature is zero marks for the Subject. For more information go to; <start.it.uts.edu.au/w/doc/student/integrity.html>.
Softcopy File Naming
The two files submitted as attachments should be named as follows (1) family name, (2) other name (both family and other names as shown on your student card), and (3) the name you have given your project. You should give your project a name that is an accurate reflection of the content or purpose of your database. For example, Madhisetty.Srinivas 013232322
Include your student number in the file name, or in any part of the softcopy submissions. Your student number should also be provided in the hardcopy submission (where it may be added in legible handwriting).
Submission Requirements
The softcopy submission should contain the following:
- The first page should be the ERD slide from the PowerPoint Presentation – taking up the whole page. The page should also bear your name (as shown on your student card), student number, and the name of the project (these may be handwritten).
- The complete PowerPoint Presentation as a “handout” (including the ERD slide that also forms page 1 of the hardcopy submission). Six slides to a page is acceptable, provided all writing on the overheads is readable with 6 slides to a page. Otherwise, students should submit 2 slides to a page.
- A complete listing of the .txt file containing the SQL for the project.
Every page of the softcopy submission should contain your name AND YOUR STUDENT NUMBER in the document header. USE YOUR NAME AS SHOWN ON YOUR STUDENT CARD. We have no way of knowing your other adopted names.
Marks and Return
Marks for this assignment will be posted to UTSOnline. The USB will not be returned. Students will receive feedback as part of their oral presentation. Students seeking more feedback should contact the subject coordinator after marks have been posted.
Assignment-Related Discussion Group(s ) in UTSOnline and Assignment Errata
There may be errors and ambiguities in this assignment specification. If so, corrections/clarifications will be posted to the UTSOnline You are expected to incorporate these corrections/clarifications into your submission. (No corrections/clarifications will require substantially new work from students.) The assignment specification for this assignment will be frozen one week prior to the submission date. (NB. Students granted extensions should consult the coordinator for corrections/clarifications up to 1 week prior to their extended submission date.)
High Distinction Assignment Specification
Build a database. You decide what database you’d like to build. However, your database must satisfy the “minimal essential” conditions given below, otherwise you will be penalized heavily in the marking.
Task 0: Choice of Database Project
Your choice of database should demonstrate some initiative, and be something that is largely your own idea. It must not resemble closely any database you found in the textbook, or any other book, and it must not resemble closely any database built by a student in a previous semester. After you have chosen a project, you should check with the subject coordinator that the project is not too similar to any database in a book or built by a former student. To check with the supervisor:
- Face-to-face communication is preferred, but you may attempt to check with the subject coordinator via email. If the subject coordinator’s answer to the email needs to be more complicated than a simple approval or disapproval, then you will need to meet face-to-face with the subject coordinator.
- Whether face-to-face or via email, you should show the coordinator your first attempt at the ERD. This may be hand drawn, and may contain errors − it just needs to sufficient to convey to the coordinator what it is you intend to build.
Unless you have special permission from the subject coordinator (see below), your database must be inspired by a real web site. Imaginary data for that web site is acceptable, but real data from that web site is preferred, where possible. You must provide the URL for the web site that inspired your project.
Special Permissions: Some students may have a particular application in mind that is not on the world wide web. For example, they may want to build something relevant to where they work. That is acceptable in principle, but students must have the explicit permission of the subject coordinator, in writing (i.e. email). Students are reminded that their assignment submissions are public documents that will be placed in UTSOnline for classmates, and future students to look at. Therefore, the databases for which students seek special permission should not be populated with commercially sensitive data.
The Database Design and the ERD
Provide a well designed ERD for this problem, using the notation from the textbook (do NOT provide something in Microsoft Access database diagram format). Indicate the primary key (with an underline) and give the complete attribute list for each entity (i.e. all columns in the given table should occur somewhere in the ERD). Indicate a foreign key with an asterisk. If foreign keys are composite, don’t worry about bracketing the attributes in the ERD. Include all attributes for each entity in the ERD.
Your diagram may be generated with any drawing tool you like, but the ERD must be included in your PowerPoint document (perhaps pasted in as an image). Hand drawn diagrams may be acceptable, provided they are legible (but where the diagrams are illegible, answers will be assumed wrong). Hand drawn diagrams need to be scanned (or digitally photographed) and submitted as part of the PowerPoint document.
In the ERD, include the full primary key of any weak entity. In the ERD, do NOT include in the weak entity any non-primary key attribute which is also in the entity upon which this weak entity depends.
The database ERD must contain at least: (1) a single one-to-many (or zero to many) relationship, and (2) a single many-to-many relationship, broken into two one-to-many relationships. Failure to meet this criterion will attract zero marks for the entire assignment. However, a database design that just meets this minimal requirement (and meets all other requirements) is likely to score only half the available marks. The complete ERD should fit on a single A4 page, with all writing in the ERD in 12 point.
The SQL
In PostgreSQL, implement your database design. Populate your database with suitable data for testing the SQL queries you will provide in a subsequent task. Also, provide enough data so that the table rows demonstrate the relationships. For example, if there is a 1:M relationship between 2 tables, ensure that there are at least two records in the M-side table that are related to a respective record in the 1-side table. If the relationship is 0:M, then the appropriate table should contain a row that does correspond to any row in the other table.
When submitting the assignment, provide all your SQL, including all the “insert” commands for placing your data into your database.
Start the SQL with “DROP” commands for each of your tables, so you can run your script more than once. Use CREATE statements to create your tables. Use “Constraint” to define primary and foreign keys.
Domain Integrity: Where appropriate, add checks on your data.
Referential Integrity (1): Your database should check foreign keys. (2) Your database should also enforce suitable deletion actions.
When you paste your SQL code into the submission document, use the “Courier New” font. It is a fixed width font, and will preserve your indenting. A variable width font like “Times Roman” will not preserve your indenting.
Queries
Write the following queries for your database:1. A simple query of a single table.2. A query which uses the words “natural join”.3. The cross product equivalent to the “natural join” query above.4. A query involving a “Group by”, perhaps also with a “HAVING”.5. A query which uses a sub query.6. A cross product which cannot be implemented using the words “natural join” (e.g. self join) Set out each of these queries as they were set out for the SQL lab exams. That is: 1. An English language description of what the SQL query does.2. The output generated by it (in Courier New font or another fixed width font). After all six queries “questions” as specified above, provide the “answers” to all six queries.
The SQL .txt File: Minimum Essential Conditions
Your database must satisfy the following “minimal essential” conditions, otherwise you will be penalized heavily in the marking, and perhaps receive zero marks.
- Your SQL in the .txt file must successfully build a database in PostgreSQL. A SQL submission that produces an error when run within PostgreSQL will attract zero marks for the entire assignment. If you can’t get some aspect of the SQL working, leave it out of your project submission.
- The file containing your SQL should begin with a comment header block (i.e. lines beginning with two dashes). The first line of the header block should contain (“High Distinction”), Spring 2015”, followed by lines providing your name and email address (but not your student number). The header block should then contain, in English, the nature of your database application. Do NOT use technical database language in this section. Write something like the description of the NASA database. You must provide the URL for the web site that inspired your project in this heard block.
- Your SQL should be laid out so it is easy to read. When writing all your SQL, you should approximately follow the indentation style used in the files provided as part of the lab exercises. When you paste your SQL commands into the PowerPoint document, use a fixed-width like Courier to preserve indentation.
- Domain Integrity: You must made good use of “check” statements.
- Referential Integrity: Your SQL should contain all reasonable “ON DELETE” actions, if appropriate both “ON DELETE RESTRICT” and “ON DELETE CASCADE”.
- All your CREATE statements should precede all your INSERT statements.
Students are warned that a token effort (i.e. seriously breaches the “minimal essential” conditions) of assignment will attract zero marks.
Students are free to produce a database that goes well beyond the requirements of the minimum essential conditions, if they wish to do so for their own satisfaction. However, students are warned that databases that go well beyond the minimum essential conditions do not attract extra marks.
PowerPoint Presentation: Minimum Essential Conditions
The required PowerPoint is a subset of what was required for the Distinction assignment:
- The first slide should be a title slide, as in the Distinction assignment. The title page should include the name of the student who produced the slides (and in the hardcopy only, his/her student number, which may be hand written).
- The next slide (or set of slides) should describe the real-world domain modeled by this database. Be succinct. One slide could be enough. The audience just needs to know enough about the domain to understand the ERD.
- The next slide should show the complete ERD for this database.
- The next slide should illustrate a single one-to-many relationship in the database (other than a one-to-many relationship that occurs as part of a many-to-many relationship). As in the Distinction PowerPoint presentation, this slide should show both the relationship as shown in the ERD and its realisations in tables, with some example values in the tables.
- The next slide should illustrate a single many-to-many relationship in the database. As in the Distinction PowerPoint presentation, this slide should show both the relationship as shown in the ERD and its realisations in tables, with some example values in the tables.
- The next set of slides should show the various queries you are required to write:
- A simple query of a single table.
- A query which uses the words “natural join”.
- The cross product equivalent to the “natural join” query above.
- A query involving a “Group by”, perhaps also with a “HAVING”.
- A query which uses a sub query.
- A cross product which cannot be implemented using the words “natural join” (e.g. self join)
For each query, provide (ideally, but not necessarily all on one slide) (1) an English
description of what the query is supposed to find, (2) the actual query, and (3) the
output generated by each of the queries (design queries that are economical in what
they return).
- The next slide (or set of slides) should illustrate the use of CHECK statements from your SQL. Show a variety of your CHECK statements.
- The next slide (or set of slides) should illustrate the use of action statements in your SQL. Two examples in the PowerPoint are sufficient. If your database contains both “ON DELETE RESTRICT” and “ON DELETE CASCADE”, give an example of each.
- The next slide (or set of slides) should illustrate the use of a view in your SQL.