CREATE TABLE ProductCode( ID_CatNum Integer, CatalogNum Integer, PrdctName VarCh

Question

CREATE TABLE ProductCode(
ID_CatNum Integer,
CatalogNum Integer,
PrdctName VarChar(50) NOT NULL,
DsgFrm VarChar(15),
Strength Real,
PRIMARY KEY (ID_CatNum),
CONSTRAINT PrdctUnique1 UNIQUE (ID_CatNum,CatalogNum),
CONSTRAINT PrdctUnique2 UNIQUE (CatalogNum,PrdctName,DsgFrm,Strength)
);

CREATE TABLE TradeDress(
ID_TradeDress Integer,
Concept_ID Integer,
RevisionNum Integer NOT NULL,
DateApproved VarChar(10),
Hyperlink VarChar(50),
PRIMARY KEY(ID_TradeDress),
CONSTRAINT ConceptRevUnique UNIQUE (Concept_ID,RevisionNum),
FOREIGN KEY(Concept_ID) REFERENCES ProductConcept (ID_Concept)
);

CREATE TABLE Employees(
employeeId Integer NOT NULL,
ssn VarChar(20),
name VarChar(25) NOT NULL,
phone VarChar(25),
employeeType VarChar(25),
jobType VarChar(25),
supervisorId Integer,
PRIMARY KEY (employeeId)
);


CREATE TABLET ProductConcept(
ID_Concept Integer NOT NULL,
ProductCode_ID Integer,
ConceptName VarChar(25) NOT NULL,
Description VarChar(150),
PRIMARY KEY(ID_Concept),
CONSTRAINT ConceptProductUnique UNIQUE (ProductCode_ID,ConceptName),
FOREIGN KEY(ProductCode_ID) REFERENCES ProductCode(ID_CatNum)
);

CREATE TABLE TabletTradeDress(
ID_TabletTD Integer,
TradeDress_ID Integer,
TabletShape VarChar(25),
TabletColor VarChar(25) NOT NULL,
FilmCoated Char(1) NOT NULL,
Bisected Char(1) NOT NULL,
Debossed Char(1) NOT NULL,
CONSTRAINT TabletTDUnique UNIQUE (TabletColor,FilmCoated,Bisected,Debossed),
FOREIGN KEY(TradeDress_ID) REFERENCES TradeDress(ID_TradeDress)
);

CREATE TABLE CapsuleTradeDress(
ID_CapsuleTD Integer,
TradeDress_ID Integer,
CapsuleColor_Top VarChar(25) NOT NULL,
CapsuleColor_Bottom VarChar(25) NOT NULL,
CapsuleSize VarChar(5) NOT NULL,
employeeID Integer,
PRIMARY KEY(ID_CapsuleTD),
CONSTRAINT CapsuleTDUnique UNIQUE (CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize),
FOREIGN KEY(employeeID) REFERENCES Employees(employeeID)
);
INSERT INTO ProductCode (ID_CatNum,CatalogNum,PrdctName,DsgFrm,Strength) VALUES(1,1523,'ProductA','Tablet',25);
INSERT INTO ProductCode (ID_CatNum,CatalogNum,PrdctName,DsgFrm,Strength) VALUES(2,1524,'ProductA','Tablet',125);
INSERT INTO ProductCode (ID_CatNum,CatalogNum,PrdctName,DsgFrm,Strength) VALUES(3,1525,'ProductA','Tablet',225);
INSERT INTO ProductCode (ID_CatNum,CatalogNum,PrdctName,DsgFrm,Strength) VALUES(4,5638,'ProductB','Capsule',37);
INSERT INTO ProductCode (ID_CatNum,CatalogNum,PrdctName,DsgFrm,Strength) VALUES(5,2453,'ProductC','Capsule',25);


INSERT INTO ProductConcept(ID_Concept,ProductCode_ID,ConceptName,Description) VALUES(1,1,'A01.1','5% film coating 7% HPMC');
INSERT INTO ProductConcept(ID_Concept,ProductCode_ID,ConceptName,Description) VALUES(2,1,'A01.2','7% film coating 7% HPMC');
INSERT INTO ProductConcept(ID_Concept,ProductCode_ID,ConceptName,Description) VALUES(3,1,'A01.3','5% film coating 3% HPMC');
INSERT INTO ProductConcept(ID_Concept,ProductCode_ID,ConceptName,Description) VALUES(4,4,'A01.1','9 to 1 minitab A to B');
INSERT INTO ProductConcept(ID_Concept,ProductCode_ID,ConceptName,Description) VALUES(5,4,'A01.2','8 to 2 minitab A to B');

NSERT INTO Employees (employeeId,ssn,name,phone,employeeType,jobType )
VALUES(1, '1234567890','John','555-123-1234','Manager','CEO');
INSERT INTO Employees (employeeId,ssn,name,phone,employeeType,jobType,supervisorId )
VALUES(2, '1234567890','Adrian','555-123-1234','Manager','CEO',1);
INSERT INTO Employees (employeeId,ssn,name,phone,employeeType,jobType,supervisorId )
VALUES(3, '1234567890','Jane','555-123-1234','Manager','CFO',1);


INSERT INTO TradeDress (ID_TradeDress,Concept_ID,DateApproved,RevisionNum) VALUES(1,1,'2010-02-04',1);
INSERT INTO TradeDress (ID_TradeDress,Concept_ID,DateApproved,RevisionNum) VALUES(2,1,'2010-05-06',2);
INSERT INTO TradeDress (ID_TradeDress,Concept_ID,DateApproved,RevisionNum) VALUES(3,2,'2010-06-08',1);
INSERT INTO TradeDress (ID_TradeDress,Concept_ID,DateApproved,RevisionNum) VALUES(4,3,'2010-01-31',1);
INSERT INTO TradeDress (ID_TradeDress,Concept_ID,DateApproved,RevisionNum) VALUES(5,3,'2010-04-20',2);
INSERT INTO TradeDress (ID_TradeDress,Concept_ID,DateApproved,RevisionNum) VALUES(6,4,'2010-04-20',1);


INSERT INTO TabletTradeDress (ID_TabletTD,TradeDress_ID,TabletShape,TabletColor,FilmCoated,Bisected,Debossed)
VALUES(1,1,'capsule-shaped','orange','y','n','y');
INSERT INTO TabletTradeDress (ID_TabletTD,TradeDress_ID,TabletShape,TabletColor,FilmCoated,Bisected,Debossed)
VALUES(2,2,'capsule-shaped','orange','y','y','n');
INSERT INTO TabletTradeDress (ID_TabletTD,TradeDress_ID,TabletShape,TabletColor,FilmCoated,Bisected,Debossed)
VALUES(3,3,'capsule-shaped','pink','y','y','n');
INSERT INTO TabletTradeDress (ID_TabletTD,TradeDress_ID,TabletShape,TabletColor,FilmCoated,Bisected,Debossed)
VALUES(4,4,'capsule-shaped','none','n','y','n');
INSERT INTO TabletTradeDress (ID_TabletTD,TradeDress_ID,TabletShape,TabletColor,FilmCoated,Bisected,Debossed)
VALUES(5,5,'capsule-shaped','purple','y','y','n');


INSERT INTO CapsuleTradeDress (ID_CapsuleTD,TradeDress_ID,CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize, employeeId)
VALUES(1,6,'yellow','white','00',1);
INSERT INTO CapsuleTradeDress (ID_CapsuleTD,TradeDress_ID,CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize, employeeId)
VALUES(2,7,'blue','purple','00',2);
INSERT INTO CapsuleTradeDress (ID_CapsuleTD,TradeDress_ID,CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize, employeeId)
VALUES(3,8,'orange','orange','01',2);
INSERT INTO CapsuleTradeDress (ID_CapsuleTD,TradeDress_ID,CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize, employeeId)
VALUES(4,9,'purple','purple','01',3);
INSERT INTO CapsuleTradeDress (ID_CapsuleTD,TradeDress_ID,CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize, employeeId)
VALUES(5,10,'pink','pink','01',4);
INSERT INTO CapsuleTradeDress (ID_CapsuleTD,TradeDress_ID,CapsuleColor_Top,CapsuleColor_Bottom,CapsuleSize, employeeId)
VALUES(6,11,'yellow','orange','02',1);


DML (SQL queries)and relational algebra:
a. Display all tablet trade dress info for all revisions of Product A
b. Display product name, strength and concept name and rev num for latest revisions only
c. Display productname, strength and concept name and rev num for latest revisions for all capsule products that use size 01 capsules
d. Show latest revision of all tablet products that are bisected and NOT debossed
e. Display productname, strength and concept name and rev num for all concepts of tablet products that are green or pink

Explanation / Answer

3.select p1.PrdctName,p1.Strength,p2.ConceptName,r.RevisionNum from ProductCode p1,ProductConcept p2,TradeDress r UNION ALL
select  TabletColor from TabletTradeDress WHERE TabletColor ='green' OR 'pink'

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.