Implement a relational database using a database

Assignment Help Database Management System
Reference no: EM131222000

Overview

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.

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

FHTSIncorporated 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 (wherennnnnnnn 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 requiredto 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 bytheir 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 customerswho 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 givestheir frequent flyerdiscount.i.e for each frequent flyer customer on a booking perform the multiplication between theirfrequent flyer points and their respectivefrequent flyer rate perpoints,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 bookingsthat 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.

Reference no: EM131222000

Questions Cloud

Part of project integration management : Name the main planning tasks performed as part of project integration management? What are the main documents created, and what should they contain?
What is the minimum number of cells possible in c : A data cube, C, has n dimensions, and each dimension has exactly p distinct values in the base cuboid. Assume that there are no concept hierarchies associated with the dimensions.
Payment of interest and amortization of debt issue costs : Cupola Fan Corporation issued 12%, $410,000, 10-year bonds for $394,000 on June 30, 2016. Debt issue costs were $1,600. Interest is paid semiannually on December 31 and June 30. Prepare the journal entry to record the issuance of the bonds, the payme..
Prepare the revaluation and pre-acquisition journal entries : ACC/ACF2100 Financial Accounting Assignment. Required: Prepare an acquisition analysis at 1 July 2011. Prepare the revaluation and pre-acquisition journal entries at 30 June 2015. Prepare the consolidation journal entries for intra-group transactions..
Implement a relational database using a database : ITECH 1006 - Database Management Systems-  Design and implement a relational database using a database management system and utilise a query language tools and techniques to obtain data and information from a database
Scheduling the staffing of the center : Scheduling staff. You are the director of the Computer Center for Gaillard College and responsible for scheduling the staffing of the center, which is open from 8a.m. until midnight. You have monitored the usage of the center at various times of..
What are some of the current trends organizations face : What are some of the current trends organizations face with mobile computing security and what are some of the remediation steps companies take to ensure data confidentiality and integrity?
What are three main types of data warehouse usage : What are the differences between the three main types of data warehouse usage: information processing, analytical processing, and data mining? Discuss the motivation behind OLAP mining (OLAM).
What is the ending inventory balance and cost of goods : What is the ending inventory balance and cost of goods manufactured during the month?

Reviews

len1222000

9/27/2016 5:48:19 AM

Q j • Correct syntax • Correct use of the sub-query • Where statement correctly formulated • Output formatted correctly Q k • Correct syntax • Correct table used • Inserts done in correct order with correct results Ql • Correct syntax • Correct tables used • Correct use of the wild card string and comparison • Output formatted correctly

len1222000

9/27/2016 5:48:10 AM

Q f • Correct syntax • Correct table used • Correct use of the primary and foreign keys connections • Correct answer given in the correct order Q g • Correct syntax • Correct table used • Correct result for deletion Q h • Correct syntax • Correct table used • Correct result for update Q i • Correct syntax • Correct tables used • Where statement correctly formulated • Output formatted correctly

len1222000

9/27/2016 5:47:58 AM

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 5. SQL statements Total 65 Marks Q a • Correct syntax • Correct table used • Correct use of sorting statement Q b • Correct syntax • Correct tables used • Where statement correctly formulated and correct using of sorting statements Q c • Correct syntax • Correct tables used • Where statement correctly formulated Q d • Correct syntax • Correct table used • Where statement correctly formulated • Correct indication of desired values Q e • Correct syntax and fields used • Correct tables used • Correct derived field and calculations • Where statement correctly formulated

len1222000

9/27/2016 5:47:52 AM

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. 2 Revised Schema • Any assumptions explained • All tables included • Data types correct • Primary and foreign keys indicated and referenced correctly 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

Write a Review

 

Database Management System Questions & Answers

  Write a query that will show how may bookings have been made

Display the names of guests that have stayed at the hotel more than 10 times - Display details of the room(s) with the most bedrooms in the hotel.

  Create a relational schema

There are errors in the Company Schema. Identify all of the errors. Using ER Diagramming notation, diagram a relational model that is capable of modeling a e-commerce-based company. It should include the following information

  Is it possible for r to be in bcnf if so under what conditon

Consider the relation schema R(A,B,C), which has the FD B → C. If A is a can-didate key for R, is it possible for R to be in BCNF? If so, under what conditions? If not, explain why not.

  What are four oracle system processes that run for database

What is an Oracle Instance? What are the four Oracle system processes that must always be up and running for the database to be useable.

  Application important part of security model for databases

Using Internet as the research tool for extra information not presented in text, describe why application roles are important part of the security model for databases.

  Fetch customers from germany-mexico

1) Fetch all the customers that are not from region 'BC' or 'SP' 2) Show all customer contact names and titles for the business owners who are from Germany or France 3) Fetch customers from Germany, Mexico and Sweden

  How to change content in order for new list

To what kind of list would you change it? What would you require to change about content in order for new list type to be effective for purpose of list?

  Database and data warehousing design

Database and Data Warehousing Design

  Create a new manager class

Create a new package called task04. Copy your classes from package task03 into this package. Create a new class: Manager class.

  What are the typical departments in a data center

What techniques would you use to effectively manage a team of system administrators (network, database, application, etc.) working in your data center?

  Implement a program to query and update the text database

Implement a program to query and update the text database for a library using a client/server model allowing multiple clients safe access to the database, and using a producer-consumer model to control the inventory of each item.

  Create a physical inventory document for warehouse stock

Create a new Purchase Requisition for 50 Child Deluxe Bikes, to be delivered to Plant MI00, Storage location TG00, in 1 month from today and Create a quotation with reference to the created inquiry which will be valid for one month from today, PO n..

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd