Database Systems and Design

 

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