Conceptual ERD( Entity Relationship Daigram) and SQL Scripts

QUESTION

 

Task details:

You are required to complete all the five tasks reported in the ‘Submission details’ section.

• In Task 1 you are required to produce a conceptual ERD (Entity Relationship Diagram) for the case study reported.

• In Task 2, you are required to map the conceptual ERD provided in Figure 1 into a logical ERD which should be ready to be implemented.

• In Tasks 3 to 5, you are expected to create appropriate SQL scripts to answer all the questions.

Submission details and deliverables:

You must submit a single PDF document with the following tasks:

1. A conceptual ER diagram (max 1 page) to represent the data architecture for the case study description reported below. You need to clearly show in the diagram which entities you identified, which attributes belong to each entity, which attributes are the Primary Keys of each entity, the relationship between entities (remember to give each relationship a meaningful name, as the naming of relationships will be given particular attention) and the cardinality of each relationship that exists between entities.

2. A logical ERD (1 page) which is the result of mapping the conceptual presented in Figure 1 on this document. Remember that the mapping process requires for you to carefully consider each relationship between entities in the conceptual model and apply the relevant mapping rules to create the logical model. Also, Foreign Keys need to be added to entities and properly labelled.

3. SQL ‘CREATE TABLE’ scripts needed to create only the tables Staff and Activity from the logical ERD you have created as a result of solving Task 2. In your SQL scripts you need to add the options about Referential Integrity to the Foreign Keys constraints.

4. SQL ‘INSERT INTO’ scripts showing the data being inserted into the two tables Staff and Activity which should then look like the following:

Staff

sID	sName	sEmail	sEmployment
1	Emma	emma@coolcitytours.com	Full-time
2	Nicholas	nick@coolcitytours.com	Part-time
3	Laura	laura@coolcitytours.com	Full-time

Activity

aID	aType	aPrice	aDateAndTime	gID	gName	sID
10	River Boat (day)	24.99	2018-09-10 11:00:00	300	Adrian	2
20	Night walk	6.49	2018-10-31 21:30:00	100	Lucy	1
30	Segway tour	21.99	2018-07-11 16:15:00	400	Elizabeth	3
40	Walking tour	4.99	2019-04-02 10:45:00	200	Fatima	1
50	River Boat (night)	29.99	2019-02-14 19:00:00	500	Steve	2

Please note: Full marks for this question will be awarded only to those students who insert the values of the Foreign Key in the table Activity using subqueries to retrieve those values.

5. SQL query scripts needed to solve the following statements. The SQL should be provided, together with a screenshot (clearly displaying your University username) of the output they produce.

   1. Retrieve the activity ID, the activity type, the activity date and time, and the activity price (in this order), for all those activities that cost less than 24 pounds.

   2. Show the activity type, the activity price, and the activity date and time for those activities happening between 1^st August 2018 and 28^th February 2019.

   3. Retrieve all the information from the Staff table for those staff who have the letter ‘a’ immediately before the ‘@’ symbol in their email address. Order the results in descending alphabetical order of the staff name.

   4. Using a GROUP BY clause, retrieve the staff name and the average cost of activities for each member of staff responsible for such activities. Use an alias to rename the column about the average cost of activities.

Task 1: Coursework Case study

FabTrains is a local company running train services. The operational team wants to keep track of their operations by building a database that captures the following details. Train journeys are defined by their properties: names of the departure and arrival stations, and a description of the journey. Because multiple journeys can run between the same two stations on the same day, individual services will also be recorded. Therefore, a service can be defined as the specific running of a particular train journey. Details about services include the day and time of departure and the day and time of arrival. A particular service is associated with only a single train journey. Services are run by drivers, with the names and date of birth of these been recorded in the database. Multiple drivers can run the same service, but each driver will only run one service at a given time. On board each service there can be several conductors, but one conductor only travels on one service. The names of the conductors and their employment dates will be recorded in the database. For each entity, the database manager decides that there should be a unique identifier to facilitate the usability of the whole system.

Task 2: Conceptual ERD to be mapped into a logical ERD

Figure 1: Conceptual ERD for CoolCityTours

Brief description of the conceptual ERD above

CoolCityTours is a small company that organises Activities across London for Visitors to book. These activities are organised by members of Staff and run by professional Guides. Guides are contractors hired by CoolCityTours and therefore are considered separately from members of staff. Activities include details like their price, type and date and time they take place. When booking activities, visitors need to provide their name and contact details.

