CSC1033: Foundations of Data Science - IT Computer Science Assignment Help

Assignment Task

Aims

To assess your ability to:

• Interpret and extend an entity-relationship diagram.
• Implement a relational database from the design.
• Construct SQL queries over the resulting database implementation.
• Decide whether a table is in the first three normal forms or not.
• Suggest possible metadata for a table or query.

Learning Outcomes

• To design part of a relational database.
• To create a MySQL database.
• To formulate SQL queries.
• To normalise a database table.
• To investigate possible examples of metadata.

The transport department of a city council would like to implement a database for bus users indicating where bus routes go, how frequently they run and how to contact the relevant bus operator.

The council has identified some data items they would like to be recorded and also have provided some sample data (this can be found at the very end of this assignment).

For the bus operators, the council simply want to store their name (unique) and some basic contact details such as address, telephone and e-mail. The council are concerned that someone might accidentally delete a current operator or route and wish to ensure that such an operator or route cannot be easily deleted.

For bus stops, they wish to store each stop’s unique reference number (or ID) and a description of that stop’s location, e.g. “Railway Station.” They also want to store some information about which council district team manages each stop and how to phone the relevant team, e.g. to report a problem with a bus stop such as the lights not working or vandalism damage.

For the bus routes, they wish to store the route number (unique), the starting and destination bus stops and the frequency – the number of buses per hour. Note that the inclusion of both start and end bus stops makes the relationship between stop and route a 2-many relationship (just like a 1-many but with a bus stop participating exactly twice). They would also like to know which bus operators work each route. Some routes are shared between multiple operators with each operator working a proportion of the journeys on that route. A proportion of 50 for a route and an operator indicates that the operator operates 50% of all journeys on that route and a proportion of 100 indicates that all journeys on that route are provided by that operator. The council only wish to store the information described in this spec and do not wish to store anything additional.

The council started producing a sketch of an entity-relationship (E-R) diagram for their planned database. They are happy with the format and do not want this changing. It is not complete however and does not capture many aspects of the problem statement. The Bus Stop and District entities are complete as is the Uses relationship so these must not be changed. However, the rest is either incomplete or missing.

Tasks

i. Complete the diagram so that it correctly represents the scenario described above. You can download the diagram from Canvas if you wish to do so.

ii. Decide on the database tables you will need to implement the database, using the E-R diagram to help you. Create ALL of these tables in MySQL. In your answer document, you MUST show the CREATE TABLE statements you use. Populate your database with the sample data given at the end of this assignment. If you make any changes from your design, you MUST explain these here.

iii. Give the SQL for the following and show screenshots of the results for each query. If you are unable to complete a query, you should still show a screenshot of you attempting to run what you have.

a) Which operator has the e-mail address which comes last if all the e-mail addresses were ordered alphabetically?

b) On a Sunday, those routes which run more than once per hour run at half frequency, e.g. Route 1 goes to 1 per hour. Show the route number and Sunday frequency for all routes which have an altered frequency.

c) There is one bus stop which serves as both the start and end point for the same routes. What district is it in and what is the office phone number for that district? Give your answer as simply as you can.

d) Are there any bus operators who serve all three districts? Your query result screenshot (showing either operator names or “Empty Set”) will answer this question.

iv) The council is considering extending the database. Currently, it only shows the basic information about the route and there is no mention of the bus timetable. In reality, each bus route has a number of departures per day, for example there might be an 06:00 bus departing from the Park Gates to the Railway Station on Route 1, then a 06:30 departure, etc. Each departure can be uniquely identified by a combination of time, route number and direction of travel because there are no two buses departing from the same place at the same time on the same route. The journey time in minutes must also be stored (some departures are timetabled to take longer if the traffic is expected to be heavy).

For each of the following scenarios, state which of the three normal form rules (if any) are broken and give a SINGLE SENTENCE explaining why. Or, if you feel the approach would work, state that this is a valid solution.

a) Alter the existing Route entity to contain three new attributes – a departure time to list all departure times and similar attributes for the direction of travel and journey time.

b) Create a new Departures entity in the E-R diagram containing the departure time, route number, direction of travel and journey time. Add in a 1-many relationship from Departures to the Route entity. The PK of the new entity is a composite of the three key attributes listed above.

c) Rename the Route entity Route_Departure. The entity’s attributes would be the route number, the departure time, the direction of travel, journey time and all of the existing attributes of the Route entity. The PK of the new entity is a composite of the three key attributes listed above.

Note: You do not have to implement the extension, just answer the three questions in your answer document.

v) Imagine you have taken on the role of administrator of the bus database for the council. Your key roles are to analyse the usage of the database to ensure that the database is online with minimal disruption and works as efficiently as possible. This may include future modifications to the schema and/or the server hardware.

a) Your company is wanting to ensure the database is as secure as possible with no unauthorised changes for example - give three examples of relevant metadata and explain their relevance.

b) Your company is interested getting certificated for ISO9001 compliance. Explain as part of your role as database administrator, how you can assist your company in attaining ISO9001 certification, by providing one example with a brief explanation.

This CSC1033 - IT Computer Science has been solved by our Phd Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing Style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered.You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turn tin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.