fb
$20 Bonus + 25% OFF
$20 Bonus + 25% OFF
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Stuck on Your Question?

Get 24x7 live help from our Top Tutors. All subjects covered.

loader
250 words

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Students Who Viewed This Also Studied

16 Pages
9938 Big Data Program Analysis Report

Question: On successful completion of this assessment, students will be able to: 2. Design data collection strategies that provide relevant information for business decision-making.3. Effectively co ...

Course

9938

Type

Programming

Subject

Database

University

University Of Canberra

16 Pages
Business Management

Overview of business case: Drive2Impress is a small business that sells cars in Canberra. Drive2Impress can obtain cars from several different suppliers. Drive2Impress keeps its cars in several s ...

Type

Programming

Subject

Database

1 Page
ORACLE database

Develop a database design (both conceptual and logical) for an appropriate business situation of your choice, and then implement and subsequently query an ORACLE database that is derived from your dat ...

Type

Programming

Subject

Database

10 Pages
200503 Construction Information Systems

Assignment 4 deals with a preparation of relational database for different type of trades used in the building industry (minimum 25 entries per student). Assessment schedules for all components will b ...

Course

200503

Type

Programming

Subject

Database

University

Western Sydney University

Season

Spring

KC7013 Database Modelling

Question

Answered

Question:

You are expected to produce a word-processed answer to this assignment. Please use Arial font and a font size of 12. For SQL code and output, you can use courier new font, which preserves SQL format and layout.

You are required to use the Harvard Style of referencing and citation. The “Cite them right” guide is recommended for referencing and citation (Pears and Shields, 2008) which should be followed throughout your answer especially Part 3. A good alternative is Northumbria (2018). Please do not include references to lecture notes.

This assignment covers the following learning outcomes for the module:

Conceptual data modelling, relational database design and implementation in SQL & PL/SQL, and object-based databases.

Design and Implementation of a data warehouse using Oracle database system.

You are advised to read the guidance for students regarding assessment policies. They are available online here.

Where coursework is submitted late without approval, after the published hand-in deadline, the following penalties will apply.

These provisions apply to all assessments, including those assessed on a Pass/Fail basis.

The full policy can be found here.

Students must retain an electronic copy of this assignment (including ALL appendices) and it must be made available within 24hours of them requesting it be submitted.

Academic Misconduct

The Assessment Regulations for Taught Awards (ARTA) contain the Regulations and procedures applying to cheating, plagiarism and other forms of academic misconduct.

 

The full policy is available at here

You are reminded that plagiarism, collusion and other forms of academic misconduct as referred to in the Academic Misconduct procedure of the assessment regulations, which are taken very seriously. Assignments in which evidence of plagiarism or other forms of academic misconduct is found may receive a mark of zero.

Assessment Background and Scenario

This assessment is based on two scenarios as follows:

a)The scenario ‘Academic Information System (AiS)” of a fictitious university called University of Gharnata.  The university wants to develop an information system to support its academic activities. The details of this scenario are provided in Appendix 1.

b)The Sales History (SH) Data Warehouse scenario. SH is a sample database schema provided by Oracle, which has been extensively used in the Oracle’s Data Warehousing Guide (Lane, 2013). The details of this scenario are provided

Assignment Questions

Part 1: Implementing object-relational database for AiS (50 Marks)

This part is based on the ‘AiS’ scenario as described in Appendix 1.

(A) Using object-oriented / extended entity relationship modelling, produce a conceptual and logical database model for AiS.

You have the option to produce a fresh conceptual and logical database model using object-oriented or extended entity relationship modelling or incorporate advanced entity modelling concepts (e.g., aggregation & composition, inheritance or generalisation / specialisation/, etc) into your entity relationship model for AiS from your assignment 1.

(B) Create an object-based database for AiS using object-relational (O-R) features of Oracle 10g//11g/12c based on the conceptual and logical database model from (A) above.

Note that all relationships (e.g., one-to-one, one-to-many, many-to-many) must be bi-directional. Moreover, each to-many side of a relationship (e.g., in case of many-to-one and many-to-many) should be implemented using nested tables. In addition, your implementation should allow all objects to be shareable (i.e. all relationships should be REF based). Oracle (2005a) provides a more detailed documentation on Oracle object-relational features.

(C) Populate the O-R version of your database using data from relational version of the AiS database you developed in Assignment 1.

Using PL/SQL and/or SQL, populate your O-R database by querying the relational tables you implemented earlier during your Assignment 1 solution. This task can be seen to contain the following sub-tasks:

(i) Creating / inserting objects in your object tables.

(ii) Populating one-to-many, many-to-one, and many-to-many relationships among the objects created in sub-task (i).

(D) Query the O-R version of your AiS database

For this part, you must use PL/SQL procedures (2 procedures, each worth 4 marks). Answer the following questions after populating the object-relational database:

1)Display names of students, details of the course they study, details of the module they have studied and their marks for all postgraduate students.

