Please help me with this assignment because I don't get anything they are saying
Page 1 of 4 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- Page 2 of 4 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. Below is his email. Dear Mike, Following our conversion please find a short summary of our business process. We are a car rental company with 30 staff in total. Each staff members works at one of our branches. Each branch has a manager who is also a staff member. When recruiting a staff member, we provide them with a from that they have to fill out. This form requires the staff to provide us with his/her first name, last name, address, phone number, email, and an emergency contact number, while the branch includes the staff position and salary. We currently have 30 cars in each branch. Each car is uniquely identified by its registration number, and has a make (e.g., Toyota, BMW, etc), model (e.g., FJ Cruiser, X3, etc), type (e.g., sedan, SUV, etc) and a daily rental rate (i.e., rental price). We also categorise each car as either being ̀ Luxury’ or ̀ Normal’. The luxury cars have a higher rental fee and are typically rented out for special occasions. Also, each of our cars has an actual owner (we do not own the cars). We store each car owner details including their first name, last name, address, and phone number. Each owner may own cars in multiple branches. To hire a car, customers must be registered in our registration book. They are provided a from, which asks them for their first name, last name, address, phone number, email, and license number. The staff member at the counter asks then for a PIN (which could be used along with their license number in the future for telephone bookings), adds the registration date, branch number as well as a unique ID when adding the record of each customer. Once a user is registered, a customer can hire an available car (a car currently rented out is not available for rental until it returns). Note that if a car is rented out, it is not available for renting until it is returned. To hire a car, payment details of the customer are stored including card number, and total amount. No cash payment is accepted. Each branch keeps a rental logbook covering rental history of each customer. The logbook keeps the customer’s ID, Car ID renting out dates, returning dates, total paid mount in Australia Dollars, and whether the customer received any fines during the rental period. 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. Page 3 of 4 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. The task consists of the following four parts. Task A. Entity-Relationship (E-R) diagram (30 marks) 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 (20 marks) 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 (45 marks) 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.] Page 4 of 4 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 (5 marks + up to 5 marks bonus for part b) 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? (5 marks) b) How it is possible to prevent them? (up to 5 bonus marks) Note: Answering `Part a’ is required as it includes 5 marks out of 100 for this Assessment Task. `Part b’ on the other hand is a bonus question allowing you to get up to 5 bonus marks. o The 5 bonus marks will be added to the final mark from this Assessment Task. If you get full mark for this Assessment Task, it will be added to your Assessment Task 1. If you get full mark from both Assessment Tasks, then the 5 bonus marks or any remaining of it cannot be used further (e.g., cannot be claimed for exam, final mark, etc.). 2 references for each part are expected (so if you are answering both part a and b, it’s 4 in total). You may use either Harvard or IEEE format (this change was made to accommodate feedback after Assessment Task 1). Page 5 of 4 In your answer include an example SQL injection code. To accommodate feedback received from Assessment Task 1, there is no word limit. Suggested word limit is 400 if you were to attempt both parts of the question. SQL injection is a typical interview question. You