Reference no: EM132366827

Data and Information Management

Assessment Task - Project Documentation and DB

Introduction

• This assignment is for students to develop the capacity to design and implement a database.
• This assignment requires students to identify business rules, create an Entity-Relationship diagram, create a data dictionary, and develop an Oracle database using SQL.
• This is an individual assessment task.

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.
The assessment of this assignment will indicate whether students can partially attain these unit learning outcomes.

Instructions (READ CAREFULLY)

• Place your name and ID on the first page of your report. The report filename should also be specified as: Project Documentation and DB - John Smith.docx [replace `John Smith' with your own name!].
• Label your answers Task A, Task b, Task C.1, Task C.2 and so on, as this assists a marker to find your answers.
• Please note that Word (docx) documents are expected to be submitted via the SIT103 unit site. Please do not submit PDF files.
• Students are encouraged NOT to apply for an extension unless there has been some long-term negative impact on their studies during the trimester. Having multiple assignments due on the same day/week will not be accepted as grounds for an extension. Only the application for extension received by before the due date will be considered unless you can demonstrate that it was impractical to apply otherwise (evidences will be needed for all such requests). Applying for extension does not necessarily mean it will be granted, so you need to assume it won't until you hear otherwise from the unit chair. There will a much stricter approach to granting extensions for Assessment Task 2 compared to Assessment Task 1.

Support

As specified in the unit guide document:

• The unit staff are unable to answer technical questions through email. All such questions should be posted on the discussion forum of the unit site.
• A strict deadline policy applies for support received from unit staff on the unit site discussion forum. Assessment Task 2 is due on Thursday 26 September, the last day that the staff will attend to technical questions related to Assessment Task 2 is the preceding Thursday (i.e., 19 Aug). There will be no exception to this policy. There were 39 Threads and 129 Posts in the Discussion Forum for Assessment Task 1, and 110 related emails sent to the unit chair (not counting the assistance provided by the tutors and the Geelong campus coordinator). Similar support will be available for Assessment Task 2. Students are expected to use the available resources and not to leave this Assessment Task to the last few days.

Scenario

In this simplified fictious 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 raising 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 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 discussion, 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

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

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

To accommodate feedback received, the unit chair has decided to allow the students to pick the SQL dialect of their choosing between Oracle SQL, MySQL, and T-SQL when working on Task C. There are no specific requirements for DBMS, and you can pick the one you are more comfortable with. No support will be given by the staff for SQL dialects other than Oracle SQL, which is the standard define for this unit. You need to be consistent in the dialect you choose for all Task C sub-questions. This means your answers need all to be in Oracle SQL or MySQL or T-SQL dialect. Please see further information on Task C at the end of this document to know what you need to include in your submission when answering Task C subtasks.

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.
• [Few records are sufficient for each table. However, first read the other questions in this section so you can add sample data useful when answering them.]

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.
• [you allocate salary values yourself in your sample data.]

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 Melbourne branch and sort them in ascending order.
• [3 records are expected here in your sample data.]

9. For a given car owner, display all car he/she owns in different branches.
• [so, in your sample data, you could for example assume 3 car owners who own 2 cars each and these are being rented out at a separate branch. For example, 1 of the owner cars is at Melbourne and another is in Brisbane.]

10. Display the rental history of a given customer (identified by customer ID number), showing customer name, email, paid amount, car type, car model, car registration number, renting out 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 discuss:

a) What is SQL injection attack?

b) How it is possible to prevent them?

Attachment:- Data and Information Management.rar