2)Display details of all people of ‘AiS’ (students and academic staff), e.g., their names, their home addresses and name of the department where they work or study.

NOTE the following important points for Part 1 (A to D):

·You must submit all the SQL and PL/SQL code used for creating, populating and querying the O-R database.

·You must submit the output of running your code (e.g., SPOOL files).

(E)Compare and contrast your relational and object-relational versions of the AiS database. You should highlight the advantages and disadvantages of both versions. Comment on which version will best suit the AiS database.

(A)Study the index definitions in sh_idx.sql. Discuss in detail (using cost-based analysis) why these indexes (at least two of them) are useful for answering queries over the SH2 and DWn versions of the database. You should not run the sh_idx.sql script at all.                                                                  

(B)Identify two new indexes and justify why they could be useful. Write the SQL code for creating these indexes under your DWn user. Give example queries with cost-based analysis for both DWn (which will have the new indexes) and SH2 users (which will not have any of your new indexes).

(C)Given the materialized views (MVs) defined in sh_cremv.sql, discuss in detail why these MVs are useful for users of the SH database. You should provide detailed examples (cost based analysis, e.g., using Explain Plan for running sample queries on both SH2 and DWn) to illustrate your answer. You should not run the sh_idx.sql script at all.

 

(D)Identify two other MVs based on the base tables in the SH schema under your DWn user and justify why they would be useful. Write the SQL code for creating these MVs.

(E)Prior to the introduction of the aggregation function ROLLUP, there was no possibility to express an aggregation over different levels within one SQL statement without using the set operation UNION ALL. Every different aggregation level needed its own SQL aggregation expression, operating on the exact same data set n times, once for each of the n different aggregation levels. With the introduction of ROLLUP in the recent editions, Oracle provided a single SQL command for handling the aggregation over different levels within one single SQL statement, not only improving the runtime of this operation but also reducing the number of internal operations necessary and reducing the workload on the system.

i.Using ROLLUP, write an SQL query over the SH schema under your DWn user involving one fact table (SALES or COSTS) and at least two dimension tables. Provide reasons why your query may be useful for users of the SH data warehouse.

ii.Using set operation UNION ALL (and not ROLLUP), write an SQL query that produces the same result as the query in (a) above.

iii.Using EXPLAIN PLAN, provide a detailed discussion analysing costs of evaluating the above queries (i.e. with and without ROLLUP).

KC7013 Database 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.

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

MyAssignmenthelp.com is the best essay writing service provider because of its efficient team of essay writers based in USA. Each member of this team is capable enough of providing highest quality custom essay help in more than 100 subjects. Despite being a top-quality help provider, we ensure our prices remain affordable so that students who search write my essay at cheap price find our services useful.

More KC7013 KC7013 Database Modelling: Questions & Answers

Q
icon

We aren't endorsed by this University

9938 Big Data Program Analysis Report

Question: On successful completion of this assessment, students will be able to: 2. Design data collection strategies that provide relevant information for business decision-making.3. Effectively communicate an understanding the various types of big data and the function of each4. Interpret big da ...

View Answer
Q

Business Management

Overview of business case: Drive2Impress is a small business that sells cars in Canberra. Drive2Impress can obtain cars from several different suppliers. Drive2Impress keeps its cars in several showrooms across the city. At this stage customers cannot view the available cars online so to order ...

View Answer
Q

ORACLE database

Develop a database design (both conceptual and logical) for an appropriate business situation of your choice, and then implement and subsequently query an ORACLE database that is derived from your database design ...

View Answer
Q
icon

We aren't endorsed by this University

200503 Construction Information Systems

Assignment 4 deals with a preparation of relational database for different type of trades used in the building industry (minimum 25 entries per student). Assessment schedules for all components will be included with the instruction sheets setting out details of requirements. It is a group Assignment ...

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

expert
student rating student rating student rating student rating student rating
Maeve Whitaker

Master’s degree in Biology from Northwestern University

650 - Completed Orders

Hire Me
expert
student rating student rating student rating student rating student rating
Amari Beltran

I have acquired my Master’s degree in Literature from the University of Glasgow

380 - Completed Orders

Hire Me
expert
student rating student rating student rating student rating student rating
Colin Forsyth

M.Phil in Pharmacy with Specialization in Health Services and Patient Safety

628 - Completed Orders

Hire Me
expert
student rating student rating student rating student rating student rating
Cherry Wendt

PhD in English

857 - Completed Orders

Hire Me

Still in Two Minds? The Proof is in Numbers!

33845 Genuine Reviews With a Rating of 4.9/5.

student rating student rating student rating student rating student rating
27 Jan 2022

Psychology

Home Work: 2 Pages, Deadline: 1 day

This is a really good service, It is rare to have them miss the assignment. I always come back with at least an 85

flag User ID: 4***21 Charlotte, United States

student rating student rating student rating student rating student rating
27 Jan 2022

Psychology

