Question

Write the SQL DDL to create the following 5 tables for an App store: Publisher, Category, App, AppVersion, AppVersionReview:

1. A Publisher table where each publisher is identified by an integer id and has a name (up to 40 characters). (1 mark)

2. A Category table where each category has an id (integer), a name (up to 50 characters), and a parentId to identify its parent category. The parentId should be a foreign key to the Category table. (1.5 marks)

3. An App table storing each app that is identified by a field called id that is an integer. Other attributes include name (string up to 40 characters), publisherId (integer), categoryId (integer), and description (string up to 255 characters). Make all foreign keys set to null on delete and no action (generate error) on update.

4. A AppVersion table that stores each version of the app. The primary key is the appId and version (exactly 10 characters). Each release has a releaseDate (DATETIME), an integer rating, a price (up to 10 digits with 2 decimals), and a description (up to 500 characters). Make all foreign keys set to perform cascade on delete and cascade on update.

5. A AppVersionReview table that stores ratings for each application version. The primary key is the appId, version, and reviewer (exactly 20 characters). There is also a reviewDate (DATETIME), rating (int), and review (up to 1000 characters). Make all foreign keys set to cascade on both update and delete. A value for the reviewDate field is always required.

Answer 1

Solution:

Syntax for creating a table in database is

create table tablename(column datatype, column datatype,....................)

1) create table Publisher(publisherId int, name varchar(40))

2) create table Category(categoryId int, name varchar(50), parentId int, Foreign Key(parentId))

3) create table App(Id int, name varchar(40), publisherId int, categoryId int, description varchar(255),

Constraint fk_ap_id

Foreign Key(publisherId, categoryId)

References Publisher(publisherId), Category(categoryId)

On Delete set NULL [ On Update { No Action } ] )

4) create table AppVersion(appId int Primary Key, version varchar(10), releaseDate Date, rating int, price decimal(10, 2), description varchar(500),

Constraint fk_appver_id

Foreign Key(appId)

References App(id)

On Delete set NULL [ On Update { Cascade } ] )

5) create table AppVersionReview(appId int Primary Key, version varchar(10), reviewer varchar(20), reviewDate Date NOT NULL, rating int, review varchar(1000),

Constraint fk_appver_rev_id

Foreign Key(appId)

References App(id)

On Delete set NULL [ On Update { Cascade } ] )