Entrepreneurship Project 1

ITECH3002 – Assessment 4 – Entrepreneurship Project 1
ITECH 3002 Professionalism and Entrepreneurship
Assessment 4 – Entrepreneurship Project
Purpose
This assessment enables students to develop their report writing skills, information presentation
skills, oral communication skills, video recording and editing skills, as well as an opportunity to
research and plan an IT business concept.
Timelines and Expectations
Percentage Value of Task: 25% (60 marks).
Due: Week 11 – Sunday 3rd October 2021, 11:59 pm.
Minimum time expectation: This task will take approximately 10 – 14 hours to complete.
Learning Outcomes Assessed
The following learning outcomes are assessed by completing this assessment: K3-K4, S1-S3, A1
and A2.
In particular, this assessment covers topics from weeks 10 and 11.
Assessment Details
Background
The entrepreneurial process includes all the functions, activities and actions that are part of
perceiving opportunities and creating organisations to pursue them. Bill Gates and Steve Jobs
are examples of entrepreneurial leaders who drove the information technology revolution that
transformed the way in which we live, work, and play (Zacharakis, Bygrave & Corbett, 2016).
Moore (1986) and Bygrave (2004) developed a four-stage model depicting the entrepreneurial
process. The process begins with recognising an opportunity from a business problem and
creating a business concept that articulates it. For the first steps, the entrepreneur (1) builds a
concept to solve the business challenge; (2) fashions a story that conveys the meaning of the
new venture, and (3) prepares a presentation that tells the story and explains the concept to
potential customers, investors and partners. After testing the concept with stakeholders, the
entrepreneur may go on to develop a complete business plan.
Requirements
This is an individual task. Students will be required to:
a) undertake ideation and research then create (1) a written report and (2) a poster
presentation which champions a new IT business concept, and
b) record a video of an oral presentation discussing your business concept.
ITECH3002 – Assessment 4 – Entrepreneurship Project 2
Report (25 marks)
The written report should be in the format of a business report including the following sections:
 Operations plan – Identify the problem and discuss the business opportunity that may
address it, showing an understanding of the market and potential customers.
Also discuss (as appropriate) business details, registration details, premises, organisational
chart, management and ownership, key personnel, products and/or services, innovation,
insurance, risk, legal considerations, operations and sustainability.
 Marketing and social entrepreneurship plan – Define your marketing strategy this should
include: target market, market segmentation, unique value proposition, SWOT analysis,
marketing mix and marketing communication and social media (includes details of marketing
tools and advertising approach).
 Financial plan – Explain the business/revenue model that your will utilise to realise the
opportunity. Produce a projected budget, this should include all revenue/income and expense
projections for the first 12 months. Indicate all sample costs such as registrations, insurance,
plant and equipment, office equipment etc. (as appropriate).
 Strategic plan – Provide a mission statement. Explain your long-term strategy beyond the
first 12 months, indicate your business goals.
 Include a list of any references consulted in preparation of the report.
A high quality report will include detailed accurate descriptions of all elements of the business
plan. It will demonstrate clear and concise language and provide evidence of research from
quality authentic sources.
Poster Presentation (20 marks)
The purpose of the poster is to give a brief visual summary of key aspects of your report on a
single, large page. It is not meant to be a reproduction of the report’s entire contents.
Specific criteria for the poster includes:
 Details of your name, student ID number, title of work, lecturer/tutor name.
 Key aspects of these sections of your business plan:
o Operations plan – Identify the problem and discuss the business opportunity that may
address it, showing an understanding of the market and potential customers. Highlight
key points from the written report.
o Marketing and social entrepreneurship plan – Summarise your marketing ideas.
o Financial plan – Explain key aspects of the business/revenue model that your will
utilise to realise the opportunity. Show the projected budget for the first 12 months.
o Strategic plan – Provide a mission statement. Explain key elements of your long-term
strategy beyond 12 months, indicate your business goals.
 Include appropriate graphics, images and graphs.
 Use appropriate design/layout techniques i.e. font type and size, and colour.
 Be delivered electronically.
