Create a database named ctsdb studentid

Assignment Help Database Management System
Reference no: EM13841354

Aims

• To analyse and comprehend a given ER diagram and Database schema

• To implement a database based on the given ER diagram and Database scheme

• To write required SQL statements query the database

• Write SQL statements to manipulate the data in the database

Learning Objectives

In the process of this assessment task you will:

• plan, schedule and execute project tasks with a view to improving your personal productivity;

• gain awareness of the typical challenges related to the practical implementation of databases;

• learn how to used Data Definition statements to implement a database from a given ER diagram and the corresponding Database schema

• learn how to use Data Manipulation statements to query a database, and insert and update data in the tables

You are required to perform the followings tasks:

1. Create a text file named Create_ StudentID .sql (for example, Create_3087654.sql) that will contain SQL statements to:

I. Create a database named CTSDB StudentID

II. Create all of the tables for the database according to the Database schema given at the end of this document

2. Create a text file named Insert_ StudentID .sql that will contain SQL statements to:

I. Insert at least five records in each of the tables. The test data inserted into the table must ensure that each of the queries, specified in Task 3, outputs at least one record

3. Create a text file named Query_ StudentId .sql that will contain all the queries to display the following

I. A list of available Vehicles sorted according to seating capacity. Display the Model, Registration number, and the Seating capacity.

II. A list of Official sorted according to their First name followed by Last name. Display their country name as well as the languages they speak.

III. List the drivers who speak French. Display their First name and Last name.

IV. List the drivers having first aid training. Display their First name, Last name, First aid level, and the Date the training was completed

V. List the drivers having special security training. Display their First name, Last name, First aid level, the Date the training was completed, and the Name of the certifying body.

VI. Find the vehicles which went for repair and/or maintenance in March 2015. Show their Registration number, Model, and the date of maintenance group by maintenance type.

VII. Find the total cost incurred for maintenance and repair. Display type of maintenance and the total cost.

VIII. Find the vehicles whose repair cost was more than the average repair cost.

IX. Find the locations whose street name starts with ‘B'.

X. List the names of the drivers who has Security clearance level above T and who speak English. Display their First name, Last name, and the Security clearance level.

4. Create a text file named Transaction_ StudentId .sql that will perform the followings. Make appropriate assumption if needed.

I. An Official named Daniel Ortega from UK, having OfficialID AUS997, wants to make a booking. He speaks English and he will play role of a ‘Judge' in the games. He wants to travel from 10 Elizabeth St, Brisbane to 117 Kings Road, Gold Coast on April 9, 2015. His expected start time is 10:00 am and end time is 1:15 pm.

II. The above trip was performed using the vehicle having VIN number SANFDAE11U1286116. The starting odometer reading for the vehicle was 26982 KM. The trip started at 10:15 and ended at 1:30 pm. At the end of the trip the odometer reading was 27190 KM. The driver for the trip was John Arnold having Driver licence number 098674432. John Arnold, although has a First Aid training (level D), completed on August 17, 2013, he does not have a special security clearance. John Arnold also speaks English.

You are required to adhere to the following output formatting conventions:

• All monetary values should be printed with a dollar symbol ($), two digits after the decimal point, and with space for 7 digits before the decimal point

• You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an -unusual- approach.

What to submit

An electronic copy of your assignment should be submitted through Moodle and should include a copy of your report, completed according to the Federation University Australia Guide for the Presentation of Academic Work and the four files described in Task 1 - Task 4 above. Zip all the files into a single file before uploading.

Your report should include:

• A copy of the SITE Assignment Coversheet that includes a copy of the plagiarism statement.

• A report of the results from running the SQL queries (Task 3) by using Copy/paste of their output.

• A bibliography containing a list of all resources used to complete the assignment. If no resources, apart from the course materials, have been used please indicate this.

Assessment Criteria

• How clear and well organised your presentation is. On the front page of your report you should include a list of acknowledgements of all people who have assisted you with this assignment including fellow students, along with a statement of completion.

• Adherence to our standards. How clear and well organised your presentation is. You should write all the queries in consistent style and use indent format.

• Data correctness and quality. Please use appropriate data for your examples (e.g. do not use inappropriate person names)

• Joining of data from multiple tables should be completed using a WHERE statement only. JOINs are not to be used within any of the SQL statements. Use of any JOINs will result in 0 (zero) marks being allocated for each SQL statement that utilizes them.

• Please refer to the provided marking guide (below) to see the distribution of marks. Assignment Resources

• The Standard ER Diagram

• Relational Database Schema.

Reference no: EM13841354

Questions Cloud

What is the mass of the solid nh4cl formed : What is the mass of the solid NH4Cl formed when 73.0 g of NH3(g) are mixed with an equal mass of gaseous HCl? What is the volume and identity of the gas remaining, measured at 14.0°C and 752 mmHg? (8 points)
Write paper on behavioral tools : Create a 7 page APA paper on behavioral tools that will be helpful for each team member and the student. Each team member is to select a behavioral tool
What is formula for the compound : A 7.3648 g sample of an oxide of mercury, when heated in a stream of hydrogen, produces Hg() and 0.6126 g H2O. What is formula for the compound?
Does the icon have more than one meaning : Does the icon have more than one meaning?
Create a database named ctsdb studentid : Create a text file named Create_ StudentID .sql (for example, Create_3087654.sql) that will contain SQL statements to: Create a database named CTSDB StudentID.
Strategies business writers can to conduct quality research : Which factors weigh into the decision to choose a particular type of graph or chart? Once you choose a type, how can you ensure that it is clear, readable, and ethical? What are some strategies business writers can use to conduct quality research?
What is the concentration expressed in molality : An aqueous solution is 0.387M in HCl. If the density of the solution is 1.23g/mL, what is the concentration expressed in molality
Impact of wrong ordering decision is the mismatch cost : The total impact of a “wrong” ordering decision is the “mismatch” cost. Why does having an opportunity to have a second order prove beneficial?
Develop regression model and what is expected ridership : Plot these data and determine whether a linear model is reasonable - Develop regression model and what is expected ridership if 10 million tourists visit the city?

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

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