Ford.dbm

CREATE DATABASE Ford
USE Ford
CREATE TABLE Managers (MGR_ID INT PRIMARY KEY, MGR_NAME VARCHAR(50), AGE INT, SEX CHAR(1), PLACE VARCHAR(50))
CREATE TABLE Employees (EMP_ID INT PRIMARY KEY, EMP_NAME VARCHAR(50), AGE INT, SEX CHAR(1), PLACE VARCHAR(50))
CREATE TABLE Showrooms (SHOWROOM_ID INT PRIMARY KEY, OWNERSHIP VARCHAR(100), LOCATION VARCHAR(50), COUNTRY VARCHAR(50), MGR_ID INT)
CREATE TABLE Divisions (DIVISION_ID INT PRIMARY KEY, DIVISION_NAME VARCHAR(50), MGR_ID INT)
CREATE TABLE Cars (CAR_ID INT PRIMARY KEY, CAR_NAME VARCHAR(50), CAR_TYPE VARCHAR(50), ENGINE_TYPE VARCHAR(50), TRANSMISSION_TYPE VARCHAR(50), FUEL_CAPACITY VARCHAR(10), MILEAGE VARCHAR(10), PRICE VARCHAR(50))
CREATE TABLE Availability (CAR_ID INT PRIMARY KEY, SHOWROOM_ID INT)
ALTER TABLE Availability ADD FOREIGN KEY(SHOWROOM_ID) REFERENCES Showrooms(SHOWROOM_ID)
ALTER TABLE Showrooms ADD FOREIGN KEY(MGR_ID) REFERENCES Managers(MGR_ID)
ALTER TABLE Divisions ADD FOREIGN KEY(MGR_ID) REFERENCES Managers(MGR_ID)
ALTER TABLE Availability ADD FOREIGN KEY(CAR_ID) REFERENCES Cars(CAR_ID)
INSERT INTO Managers (MGR_ID, MGR_NAME, AGE, SEX, PLACE) VALUES (1, 'Alice Johnson', 45, 'F', 'New York'),(2, 'Bob Smith', 50, 'M', 'Los Angeles'),(3, 'Charlie Brown', 38, 'M', 'Chicago'),(4, 'Diana Prince', 42, 'F', 'Houston'),(5, 'Edward Elric', 35, 'M', 'Phoenix')
INSERT INTO Employees (EMP_ID, EMP_NAME, AGE, SEX, PLACE) VALUES (1, 'Frank Castle', 30, 'M', 'Miami'),(2, 'Gina Carano', 28, 'F', 'Seattle'),(3, 'Hank Pym', 40, 'M', 'Boston'),(4, 'Ivy Adams', 26, 'F', 'Atlanta'),(5, 'Jack Reacher', 37, 'M', 'San Francisco')
INSERT INTO Showrooms (SHOWROOM_ID, OWNERSHIP, LOCATION, COUNTRY, MGR_ID) VALUES (1, 'Owned', 'Downtown', 'USA', 1),(2, 'Franchise', 'Uptown', 'USA', 2),(3, 'Owned', 'Suburb', 'USA', 3),(4, 'Franchise', 'City Center', 'USA', 4),(5, 'Owned', 'West End', 'USA', 5)
INSERT INTO Divisions (DIVISION_ID, DIVISION_NAME, MGR_ID) VALUES (1, 'Sales', 1),(2, 'Service', 2),(3, 'Finance', 3),(4, 'Marketing', 4),(5, 'Parts', 5)
INSERT INTO Cars (CAR_ID, CAR_NAME, CAR_TYPE, ENGINE_TYPE, TRANSMISSION_TYPE, FUEL_CAPACITY, MILEAGE, PRICE) VALUES (1, 'Model S', 'Sedan', 'Electric', 'Automatic', '100 kWh', '396 miles', '$80,000'),(2, 'Mustang', 'Coupe', 'Gasoline', 'Manual', '16 gallons', '25 mpg', '$55,000'),(3, 'F-150', 'Truck', 'Gasoline', 'Automatic', '23 gallons', '20 mpg', '$40,000'),(4, 'Escape', 'SUV', 'Hybrid', 'Automatic', '14 gallons', '30 mpg', '$28,000'),(5, 'Bronco', 'SUV', 'Gasoline', 'Manual', '21 gallons', '20 mpg', '$35,000')
INSERT INTO Availability (CAR_ID, SHOWROOM_ID) VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5)
INSERT INTO Cars VALUES (86, 'Mustang', 'yum', 'run', 'blah', '17999', '17', '140000')
UPDATE Cars SET CAR_NAME = 'Mustang', CAR_TYPE = 'yum', ENGINE_TYPE = 'run', TRANSMISSION_TYPE = '17999', FUEL_CAPACITY = '17999', MILEAGE = '17', PRICE = '140000' WHERE CAR_ID = 9