Answered: lotor Vehicle Dealership Database:…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

Transcribed Image Text:Motor Vehicle Dealership Database: Consider Aaron's dealership, a multi-brand vehicle dealership whose database consists of four relations as described below. Using this database, answer questions Vehicle(make, model, manfYear, color, vinNum, type, purchaseld) Stock(stockNum, unitPrice, deliveryDate, vehiclesRemaining) Manufacturer(name, manfCode, plantLocation) Purchase(purchaseld, mCode, numVehicles, orderDate, deliveryStatus) • A vehicle can be one of the following types: Car, Truck, SUV • vinNum →Vehicle Identification Number • manfCode/mCode → a 4 character alphanumeric code for the manufacturer • manfYear → year of vehicle manufacture • purchaseld and stockNum refer to the same item • The delivery status is true if the order has been delivered to the dealership and false otherwise • Manufacturer name may not contain the same data as the make of the Vehicle. For example, • name of the manufacturer: Ford Motor Company • make of the vehicle: Ford Q1. Using SQL, list the names of all vehicle manufacturers whose plant location is a 8-letter city that starts with 'S! Q2. Using relational algebra operators, find the make and model of all vehicles that were purchased on 28th April 2020 Q3. a. To the existing database, insert a foreign key constraint from mCode in the relation Purchase to the manfCode in the relation Manufacturer. All update operations must be mimicked at the foreign key whereas other changes to the database must be rejected. b. Rewrite the above foreign key constraint using the CHECK constraint. Only write the CHECK constraint c. Can the CHECK constraint in part b replace the foreign key constraint in part a, Justify your answer. Q4. Using a subquery in the FROM clause, find the average unit price of all in-stock SUV's of any make/model in SQL.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 1 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions