Tintoretto’s are art dealers who specialize in sourcing old master paintings for collectors and are renowned for their expertise
CIS 2002 – Assignment 2
CIS2002 SEMESTER 1, 2018
Assignment 2 specification
Description
Marks out of
Wtg(%)
Due date
ASSIGNMENT 2
100.00
25.00
24 May 2018
1 June 2018
IMPORTANT INFORMATION
Submit the assignment electronically by the due date via the Assignment link on the study desk. If you are unable to complete the whole assignment by the due date, submit what you have done rather than nothing.
This assignment may require a significant amount of work but you should treat it as an exercise in examination preparation as well as an assignment. This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are at examination standard although the data modelling exercise is somewhat more substantial than what would be expected in an examination setting.
SECTION A
(Data Modelling) (40 marks)
You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2 located in the assignment section on Moodle) and all the examples in the lectures, study book and the tutorials use this methodology.
If you do not use the USQ methodology, you will probably be marked down.
It is perfectly acceptable if you submit neat hand-drawn ERD’s. Alternatively, you might wish to use Word. If you use a CASE or drawing tool, you must adapt the drawing to conform to the USQ methodology.
Arts College.
ACME Arts College offers on-campus night courses in a range of educational areas across three semesters per year. Students can enrol in multiple courses in a semester but they can only enrol in a course if they meet the prerequisites and haven’t passed the course before. In addition to a student’s final grade for each course, ACME also stores the student’s id number, name, and contact details (phone, address). Students can either enrol directly as a full fee paying student or on an arts scholarship. For scholarship students we record the granter of the scholarship the year and amount of the grant.
Most of ACME’s instructors are working professionals, so in addition to instructor id, name and contact details, ACME also tracks each instructor’s verified skills and certifications.
Each course has a course code, a title and a list of prerequisite courses. For example you can’t enrol in ART3001 until you’ve passed ART1001 and ART2003. Each of the courses can have multiple offerings in a year, and each offering will have an instructor, a scheduled class time and classroom and an enrolment capacity. Each course may also have a list of required supply items which can be purchased from ACME’s Campus Store.
The campus store maintains a list of all required supply items for each course. Multiple courses may use the same supply items. For each item the store records its name, price, category (painting, sculpting, photography, or drawing) and the supplier it can be purchased from. Some items can be purchased from multiple suppliers. Some suppliers provide multiple items. Each supplier’s company name and contact details are recorded.
Prepare the following:
a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.
(20 Marks)
) A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.
(15 Marks)
c) An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.
(5 Marks)
SECTION B
(Normalisation) (30 marks)
a) Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entities for 1NF, 2NF and 3NF. You must use the Finkelstein methodology as used in the study book and tutorials.
(30 marks)
CAR PURCHASE CONTRACT (contract number, contract date, details of purchase, total purchase amount, car delivery date, customer number, customer name, customer address, customer email, car stock number, car registration number, make, model, colour, body type, VIN number, engine number, manufacture date, manufacturer code, manufacturer name, manufacturer contact details)
Notes:
1. One car purchase contract is a legally binding agreement between one customer and a car offered for sale.
2. A car is identified by the car stock number and not the car registration number.
3. A car is manufactured by a car manufacturer.
4. There are no repeating groups associated with a car purchase contract.
SECTION C (SQL) (30 marks)
Below is a reproduction of the ERD for the JustLee books database. It should help you navigate the tables in the database. The database script to create the tables is located on the Moodle site under the assignment specifications. If you have run a version of the script earlier in the semester please run it again to ensure that you are using the co
ect version of the tables. The ERD diagram may not necessarily have all the fields listed so you might have to refer to the description of the table by using the DESCRIBE command in Oracle
The questions in this section are challenging. Most require a number of tables and/or nested queries. When solving each question it is best not to try and write the solution as a single activity. Instead try and write a separate query to solve each of the parts and once you understand the data and the results rewrite the query into a solution.
Each question below is worth 5 marks. For each question,
· provide formatted SQL query copy using Cu
ier New font
· capture of the output result of running your query
· screen capture of the execution of the SQL query
1) Display the category, book title, retail cost (formatted as $XXX.XX) and gift details for all books which are eligible for a gift. Order the result by category and retail cost (retail cost in descending order).
2) Display the last name, first name, order number for all customers who have placed an order for which they have not received a free gift of “BOOK LABELS” listed in the promotion table. Here we want to use the amount that they paid for each item to see is they were eligible for a gift of “BOOK LABELS”. The gifts are only available for the price paid and does not take into consideration the quantity of a book purchase.
3) Display the unique customer number, customer last name, customer first name for all customers who have bought a book that was co-authored by more than 2 authors. Order the resulting set by customer number.
4) Display the book title and the publisher name of all the books that have been written by a single author and where that book has had no sales recorded.
5) Display the publisher name, contact details and phone number for all the publisher that have had sales of more than 5 book from the list of books they publish.
6) Display the state, count of number of orders (rename the field “Number of Orders”), and the number of book categories (rename the field “Number of Categories”) for all states that have the number of book categories same as the highest number of book categories in all the states. We are looking for states that buy the most diverse categories of books.
MARKING CRITERIA
SECTION A
1) Entities – no missing entities, appropriate names, no redundant entities, etc.
2) Cardinalities and optionalities all shown and co
ect.
3) Complete list of relations, showing all applicable attributes, primary keys and foreign keys.
4) Sophistication: well-presented solution; good layout; innovative approach; co
ect diagrams/notation; solution easy to read and understand; solution comprehensive
5) SQL CREATE TABLE uses an appropriate relation, is syntactically co
ect and meets the business rules.
6) USQ methodology used throughout.
SECTION B
1) Relations – no missing relations, appropriate names, no redundant relations.
2) All primary keys present and co
ectly notated.
3) All foreign keys present and co
ectly notated.
4) All attributes present.
5) All repeating groups resolved.
6) Derived attributes indicated in
ackets.
7) All 2NF and transitive dependencies resolved.
8) All relations co
ectly notated using USQ methodology.
9) Final solution in 3NF
SECTION C
1) Five marks awarded for each co
ect SQL statement.
2) Alternative approaches to the model answer could be accepted unless they do not follow the rules requirements set out in the specification, are poorly optimised or are poorly constructed (SQL).
3) Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally constructed or may only work in the sample dataset.
Academic misconduct
Please ensure that you do not share any information about your assignment with anyone. Do not accidentally leave printouts or USB drives that contain your assignment details where someone else can access them. Make sure not to store your assignments on a computer where another student can access your assignment.
Academic misconduct is unacceptable and includes plagiarism, collusion and cheating. You will find further explanation if you follow the link on the USQ home page to "Cu
ent Students" or http:
www.usq.edu.au/li
ary
eferencing/what-is-plagiarism.
· This assignment must be all your own work
· The source of all information must be co
ectly cited and referenced
· Do not leave printouts for other people to access your work
· Do not leave USB with assignments plugged in public computers
Assignment that do not adhere to these requirements have the potential to be deemed to be the result of academic misconduct. Please make sure to read the USQ policies.
Should you have any queries regarding this assignment, please do not hesitate to email the course examiner.
Assessment policy
Please ensure to check the USQ assessment procedure http:
policy.usq.edu.au/documents/14749PL.
Late submission of Assignments
Students can apply for an extension of time, to submit an assignment, at any time up to the deadline. You will see a specific Assignment extension request submission has been set up for this purpose. Any requests for extension must be made there and follow the USQ policies (Assessment: http:
policy.usq.edu.au/documents/14749PL, Compassionate and Compelling Circumstances: http:
policy.usq.edu.au/documents/131150PL).