ITECH2004 Data Modelling: Database Components

$20 Bonus + 25% OFF

Securing Higher Grades Costing Your Pocket?
Book Your Assignment at The Lowest Price
Now!

Students Who Viewed This Also Studied

ITECH2004 Data Modelling

Tasks:

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.

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.

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 DetailedRequirements 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.

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 (nonidentifying)/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.

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. 

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.  

ITECH2004 Data Modelling

Answer in Detail


Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

48 More Pages to Come in This Document. Get access to the complete answer.

Are you looking for the best dissertation help in all of Australia? Then visit MyAssignmenthelp.com as it offers both dissertation editing services and dissertation writing services at highly competitive prices. Our writers carry out thorough research before doing your dissertation and always manage to deliver a high quality copy on time.

More ITECH2004 ITECH2004 Data Modelling: Questions & Answers

SYST17796 Java Programming Assignment

Overview: Need to create Use Cases and UML diagram for the card game UNO. The code was provided by you guys and it is under Order ID # 1757341. Attached are code files that I received from you guys. But now the ask is that using that code you guys provided, I need fully developed Use case and use te …

View Answer

SYAD 310 Systems Analysis and Design

Task:
Unit Learning Outcomes
1. Describe and apply iterative incremental systems software development processes, such as structured, object-oriented and agile approaches
2. Critically analyse and verify business and system requirements, including producing
3. Analysis and design models for an in …

View Answer

MIS605 Systems Analysis and Design

Task:
Learning outcomes
The Subject Learning Outcomes demonstrated by successful completion of the task below include:
a) Identify, critically evaluate and recommend information systems solutions for inefficiencies in business processes, procedures and work practices using data and process modell …

View Answer

Management

There will be two stages for submitting the assignment:  Stage 1 of the assignment will include Tasks 1 to 5. You should put all the outcomes of Tasks 1 to 5 into one document in a single .doc, .docx, or .pdf fil  The second and final stage of the assignment will include all tasks. Though …

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the
content removal request.

Choose Our Best Expert to Help You





Dustin Brown

I did my masters in economics from the University of Cambridge

500 – Completed Orders

Hire Me

Still in Two Minds? The Proof is in Numbers!

33845 Genuine Reviews With a Rating of 4.9/5.

Statistics

Course Work: 4 Pages, Deadline:
2 days

The solution I received is a great one and beyond my expectation. Well done team!

User ID: 8***50 Somalia

Economics

Home Work: 1 Page, Deadline:
1 day

Expert did Well in terms of reviewing the attached materials on the assignment , and he got 17/20.

User ID: 8***42 Bahrain

Healthcare

Assignment: 5 Pages, Deadline:
5 days

Excellent work without mistakes or errors. I liked the input about the subject and the number of pages to do a good work

User ID: 3***25 United States

Marketing

Assignment: 4 Pages, Deadline:
30 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Marketing

Assignment: 4 Pages, Deadline:
4 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Management

Thesis: 1 Page, Deadline:
6 days

Thanks to your efforts. it’s well researched and well written your idea. Really appreciate it.

User ID: 6***55 Kuala Lumpur, Canada

Property Law

Assignment: 1.4 Pages, Deadline:
12 hours

It is a good job but there are no references given that I can refer back to it. I hope if the expert read this, please send me the references.

User ID: 8***44 Kuala Lumpur, Malaysia

Healthcare

Programming: 2 Pages, Deadline:
13 days

The writers are very kind and they revise the work for you if you’re not completely satisfied. I would recommend them

User ID: 7***23 Kuala Lumpur, Canada

Finance

Assignment: 1 Page, Deadline:
12 days

Thank you very much for preparing the PowerPoint presentation. I appreciate you going back and doing what I requested. I used a different PowerPoint …

User ID: 8***35 Kuala Lumpur, United States

Database

Assignment: 16 Pages, Deadline:
1 day

Excellent job, I am very surprised! Well done! I,d like to work with this expert in the future!

User ID: 5***90 United Kingdom, Great Britain

English

Essay: 4 Pages, Deadline:
2 days

Excellent and wonderful work. Thank you for the effort and thank you for your cooperation, expert. It is indeed a complete and excellent file, and it …

User ID: 9***3 United Kingdom, Kuwait

Management

Home Work: 1 Page, Deadline:
1 day

Great job. Thanks for the help on this assignment. I got an A. Will use expert again.

User ID: 5***56 San Francisco, United States

Statistics

Assignment: 3 Pages, Deadline:
4 days

Well done, sent detailed explanation of each part from your side, though you missed output of first task got 74 out of 100. Thank you

User ID: 8***63 United Kingdom, Great Britain

Marketing

Assignment: 3 Pages, Deadline:
2 days

The solution is legendary. Thank you, thank you, thank you, and thank you for your cooperation and patience with me, and I am sorry for any actions. …

User ID: 9***3 United Kingdom, Kuwait

Marketing

Programming: 6 Pages, Deadline:
2 days

