Faculty of Computing, Engineering and Media (CEM) – Resit Course work Specification 2020/21
|
Module
name: |
Database
Systems and Design |
||
|
Module
code: |
IMAT5103 |
||
|
Title
of the Assignment: |
Database
design and Implementation (EER Modelling and SQL) |
||
|
This
coursework item is: |
Summative |
||
|
This
summative coursework will be marked anonymously |
Yes |
||
|
The
learning outcomes that are assessed by this coursework are: 1.
Select and comprehensively 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 |
|||
|
This coursework is: |
Individual |
||
|
This coursework constitutes 70 % to the overall
module mark. |
|||
|
Date Set: |
1st
November, 2020 |
||
|
Date & Time
Due: |
14th
January, 2021, @ 12:00pm (midday) |
||
|
Your mark and
feedback will be available to you on: If for any reason this is not forthcoming by the due date your module
leader will let you know why and when it can be expected. The Head of Studies
(headofstudies-tec@dmu.ac.uk ) should be
informed of any issues relating to the return of marked coursework and
feedback.
|
10th
February, 2021 |
||
|
When completed you
are required to submit your summative coursework to:
|
|||
|
Late
submission of coursework policy: Late
submissions will be processed in accordance with current University
regulations which state: “The time period during which a student may submit
a piece of work late without authorisation and have the work capped at 40%
[50% at PG level] if passed is 14
calendar days. Work submitted unauthorised
more than 14 calendar days after the original submission date will receive a
mark of 0%. These regulations apply to
a student’s first attempt at coursework. Work submitted late without
authorisation which constitutes reassessment of a previously failed piece of
coursework will always receive a mark of 0%.”
|
|||
|
Academic
Offences and Bad Academic Practices: These include plagiarism, cheating, collusion, copying work and reuse
of your own work, poor referencing or the passing off of somebody else's ideas as your
own. If you are in any doubt about what constitutes an academic offence or
bad academic practice you must check with your tutor. Further information and
details of how DSU can support you, if needed, is available at: http://www.dmu.ac.uk/dmu-students/the-student-gateway/academic-support-office/academic-offences.aspx and |
|||
|
Tasks
to be undertaken: You are tasked 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.
Task 1:
Selection of the case upon which the database design and implementation is to
be based
You need to identify a
suitable scenario and ensure that
your business situation is suitably complex to provide you with at least four strong entities, and at least one specialisation:
generalisation structure, 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 Task 2:
Provide a conceptual database design for your scenario
Provide a conceptual database design
for your scenario. 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). 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 scenario) should be listed.
Task 3:
Create the tables using Oracle DBMS
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. Create
the tables using Oracle DBMS. You need to create all the tables that you
identified within your logical database design. 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 4:
SQL Query writing
Define
and run the following queries and justify
as to why the query would be useful to your case study organisation:
·
Selection of particular table columns ·
Use of count and/or another similar mathematical expression ·
Use of a sorting/ordering facility ·
A condition using “<”, “>”, LIKE etc. ·
A condition using IN, NOT NULL, or similar. ·
A sub-query
|
|||
|
Deliverables to be submitted for assessment:
The results of the above Tasks need to be
submitted, i.e.: • Your case scenario, 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 each of the 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. ONE electronic copy containing all of the
above aspects must be submitted for summative assessment via Blackboard’s
TurnitIn. You are permitted to attach a small amount of additional and
appropriate evidence to support one or more of your claims, should this be
necessary. Sometime after submission, you may be asked
to attend a viva lasting for up to 15 minutes with one or more tutors. You
may be selected for a viva for a variety of reasons; for instance, to verify
that the work you have submitted is understood/written by you, to clarify
aspects of the work to aid marking or just because you were randomly
selected. Your mark may go up or down as a result of a viva. Failure to attend a viva, if you have been
asked to do so, may result in you obtaining zero marks for the entire
assessment.
|
|||
|
How the work will be marked:
In
order to achieve a 70%+ (Distinction)
grade, the work must be excellent in almost all respects, only very minor
limitations. In
order to achieve a 60-69% (Merit)
grade, the work should show strength in most respects. Whilst there may
have some limitations in one or two areas, it is still a very good piece of
work. In
order to achieve a 50-59% (Pass) grade,
the work should be of a satisfactory standard, showing strength in some
areas, but typically let down by some other aspects. A 0-49% (Fail) grade will be given where the work contains serious
errors/limitations. (0% is used either when nothing is correct or no attempt
is made.)
Please
refer to the criteria marking grid
bellow for the details of the assessment of the work.
|
|||
|
Module
leader/tutor name: |
Eirini Kalaitzopoulou |
||
|
Contact
details: |
erini.kalaitzopoulou@dmu.ac.uk,
|
||
Scenario,
Conceptual and Logical Database Design & Oracle SQL implementation/querying
|
Criteria |
0
to 49 % |
50
to 59 % |
60
to 69 % |
70
to 100 % |
|
Scenario – content appropriate and sufficient 20%
|
|
|
|
|
|
EERD – content quality, contains minimum
standard of complexity and matches scenario 20%
|
|
|
|
|
|
Assumptions complete and appropriate to
scenario 10% |
|
|
|
|
|
Associated attribute lists appropriate and
complete 10%
|
|
|
|
|
|
Tables – quantity correct and in appropriate
format, with primary/foreign keys indicated and appropriate. Well normalised
tables, with all appropriate non-key attributes. 20% |
|
|
|
|
|
Development of the required syntactically
correct queries; that together cover the required SQL SELECT statement
elements as defined, with reason(s) for each query choice. 20% |
|
|
|
|
|
Overall Grade |
|
|
|
|
