Title of the Assignment:.
Added on - 16 Sep 2019
Title of the Assignment:EER and SQLName:Database Systems and DesignThe learning outcomes that are assessed by this coursework are:1.Select and analyse a problem domain so as to identify data requirements inbusinesses.2.Design and implement a database system for the identified requirements usingdatabase modelling techniques and appropriate data description andmanipulation languages.Tasks to be undertaken:You are to develop a database design (both conceptual and logical) for an appropriate businesssituation of your choice, and then implement and subsequently query an ORACLE databasethat 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 theassessment 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 businesssituation of your choice, and then implement and subsequently query an ORACLE database that isderived from your database design. For this assignment you must work individually. There are twostages 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 basedYou need to identify a suitable case study from which to derive your database requirements. Thismay be a situation based on a company with which you are familiar or in which you are (or havebeen) employed, or may be one (or based on one) that you have read about within the trade oracademic literature or identified from their web presence. You need to ensure that your businesssituation is suitably complex to provide you with at least four strong entities, and at least onespecialisation: generalisation structure, yet suitably scoped so as to not require a huge quantity ofresultant tables (i.e., normally no more than 15) and subsequent input of sample data for queryingpurposes. It must not be based on a library (video, book, CD or film) and not just solely about ordersof 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 principaloperations/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 oneside 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 beingmodelled.The second task is to develop an EER Diagram that captures the detailed requirements for thedatabase system that you identified within the scenario you wrote to satisfy Task 1.1. The EERDiagram needs to show any weak and strong entities, the primary keys for strong entities, and anyrelationships between entities (including any generalisation: specialisation structures). *:*relationships must be decomposed, and any actual traps identified should be eliminated usingappropriate methods. For each entity, there should be an associated written list of all the attributesthat the entity possesses which are not written on the EER Diagram. Any assumptions made duringconceptual database design (i.e., anything that you assume that is not written in your Task 1.1scenario) should be listed.As well as the conceptual database design, you also need to provide the exact list of enterprise rulesthat your EER Diagram is diagrammatically representing. (Every relationship will need at least oneenterprise rule, depending on its multiplicity and degree. Each binary relationship will typically havetwo enterprise rules associated with it, for instance.)Stage 2:Logical Database Design and Oracle SQL Implementation/queryingTask 2.1: Provide a Logical Database Design for your scenarioFrom 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 andconsistent notation. All key and any non-key attributes should be listed within each table.Task 2.2: Create the tables using Oracle DBMSYou need to create all the tables that you identified within your logical database design. Make surethe 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 nameis ‘mit10sf’, then if you needed a Car table then you would create a table ‘mit10sfCar’. (Hint: makesure you create the tables in an appropriate order – for instance, those that have foreign keys cannotbe created first – why? Think about it!).Task 2.3: Create the four most useful indexes on your tablesYou need to create a total of FOUR appropriate indexes on the tables using the CREATE [UNIQUE]