Blog

Garage Database Design (Part 2) — SQL Statements Instruction…

Garage Database Design (Part 2) — SQL Statements Instructions Using the E/R diagram from Question 3, fill in the blanks in the SQL statements below to create the following tables: customer, vehicle, parts, job, and involvement. Ensure the relational schema accurately reflects the relationships in the E/R diagram. Ensure any key and participation constraints in the ER diagram are captured. SQL Statements Should Include Valid SQL syntax Primary keys Column names with data types Foreign keys (if applicable) Fill in the Blanks CREATE TABLE customer (    [customerName] CHAR(20) [customerPK],    address CHAR(20),    [phoneNumber] CHAR(15)); CREATE TABLE vehicle (    [vehicleVIN] CHAR(50) [vehiclePK],    vehicleYear INTEGER,    [vehicleMake] CHAR(10),    vehicleModel CHAR(20),    [ownerName] CHAR(20) NOT NULL,    FOREIGN KEY ([vehicleOwnerFK]) REFERENCES [customerTable]([customerTablePK])); CREATE TABLE parts (    [partNumber] CHAR(50) [partPK],    [partName] CHAR(20),    [partCost] REAL); CREATE TABLE job (    [jobVIN] CHAR(50),    [jobDate] DATE,    [jobDescription] VARCHAR(200),    [jobCost] REAL,    PRIMARY KEY ([jobVINPK], [jobDatePK]),    FOREIGN KEY ([jobVehicleFK]) REFERENCES [vehicleTable]([vehicleTablePK])); CREATE TABLE involvement (    vehicleVIN CHAR(50),    [involvementDate] DATE,    [involvementPartNumber] CHAR(50),    PRIMARY KEY ([involvementVINPK], [involvementDatePK], [involvementPartPK]),    FOREIGN KEY ([involvementVINFK], [involvementDateFK]) REFERENCES [jobTable]([jobVINRef], [jobDateRef]),    FOREIGN KEY ([involvementPartFK]) REFERENCES [partsTable]([partsTablePK]));

Garage Database Design (Part 2) — SQL Statements Instruction…

Garage Database Design (Part 2) — SQL Statements Instructions Using the E/R diagram from Question 3, fill in the blanks in the SQL statements below to create the following tables: customer, vehicle, parts, job, and involvement. Ensure the relational schema accurately reflects the relationships in the E/R diagram. Ensure any key and participation constraints in the ER diagram are captured. SQL Statements Should Include Valid SQL syntax Primary keys Column names with data types Foreign keys (if applicable) Fill in the Blanks CREATE TABLE customer (    [customerName] CHAR(20) [customerPK],    address CHAR(20),    [phoneNumber] CHAR(15)); CREATE TABLE vehicle (    [vehicleVIN] CHAR(50) [vehiclePK],    vehicleYear INTEGER,    [vehicleMake] CHAR(10),    vehicleModel CHAR(20),    [ownerName] CHAR(20) NOT NULL,    FOREIGN KEY ([vehicleOwnerFK]) REFERENCES [customerTable]([customerTablePK])); CREATE TABLE parts (    [partNumber] CHAR(50) [partPK],    [partName] CHAR(20),    [partCost] REAL); CREATE TABLE job (    [jobVIN] CHAR(50),    [jobDate] DATE,    [jobDescription] VARCHAR(200),    [jobCost] REAL,    PRIMARY KEY ([jobVINPK], [jobDatePK]),    FOREIGN KEY ([jobVehicleFK]) REFERENCES [vehicleTable]([vehicleTablePK])); CREATE TABLE involvement (    vehicleVIN CHAR(50),    [involvementDate] DATE,    [involvementPartNumber] CHAR(50),    PRIMARY KEY ([involvementVINPK], [involvementDatePK], [involvementPartPK]),    FOREIGN KEY ([involvementVINFK], [involvementDateFK]) REFERENCES [jobTable]([jobVINRef], [jobDateRef]),    FOREIGN KEY ([involvementPartFK]) REFERENCES [partsTable]([partsTablePK]));