Assignment: 1 Page, Deadline: 1 day

This is a really good service, It is rare to have them miss the assignment. I always come back with at lease an 85

flag User ID: 4***21 Charlotte, United States

student rating student rating student rating student rating student rating
27 Jan 2022

Maths

Assignment: 2 Pages, Deadline: 4 days

helped me so much when I was at a time crunch, and they really pulled through for me

flag User ID: 5***12 Charlotte, United States

student rating student rating student rating student rating student rating
26 Jan 2022

Statistics

Assignment: 5 Pages, Deadline: 12 days

Great service and perfect work and fast feedbacks as well i have no doubt in the work

flag User ID: 2***25 Khobar, Saudi Arabia

student rating student rating student rating student rating student rating
26 Jan 2022

Marketing

Assignment: 1 Page, Deadline: 6 days

Got help from this expert for this assignment. I got an A and was very pleased with the help and grade. Will use again.

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

student rating student rating student rating student rating student rating
26 Jan 2022

Property Law

Assignment: 12 Pages, Deadline: 12 days

Just received very good marks. Very happy with his service. Answer all questions nicely and all require points are included and good eassy writing sty ...

flag User ID: 6***34 San Francisco, Singapore

student rating student rating student rating student rating student rating
26 Jan 2022

Project Management

Assignment: 6 Pages, Deadline: 1 day

I got my assignment delivered before the deadline, and it was an excellent job done.

flag User ID: 8***21 San Francisco, Nigeria

student rating student rating student rating student rating student rating
26 Jan 2022

Healthcare

Assignment: 0 Pages, Deadline: 22 hours

great work and need more concise and explanation, provide feedback as soon as possible

flag User ID: 8***48 San Francisco, Saudi Arabia

student rating student rating student rating student rating student rating
26 Jan 2022

Management

Essay: 4 Pages, Deadline: 2 days

I was impressed with the adjustment made to the report to suit the APA 7 edition style with citations for all answers.

flag User ID: 8***17 San Francisco, United States

student rating student rating student rating student rating student rating
26 Jan 2022

Marketing

Assignment: 8 Pages, Deadline: 21 days

Thank you once again excellent paper, will use service again, extremely efficient understood the requirements.

flag User ID: 7***88 Melbourne, Australia

student rating student rating student rating student rating student rating
26 Jan 2022

Economics

Course Work: 4 Pages, Deadline: 6 days

Thank you for the constant support and understanding and work towards my project

flag User ID: 8***56 Melbourne, Canada

student rating student rating student rating student rating student rating
26 Jan 2022

English

Essay: 8 Pages, Deadline: 10 days

the expert who worked on this assignment did a great job of following all the instruction I provided for this assignment and delivered it perfectly. G ...

flag User ID: 3***04 Melbourne, Bahrain

student rating student rating student rating student rating student rating
25 Jan 2022

Finance

Programming: 2 Pages, Deadline: 23 hours

Good not bad. But if the solution with more analysis and explanation will be great

flag User ID: 8***49 Melbourne, Saudi Arabia

student rating student rating student rating student rating student rating
25 Jan 2022

Management

Assignment: 6 Pages, Deadline: 5 days

Perfect work was delivered by the expert, I have been using my assignment help for quite some time now and it has been very helpful in providing excel ...

flag User ID: 8***14 Mississauga, Canada

student rating student rating student rating student rating student rating
25 Jan 2022

Chemistry

Assignment: 1 Page, Deadline: 5 days

Assignment was completed as requested! Was very organized and neat. Included all the parts!

flag User ID: 2***50 Kitchener, Canada

student rating student rating student rating student rating student rating
25 Jan 2022

Psychology

Assignment: 4 Pages, Deadline: 1 day

It was a good one thank you. I did have to correct some of the mistakes. After that, everything was good.

flag User ID: 6***66 Kitchener, United States

student rating student rating student rating student rating student rating
25 Jan 2022

Statistics

Programming: 2.6 Pages, Deadline: 7 days

Fully trust this service with perfect score. Would recommend to friends and family. Keep up the good work. Will definitely use the service.

flag User ID: 6***65 Hughesdale, Australia

student rating student rating student rating student rating student rating
25 Jan 2022

Management

Essay: 6 Pages, Deadline: 2 days

Perfect score. Great results and the marks was as expected. I trust this service fully with all my assessments.

flag User ID: 6***65 Hughesdale, Australia

student rating student rating student rating student rating student rating
25 Jan 2022

Criminal Law

Thesis: 8 Pages, Deadline: 4 days

Great results and the marks was as expected. I trust this service fully with all my assessments.

flag User ID: 6***65 Hughesdale, Australia

student rating student rating student rating student rating student rating
25 Jan 2022

Nursing

Home Work: 1 Page, Deadline: 12 hours

Satisfied with the assignment solution. All is correct, no amendment needed.Thanks

flag User ID: 6***60 Hughesdale, United States

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?