Case Study

‘Stepping into History' is a fast-growing business that offers the history enthusiast something a little bit different. Through its website and attendance at various events, the company promotes the following services and products:

Guided tours of famous historical sites, buildings and battlefields - across the world

Customized expert lectures from world-renowned historians for private functions

Genealogical research and family tree services - either face-to-face or remotely

Historical documents and books location service - from across the world

Historical book sales - from its own on-line book store

History magazine subscriptions - issued quarterly in hardcopy and e-copy format

Hands-on training workshops and courses - in historical and archaeological skills

The company needs a database to run the company - your job is to deliver that database. For each aspect of the business you must identify and capture the relevant data - such as speaker details, customer details, lecture/visit dates and locations, book details, workshop details etc. etc.

The above is simply an outline of the company and you will need to make your own assumptions and interpret or even extend the scenario as you go. Use your imagination as you see fit but you must clearly document all assumptions and extensions.

Your Tasks

1. Produce an Entity-Relationship Model for the scenario described above (15 marks)

Develop a top-down design of the data in the form of an entity-relationship diagram. You should note all assumptions you make about the data and the reasoning behind your design choices. Also include any appropriate constraints and a list of entity types showing their attributes and identifiers.

2. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF). (15 marks)

Once you are satisfied that the ER diagram is a good representation of the data, produce a logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show all intermediate steps.

3. Implement your final database design. (15 marks)

Take each of the relations from your relational model and implement them as SQL tables. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate. Then, using appropriate sample data and your own imagination, populate your finished tables.

4. Query your database. (20 marks)

Using SQL, write a set of realistic sample queries based on the above scenario (use your imagination for details of each query) but they should include the following SQL query techniques:

Joins (using two, three or more tables)

Set operations (UNION, INTERSECT and MINUS)

Ordering

Grouping

Aggregate functions (MIN, MAX, AVG, COUNT, SUM)

T able aliases

Renaming columns

Sub-queries (nested queries)

You should aim to write at least ten sample queries - ranging from basic SELECT...FROM...WHERE queries to more advanced ones using the above techniques.

5. Optimize your Database (10 Marks) You should now optimize your database:

Apply suitable database optimization techniques to your final set of tables.

Aim to implement a range of indexes.

Run a suite of queries that will invoke those indexes.

Aim to demonstrate some index suppression techniques.