ITECH3002 – Assessment 4 – Entrepreneurship Project 3
The poster can be created using any appropriate software for example, MS PowerPoint, Prezi,
Publisher etc.
For assistance in preparing a poster presentation, see:
https://www2.le.ac.uk/offices/ld/resources/presentations/designing-poster/poster
For example poster presentations, see:
http://www.charithperera.net/posters
Video Presentation (15 marks)
You will be required to record a short video using Kaltura or some other appropriate video editing
software in which you will discuss your business idea. (You can use other video recording
software to record, and then upload into Kaltura via Moodle).
Each student’s video should adhere to the following:
 Include a discussion of key points of all business plan content sections
 Demonstrate appropriate communication techniques (e.g. clear voice, audio easy to hear)
 You should include appropriate visuals, such as slides from Powerpoint containing
images or text (not too much and not squished onto the slide), but should aim to include
your face for at least some portion (or all) of the recording.
 Duration of no more than 6 minutes
Academic Presentation
For assistance in report writing techniques, see:

Referencing
For advice about Referencing, see:

Submission
There are two submission links for this assessment.
Use the “Submit Assessment 4 Report and Poster” to submit your report (as either a PDF or
Word Document), and your Poster (as either a PowerPoint file, Prezi file or Microsoft Publisher
file).
Use the “Submit Video Presentation” link to embed a video that you have uploaded to Kaltura (it
gives you the opportunity to upload the video to Kaltura, before you then select “embed”. (Note
that Moodle/Kaltura have a restriction of 100 MB maximum on file uploads). Please use
appropriate CODECs.
ITECH3002 – Assessment 4 – Entrepreneurship Project 4
Feedback
This assignment will be marked by the course coordinator, lecturer and/or tutors. Feedback and
marks will be provided individually in Moodle. Marks will also be available in FDL Marks.
The marking guide is provided on the next page.
Plagiarism
Plagiarism is the presentation of the expressed thought or work of another person as though it is
one’s own without properly acknowledging that person. You must not allow other students to copy
your work and must take care to safeguard against this happening. More information about the
plagiarism policy and procedure for the university can be found at
http://federation.edu.au/students/learning-and-study/online-help-with/plagiarism
Your report and poster will be checked by Turn-it-in to confirm that the work is substantially
original work. The work you submit must not have been created for a previous assessment in
your degree.
Please refer to the Course Description for information regarding late assignments, extensions,
and special consideration. A reminder all academic regulations can be accessed via the
university’s website, see: http://federation.edu.au/staff/governance/legal/feduni-legislation
Bibliography
Bygrave, W., & Zacharakis, A. (Eds.). (2009). The portable MBA in entrepreneurship (4th ed.).
Hoboken, NJ: Wiley.
Moore, C. F. (1986). Understanding Entrepreneurial Behavior: A Definition and Model. In
Academy of Management Proceedings (Vol. 1986, No. 1, pp. 66-70). Briarcliff Manor, NY:
Academy of Management.
Zacharakis, A., Bygrave, W., & Corbett, A. (2016). Entrepreneurship (4th ed.). Hoboken, NJ:
Wiley.
ITECH3002 – Assessment 4 – Entrepreneurship Project 5
Marking Criteria/Rubric
Assessment Criteria Marks Available
Report:
Business plan content sections:
 Operations plan /6
 Marketing and social entrepreneurship plan /6
 Financial plan /4
 Strategic plan /4
Supporting Graphics and design /3
References /2
Report Total [25 marks] 0.0
Poster:
Business plan content sections:
 Operations plan – problem/business opportunity is discussed with
other key information /3
 Marketing and social entrepreneurship plan /3
 Financial plan – key aspects, budget is included /3
 Strategic plan – mission statement, key elements of strategy, goals /3
Graphics help to communicate ideas
Layout is appealing and draws attention to key items
/3
/3
References /2
Poster Total [20 marks] 0.0
Video Presentation:
 Discussion covers relevant key points from all 4 content sections /5
 Communication approach/techniques and quality of voice
 Organisation/Structure of the talk
/4
/3
 Quality of support materials (such as slides/visuals) /3
Video Presentation Total [15 marks] 0.0
Total Mark [60 marks] 0.0
Total Worth after Scaling to be out of 25 /25

Big Data Analytics

. Input is a sorted array a[1 : n] of arbitrary real numbers. The array could only be of one of the
following two types: 1) Type I: All the elements in the array are distinct; or 2) Type II: The
array has √
n copies of one element, the other elements being distinct. Present a Monte Carlo
algorithm that determines the type of the array in O(

n log n) time. Show that the output of
your algorithm will be correct with high probability.(Fact: (1 − x)
1/x ≤ 1/e for any 1 > x > 0.)
2. Show that the maximum of n given elements can be found in O(1) time using n
1+
common
CRCW PRAM processors, where  is any constant > 0.
3. Present an O(

n) time algorithm for the selection problem. You can use up to √
n CREW PRAM
processors.
4. What happens to the I/O complexity of the sorting algorithm we discussed in class if we choose
k to be cM
B
for some integer c > 1?
5. Present an efficient implementation of Djikstra’s algorithm for the single source shortest paths
problem on an out-of-core computing model with a single disk. What is the I/O complexity of
your implementation?

