Translate the below diagram into the relational model by supplying the appropriate CREATE TABLE statements. A phone number is a 10 digit number that never starts with a zero. A kind of car is one of the following: 2-Door sedan, 4-door sedan, convertable, SUV, sports car, wagon, hatchback, light truck, or other. Specify these as domains. Specify all the key and foreign key constraints. Try to

preserve as many participation constraints as possible. List all the participation constraints that are present in the E-R diagram, but not in its translation to SQL.







What will be an ideal response?

CREATE DOMAIN PHONENUM INTEGER

CHECK ( VALUE $>$ 999999999 AND VALUE $<$10000000000)

CREATE DOMAIN CARKIND CHAR(15)

CHECK ( VALUE IN

('2-door sedan','4-door sedan', 'convertable', 'SUV', 'sports car',

'wagon', 'hatchback', 'light truck', 'other'))

CREATE TABLE Manufacturer (

MName CHAR(30),

HQAddress CHAR(100),

Country CHAR(30),

PRIMARY KEY (MName))

CREATE TABLE Car (

Make CHAR(20),

ModelNum INTEGER,

MName CHAR(30),

Kind CARKIND,

PRIMARY KEY (Make, ModelNum),

FOREIGN KEY (MName) REFERENCES Manufacturer)

CREATE TABLE Dealership (

DName CHAR(30),

DAddress CHAR(100),

PRIMARY KEY (DName))

CREATE TABLE HasPhoneNum (

DName CHAR(30),

PhoneNum PHONENUM,

PRIMARY KEY (DName, PhoneNum),

FOREIGN KEY (DName) REFERENCES Dealership ))

CREATE TABLE Sell (

DName CHAR(30),

Make CHAR(20),

ModelNum INTEGER,

PRIMARY KEY (DName, Make, ModelNum),

FOREIGN KEY (DName) REFERENCES Dealership,

FOREIGN KEY (Make, ModelNum) REFERENCES Car)