Conceptual and Logical Data Model for ArchipelagoCrazy and SoundStuff

Part A: ArchipelagoCrazy

Produce a conceptual data model & a logical data model following given specs. Write SQL statements to complete specific tasks. Produce a supporting report.

LO1 Design a robust relational database schema using UML notations;

LO2 Produce robust SQL statements to create logically connected database tables and populate them;

LO3 Produce solid SQL queries to retrieve, aggregate, update and delete data from one or multiple database tables;

LO5 Critically evaluate the needs for non-relational databases e.g. No-SQL databases and XML Databases.

ArchipelagoCrazy is a touristic experience/adventure company that operates over an archipelago of a dozen islands located at about 15-20 miles off the Italian coast. Essentially, ArchipelagoCrazy organises exciting trips from May to November to allow tourists to visit the islands and fully experience the outstanding beauty of the landscape in this unspoilt corner of Italy.

The company operates a float of several boats to provide this touristic experience/adventure service. Larger motorised boats which can handle a higher number of customers are used for crossings to carry people from the mainland to the islands but also between different islands. Smaller motorised boats which can take 6-8 people are used to provide a local beach taxi service on the larger islands to allow tourists to conveniently travel to the main beautiful beaches. Finally, paddle-propelled boats (like kayaks, stand up paddleboards, canoes, etc.) can be used to explore an island locally and access the more remote bays and creeks. The availability of a boat on an island needs to always be verified for it to be used to deliver a specific service as well as the capacity of the boat. In addition to the great boating experience, ArchipelagoCrazy also organises nice walking tours on the islands and guided visits of the main landmarks located on the islands such as castles, fortresses, lighthouses, churches, monasteries, etc.

On their promotional material, ArchipelagoCrazy offers the possibility to book different adventures to suit one’s individual needs. An adventure can either be a Sea Adventure or a Land Adventure and customers can place bookings for more than one. A Sea Adventure can consist of a simple sea crossing (to an island or between islands on a larger motorised boat, with the option of a meal on board if required), a beach taxi service (to a specific beach or to hop between beaches on a smaller motorised boat) or a marine exploration (using a paddle-propelled boat) and again more than one can be booked. On the other hand, a Land Adventure refers to a walking tour with a guide or the guided visit of a key landmark on an island or both. Also, walking tours, can be nocturnal if they are scheduled to take place at night. Therefore, customers should be able to place bookings for a wide range of interesting adventures to make the most of their visit to the beautiful archipelago.

Part A Questions

In terms of staffing, the company relies on many employees with very specialised roles to fully support the many activities and adventures on offer. Noticeably, experienced sea crew help organise and manage the Sea Adventures (whether they are sea crossings, beach taxi services or marine explorations). Expert walking guides oversee the running of Land Adventures (walking tours and visits of key landmarks) and take people out on walks and cultural visits. Finally, and importantly, boat mechanics play a key role in ensuring the maintenance of all the boats used by ArchipelagoCrazy. They are assigned to work on the frequent servicing of boats. Each servicing needs to be very carefully recorded to keep a track of the regular top-quality maintenance of the watercrafts in use. It is a crucial business operation to ensure that all boats are always in top condition to ensure the best experience and the highest safety of ArchipelagoCrazy’s paying customers.

You have been hired by ArchipelagoCrazy as a database architect to undertake a database project to support the data needs of the company. Your job in this first part is to produce a high-quality Conceptual Entity Relationship Diagram (ERD) and to produce a data dictionary to document and support your model.

Prefix the names of all entities and attributes with your id number starting with w.

• Produce a complete Conceptual ERD for ArchipelagoCrazy.

This Conceptual ERD needs to include all the entities, relationships, multiplicities, attributes and primary keys that you have identified. It should be easy to read and needs to fit on one page of the report.

• Create a data dictionaryto document how you identified the entities for ArchipelagoCrazy. To achieve this, fill in the 2 tables below to summarise and briefly explain the meaning of each entity.
• Create a data dictionaryto document how you identified the relationships and multiplicities for ArchipelagoCrazy. To achieve this, fill in the table below to summarise and justify the multiplicities for each relationship.
• Create a data dictionaryto document how you identified the attributes and primary keys for each entity for ArchipelagoCrazy. To achieve this, fill in the table below to summarise and explain the meaning of each attribute and primary key.

SoundStuff is a UK company that is specialised in hiring out audio equipment to recording artists to be used for the recording, mixing and mastering of audio projects, such as music tracks and albums. The company runs a number of recording studios located all over the UK. SoundStuff offers a really wide range of studio equipment to their customers such as analogue and digital recorders, mixing desks, audio interfaces, microphones, studio monitors, digital converters, controllers and other studio accessories. Some of these equipment items are fixed and can only be used in the SoundStuff studios under the supervision of an engineer. Other equipment items are portable and can be lent out to customers to be used in their home studios and can be delivered by a SoundStuff driver.

The conceptual ERD for SoundStuff is given in Figure 1 and Figure 2 (larger version).

 

Figure 1. Conceptual ERD for SoundStuff

Prefix the names of all tables and attributes with your id number starting with w.

You have been hired by SoundStuff as a database consultant to undertake a database project to support the data needs of the firm. In this second part, you are given a conceptual data model for SoundStuff (figure 1), and your first goal is to map it onto a high-quality Logical Entity Relationalhip Diagram (ERD) to logically represent how the key business data needs can be organised as a set of interrelated tables that can then be implemented. These tables need to be interconnected according to the strict rules of the relational model to be implementable. You also have to write a key SQL query to retrieve specific data. Finally, you need to present a brief comparative analysis of relational databases vs. NoSQL databases with the aim to provide SoundStuff with informed guidance on the best option for them to select.

• Map the Conceptual ERD given on figure 1to produce a complete Logical ERD for SoundStuff.

This Logical ERD needs to include all the correct tables, relationships, multiplicity constraints, attributes, primary keys and foreign keys. It should be easy to read and needs to fit on one page of the report. 

• Write an SQL queryto retrieve a list of studio names for those studios that have an address in London, along with the makes, series, and models of the items of equipment in these studios, but only for the equipment that cost more than £125 a day to hire.

• Create a comparative analysis tableto compare and contrast Relational Databases vs. NoSQL databases with a view to inform the decision-making of the management of a firm.

Your table should present clear comparison criteria (or informative decision factors) as rows and have a column for Relational Databases and a column for NoSQL databases so that you can compare them side by side. You could consider areas such as schemas, data consistency, storage, performance, workload, infrastructures, security, etc.