Securing system using IPTable firewall

Assignment 2: Securing system using IPTable firewall
Due Week 8, Worth 25%
You are required to set up, configure, and test your firewall. You need to do research and reading to be able to complete this assignment.
You need to discuss the main uses, limitations, and possible security holes of your firewall and write it in your report. You should test that following packages are installed on your machines: Telnet, MySQL, and Apache webserver. Start the services, and test that they are working prior to your experiments with the IPTables firewall. Include screenshots in your answers to show that the services are working and the output of your results to show that the requested filtering is performed.
Important:
You need to save copies of all different configurations (for each part) that you have done. (You should include your firewall rules and the results (screenshots) in the report)
Configure your firewall to:
1. Reject all incoming and outgoing ping packets.
2. Reject all incoming telnet packets and allow all outgoing telnet packets.
3. Reject all traffic coming to MySQL server.
4. Block incoming packets to the IP address of your virtual machine.
5. Allow packets inbound to port 80 (inbound) and reject packets going out (outbound) through port 80.
You then need to:
a. Discuss the advantages and disadvantages of firewalls with iptables and make suggestions to overcome the disadvantages in your report.
Submission
You should submit your report on the Moodle. The length of the report should be no longer than 10 pages.
Scoring
Question
Score
Description
Content
1 Ping service denial
5
Show that ping traffic is filtered
2 Control telnet traffic
10
Show that the telnet service is working, incoming connections are rejected and outgoing connections are allowed
3 Traffic to MySql
10
Show that the Mysql service is working, show that all traffic coming to MySql is rejected
4 IP address access control
10
Demonstration of blocking traffic connection to your IP address of your virtual machine
5 Control port 80 traffic
10
Show that apache service is working, show that inbound traffic to port 80 is allowed, but outbound traffic from port 80 is rejected
Subtotal:
45
Subtotal for content
Presentation
Experiment setup in Kali
15
Report should show the details how you have tested in parts: 1,2,3,4,5 with practical tests and/or with your gathered information
IPTable advantages and disadvantages
15
Include advantages and disadvantages of firewalls with iptables and make suggestions to overcome the disadvantages in your report.
Report is comprehensive
15
Does the report reflect an understanding of the use of the IPtables firewall?
Spelling, grammar, presentation, style, and references
10
The report’s contents are appropriately written in English, with no spelling errors or grammar issues. The report is well presented, with diagrams, headings, tables and other visual aids. The report contains appropriate references and referencing style.
Subtotal:
55
Subtotal for presentation
Total:
100

Assignment 1 – SQL Database Design and Implementation

