Background:
The Antiquity Car Rentals company is stuck in the past. For years the organisation has depended on manual information recording procedures to store information about their customers sales staff and cars. The rental car company main tains these different types of information in separate paper- based files.
CSE2DCX Database Fundamentals On The Cloud Assignment
As you can imagine retrieving a required record from a massive file is both difficult and time consuming as is extracting information for the development of reports A part from these problems storing many paper based files requires a significant amount of storage space.
To over come these issues Antiquity Car Rentals has decided to finally take advantage of modern technology to create an application that will allow them to perform these functions in a far more efficient manner.Your organisation has been hired by Antiquity to assist with the digitization of their records. You have been tasked to handle the initial work on the database systems, which will consist of constructing tables, populating data and running SQL queries.
It has been decided that the initial database will contain 5 tables.
- CUSTOMER
- TRANSACTION
- CAR
- EMPLOYEE
- SHIFT
The list of attributes and data for the CUSTOMER, TRANSACTION, CAR, EMPLOYEE, and SHIFT tables are given in Tables 1, 2, 3, 4, and 5 respectively (see tasks 6, 7, 8, 9 and 10).
CSE2DCX Database Fundamentals On The Cloud Assignment
Login to the AWS RDS SQL Server database using the credentials username and password) that you have created earlier. If you are unable to log on please do not proceed any further and contact the academic team for advice.
Instructions:
Provide a screenshot of the output for all tasks. Failing to do so may result in your assessor being unable to award marks for that task.Note that it is recommended that you carefully consider the order in which queries are run to ensure foreign keys are created correctly.
You will not receive marks for queries that contain hardcoded values for instance, the highest value car in the database may currently be a car with an ID of 1005. If a query asks you to identify the highest value car it would not be expected that the clause “Where Car = 1005” would be used,as the value of ‘1005’ is hardcoded (and furthermore the actual ‘highest value’ car in the database could change in the future) Instead it would be expected that the query should retrieve the correct record without needing to directly refer to any specific value.
Tasks
Task 1:
Login to the SQL Server and create the CUSTOMER table using SQL statements (refer Table 1 below).Write the finalised SQL statements that you used to create the table.
Task 2
Write the finalised SQL statements that you used to create the table. Provide a screenshot of the output.
Task 3
Write the finalised SQL statements that you used to create the table.
Task 4
Write the finalised SQL statements that you used to create the table.
Task 5
Write the finalised SQL statements that you used to create the table.
Task 6
Write a query to insert the following data to populate the CUSTOMER table. Make any reasonable modifications so that the data matches your field names and data types and conform with integrity constraints.
Task 7
Write a query to insert the following data to populate the TRANSACTION table. Make any reasonable modifications so that the data matches your field names and data types and conform with integrity constraints.
Task 8
Write a query to insert the following data to populate the CAR table. Make any reasonable modifications so that the data matches your field names and data types.
Task 9
Write a query to insert the following data to populate the EMPLOYEE table. Make any reasonable modifications so that the data matches your field names and data types and conform with integrity constraints.
Task 10
Write a query to insert the following data to populate the SHIFT table. Make any reasonable modifications so that the data matches your field names and data types.
Task 11
Write a query to display all details of Customers who do not have their last name recorded in the database.
Task 12
Write a query to display all details of Customers who live in the Sydney CBD (postcode 2000).
Task 13
Write a query to display all details of transactions that were made at least one week before the pickup date.
Task 14
Write a query to display all information regarding all tables that are present in the current database. The out put should contain all database tables including those which were created by the system. (Hint: Use consider using data dictionary).
Task 15
Create a view called Young_Customers that displays all information for Customers who are less than 25 years of age.
Task 16
Write a stored procedure called ‘Tax_Due’. The procedure should require a transaction id as input and display the tax for that transaction. Tax is calculated as 10% of the transaction value (transaction value = car price * number of days). Execute the procedure for transaction 1002.
Task 17
Write a trigger called ‘TransactionInfo’. The trigger should run whenever a new row is added or an existing row is updated in the Transaction table. It should display the transaction id, car make, car model, pickup and return dates.A query should be written to test the trigger.
Task 18
Write a query to add a new column ‘Amount_Due’ to the TRANSACTION table. This column will be used to represent the total amount due for a Transaction.
Task 19
The newly added Amount_Due column in the TRANSACTION table should currently contain only NULL values. Write a query to assign values to this column. The amount due will be calculated as (Price * number of days). Number of days can be calculated by using pickup and return dates.
Task 20
Write a query to display all information about the Customer who made the highest transaction. It is expected that the newly created amount due column be used to identify the highest transaction.
Task 21
Write a query to display the number of transactions for each type of car. The output should have a separate row for each type of car such as sedan, hatch, ute etc.
Task 22
Write a query to identify emails addresses that are incorrect (missing @). Provide a screenshot of the output.
Task 23
Write a query to display the day on which the CEO joined the company. Hint: There are functions that can be used to display day of week for dates.
Task 24
Write a query to display information about the current user (This is the user you have logged in as) Hint: Data dictionary.Write a query to display all information about the manager who manages the greatest number of employees. Hint: The manager column in the employee table contains eID (employee ID) for Managers.
Task 26
Write a query to display each employees’ fName, lName, and commission.Commission is calculated as 2.5% of the transaction value. Hint: Have a look at the transaction table and you will find the employee who was responsible for each transaction.
CSE2DCX Database Fundamentals On The Cloud Assignment
Task 27
This task requires you to allocate shifts to employees. You can manually decide which employee works which shift as long as you follow the following rules:
1.No employee will work more than 38 hours
2.Each shift will have a manager or CEO.
How this is implemented is up to you One approach may be to add new columns to the existing shift and employee tables or you could instead create a new table all together. Hint: This is a typical many to many relationship as one shift can have many employees and each employee can be part of multiple shifts. Note: You can write up to three queries to complete this task the fewer the better though.
Task 28
Write a query to display the employees who will be working today You should use a built in function to identify which day of the week it is today. Use this day to extract information form the time table (tTask 27).
ORDER This CSE2DCX Database Fundamentals On The Cloud Assignment NOW And Get Instant Discount
Read More :
