SIT103 - Data and Information Management - Mike Smith Case Study - Entity Relationship Diagram - IT Assessment Answer

Assessment Task:

SIT103 - Data and Information Management IT Assessment Answer

Unit Learning Outcomes (ULOs)

Of the three Unit Learning Outcomes (ULOs) of this unit, this assignment will focus on the last two ULOs. These are:

• ULO 2 - At the end of this unit students will be able to evaluate data models and apply data modelling techniques to capture the data aspects of real-world situations.

• ULO 3 - At the end of this unit students will be able to design and develop relational databases by using SQL and a database management system.

Scenario In this simplified fictitious story you are ‘Mike Smith’ and you are a fresh Deakin graduate with an excellent grade in SIT103.

Anderson is one of your senior relatives (who you care for deeply). He owns a car hire company with 4 branches across the country (Melbourne, Brisbane, Sydney, and Adelaide). However, up until today, their company’s use of IT has been very limited throughout their business (both for internal and external matters). You have recently been hearing that their business is not profitable as the old days and you suspect that this could be improved if they were to leverage IT capabilities to their advantage. As the rising star of the family equipped with technical skills and knowledge acquired during your IT-related studies at one of Australia’s better universities, you decide to roll up your sleeves and take initiative. You team-up with a few friends who are also excellent in web design and they will be taking care of all front-end development. You, on the other hand, take the role of a database developer. You arrange a meeting with the relative who owns the car rental company with the hope of convincing him to let you and your team give this a try. After a lot of discussions, he agrees – he is old-school and generally, hesitant when it comes to IT for his business. At this point, you ask him to please send an email to you describing the business process so you and your team can start the work right away.

Your Task

Your job is to design and implement a database that will be useful to support the management and services of the company. For services, your friends will take care of the front-end, but you need to provide them with the database they will be needing. For management, you are unsure what type of service will be added later (they may add an employee management portal or a like) on and you just want to make sure that you create a DB that stores the essentials Anderson has defined.

The task consists of the following four parts.

Task A. Entity-Relationship (E-R) diagram

An ER diagram is based on the business rules, and should clearly label all entities, entity attributes, primary and foreign keys, relationship and connectivity. The cardinality is optional.

Instruction: Use professional software (e.g., MS Office Visio, LucidChart, or Draw.io) to draw the ER diagram. Crow’s Foot notation is preferable.

Task B. Data dictionary

The data dictionary is based on the ER diagram and contains all table names, table attribute definitions, as well as their characteristics and relationships that are to be implemented in the database.

Instruction: Use data dictionary template (e.g., Table 3.6 on page 92, Table 7.3 on page 253 of the textbook, or the example in lecture notes).

Task C. SQL commands

Based on the ER diagram and data dictionary, you are required to use SQL to create a database and implement the database functions as indicated below:

1. Create all tables in Oracle SQL or MySQL or T-SQL or (including composite tables that you identify in your E-R diagram) and populate the tables with sample data.

2. Display all staff members whose annual salary is between $20,000 and $50,000 (inclusive), sorted by the annual salary from the highest to the lowest.

3. Increase the annual salary for all managers by 5%.

4. Display the monthly salary for the staff members who work in a given branch (identified by branch number), showing the staff number, name, position and monthly salary, sorted by the monthly salary from the highest to the lowest.

5. For a given branch (identified by branch number), display the number of staff members, minimum, maximum, and average annual salaries.

6. Display all customer members for a given branch (identified by branch number), sorted by the last name.

7. Display all cars with `Luxury’ category.

8. Display the total amount for all rentals made by each customer in the Melbourne branch and sort them in ascending order.

9. For a given car owner, display all car he/she owns in different branches.

10. Display the rental history of a given customer (identified by customer ID number), showing the customer name, email, paid amount, car type, car model, car registration number, renting out the date, returning date, as well as the branch number.

Task D. SQL Injection

In your team meeting about the work, one of the team members asks you about `SQL Injection’ attacks – he is expecting you as the database developer to have some knowledge about this. In your owns words to discuss:

a) What is SQL injection attack? 

b) How it is possible to prevent them?

This SIT103: IT Assessment has been solved by our IT Assessment 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 Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.