DATA ANALYST FOR SQL
Budget: ₹600 – ₹1,500 INR
I am looking for a data analyst to help with a project that requires descriptive data analysis.
#...THE PROJECT...#
1.An entity-relationship (ER) diagram. The ER diagram should show all relevant entity types, relationship types,
attributes, keys, and structural constraints. Note that not all keys are
identified/mentioned in the scenario, so you are required to identify/devise
appropriate keys for all the entity types. Your ER diagram must not show any foreign
keys.
2. As part of the mapping process, for each relation, you should identify appropriate
primary keys as well as foreign keys (if applicable). Furthermore, you need to make
sure your relations obtained from mapping your ER diagram are in 3rd normal form. 3. An SQL script file containing appropriate SQL DDL (e.g., CREATE TABLE, ALTER TABLE, etc.)
statements for creating all the relations from Part 1 (A).
• The output from running the script file in a live Oracle 11g/12c/19c session (e.g., using
screenshots).
• You should use relational features from the SQL92 standard in Oracle 11g/12c/19c for
constructing your data structures / tables, including appropriate primary and foreign keys.
• You should aim for a high degree of reliability in the data with the use of as many
constraints as possible, e.g., check constraints on various columns (e.g., particular format
of primary key values, positive physical values as described in the module on integrity
constraints).
8:41 PM
this is the task: TravelPortal is an established transport company that has a fleet of luxury coaches/buses that
serves various cities, towns and popular tourist sites in and around England. Tickets can be
bought through selected tour agents throughout the country. Tickets are obtained on first
come first serve basis. Because TravelPortal provides excellent services for the comfort of its
passengers, tickets are sold very fast.
Currently each agent has a paper-based system. There is also a paper-based system in
TravelPortal. It is felt that it would be more efficient to have one computer-based system,
which tracks the transactions by all agents and at the same time enable TravelPortal to carry
out its tasks efficiently.
The current paper-based system manages logistics besides transactions in the company.
Firstly, there is a sales book for the tickets sold. Each agent submits a sales list(see Figure 1)
at the end of the day. The sales lists from the agents are filed in the sales book each day.
Tickets are provided for each service that runs on a certain route, which has stops at certain cities/locations. Different routes may have different duration for each stop to cater for
various customer needs. There is a need to record what sorts of meals are available at each
stop. There may be several services for routes at different times each day (see Figure 2).
Travel Portal constantly provides the agents with updated information on what services are
available to be booked by selling tickets. The agent or its employees phone Travel Portal
company clerks to obtain the ticket number by specifying the service id, or if unknown, the
route id, date and time. Once the ticket is confirmed, the information is entered into the sales
list.
The clerk updates the number of available seats in the service list (see Figure 2) whenever
tickets are sold. He/she has also to inform the company manager if there are no available
seats for a particular service. The manager may at his/her discretion add an available vehicle
and driver to a new service. The clerk must phone the agents to inform them if there are any
updates to the list of services.
#...THE PROJECT...#
1.An entity-relationship (ER) diagram. The ER diagram should show all relevant entity types, relationship types,
attributes, keys, and structural constraints. Note that not all keys are
identified/mentioned in the scenario, so you are required to identify/devise
appropriate keys for all the entity types. Your ER diagram must not show any foreign
keys.
2. As part of the mapping process, for each relation, you should identify appropriate
primary keys as well as foreign keys (if applicable). Furthermore, you need to make
sure your relations obtained from mapping your ER diagram are in 3rd normal form. 3. An SQL script file containing appropriate SQL DDL (e.g., CREATE TABLE, ALTER TABLE, etc.)
statements for creating all the relations from Part 1 (A).
• The output from running the script file in a live Oracle 11g/12c/19c session (e.g., using
screenshots).
• You should use relational features from the SQL92 standard in Oracle 11g/12c/19c for
constructing your data structures / tables, including appropriate primary and foreign keys.
• You should aim for a high degree of reliability in the data with the use of as many
constraints as possible, e.g., check constraints on various columns (e.g., particular format
of primary key values, positive physical values as described in the module on integrity
constraints).
8:41 PM
this is the task: TravelPortal is an established transport company that has a fleet of luxury coaches/buses that
serves various cities, towns and popular tourist sites in and around England. Tickets can be
bought through selected tour agents throughout the country. Tickets are obtained on first
come first serve basis. Because TravelPortal provides excellent services for the comfort of its
passengers, tickets are sold very fast.
Currently each agent has a paper-based system. There is also a paper-based system in
TravelPortal. It is felt that it would be more efficient to have one computer-based system,
which tracks the transactions by all agents and at the same time enable TravelPortal to carry
out its tasks efficiently.
The current paper-based system manages logistics besides transactions in the company.
Firstly, there is a sales book for the tickets sold. Each agent submits a sales list(see Figure 1)
at the end of the day. The sales lists from the agents are filed in the sales book each day.
Tickets are provided for each service that runs on a certain route, which has stops at certain cities/locations. Different routes may have different duration for each stop to cater for
various customer needs. There is a need to record what sorts of meals are available at each
stop. There may be several services for routes at different times each day (see Figure 2).
Travel Portal constantly provides the agents with updated information on what services are
available to be booked by selling tickets. The agent or its employees phone Travel Portal
company clerks to obtain the ticket number by specifying the service id, or if unknown, the
route id, date and time. Once the ticket is confirmed, the information is entered into the sales
list.
The clerk updates the number of available seats in the service list (see Figure 2) whenever
tickets are sold. He/she has also to inform the company manager if there are no available
seats for a particular service. The manager may at his/her discretion add an available vehicle
and driver to a new service. The clerk must phone the agents to inform them if there are any
updates to the list of services.