EER-Oracle Database Design Implementation

The assignment requires students to design and implement a database system for an identified business situation, using EER (Entity-Relationship) diagrams and Oracle SQL. The task involves developing a conceptual database design, implementing the design in Oracle, and creating six useful queries. Students must work individually and submit their case scenario, enterprise rules, logical database design, table creation code, index creation code, and query results.

Title of the Assignment: EER and SQL
Name: Database Systems and Design
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 to be undertaken:
You are to 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. For this assignment you must work individually.
There are two stages to the work you need to undertake: each stage is worth 50% of the
assessment mark. Perform Stage 1 first, then Stage 2!
Tasks to be undertaken:
You are to 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. For this assignment you must work individually. There are two
stages to the work you need to undertake: each stage is worth 50% of the assessment mark.
Perform Stage 1 first, then Stage 2!
Stage 1 (Scenario and Conceptual Database Design):
Task 1.1: Selection of the case upon which the database design and implementation is to be based
You need to identify a suitable case study from which to derive your database requirements. This
may be a situation based on a company with which you are familiar or in which you are (or have
been) employed, or may be one (or based on one) that you have read about within the trade or
academic literature or identified from their web presence. You need to ensure that your business
situation is suitably complex to provide you with at least four strong entities, and at least one
specialisation: generalisation structure, yet suitably scoped so as to not require a huge quantity of
resultant tables (i.e., normally no more than 15) and subsequent input of sample data for querying
purposes. It must not be based on a library (video, book, CD or film) and not just solely about orders
of products. Once researched and identified, a written scenario needs to be produced that (a)
provides relevant background information on the organisation (e.g., its purpose, its principal
operations/structure, its products/services, its target markets, etc.), and (b) provides an overview of

what operations a database would need to support. Your scenario will probably be no less than one
side of A4, but no longer than three slides of A4. Also provide assumptions for your scenario.
You should aim to complete this task by the end of Week 6.
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise rules being
modelled.
The second task is to develop an EER Diagram that captures the detailed requirements for the
database system that you identified within the scenario you wrote to satisfy Task 1.1. 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 your Task 1.1
scenario) should be listed.
As well as the conceptual database design, you also need to provide the exact list of enterprise rules
that your EER Diagram is diagrammatically representing. (Every relationship will need at least one
enterprise rule, depending on its multiplicity and degree. Each binary relationship will typically have
two enterprise rules associated with it, for instance.)
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
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 ‘mit10sf’, then if you needed a Car table then you would create a table ‘mit10sfCar’. (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
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 don’t forget that primary keys don’t need any user-defined indexes as these are provided
automatically by Oracle). Write a short explanation as to why you decided to create each index.
Task 2.4: Data Population
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 your case study organisation:
o Selection of particular table columns
o Inner Join of at least 2 tables
o Outer Join of at least 2 tables
o Use of count and/or another similar mathematical expression
o Use of a sorting/ordering facility
o A condition using “<”, “>”, LIKE etc.
o A condition using IN, NOT NULL, or similar.
o A sub-query
Deliverables to be submitted for assessment:
The results of the above Tasks 1.1 to 2.5 need to be submitted, i.e.:
•Your case scenario, the list of enterprise rules being represented within your EER Diagram, and the
whole conceptual database design.
•The logical database design that follows from your conceptual database design.
•A print out of each of the tables (i.e., the extension of each table) that you have created in ORACLE
and the SQL code required to create them (including the code for the integrity rules).
•A print out of the SQL code required in order to create the four indexes. You should provide a short
written section justifying your choice of indexes.

•A print out of each of the six queries you devised, showing both the SQL query statement and the
query result. You should provide a brief explanation of what you expect each query to achieve and
why you think this query is relevant to your case scenario.