CSIT115/CSIT815: Data Management and Security - Report Writing IT Assignment Help

Assignment Task:

Task 1 (10 marks) Read and analyse the following specification of a sample database domain. 

A hypothetical bank is divided into several branches; each managed by exactly one branch manager. A branch is characterised by a branch number, branch name and address. 

The bank employs a number of tellers, whose duties include cashing checks, accepting deposits, and handling withdrawals for the customers. Every teller has a unique identification number and he/she is described by a set of standard attributes such as: name, address, date of birth, and salary level. The tellers are rotated among the branches, but once a teller is sent to a branch he/she must stay at that branch for a whole day. 

The bank opens accounts for customers. To open an account a customer provides his/her personal data, i.e. name, date of birth, address and at least three identification documents. The personal and identification information of each potential customer is recorded. Each customer obtains a unique identification number. Then he/she is allowed to open one or more accounts. Each account is described by its unique number, balance, and type. Whenever an account is opened, the current date and time is recorded. Customers are allowed to open many accounts at different branches, but each account is assigned to only one branch. 

An objective if this task is to construct a conceptual schema for the specification of a database domain listed above. 

It is not allowed to add any artificial identification attributes commonly known as "id" attributes to the specification listed above. 

Use UMLet tool to create a drawing of a conceptual schema in a notation of UML simplified class diagrams explained to you during the lecture classes in CSIT115/CSIT815. No other notation will be accepted! 

Use an option File->Export as... to export your diagram into a file solution1.bmp in BMP format. Do not delete an exported file. You will submit it as one of the deliverables from your laboratory work. 

In this task there is NO NEED to provide a detailed analysis of a conceptual schema. The final conceptual schema expressed in a notation of UML simplified class is completely sufficient. 

Deliverables A file solution1.bmp with the final design of a conceptual schema. Submission of a file with a different name and/or different extension and/or different type scores no marks. 

Task 2 (10 marks) Consider a conceptual schema given below. 

(1) Perform a step of logical database design and transform a conceptual schema given above into a collection of relational schemas. No report is expected from a step of logical design. 

(2) Next, use the relational schemas obtained in the previous step to create SQL script solution2.sql with CREATE TABLE statements that implement the relational schemas. 

Your CREATE TABLE statements must enforce the following types of the columns in the relational tables. 

(i) All columns that contain date (e.g. dob, etc.) must be of type DATE.

(ii) Balance of an account must be decimal values. The whole number part must be 8 digits, and the fractional part must be 2 digits. It is a positive value up to 99999999.99.

(iii) Branch number is a fixed size decimal of 6 digits.

(iv) Customer number is a fixed size decimal of 16 digits.

(v) Document number is a fixed size string of 16 characters. (vi) The type of account can only be either SAVING, or CREDIT, or HOMELOAN, or PERSONALLOAN. (vii) The salary level of teller can only be either A, or B, or C, or D.

(viii) The types of other columns in the relational tables are up to you. However, the types must make sense. For example, an address of type integer will not get a lot of appreciation from a person evaluating your solution.  

(3) Next, drop the relational tables created in the previous step at the end of SQL script solution2.sql with DROP TABLE statements. Beware the order of relational tables that are dropped is important. No report is expected from a step (3). 

(4) When your script is ready connect to the command line interface mysql and process the script solution2.sql implemented in the previous steps (2) and (3). Processing of the script must create a report. The report from processing of a script solution2.sql must be saved in a file solution2.rpt. 

If processing of the file returns the errors then you must eliminate the errors! Processing of your script must return NO ERRORS! A solution with errors is worth no marks! 

QUESTION 3 (8 marks) Download a file solution3.sql and insert into the file the implementations of the following modifications of the structures, consistency constraints, and/or the contents of the sample database specified in Prologue on Page 2. 

Note, that you are not allowed to modify and/or to drop any consistency constraints. You may need more than one SQL statement to implement a single subtask listed below. 

Your implementation must directly follow a comment with a specification of a subtask. 

(1) Modify structures of the sample database such that after modifications it is possible to record in the database information about the country of AIRLINE and PASSENGER that can store up to 50 characters. 

(2) Modify consistency constraints of the sample database such that after modifications it is possible to store in the database information about arrival time is later than departure time for a flight. The capacity of a flight is a positive integer less than 1000. 

(3) Modify consistency constraints of the sample database such that after modifications it is possible to store in the database information about class of a seating is either FIRST, or BUSINESS, or ECONOMY. The available number of seats is a positive integer less than 1000. 

(4) Modify a structure and consistency constraint of the sample database such it is possible to store in the database information about the number of flights an airline company has. The total number of flight must be a positive integer less than 100000. 

It is recommended to use a script dbdrop_f.sql to drop all relational tables modified during the processing of a script solution3.sql and then to re-create the original database with a script dbcreate_f.sql, then load data into the database with a script dbload_f.sql. In such a way your script always operates on the original structures of the sample database. 

QUESTION 4 (8 marks) Download a file solution4.sql and insert into the file the implementations of the following modifications of the structure, and the contents of the sample database specified in Prologue on Page 2. 

Note, that you are not allowed to modify and/or to drop any consistency constraints. Also note, that to implement some of the modifications listed below you may need more than one data manipulation statement of SQL. 

Your implementation must directly follow a comment with a specification of a subtask. 

(1) Assume, that a new passenger Peter, who was born on 20 April 1976, has booked an ECONOMY ticket of a flight JQ501 from SYD to MEL on 05 May 2019 at 06:05. Insert appropriate information into the sample database. The other values are up to you. Reduce the available number of seat for the economy class of the flight by one. 

(2) Modify a structure of the sample database such it is possible to store in the database information about the number of seat booked by a passenger. Update the total number of seat for each passenger by using an advanced DML statement. Display the name, date of birth, total number of seat booked by each passenger. 

(3) Use a single advanced DML to create a new table and to load into the table information of bookings which departure from SYD. There is no need to enforce any consistency constraints on the new table. Display the information from the new table. 

(4) The flight QF497 from SYD to PEK on 26 May 2019 at 22:05 has been cancelled. Delete from the database information about the flight. Remember, that the foreign keys in all CREATE TABLE statements have no ON DELETE CASCADE clause. 

It is recommended to use a script dbdrop_f.sql to drop all relational tables modified during the processing of a script solution4.sql and then to re-create the original database with a script dbcreate_f.sql, then load data with a script dbload_f.sql. In such a way your script always operates on the original structures of the sample database. 

QUESTION 5 (10 marks) Download a file solution5.sql and insert into the file the implementations of the following queries as SQL SELECT statements of the sample database specified in Prologue on Page 2. 

Your implementation must directly follow a comment with a specification of a subtask. 

(1) Find airline name, total number of flights for each airline. Sort the results in the descending order of total number of flights, then in the ascending order of airline name. 

(2) Find the flight number, departure airport, departure time and class of seats that haven’t been booked so far. Sort the results in the ascending order of flight number. 

(3) Find the name and date of birth of all passengers that have not booked flights of Qantas. (2 marks) 

(4) Find the name and date of birth of all passengers that have booked flights of an airline, of which CEO is JianJiang Cai. 

(5) Find the name, street, city, state, and country of all airlines that have maximum total number of flights. 

This CSIT115: IT Assignment has been solved by our IT 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.