Thank you so much Professor Thomas Lee. i got 21/30 for my assignment, definitely will ask for you to do the part two

User ID: 2***37 Sydney, Australia

Statistics

Home Work: 1 Page, Deadline:
6 days

Second writer did a great job to complete the task… Thank must go to the second writer and my assignment help group.

User ID: 8***32 Sydney, Sri Lanka

Maths

Home Work: 1 Page, Deadline:
3 days

The assignment was done with complete details showing all the essential steps. I am very happy with the work.

User ID: 4***77 Sydney, United States

Management

Assignment: 0 Pages, Deadline:
5 days

Great work on this assignment and I will definitely be returning for additional help

User ID: 6***02 Sumter, United States

Psychology

Essay: 1 Page, Deadline:
15 hours

Perfect job and a quick job done by the writer. Thanks for the help. Grammar and sentences was great

User ID: 7***54 Sumter, Spain

Assignment

Essay: 8 Pages, Deadline:
13 days

Good work but cloud be better, hope next work is better sadasdasdasdasdasasdsadsadsadas

User ID: 5***28 United Kingdom, Great Britain

Statistics

Course Work: 4 Pages, Deadline:
2 days

The solution I received is a great one and beyond my expectation. Well done team!

User ID: 8***50 Somalia

Economics

Home Work: 1 Page, Deadline:
1 day

Expert did Well in terms of reviewing the attached materials on the assignment , and he got 17/20.

User ID: 8***42 Bahrain

Healthcare

Assignment: 5 Pages, Deadline:
5 days

Excellent work without mistakes or errors. I liked the input about the subject and the number of pages to do a good work

User ID: 3***25 United States

Marketing

Assignment: 4 Pages, Deadline:
30 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Marketing

Assignment: 4 Pages, Deadline:
4 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Management

Thesis: 1 Page, Deadline:
6 days

Thanks to your efforts. it’s well researched and well written your idea. Really appreciate it.

User ID: 6***55 Kuala Lumpur, Canada

Property Law

Assignment: 1.4 Pages, Deadline:
12 hours

It is a good job but there are no references given that I can refer back to it. I hope if the expert read this, please send me the references.

User ID: 8***44 Kuala Lumpur, Malaysia

Healthcare

Programming: 2 Pages, Deadline:
13 days

The writers are very kind and they revise the work for you if you’re not completely satisfied. I would recommend them

User ID: 7***23 Kuala Lumpur, Canada

Finance

Assignment: 1 Page, Deadline:
12 days

Thank you very much for preparing the PowerPoint presentation. I appreciate you going back and doing what I requested. I used a different PowerPoint …

User ID: 8***35 Kuala Lumpur, United States

Database

Assignment: 16 Pages, Deadline:
1 day

Excellent job, I am very surprised! Well done! I,d like to work with this expert in the future!

User ID: 5***90 United Kingdom, Great Britain

English

Essay: 4 Pages, Deadline:
2 days

Excellent and wonderful work. Thank you for the effort and thank you for your cooperation, expert. It is indeed a complete and excellent file, and it …

User ID: 9***3 United Kingdom, Kuwait

Management

Home Work: 1 Page, Deadline:
1 day

Great job. Thanks for the help on this assignment. I got an A. Will use expert again.

User ID: 5***56 San Francisco, United States

Statistics

Assignment: 3 Pages, Deadline:
4 days

Well done, sent detailed explanation of each part from your side, though you missed output of first task got 74 out of 100. Thank you

User ID: 8***63 United Kingdom, Great Britain

Marketing

Assignment: 3 Pages, Deadline:
2 days

The solution is legendary. Thank you, thank you, thank you, and thank you for your cooperation and patience with me, and I am sorry for any actions. …

User ID: 9***3 United Kingdom, Kuwait

Marketing

Programming: 6 Pages, Deadline:
2 days

Thank you so much Professor Thomas Lee. i got 21/30 for my assignment, definitely will ask for you to do the part two

User ID: 2***37 Sydney, Australia

Statistics

Home Work: 1 Page, Deadline:
6 days

Second writer did a great job to complete the task… Thank must go to the second writer and my assignment help group.

User ID: 8***32 Sydney, Sri Lanka

Maths

Home Work: 1 Page, Deadline:
3 days

The assignment was done with complete details showing all the essential steps. I am very happy with the work.

User ID: 4***77 Sydney, United States

Management

Assignment: 0 Pages, Deadline:
5 days

Great work on this assignment and I will definitely be returning for additional help

User ID: 6***02 Sumter, United States

Psychology

Essay: 1 Page, Deadline:
15 hours

Perfect job and a quick job done by the writer. Thanks for the help. Grammar and sentences was great

User ID: 7***54 Sumter, Spain

Assignment

Essay: 8 Pages, Deadline:
13 days

Good work but cloud be better, hope next work is better sadasdasdasdasdasasdsadsadsadas

User ID: 5***28 United Kingdom, Great Britain

Have any Query?

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER

Leave a Reply

Your email address will not be published. Required fields are marked *