For faster services, inquiry about new assignments submission or follow ups on your assignments please text us/call us on +1 (251) 265-5102
Assignment Brief
Maximum word count: 3,000 words
You have been engaged as a database consultant for the following organization. ‘Time Tunnel’ is a very successful and fast-growing business that offers the history enthusiast something a little bit different. Through its website and attendance at various events and locations, the company promotes the following services and products:
Small and intimate guided tours of famous historical sites, buildings and battlefields – across the world – accompanied by a world-authority on that time period and subject. Want to explore long-hidden underground bunkers in Berlin dating from the Nazi Third Reich? Or maybe investigate battle strategies used by the longbow men on Middle Age battle fields? No problem – the company can arrange for a Nazi or Medieval longbow specialist to show you around in a small group with a maximum size of six.
Customized expert lectures from world-renowned historians for private functions, any venue, anywhere, anytime. Talks typically last for 60 – 90 minutes with a 30 minute Q+A afterwards, followed by a buffet and drinks so that guests can talk informally to the speaker. Handouts of the talk are also issued to take away. Talks can cover any period or topic. Previous talks have addressed post-colonial migration in the British Empire, castles and siege technology in the European Middle Ages and capitalist investment during the Industrial Revolution in Great Britain.
Genealogical research and family tree services – either face-to-face or remotely. In face-to-face situations, the client can either travel down to the company offices or the researcher can visit the client at home at a time to suit them. This allows more in-depth discussions and greater access to documentation. However, an online/remote service is also available.
Historical documents and books location service – from across the world. Do you have a passion for a niche or unusual historical period, topic or location and cannot find the book you want? Then let Time Tunnel hunt it down for you. With their massive network of academic and library contacts around the world, they can locate any published book or academic paper you may need.
Historical book sales – from its own on-line book store. Just like Amazon – but on a smaller scale! Customers set up an online account and can then buy both new and used history books.
History magazine subscriptions – issued quarterly in hardcopy and e-copy format. The company send out four issues per year and use a mixture of their own in-house experts plus special invited guest writers for more in-depth and niche topics.
Hands-on training workshops and courses – in historical and archival skills. This service is aimed at professional and serious amateur historians, librarians and archivists. Training is conducted at the company office. 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. The specific tasks you need to satisfy are listed below…
Question 1
(a) Summarize the key benefits that the database approach to data management offers over traditional file-based data management. Answer with regards to this case study.
(b) List and briefly describe the major features to be found in a commercial database management system (DBMS).
Question 2
Develop a semantically rich data model that captures the above scenario in the form of an entity-relationship diagram (ERD). You should note (and number) all assumptions you make about the data and the reasoning behind your design choices. Also, include (and number) any appropriate constraints and a list of entity types showing their attributes and identifiers.
Question 3
Once you are satisfied that the ER diagram is a good representation of the organisation’s data requirements, produce a logical design by mapping the ER diagram to a set of normalized relations – to third normal form (3NF). You should annotate and explain this process.
Question 4
Take each of the normalized relations and implement them as SQL tables using an appropriate DBMS – such as Oracle, MySQL or another one you have access to – and a series of CREATE TABLE statements. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate such as NOT NULL, CHECK, UNIQUE and DEFAULT. Provide screenshots of the working code. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code.
Question 5
Using appropriate sample data and your own imagination based on this case study, populate your finished tables with at least 10 rows of data in each table. Provide screenshots of the working code. When done, display the full contents of each populated table to screen and take screenshots. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code.
Question 6
To demonstrate that your final database is useful, write a set of realistic sample SQL queries based on the above scenario (use your imagination for details of each query) but they should include the following techniques: • SELECT…FROM…WHERE… • Joins (using two, three or more tables) • Ordering output (ORDER BY) • Grouping output (GROUP BY) • Aggregate functions (MIN, MAX, AVG, COUNT, SUM) You should aim to write a minimum of ten sample queries – ranging from basic SELECT…FROM…WHERE queries to more advanced ones using the above techniques. Be sure to fully evidence all SQL work by taking screenshots of the inputted code and the outputted results. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code.
For faster services, inquiry about new assignments submission or follow ups on your assignments please text us/call us on +1 (251) 265-5102