KC7013 Database Modelling:Compare and Contrast your Relational

KC7013 Database Modelling:Compare and Contrast your Relational

$20 Bonus + 25% OFF

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

Students Who Viewed This Also Studied

KC7013 Database Modelling

Questions:

Implementing Object Relational Database for AiS (50 Marks)

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

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. (20 marks)

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. (10 marks)

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 (8 marks)

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:

Task

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

Q2) 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.

(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. (4 marks)

Part 2:  Data Warehousing Tasks (50 Marks)

This part is based on the Sales History scenario as described in Appendix 2.

You must submit all the SQL queries and any other code that you wrote in answering any of the tasks / questions (e.g., the use of Explain Plan statements for the queries and their SPOOL outputs).

(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. (10 marks)                                                         

(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). (10 marks)            

(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. (10 marks)

    

(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. (10 marks)

(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. (3 marks)

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

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

Appendix 1

‘Academic Information System (AiS)” Scenario

The scenario described here is that of a fictitious university called University of Gharnata.  The university wants to develop an information system to support its academic activities.

The university has several academic departments. Each department provides one or more academic courses. Each course is composed of several modules, where a module may be part of more than one course. A student enrolls on a course and every year takes a specified number of modules. Note that several students are usually registered for a course. Every student is assigned a tutor at the start of the course, who is a faculty member (e.g., lecturer) in the department providing the course. A faculty member works for a department and usually teaches on several modules. Each module has a module tutor who is also a faculty member. A faculty member may be tutor for several modules.

Each department is chaired by a professor, who is a faculty member and works for the same department. It is important that the system makes a note of when a professor became the chair of a department. The details of a department include its name, a primary location / address where its main building is located, a secondary address where it may provide its services when needed, telephone and fax numbers, etc. 

Each course is assigned a course leader (a faculty member), who manages the day-to-day issues of the course. Details of a course include a name, whether it is undergraduate/postgraduate/research course, standard duration of the course in months and the number of credit hours to complete to pass the course.

For each module, the system needs to store its details (e.g., title, number of credit hours, level (1, 2, 3, M, R), etc). For each student who is doing a module, the system needs to record marks.

For each student, the system needs to store details like name (first, middle, last), term address (street, city, region, postal code, etc), home address, telephone numbers, email address, date of birth, gender, and details of next-of-kin (e.g., name, address, relationship, telephone numbers). The system needs to record whether a student is from the UK, EU, Commonwealth or other.

Each faculty member is assigned a line manager, who is also a faculty member and works in the same department. Details of a faculty member include name, home address, office location and room number, telephone extension, email address, home and mobile phone numbers, data of birth, gender, next-of-kin details, date joined the university, salary, and details of qualifications. When a faculty member is assigned to teach on a module, the system needs to record how many hours a week he/she is expected to be teaching on that module.

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.

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

MyAssignmenthelp.com commenced its journey with the purpose of relieving the students from their problems in writing essay ,assignment or dissertation and expanded through 3000+ experts. It is a chance for the students of Australia, UK and US to get superior quality academic writing help services without losing a huge chunk of money.

More KC7013 KC7013 Database Modelling: Questions & Answers

ICT284 System Analysis and Design

Contractors want to know at the end of every day what the work schedule for the next two (2) days will be, and where (which postal code). The role for each job on the schedule (as pruner or deliverer) is also needed by the contractor, to ensure their truck is never overfull. The contractor also need …

View Answer

Business Process Stakeholders

Presentation:- All assignments should be formatted with 1.5 line spacing with margins of at least 2.5 cm on all sides. Referencing:- APA Assignment 3 Specification This assignment requires short answer-type responses. There is no need to submit a formal report. PART 1 [Unit 3] (15%) Referring once a …

View Answer

Business Normalisation

Task:
1. Draw a dependency diagram to show the functional dependencies in the relation 2. Decompose the relation into a set of 3NF relations3. Draw a relational schema for your 3NF relations and show the referential integrity constraints4. Create an ERD that represents your 3NF relationsRationaleTh …

View Answer

ISY00243 Systems Analysis and Design

Activity 11.Use the background information to create a short summary (one short paragraph) about the “Natural-Lee”. 2.Create an organisation chart for the business.3.Use the information above to describe the area of the organisation under study (i.e. the business functions that will be h …

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

Still in Two Minds? The Proof is in Numbers!

38983 Genuine Reviews With a Rating of 4.9/5.

Supply Chain

Programming: 3 Pages, Deadline:
9 days

Outstanding work. I will work with you again and again soon. I really appreciate your help; thank you so much.

User ID: 8***71 Riyadh, Saudi Arabia

Property Law

Course Work: 1 Page, Deadline:
20 days

all work is done very well, i think i will ask more help from you in future. Thanks

User ID: 8***63 Riyadh, Australia

Management

Assignment: 12 Pages, Deadline:
10 days

Credit to the expert who did a great job with this assignment! Covered most of the requirements! Thank you!

User ID: 3***95 Riyadh, Singapore

Business Law

Home Work: 4 Pages, Deadline:
9 days

Solution was provided in a timely manner. Expert managed to meet my expectation. Have yet to receive my result yet

User ID: 8***16 Riyadh, Singapore

Statistics

Programming: 4 Pages, Deadline:
4 days

This company has taken their work seriously and did a great job in producing a top quality assignment. highly recommended.

User ID: 7***37 Riyadh, Singapore

Assignment

Essay: 5 Pages, Deadline:
3 days

The writer did an awesome job with my assignment. This is my third time with them and they have kept to their quality of work.

User ID: 7***37 Riyadh, Singapore

Management

Assignment: 12 Pages, Deadline:
19 days

I am very happy to get my 100% Grade in my assessment. I wish say thank you so much.

User ID: 4***18 Riyadh, Australia

Project Management

Assignment: 4 Pages, Deadline:
9 days

Well written with sheer creativity. Always on time. Well done. I surely will recommend.

User ID: 1***16 Bella Vista, Australia

Healthcare

Essay: 2 Pages, Deadline:
15 days

Nice work! Thanks for the revision. It meets the requirement now. I am very satisfied with the work.

User ID: 7***67 Bella Vista, United States

Marketing

Home Work: 1 Page, Deadline:
23 days

Good job. Will use expert again. Thank you for your help and time that I needed on this assignment.

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

Accounting

Essay: 2 Pages, Deadline:
8 days

Received a 90/90 on my assignment, thank you to the professionals for completing this in a timely manner.

User ID: 8***75 San Francisco, United States

Assignment

Home Work: 1 Page, Deadline:
5 days

This service is an exquisite one. Answers came out early, accurate work was done and all

User ID: 8***17 Accra, Ghana

Assignment

Home Work: 1.2 Pages, Deadline:
7 hours

So far the papers were before time. The is not graded yet but overall i like the personal opinion. I did do little amend. I like the paper.

User ID: 7***0 Castro Valley, United States

Assignment

Home Work: 1.2 Pages, Deadline:
7 hours

So far there was 2 places where it was note quotes correctly with critiques, but i fixed it. overall the paper was very good

User ID: 7***0 Castro Valley, United States

Psychology

Essay: 2 Pages, Deadline:
1 day

Great job on working on this paper that allowed me to get a good mark. Would recommend this service

User ID: 6***81 Castro Valley, Canada

Nursing

Essay: 5 Pages, Deadline:
26 days

I am satisfied with the assignment. Prof. commented excellent job and i got good grades. Thank you.

User ID: 7***90 Castro Valley, Canada

Civil Law

Assignment: 10 Pages, Deadline:
15 days

My marks 15/20 thank you very much. and well done! My lecturer is veteran person he loves long essay 😉

User ID: 1***1 Bintulu, Malaysia

Business Law

Home Work: 12 Pages, Deadline:
7 days

OMG my marks 18/20 luckily im so worry at first but at the end it’s coming out nicely, thank you very much and well done! 😉

User ID: 1***1 Bintulu, Malaysia

Business Law

Assignment: 18 Pages, Deadline:
8 days

OMG just received marks today 28/30 thank you so much 😉 the way writing follow as what i need, well done!

User ID: 1***1 Bintulu, Malaysia

Management

Essay: 15 Pages, Deadline:
10 days

All learning outcomes achieved.Fair discussion of PESTLE/SWOT/Porter forces. But can evaluate by prioritizing the factors in the order of its impact a …

User ID: 6***76 Bintulu, Hong Kong

Supply Chain

Programming: 3 Pages, Deadline:
9 days

Outstanding work. I will work with you again and again soon. I really appreciate your help; thank you so much.

User ID: 8***71 Riyadh, Saudi Arabia

Property Law

Course Work: 1 Page, Deadline:
20 days

all work is done very well, i think i will ask more help from you in future. Thanks

User ID: 8***63 Riyadh, Australia

Management

Assignment: 12 Pages, Deadline:
10 days

Credit to the expert who did a great job with this assignment! Covered most of the requirements! Thank you!

User ID: 3***95 Riyadh, Singapore

Business Law

Home Work: 4 Pages, Deadline:
9 days

Solution was provided in a timely manner. Expert managed to meet my expectation. Have yet to receive my result yet

User ID: 8***16 Riyadh, Singapore

Statistics

Programming: 4 Pages, Deadline:
4 days

This company has taken their work seriously and did a great job in producing a top quality assignment. highly recommended.

User ID: 7***37 Riyadh, Singapore

Assignment

Essay: 5 Pages, Deadline:
3 days

The writer did an awesome job with my assignment. This is my third time with them and they have kept to their quality of work.

User ID: 7***37 Riyadh, Singapore

Management

Assignment: 12 Pages, Deadline:
19 days

I am very happy to get my 100% Grade in my assessment. I wish say thank you so much.

User ID: 4***18 Riyadh, Australia

Project Management

Assignment: 4 Pages, Deadline:
9 days

Well written with sheer creativity. Always on time. Well done. I surely will recommend.

User ID: 1***16 Bella Vista, Australia

Healthcare

Essay: 2 Pages, Deadline:
15 days

Nice work! Thanks for the revision. It meets the requirement now. I am very satisfied with the work.

User ID: 7***67 Bella Vista, United States

Marketing

Home Work: 1 Page, Deadline:
23 days

Good job. Will use expert again. Thank you for your help and time that I needed on this assignment.

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

Accounting

Essay: 2 Pages, Deadline:
8 days

Received a 90/90 on my assignment, thank you to the professionals for completing this in a timely manner.

User ID: 8***75 San Francisco, United States

Assignment

Home Work: 1 Page, Deadline:
5 days

This service is an exquisite one. Answers came out early, accurate work was done and all

User ID: 8***17 Accra, Ghana

Assignment

Home Work: 1.2 Pages, Deadline:
7 hours

So far the papers were before time. The is not graded yet but overall i like the personal opinion. I did do little amend. I like the paper.

User ID: 7***0 Castro Valley, United States

Assignment

Home Work: 1.2 Pages, Deadline:
7 hours

So far there was 2 places where it was note quotes correctly with critiques, but i fixed it. overall the paper was very good

User ID: 7***0 Castro Valley, United States

Psychology

Essay: 2 Pages, Deadline:
1 day

Great job on working on this paper that allowed me to get a good mark. Would recommend this service

User ID: 6***81 Castro Valley, Canada

Nursing

Essay: 5 Pages, Deadline:
26 days

I am satisfied with the assignment. Prof. commented excellent job and i got good grades. Thank you.

User ID: 7***90 Castro Valley, Canada

Civil Law

Assignment: 10 Pages, Deadline:
15 days

My marks 15/20 thank you very much. and well done! My lecturer is veteran person he loves long essay 😉

User ID: 1***1 Bintulu, Malaysia

Business Law

Home Work: 12 Pages, Deadline:
7 days

OMG my marks 18/20 luckily im so worry at first but at the end it’s coming out nicely, thank you very much and well done! 😉

User ID: 1***1 Bintulu, Malaysia

Business Law

Assignment: 18 Pages, Deadline:
8 days

OMG just received marks today 28/30 thank you so much 😉 the way writing follow as what i need, well done!

User ID: 1***1 Bintulu, Malaysia

Management

Essay: 15 Pages, Deadline:
10 days

All learning outcomes achieved.Fair discussion of PESTLE/SWOT/Porter forces. But can evaluate by prioritizing the factors in the order of its impact a …

User ID: 6***76 Bintulu, Hong Kong

Have any Query?

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