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)
You might also like to view...
The ____ perimeter is essentially the extent to which virtual assets like information are controlled.
A. non-electronic B. physical C. logical D. electronic
BO OTP is characterized best by which of the following?
a. It enables computers to broadcast their own IP addresses. b. It enables computers to discover their own MAC addresses. c. It enables computers to discover their own IP addresses. d. It is outdated and no longer used.
Which of the following is not one of Bohm and Jacopini's three control structures?
a) goto-less structure b) sequence structure c) selection structure d) repetition structure
Which of the following statements about Creative Commons licensing is false?
A) It waives your copyright for any images you register with them. B) It is a non-profit organization. C) The company which handles this is currently located in Mountain View, California. D) It enables users to share the use of creativity and knowledge through free legal tool.