IMAT5103 - Computer Science - Database Systems and Design - EER Modelling and SQL - Computing Assessment Answer

IMAT5103 - Computer Science - Database Systems and Design - EER Modelling and SQL - Computing Assessment Answer

Task:

The learning outcomes that are assessed by this coursework are:
1. Select and analyse a problem domain so as to identify data requirements in businesses.
2. Design and implement a database system for the identified requirements using database modelling techniques and appropriate data description and manipulation languages.

Tasks: - IMAT5103
You are to develop a database design (both conceptual and logical) for ABC’s bookings system, and then implement and subsequently query an ORACLE database that is derived from your database design. For this assignment, you must work individually.

Task 1: Provide a conceptual database design for ABC’s bookings database system 
The first task is to develop an EER Diagram that captures the detailed requirements for ABC’s bookings database system The EER Diagram needs to show any weak and strong entities, the primary keys for strong entities, and any relationships between entities (including any generalisation: specialisation structures). *:* relationships must be decomposed, and any actual traps identified should be eliminated using appropriate methods. For each entity, there should be an associated written list of all the attributes that the entity possesses which are not written on the EER Diagram. Any assumptions made during conceptual database design (i.e., anything that you assume that is not written in the ABC scenario) should be listed.

Task 2: Logical Database Design and DB creation/manipulation - IMAT5103
There are five components to this task:
Task 2.1: Provide a Logical Database Design for ABC’s bookings database system
From your conceptual database design, derive a corresponding set of well-normalised tables. Remember to indicate all primary and foreign key fields for each of the tables using suitable and consistent notation. All key and any non-key attributes should be listed within each table.

Task 2.2: Create the tables using Oracle DBMS
You need to create all the tables that you identified within your logical database design. Make sure the appropriate fields are defined as key, and that other suitable data integrity rules are enforced. Each of your tables should contain your user name as part of the table name. E.g. if your user name is ‘mit13sf’, then if you needed a Booking table then you would create a table ‘mit13sfBooking. (Hint: make sure you create the tables in an appropriate order – for instance, those that have foreign keys cannot be created first – why? Think about it!).
Task 2.3: Create the four most useful indexes on your tables - IMAT5103
You need to create a total of FOUR appropriate indexes on the tables using the CREATE [UNIQUE] INDEX statement. (Hint: it may be useful to consider what queries you wish to perform in Task 2.5 first, and do not forget that primary keys do not need any user-defined indexes as these are provided automatically by Oracle). Write a short explanation as to why you decided to create each particular index.

Task 2.4: Data Population
Using suitable Oracle statements, populate your Oracle tables with some fictitious yet appropriate test data (about FIVE records per large table and TEN records per small table (or as many rows as is relevant) should be enough).

Task 2.5: SQL Query writing
Define and run SIX queries of your choice (but appropriate to the scenario). Each query should require TWO or more of the following querying facilities, (and all of these facilities should be used at least once in your set of queries) and should be properly justified as to why the query would be useful to the ABC Hotel:

This IMAT5103: Computer Science Assessment has been solved by our IT/ Computer Science experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.