Welcome to Live Chat

Welcome to LiveWebTutors Services, World's leading Academic solutions provider with Millions of Happy Students.

Call Back
logo

24x7 Support Available

To Get the Best Price Chat With Our Experts

chat now

In A Hurry? Get A Callback

shopping cart 0

Subject Solutions Code Description Price Delete

Amount Payable : $0

continue shopping proceed to checkout

World's Leading Assignment Library

FHTS Incorporated has provided you with a standard solution for the conceptual data model (the ER diagram). You have been commissioned to continue your role (in your capacity as a Database Management

Question Preview:

For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with data and to run queries on the data. Timelines and Expectations Percentage Value of Task: 20% Due: Friday Week 11 (5:00 pm) Minimum time expectation: 20 hours Learning Outcomes Assessed The following course learning outcomes are assessed by completing this assessment: A1.design and implement a relational database using a database management system A2.utilise a query language t...

View Complete Question >>

Question Preview:

For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with data and to run queries on the data. Timelines and Expectations Percentage Value of Task: 20% Due: Friday Week 11 (5:00 pm) Minimum time expectation: 20 hours Learning Outcomes Assessed The following course learning outcomes are assessed by completing this assessment: A1.design and implement a relational database using a database management system A2.utilise a query language tools and techniques to obtain data and information from a database K5. describe relational algebra and its relationship to Structured Query Language (SQL) S1.interpret entity-relationship diagrams to implement a relational database S2.demonstrate skills in designing and building a database application using a commercially available database management system development tool S3.use a query language for data manipulation Assessment Details FHTS Incorporated has provided you with a standard solution for the conceptual data model (the ER diagram). You have been commissioned to continue your role (in your capacity as a Database Management System consultant) and to write the SQL statements to create the database and its tables, add data, and to provide SQL solutions to some of their everyday queries. The database should be created based on the ER diagram provided at the end of this document. Some sample data are also provided to be used for inserting data into tables. However, you are also required to insert sufficient data to test the reports given below. To implement the database 1.Translate the ER diagram provided into a relational schema using the correct notations and standards for this course. It should include: a.Table names b.Attribute names and field types (as required by XAMPP) c.Primary and foreign keys identified 2.Create a text file called nnnnnnnn-create.sql (where nnnnnnnn is your student number). The file should include: a.The SQL statements to create a database called FHTSnnnnnnnn (where nnnnnnnn is your student ID - e.g. FHTS30011111) b.The SQL statements to create the tables, including their primary keys, foreign keys and the relationships of the tables. 3.Create a text file called nnnnnnnn_insert.sql (where nnnnnnnn is your student number). The file should include: a.The SQL statements to load sufficient data into each table you have created to test the queries given below. Sample data has been provided, but you will need to include further data to test the requirements. You should add at least one record to the existing test data for each table. b.You are required to include your name as one of the customers, (you can provide fake address details), but you MUST have your name and give your student id as the phone number. 4.Create a text file called nnnnnnnn_query.sql (where nnnnnnnn is your student number). The file should include the SQL statements necessary to display the following reports: a.List all details of all the records in the customer table, sorted by their Last name in descending order. b.List all the customers by their first name and last name in one column, (ensuring a space between the names), with an appropriate title, followed by their phone number and then followed by the name and phone number of the company they work for. Sort this list by the company name in ascending order. c.List the first name, last name and email address of all customers.who are either/or both VIP or Frequent Flyers. (hint, check the fields with “yes” or “no”), Also list the CustVIP and CustFreqFlyer fields, changing their titles to “VIP” and “Frequent Flyer” respectively. d.List all of the VIP customers, with their first name and last name joined in the title, as “VIP Customer”, followed by their years as a customer, and the money they have spent as a customer of FHTS. Hint, look at the sub type. e.List all frequent flyer customers who have made a booking, giving their first and last names, the number of frequent flyer points they have, followed by their frequent flyer rate points, then followed by a column that gives their frequent flyer discount. i.e for each frequent flyer customer on a booking perform the multiplication between their frequent flyer points and their respective frequent flyer rate per points, which is found in the FREQ_FLYER_DISCOUNT table. (This query will entail a number of joins). Also give the last column an appropriate name. Don’t worry about duplicates found from the query, this is due to multiple bookings. f.List the staffId, first name and last name of all staff members who are supervisors, also including the ID and last name of each staff member who reports to them. g.Delete the customer Tim Canterbury. h.Change customer Rachael Bernard name to Rachael Jones. i.List all bookings that have flights to the United States. Include the Booking ID, Customer ID, FlightID, Flight Number, booking date and flight booking date,in the output. (Can you see any errors in the data for the bookings?) j.List all the bookings that don’t contain flights to the United States. This time just list the Booking ID and Customer ID. Use the previous query as a sub-query here. k.Insert a new flight for Air France with the flight number “AF513”. It will depart from the Melbourne airport with a destination to London Heathrow airport. Its departure date is set for 29th September 2016 and its arrival is set for the next day, 30th September 2016. It has a large flight capacity 520 passengers. The fixed flight fee will be $180. The distance is approximately 16,927 km. l.List all accommodation details for sites connected to locations with airports that have the last word “International” in its name. Your documentation should include: 1.A title page clearly stating your name and student ID and your tutor’s name. 2.A copy of the relational schema you have developed from the ER diagram provided. 3.A copy of the three files you are required to create included in the document (ie nnnnnnnn_create.sql, nnnnnnnn_insert.sql and nnnnnnnn_query.sql). 4.Full APA referencing of any resources you have used to complete your submission You should submit the following: 1.The documentation file described above EITHER as a MS word file or a PDF. If you are using a mac, please submit in a PDF so I can be sure of opening it. 2.The nnnnnnnn_create.sql file (where nnnnnnnn is your student number). 3.The nnnnnnnn_insert.sql file (where nnnnnnnn is your student number). 4.The nnnnnnnn_query.sql file (where nnnnnnnn is your student number). Please note that the SQL files must be either .txt or .sql because I will be running them. If they are in a word or pdf file there will be extra characters that will prevent the files from running without errors and THIS WILL COST YOU MARKS! The assignment is to be submitted via the Assignment 2 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell. ER Diagram Sample Data for Tables. COMPANY CompName CompPhone CompFax CompEmail A touch of Glass 0890154261 0890154255 contact@touchofclass.com.au Global Financial services Inc 0240715034 0240715188 info@gfsinc.com All Database solution services 0353593240 contact@adbss.com.net Roberts relocation services 0353996294 0353996296 jack@robsrelocservices.com.au Ozzie trading emporium 0745392471 service@ozziestrading.com The free trader 0740024701 740024702 contact@trade4u.com First Choice Furniture 0362629569 0362629578 help@FCFtrading.com Multi-Systems Merchant Services 0882397333 0882397423 contact@multi-sys.com.au VIP_DISCOUNT VIPStatus VIPMinYearsCust VIPMinMoneySpent VIPDiscount Gold 15 50000 0.2 Silver 10 10000 0.1 Bronze 5 5000 0.05 FREQ_FLYER_DISCOUNT FreqFlyerStatusDate FreqFlyPointsPerKilometers FreqFlyRatePerPoints 30-June-2012 2.25 0.25 30-June-2013 2.5 0.5 30-June-2014 2.75 0.75 FLIGHT_BOOKING BookingID FlightID FlightBookingDate NumDaysStay ClassFareType 1 1 01-February-2013 7 Economy 1 2 03-February-2013 3 Business 2 1 30-October-2013 4 Business 3 1 04-January-2014 2 Economy 3 2 05-January-2014 7 First 3 3 06-January-2014 1 Business CLASS_FARE ID ClassFareType ClassFarePrice 1 First 15000 1 Business 9000 1 Economy 3000 2 First 11000 2 Business 6500 2 Economy 2200 3 First 13500 3 Business 7500 3 Economy 2900 FLIGHT Flight Number Flight Depart Location Flight DepLocDate Flight Dest Location Flight DestLocDate Flight Capacity Flight FeeCharge Flight NumKilometers Airline AA123 11 2013-02-25 1 2013-02-26 367 100 14,935 3 SC456 12 2013-11-15 2 2013-11-16 80 100 9,113 2 AF678 13 2014-01-14 9 2014-01-15 500 100 14,257 1 LOCATION LocationAirport LocationCountry NumOf LocAccom Hartsfield Jackson Atlanta International United States 2 Beijing Capital International China 3 Dubai International United Arab Emirates 2 Chicago O’Hare International United States 3 Tokyo International Japan 2 London Heathrow United Kingdom 4 Los Angeles International United States 5 Hong Kong International Kai Tak Hong Kong 3 Charles de Gaulle International France 2 Dallas Fort Worth International United States 1 Sydney Airport Australia 0 Melbourne Airport Australia 0 Perth Airport Australia 0 ACCOMMODATION AccomName AccomAddress Accom City Accom PostCode Accom Phone Accom Vacancy Location Renaissance Concourse Atlanta Airport Hotel 1 Hartsfield Center Pkwy Atlanta 30354 1042099999 60 1 Super 8 College Park/Atlanta Airport 2010 Sullivan Rd, College Park Atlanta 30337 7709918985 20 1 Capital Airport Hotel AirportRoad Beijing 100621 56 2 Park Inn by Radisson London Heatrow Bath Road Heathrow UB7 0DU 18 6 Campanile Roissy Zone D Activites Du Parc De Roissy Allee Des Vergers Roissy-en-France 95700 4 9 AIRLINE AirlineName AirlinePhone AirlineEmail Air France 1300390190 booking@airfrance.com China Southern Airlines 0011864008695539 booking@csair.com American Airways 0291011948 contact@aa.com Marking Criteria/Rubric Assessment Criteria and Marking Overview Tasks Marks 1. Presentation Cover page indicating student name and number and tutor name. Page numbers included in report Index giving page numbers of various sections Overall presentation of the report Full APA referencing of all materials used and full disclosure of assistance from all sources including tutors and other students. 5 2 Revised Schema Any assumptions explained All tables included Data types correct Primary and foreign keys indicated and referenced correctly 10 3. Create Statements File runs with no error messages Database created correctly All tables created correctly All primary and foreign keys created correctly Any deletion anomalies corrected 10 4. Insert Statements File runs with no error messages Default data is inserted correctly Additional data for testing is inserted Student’s name and number are included in customer file 10 5. SQL statements Total 65 Marks Q a Correct syntax Correct table used Correct use of sorting statement 3 Q b Correct syntax Correct tables used Where statement correctly formulated and correct using of sorting statements 4 Q c Correct syntax Correct tables used Where statement correctly formulated 4 Q d Correct syntax Correct table used Where statement correctly formulated Correct indication of desired values 4 Q e Correct syntax and fields used Correct tables used Correct derived field and calculations Where statement correctly formulated 8 Q f Correct syntax Correct table used Correct use of the primary and foreign keys connections Correct answer given in the correct order 6 Q g Correct syntax Correct table used Correct result for deletion 5 Q h Correct syntax Correct table used Correct result for update 6 Q i Correct syntax Correct tables used Where statement correctly formulated Output formatted correctly 6 Q j Correct syntax Correct use of the sub-query Where statement correctly formulated Output formatted correctly 6 Q k Correct syntax Correct table used Inserts done in correct order with correct results 7 Q l Correct syntax Correct tables used Correct use of the wild card string and comparison Output formatted correctly 6 Total 100

View Less >>

Solution Preview

Solution in attached

question Get solution

$30

Orginal Price : $39.0

Pay Now

Upload Assignments

250 words

side

Get Your Assignment

Don’t delay more, place your order now. Quick assignment help will be offered to you.

Order Now

CUSTOMER REVIEWS

Excellent

logo

Based on 702 reviews See all reviews here

One of the Best Service

I trust LiveWebTutors for my assignments because of their ability to deliver the perfect assignments time and again. Only a few of my assignments required minor revisions. The rest assured it is the best assignment writing service in the market.

Elizabeth
Sydney

Great Service and on time

I felt so exhausted and burdened with the large number of assignments I had to write and desperately needed someone to help me with all the writing and there was LiveWebTutors company on the internet. They finished my assignments before the due date and also offered me a first-timer discount.

Christina
Perth

One of the Best Assignment Provider

I ordered my Mathematics and Marketing assignments from them last month. I received the content on the set date. Most importantly, the assignments were well-written and plagiarism free. I scored a top grade for the assignment written by them. They are a reliable company.

Oli
Brisbane

Very Helpful Customer Service

I was quite unsure about getting my assignment written online but after coming across LiveWebTutors.com, all my worries have vanished. The quality of the assignments written by their writers is just invincible. Their customer support is very polite and helpful. You should try their service at least once

Kabir
Adelaide