Sample Content

The learning outcomes that are assessed by this coursework are:

1.Select and analyse a problem domain (see project list in appendix) 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.

• Work out automatically the amount that a client will pay for a particular booking

• Work out the total income associated with different types of booking (wedding, business events, themed party and individual) respectively.

• Provide a report showing the number of repeat customers within a given year.

• Provide an overview of the amount of bedroom bookings we get for each type of event (i.e., business events, weddings & theme parties).

Tasks: You are to develop a database design (both conceptual and logical) for SCH’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 SCH’s bookings database system (50%).  The first task is to develop:

Task 1.1 An EER Diagram that captures the detailed requirements for SCH’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. Please note that this EER diagram should not list any attributes other than the primary key.

Task 1.2   For each entity, there should be an associated written list of all the attributes that the entity possesses.

Task 1.3 Any assumptions made during conceptual database design (i.e., anything that you assume that is not written in the SCH scenario) should be listed.

Task 2: Logical Database Design and DB creation/manipulation (50%) There are five components to this task:

Task 2.1: Provide a Logical Database Design for SCH’s bookings database system from your conceptual database design.  This diagram is a significantly revised version of the diagram produced for Task 1.1.  Only primary keys and foreign keys need to be shown in the diagram.  

Task 2.2 Derive a corresponding set of well-normalised tables. A full listing of each table is required.  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.3: Create the tables using Oracle DBMS or MySQL.  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 username as part of the table name. E.g. if your username 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!).   Failure to correctly name the tables will result in zero marks for Tasks 2.4, Task 2.5 and Task 2.6

Task 2.4: 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.6 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.5: 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).

Deliverables to be submitted for assessment:

1.1An EER Diagram that captures the detailed requirements for SCH’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. Please note that this EER diagram should not list any attributes other than the primary key

1.2 For each entity, there should be an associated written list of all the attributes that the entity possesses

1.3 A list of assumptions made during conceptual database design (i.e., anything that you assume that is not written in the SCH scenario) should be listed

2.1 EER diagram representing a Logical Database Design for SCH’s bookings database system from your conceptual database design, together with a listing of the corresponding set of well-normalised tables, including workings to indicate how these were derived from 1.3.  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

2.2  SQL script for table creation. Each of your tables should contain your username as part of the table name. E.g. if your username is ‘dmu12345’, then if you needed a Booking table then you would create a table dmu12345Booking. (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!). Failure to name tables in this manner will result in zero marks for sections 2.3 to 2.6

2.3 SQL script to 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 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.

2.4 SQL script Data Population. Populate your 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).

2.5 SQL script for all queries, and a list of the corresponding results

You may be asked to attend a viva lasting for up to 15 minutes with one or more tutors. 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.