ANSWER

• Task 1

Correct identification of entities, attributes, relationships and primary keys in conceptual ERD

Conceptual ERD of FabTrains given in task 1

In this conceptual model there are four tables with names Journeys , Service , Conductor and Driver.

The journey table has a primary ID named journey_ID. Service tables contains the information about the service for each journey. Journeys and service table have one – one relationship with name associates . Next there is a table called Conductor. This contains the information about the Conductors for each service . Service and Conductor are one -many relationship with many on the conductor side and the name of the relationship is ticket checking. Next there is a table called Driver. This contains the information about the Drivers for each service . Service and Drivers are one -many relationship with many on the driver side and the name of the relationship is drives.

• Task 2

Correct mapping of entities, attributes, relationships, primary and foreign keys in logical ERD

Logical ERD of task 2

In this logical ERD there are five tables with names given to them .

As Visitor and Activity have many – many relationship to map them a new table called bookings are made which contains the primary keys of both Visitor and Activity tables as the foreign key in its own table.

The relationship between Activity and guide is one – one relationship . Hence the primary key of Guide table is given to the Activity table as the foreign key .

The relationship between Activity and Staff is one – many relationship with many on Activity Table . Hence the primary key of Staff table is given to the Activity table as the foreign key .

• Task 3

Correct SQL scripts to create tables

1. Staff Table

CREATE TABLE Staff (sID int,

sNAME varchar(20),

sEmail varchar(30),

sEmployment varchar(255),

PRIMARY KEY (sID)

);

In this command table Staff is created with respective attributes and its types with sID as the primary key.

2. Activity Table

CREATE TABLE Activity (aID int,

aType varchar(255),

aPrice float,

aDateAndTime datetime,

aName varchar(255),

gID int,

sID int,

PRIMARY KEY (aID),

FOREIGN KEY (gID) REFERENCES Guide(gID) ON UPDATE

CASCADE ON DELETE RESTRICT,

FOREIGN KEY (sID) REFERENCES Staff(sID) ON UPDATE

CASCADE ON DELETE RESTRICT

);

In this command table Activity is created with respective attributes and its types with aID as the primary key and gID for Guide table and sID for Staff table as foreign keys .

• Task 4

Correct SQL scripts to insert appropriate data into the tables.

1. Staff Table

Insert into Staff values (1,”Emma”,”emma@coolcitytours.com”,”Full-time”);

insert into Staff values(2,”Nicholas”,”nick@coolcitytours.com“,”Part-time”);

insert into Staff values(3,”Laura”,”laura@coolcitytours.com“,”Full-time”);

Insert into command is used to insert the values in the Staff table .

2 . Activity Table

insert into Activity VALUES(10,”River Boat(day)”,24.99,”2018-09-10 11:00:00″,”Adrian”,300,2);

insert into Activity VALUES(20,”Night Walk”,6.49,”2018-10-31 21:30:00″,”Lucy”,100,1);

insert into Activity VALUES(30,”Segway tour”,21.99,”2018-07-11 16:15:00″,”Elizabeth”,400,3);

insert into Activity VALUES(40,”Walking Tour”,4.99,”2019-04-02 10:45:00″,”Fatima”,200,1);

insert into Activity VALUES(50,”River Boat (Night)”,29.99,”2019-02-14 19:00:00″,”Steve”,500,2);

Insert into command is used to insert the values in the Activity table .

• Task 5

Correct SQL scripts to resolve the four queries.

a. Retrieve the activity ID, the activity type, the activity date and time, and the

activity price (in this order), for all those activities that cost less than 24

Pounds.

Where command is used to give condition.

b. Show the activity type, the activity price, and the activity date and time for

those activities happening between 1st August 2018 and 28 th February 2019.

DATE() function is used to separate date from datetime.

c. Retrieve all the information from the Staff table for those staff who have the

letter ‘a’ immediately before the ‘@’ symbol in their email address. Order the

results in descending alphabetical order of the staff name.

Like command is used to match the respective pattern in sEmail column.

d. Using a GROUP BY clause, retrieve the staff name and the average cost of activities for each member of staff responsible for such activities. Use an alias to rename the column about the average cost of activities.

Group by is used on sID with inner join to join the two tables.