School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 1 of 12
Assignment 1 – SQL Database Design and Implementation
Purpose and Learning Outcomes
Purpose
The purpose of the assignment is to provide students with the opportunity to apply knowledge and skills developed during the semester with particular reference to:
1. Interpretation of business rules from a case study;
2. Conceptual data modelling through the creation of an Entity Relationship(ER) model;
3. Application of DDL and DML components of SQL to:
a. create and populate a relational database; and
b. query the created relational database.
Learning Outcomes
The learning outcomes directly assessed are:
Knowledge:
K4. Design a relational database for a provided scenario utilizing tools and techniques including ER diagrams, relation models and normalization
K5. Describe relational algebra and its relationship to Structured Query Language (SQL).
Skills:
S1. Interpret entity-relationship diagrams to implement a relational database.
S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
S3. Use a query language for data manipulation.
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 2 of 12
Application of Knowledge and Skills:
A1. Design and implement a relational database using a database management system.
A2. Utilise a query language tools and techniques to obtain data and information from a database.
Timelines and Expectations
Marks: Assignment will be assessed based on a mark out of 60
The following information is a summary from your Course Description:
Percentage Value of Task: 30% of the course marks
Due: Friday, September 3rd, 2021 at 4:00pm
Minimum time expectation: 25 hours
Students are required to complete the assignment individually.
Students are expected to submit the required report and details (see below) to the submission box in their Moodle shell.
Assignment Requirements
Overview
Students are expected to read the provided system description and then interpret that description to create an ER model of that system.
They are then expected to provide an implementation of the ER model in the form of the DDL to create the required tables, attributes and relationships.
Students are then required to provide the DML to insert sufficient information into the database to answer a set of queries.
Finally, students are expected to provide the DML to interrogate the database to answer the queries posed. They should also provide proof of the running of those queries by providing images of the output obtained.
It is a requirement of this assignment that students use Postgres for the database components.
The submission must be presented in the format of a professional report. Further information is given in the Detailed
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 3 of 12
Requirements and Marking Criteria sections of this document.
Case Study – Appliance Deliveries System
An online retailer, AppliancesToYourDoor sells whitegoods e.g. refrigerators, washing machines and ovens and other electrical appliances over the phone and the World Wide Web. They need a system that handles the delivery of purchased goods to customers. They need to understand the data storage requirements for this system.
AppliancesToYourDoor have distribution warehouses in each of the states of Australia (the South Australia warehouse looks after the Northern Territory and the NSW warehouse looks after the Australian Capital Territory). The warehouse is usually located in an industrial sector on the outskirts of the state capital. For example the warehouse for Victoria is located at an address in Laverton North. Each warehouse has a unique id, one or more managers, address (two address lines, suburb and state (postcode is obtained from these), email address, phone number and other information is also kept about capacity space (in square metres), occupied space (in square metres), number of loading bays, number of access points/doors and a general description for any other interesting information. Information is additionally kept about the manager/s e.g. title, highest qualification obtained and date of that qualification.
As well as managers, the warehouses also have other employees – workers, drivers and jockeys (assistants). The workers pack vans for delivery of whitegoods and unload and store warehouse deliveries. All workers must be a licenced vehicle driver (the licence number and expiry date are kept on file) and also have a licence to drive forklifts and the forklift licence number and expiry date are also kept on file. All drivers have a driver’s licence and a record of any endorsement to drive certain vehicles. As well as their driver’s licence number and expiry date, the highest endorsement level and the endorsement expiry date are kept on file. A driver is able to drive a delivery vehicle with a Gross Vehicle Mass (GVM) that is equal to or less than his endorsement level. All jockeys have certificate qualifications that allow them to correctly install appliances. The certificate title and year awarded is kept on file for jockeys. Jockeys assist the driver in delivering and installing the delivered appliance. Sometimes jockeys also drive, but only in emergencies. Their driver’s licence number and expiry date must also be kept on file. For all employees, a record is kept of their employee id, first name, last name, contact phone, contact email, start date, termination date and as noted above, driver’s licence number and expiry date.
Each warehouse has a fleet of transport vehicles. These can range from 4.5 tonne trucks to small 1 tonne vans. All vehicles are identified by their registration number and information is also kept about their type, seating capacity, carrying capacity (the tonnage GVM already mentioned e.g. 4.5 tonnes), kerb mass/weight (the tare mass with a full tank of petrol i.e. the weight of an empty vehicle ready to be loaded), load space in cubic metres, maximum load area height, maximum load area width, maximum load area depth and status (e.g. “Being Loaded”, “Ready for Delivery”, “On Delivery”, “In Service”).
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 4 of 12
To make a purchase through AppliancesToYourDoor, it is necessary to sign up and create a customer account. Information kept about a customer includes customer id, first name, last name, phone, email and address (2 lines of address, suburb and state (postcode is obtained from these). For a delivery to be made a customer must have a paid invoice for the goods in question. An invoice is made up of a header record containing invoice id, date, customer id and payment status (T/F). Each line item of the invoice contains a product id, product description and sold price. Other information kept on the product includes product type code (e.g. “RF” for refrigerator, “WM” for washing machine, “TV” for television), unpacked and packed dimensions (height, width, depth) and packed and unpacked weight (in tonnes). After a customer has ordered and successfully paid for their item/s, their invoice is complete and one or more delivery requests are created. The delivery request is made up of one or more of the items on the invoice. For example, Mr. Smith may have paid for 2 refrigerators and a washing machine. These would be recorded as three separate line items on the invoice. He may want one refrigerator to be delivered to his mother who lives at an address in Sydney, and the other two items, refrigerator and washing machine delivered to his home address in Melbourne. The refrigerator delivery to his mother would be allocated to and handled by the Sydney warehouse and the Melbourne warehouse would be allocated and handle the delivery to his address. The delivery request has a unique id, request date, requested delivery date, actual delivery date, delivery address (2 lines of address, suburb and state (postcode is obtained from these)), contact name, contact phone number, delivery warehouse id and delivery instructions. For the purposes of obtaining postcodes, a record is kept of the postcode attached to the suburb and state. Additionally, for obtaining road distances between these locations, a record is kept of the distances between each suburb and state combination so that the road distance from the warehouse to the delivery location can be obtained and the road distance between a suburb and state combination and another suburb and state combination can also be obtained.
At regular intervals, a warehouse manager generates delivery schedules. In order to generate a delivery schedule she first selects a date to filter the outstanding requests for the warehouse she manages. The outstanding requests selected may be past due requests, including those deliveries that have been unsuccessfully attempted. She is then presented with all request details including information about the customer, the requested delivery item and information about the product including description, packed dimensions (height, width, depth) and packed weight (in tonnes). This information is presented in ascending order based on requested delivery date and distance from the delivery warehouse to the delivery address (looked up on the record of distances between each suburb and state combination). She then decides to create a schedule for a particular delivery type. At present, there are five types of deliveries – “Suburban”, “Regional Inner”, “Regional Outer”, “Regional Remote” and “Regional Extreme”. These types have a maximum distance attribute e.g. currently for “Suburban” it is 100 kilometres (km), for “Regional Inner” it is 200 km, for “Regional Outer” it is 500 km, for “Regional Remote” it is 1500 km and for “Regional Extreme” it is 2500 km. She also selects an available vehicle and assigns a driver and a jockey. She then starts selecting from the list to create the particular schedule. The total of the requests assigned to the schedule for the vehicle must meet the following rules:
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 5 of 12
 No single requested item can exceed the maximum load area height, width or depth of the vehicle;
 The total occupying area of the requested items (the sum of each item’s packed width multiplied by packed depth) must not exceed the total load area (maximum load area width multiplied by maximum load area depth) of the vehicle;
 The total distance to be travelled on the schedule must not exceed the maximum distance attribute for the type of delivery schedule selected (NB: after the first request is selected any further requests are added to the total distance travelled by looking up the from suburb and state and to suburb and state records to obtain the distance from the previous schedule item’s location (suburb and state) to the next chosen delivery location (suburb and state). For example, imagine a “Regional Inner” schedule is being developed and the requested deliveries for the date include (in distance from warehouse ascending order): refrigerator delivery to Tarneit (11.4 km road distance from Laverton North), television delivery to Taylors Lakes(19.1km), television delivery to Burwood (35.4 km), washing machine delivery to Frankston (71.2 km), refrigerator delivery to Ballarat East (97.1 km), washing machine delivery to Lal Lal (98.1 km), microwave delivery to Mt. Helen (103 km), television delivery to Miners Rest (110 km), refrigerator delivery to Seymour (115 km), two air conditioners to Smythesdale (117 km), refrigerator to Cardigan Village (118 km) and freezer to Warragul (121 km). The scheduler decides to choose the locations to maximize the total cumulative distance to at or just below the maximum distance attribute value of the Regional Inner type (200 km). She might choose for example, the refrigerator delivery to Ballarat East (97.1 km cumulative distance), the microwave to Mt Helen (97.1 + 8.4 (road distance between Ballarat East and Mt. Helen) = 105.5 km cumulative distance), the television to Miners Rest (105.5 + 23.5 = 129.0 km cumulative distance), the refrigerator to Cardigan Village (129.0 + 13.7 = 142.7 km cumulative distance), the two air conditioners to Smythesdale (142.7 + 15.3 = 158.0 km cumulative distance) and the washing machine to Lal Lal (158.0 + 37.4 = 195.4 km). Other combinations could be tried e.g. going from Mt. Helen to Lal Lal and then Smythesdale, Cardigan Village and Miners Rest to finish. Eventually a schedule is created with the delivery order recorded).
Once the schedule has been defined, the vehicle is packed according to that schedule and the driver and jockey attempt to deliver the goods. Each time a delivery is attempted a delivery attempt record is created with a date, success flag and a comment. When a delivery is successful, the attempt record is created with a ‘T’ success flag value and a comment and the actual delivery date on the delivery request is updated. Sometimes more than one attempt is made in the execution of a delivery schedule. Each time a delivery is unsuccessful, a delivery attempt record is created with the date, success flag set to ‘F’ and a comment. Sometimes it is not possible to deliver appliances so they are returned to the warehouse at the end of the schedule delivery run.
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 6 of 12
Detailed Requirements
This assignment is an individual assignment. It is a requirement of this assignment that students use Postgres for the database components.
Students should submit a report that follows the format of a business/professional report and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content:
1. An ER model of the case study system. This should conform to third normal form. Students should be aware there are a number of disjoint subtype entities and there is at least one example of a unary relationship that needs to be included. Students are able to use any drawing package to present the ER diagram but the diagram should use the Crows foot notation and conform to the standards identified in Coronel and Morris (2018). These include that entities are shown in a rectangle with name of entity in grey at top separated from two columns below with PK, FKn identifiers, where appropriate in the first column and attributes in second column. Primary key attributes to be separated from other attributes by a line across the rectangle. All entity and attribute names to be in upper case. All relationships should be labelled and identified as weak (non-identifying)/strong (identifying) ones. All connectivity, participation and cardinalities (if there are specific limits) should be shown. For an example ER diagram see Figures 4.31 and 4.35 of Coronel and Morris (2018).
2. A screen shot of the pgAdmin 4 GUI showing the creation of a database with the name ITECH2004_yourStudentID_Delivery_System.
3. The DDL statements required to create an implementation of the conceptual data model above. Students must use Postgres and their created database to create these tables, attributes and relationships. Transaction and Commit statements should be included in the DDL. They should include DROP TABLE commands where necessary and must show the correct order of creation. Appropriate constraints must be created. Students must follow the naming conventions i.e. uppercase for keywords, lowercase names for tables and attributes with an underscore between words and new line for each clause. Students should use the default schema i.e. there is no need to create one.
4. DML statements to insert sufficient data into the database to correctly answer a set of queries. Transaction and Commit statements should be included in the DML.
5. DML statements and screen shots of the correct operation of the following queries. Students should ensure that they follow conventions in their writing of SQL – uppercase for keywords, lower case for table and column names and new line for each clause:
a. Select the name details of all employees with the surname starting with “S”. Order by the surname.
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 7 of 12
b. Select the name and licence details of all employees whose licence is going to expire before the end of this year i.e. 31st December, 2021.
c. Select all details of vehicles where the calculated load capacity (name this column ‘calculated_load_capacity’) is between 1 and 3 tonnes.
d. Select the total unoccupied space across all warehouses i.e. one row with one value is returned.
e. Select a count of all products and the maximum price of those products, grouped by product type having a maximum product price greater than $1000.00.
f. Produce the rows of a delivery schedule with all request details including information about the customer, the requested delivery item and information about the product including description, packed dimensions (height, width, depth) and packed weight (in tonnes). Include the distance from the delivery warehouse to the delivery address and order by that distance.
g. Select all products, displaying product id and description and associated delivery request details (unique id, request date, requested delivery date, actual delivery date) of that product. If there are no delivery requests for that product, still display the product with NULL values in the delivery request details columns.
h. Select a list of all customers, showing all customer details, of those customers that have had more than one delivery request.
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 8 of 12
Additional Information
General Comments
The submission must be presented in a professional, clear and concise manner. If you need further system information, please use your initiative and make reasonable and logical assumptions. State your assumptions in your report. Ask your lecturer or tutor for further information.
Readings
Your text, course material and references listed on the Course Description will assist you with this assignment.
Submission
Each student should submit an electronic copy of their report via Moodle. Please refer to the Course Description for information regarding late assignments, extensions, special consideration, and plagiarism. A reminder all academic regulations can be accessed via the university’s website, see: http://federation.edu.au/staff/governance/legal/feduni-legislation/feduni-statutes-and-regulations
Students are reminded that there are supports available regarding writing, researching and general academic skills. Various sources of help are available at:
 https://federation.edu.au/current-students#Learning_and_study;
 https://studyskills.federation.edu.au/student-skills/; and
 https://federation.edu.au/library/student-resources.
Feedback
Assessment marks will be made available in fdlMarks, Feedback to individual students will be provided via Moodle or as direct feedback during your tutorial class.
Plagiarism
Plagiarism is the presentation of the expressed thought or work of another person as though it is one’s own without properly acknowledging that person. You must not allow other students to copy your work and must take care to safeguard against this happening. More information about the plagiarism policy and procedure for the university can be found at:
http://federation.edu.au/students/learning-and-study/online-help-with/plagiarism.
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 9 of 12
Any support material must be compiled from reliable sources such as the academic resources in Federation University library which might include, but not be limited to: the main library collection, library databases and the BONUS+ collection as well as any reputable online resources (you should confirm this with your tutor).
References
Coronel, C., & Morris, S. (2018). Database systems: Design, implementation & management (13th ed.). Cengage Learning.
Marking Criteria
Work will be assessed according to the details provided in the Marking Rubric on the following page.
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 10 of 12
Marking Rubric
Student Name and No
Marker
Date
Item
Description
Max. Marks
Student Mark
ER Model
This should conform to third normal form.
Must be valid disjoint subtype entities and hierarchy included.
Unary relationship/s correctly identified and included.
Crows foot notation and conformance to the standards identified in Coronel and Morris (2018). These include that entities are shown in a rectangle with name of entity in grey at top separated from two columns below with PK, FKn identifiers, where appropriate in the first column and attributes in second column. Primary key attributes to be separated from other attributes by a line across the rectangle. All entity and attribute names to be in upper case. All relationships should be labelled and identified as weak (non-identifying)/strong (identifying) ones.
All connectivity, participation and cardinalities (if there are specific limits) should be shown
All appropriate entities, attributes and relationships identified
25
Database creation image
A screen shot of the pgAdmin 4 GUI showing the creation of a database with the name ITECH2004_yourStudentID_Delivery_System.
2
DDL Statements
The DDL statements required to create an implementation of the conceptual data model above.
Students must use Postgres to create these tables, attributes and relationships.
Transaction and Commit statements should be included in the DDL. They should include DROP TABLE commands where necessary and must show the correct order of creation. Appropriate constraints must be created.
Students must follow the naming conventions i.e. uppercase for keywords, lowercase names for tables and attributes with an underscore between words and new line for each clause.
10
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 11 of 12
DML Statements to Insert Data
DML statements to insert sufficient data into the database to correctly answer a set of queries.
Transaction and Commit statements should be included in the DML
8
DML Queries and Screenshots
Make sure you follow conventions in your writing of the SQL – uppercase for keywords, lower case for table and column names and new line for each clause (deduct 2 marks if not followed):
a) Select the name and licence details of all employees with the surname starting with “S”. Order by the surname (1 mark).
b) Select the name details of all employees whose licence is going to expire before the end of this year i.e. 31st December, 2021 (1 mark).
c) Select all details of vehicles where the calculated load capacity (name this column ‘calculated_load_capacity’) is between 1 and 3 tonnes (1 marks).
d) Select the total unoccupied space across all warehouses i.e. one row with one value is returned (1 marks).
e) Select a count of all products and the maximum price of those products, grouped by product type having a maximum product price greater than $1000.00 (2 marks).
f) Produce the rows of a delivery schedule with all request details including information about the customer, the requested delivery item and information about the product including description, packed dimensions (height, width, depth) and packed weight (in tonnes). Include the distance from the delivery warehouse to the delivery address and order by that distance (3 marks).
g) Select all products, displaying product id and description and associated delivery request details (unique id, request date, requested delivery date, actual delivery date) of that product. If there are no delivery requests for that product, still display the product with NULL values in the delivery request details columns (3 marks).
h) Select a list of all customers, showing all customer details, of those customers that have had more than one delivery request (3 marks).
15
School of Engineering, Information Technology and Physical Sciences
ITECH2004 – Data Modelling Assignment 1, Sem9 2021 (202117)
__________________________________________________________________________________
CRICOS Provider No. 00103D ITECH2004_Assignment1_sem9_2021_202117_TKeogh Page 12 of 12
Report style and presentation
Report is well written using professional language and adheres to guidelines given for this assessment (Any assumptions must be clearly stated and appropriate) Deduct up to 5 if not followed
-5
Total Mark
60
Course Mark
30
Comments: