CSE2DCX Database Fundamentals On The Cloud Assignment

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

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.

CSE2DCX Database Fundamentals On The Cloud Assignment
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.

CSE2DCX Database Fundamentals On The Cloud Assignment
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.

CSE2DCX Database Fundamentals On The Cloud Assignment
CSE2DCX Database Fundamentals On The Cloud Assignment
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

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

Order Your Assignment

Read More :

SIT103/SIT772 Database Fundamentals Assignment

Contact us on WhatsApp Assignment help payment - PayPal



Assignment Help In Australia - Essay Help
69 Kent St Millers Point NSW 2000 Australia
Phone : +61-3-6387-7039

Disclaimer: The papers provided by Assignmenthelpinaustralia.com serve as model papers for research candidates and are not to be submitted 'as is'. These papers are intended to be used for reference purposes only.
Assignmenthelpinaustralia.com only offers consultation and research support and assistance in research design, editing, and statistics.*