A mobile product manager wants to build a database to track the team’s mobile pr

Question

A mobile product manager wants to build a database to track the team’s mobile products.

a. Write the SQL Script to create the table for a Product which needs to contain ProductId (PrimaryKey), Product Name, Product Type (Which can only contain the values “Utility”, “Social” or “Game”), Priority and Delivery Date.
Also, write the SQL Script to create the table Developer which contains the Developer’s ID (Primary Key), SSN, First Name, Last Name , CountryofBirth, Birth Date, Job title, Bonus Amount and the Product ID of the product being developed by the developer. In addition, Review the requirements below: (20pts).

Add a foreign key to ensure the relationship between the tables.

All columns EXCEPT the Ids and names (Product Name, First Name and Last Name) are NULLABLE.

A Product can have multiple developers working on it, but a developer can only work on one product.

Explanation / Answer

--SQL Script to create the table for a Product

create table Product(ProductId int PRIMARY KEY, ProductName varchar(30) NOT NULL, ProductType varchar(30), Priority varchar(10), DeliveryDate DATE,
CONSTRAINT chk_ProdType CHECK (ProductType IN ('Utility', 'Social', 'Game')));

--SQL Script to create the table Developer

create table Developer(DeveloperId int PRIMARY KEY, SSN varchar(20),FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL,
CountryofBirth varchar(20),BirthDate DATE, Jobtitle varchar(20), BonusAmount money,ProductId int,
CONSTRAINT FK_ProductId FOREIGN KEY (ProductId) REFERENCES Product(ProductId));

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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