DROP TABLE IF EXISTS SMARTPHONE_VENDOR CASCADE;
DROP TABLE IF EXISTS OPERATE CASCADE;
DROP TABLE IF EXISTS OPERATING_SYSTEM CASCADE;
DROP TABLE IF EXISTS RATING CASCADE;
DROP TABLE IF EXISTS APP CASCADE;
DROP TABLE IF EXISTS CATEGORY CASCADE;
DROP TABLE IF EXISTS DEVELOPER CASCADE;
DROP TABLE IF EXISTS USE CASCADE;
 
 
CREATE TABLE DEVELOPER (
Dev_ID TEXT NOT NULL,
Name TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
Address TEXT NOT NULL
);
 
-- Table: CATEGORY
CREATE TABLE CATEGORY (
Name TEXT NOT NULL, 
Description TEXT, 
Age_Group TEXT NOT NULL
);
 
-- Table: APP
CREATE TABLE APP (
App_ID TEXT NOT NULL,
Name TEXT NOT NULL,
Description TEXT NOT NULL,
Date TEXT NOT NULL,
Version TEXT NOT NULL,
Installations INTEGER,
Category_Name TEXT NOT NULL,
Developer_ID TEXT NOT NULL
);
 
-- Table: RATING
CREATE TABLE RATING (
Rating_ID TEXT NOT NULL,
App_ID TEXT NOT NULL,
Date TEXT NOT NULL,
Value INTEGER NOT NULL CHECK(Value >= 0 and Value <= 5)
);
 
-- Table: OPERATING_SYSTEM
CREATE TABLE OPERATING_SYSTEM (
Name TEXT NOT NULL,
Source_Model TEXT NOT NULL
CHECK(Source_Model = 'Open Source' or Source_Model = 'Closed Source'),
Website TEXT
);
 
-- Table: OPERATE
CREATE TABLE OPERATE (
App_ID TEXT NOT NULL,
Operating_System_Name TEXT NOT NULL
);
 
-- Table: SMARTPHONE_VENDOR
 
CREATE TABLE SMARTPHONE_VENDOR (
Name TEXT NOT NULL, 
Location TEXT NOT NULL, 
Founder TEXT
);
 
-- Table: USE
 
CREATE TABLE USE(
Smartphone_Vendor_Name VARCHAR NOT NULL,
Operating_System_Name  VARCHAR NOT NULL
);