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 archeological 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 (30 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). (20 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.
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.
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)
• Table 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.
• Make sure your full name and student ID are on the front page of the assessment
• Clearly label all tasks and take care to explain and discuss your technical work
• For the modelling and design work you must supply clearly annotated diagrams
• For the SQL work you must supply full code listings of the inputted code and screenshots of the outputted results - even if no rows were returned. Each screenshot must include some identifying feature - such as a username or user ID - that proves beyond doubt that it is your own work.
• If you need to research, then fully reference all such sources using the Harvard notation