This is an individual assignment. Plagiarism in oral, written or visual presentations is the presentation of the work, idea or creation of another person, without appropriate referencing, as though it’s one’s own. Plagiarism is not acceptable and may result in charges of academic misconduct which carries a range of penalties. It is also a disciplinary offence for students to allow their work to be plagiarised by another student.
Final submission is due at 23:59 27 May 2019 Monday in Week 12. Submit via the assignment submission system on Canvas ONE pdf file named after your student number. The penalty for late submission is 3 marks per day or part day. After five days, assignments get 0 marks.
There are five questions of 30 points in total (10+4+4+7+5=30). This assignment is worth 30% of the overall assessment for ISYS1057 and ISYS1055. The assessment components and weights for the course are:
|Assignment 1||Assignment 2||Exam|
There is not a hurdle for any assessment component.
Question 1. SQL (10 points).
In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial on Canvas (the Oracle section) and other resources for syntax and useful functions.
The relational schema for the Academics database is as follows:
DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title)
AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip)
Some notes on the Academics database:
Primary keys are underlined and foreign keys are marked with *. You should download the SQL script for defining and populating the database academics.sql from Canvas (the Oracle section) and run academics.sql in your Oracle account to build the database.
Write ONE SQL query for each of questions 1.1) through to 1.10). Your query must run in Oracle SQL Developer. Each query is worth 1 point.
Notes for marking:
select fieldnum from interest, academic where interest.acnum=academic.acnum and deptnum != 126;
select S.acnum from interest S where upper(S.descrip) like '%LOGIC%' and exists (select fieldnum from interest where acnum = S.acnum and upper(descrip) not like '%LOGIC%');
Consider the Customer Order database of the ABC company, which keeps data for customers and their orders. A database of only one relation is designed, as shown below:
ABC(customerNo, customerName, phone, streetAddr, city, state, postCode, orderNo, productNo, quantity, unitPrice, salesRep)
A shop keeps data about customers, salesmen, products and orders. Consider the attributes below and their associated FDs, where empID is the employee ID for salesmen.
custNo → custName, address, credit-limit, discount productNo → price, desc
orderNo → empID, custNo, discount orderNo, productNo → quantity, empID custNo, orderNo → discount
orderNo → address
Consider the below Transaction relation;
Transaction (custNo, empID, productNo, orderNo, quantity, discount)
Question 4. ER model (7 points).
Due to your experience designing the database for the O Athletic Games, you are asked to design the ER diagram for a bigger database for the 2020 Summer Olympic games in Tokyo (https://tokyo2020.org/en/). Requirements are as follows.
● The Tokyo 2020 Olympic Games will feature 33 sports, as shown on this page: https://tokyo2020.org/en/games/sport/olympic/. Some sports organise events into disciplines (for ease of scheduling events). For example, the Hockey sport has two events “Men 12-team tournament” and “Women 12-team tournament”. But the Aquatics sport has disciplines such as Swimming and Diving, and Swimming has events like “Men 100m freestyle” and “Women 100m butterfly”.
● Each athlete competing at the Olympics has a unique athlete number and has a first name, last name, and other personal details. Each athlete must represent a country.
● Each Olympic venue has name and address and description of facilities. Each venue is designed for some sports or disciplines. For example, on this page:
https://tokyo2020.org/en/games/venue/olympic/, the Olympic Stadium is designed for the purpose of opening and closing ceremonies, athletics and football events.
● Data should be kept for event schedules. Events are scheduled to venues with date and time according to its design purpose, as on this page:
https://tokyo2020.org/en/games/schedule/olympic/. An event has many participants and they first compete in groups numbered sequentially and then some are selected to participate in the final competition event for the medals.
● Event results for all athletes, including medals, rank and result recorded should be kept in the database. As a fictitious example, for Women’s 50 metres freestyle swimming
event, Cate Campbell won the Gold medal in first place with a recorded time of 24.42 seconds; Amanda Smith is placed fourth with a recorded time of 25.50 seconds.
You shall design the database such that it can be used to
● Search for Olympians and their results.
● Search the event schedule for event details.
● List sports and their events.
You are encouraged to use the “Olympian Search Page” of the Australian Olympic Committee as a motivating example for the usage of your database:
http://olympics.com.au/olympians. The difference is that your database is only for the 2020 Tokyo Olympics and does not keep historical data for previous games.
According to the requirements, give the ER diagram for the database using the UML class symbols (as used in the lecture notes), making assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that can not be expressed in the diagram.
Question 5. ER to relational schema mapping (5 points).
Consider the below Metro Train database ER model using the UML Class symbols.
● The Employee-Driver-TicketInspector-StationMaster subclass hierarchy is partial and disjoint.
● A train line can have several runs numbered sequentially and it can be express.
● Some stations are premium stations with facilities such as toilets.
For ease of reading the ER diagram can also be downloaded at https://drive.google.com/open?id=1zlEiXCipBEJ7sdt3tZIB-8TtHTdE2iin. Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) in each relation.
For solution, connect with our online professionals.