Create a database and add tables

Question

I am new to sql so I have a problem where I am asked to create a database for a city. The database I created with DB Browser.

The database code is below. When I try to use the database to add anything to it or even see if it is created successfully I get an error:

SQL logic error near "DATABASE".

I am not sure what is wrong with the code I wrote.

CREATE DATABASE City;
CREATE TABLE IF NOT EXISTS "OfficialEmployee" (
    "EID"   INTEGER NOT NULL,
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL,
    "BirthDate" TEXT NOT NULL,
    "City"  TEXT NOT NULL,
    "StreetName"    TEXT NOT NULL,
    "Number"    INTEGER NOT NULL,
    "Door"  INTEGER NOT NULL,
    "StartWorkingDate"  TEXT NOT NULL,
    "Degree"    TEXT NOT NULL,
    "DID"   TEXT NOT NULL,
    PRIMARY KEY("EID"),
    FOREIGN KEY("DID") REFERENCES "Department"("DID") On delete cascade on update cascade
);

CREATE TABLE IF NOT EXISTS "ConstructorEmployee" (
    "EID"   INTEGER NOT NULL,
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL,
    "BirthDate" TEXT NOT NULL,
    "City"  TEXT NOT NULL,
    "StreetName"    TEXT NOT NULL,
    "Number"    INTEGER NOT NULL,
    "Door"  INTEGER NOT NULL,
    "CompanyName"   TEXT NOT NULL,
    "SalaryPerDay"  INTEGER NOT NULL,
    PRIMARY KEY("EID")
);

CREATE TABLE IF NOT EXISTS "Employee_Phones" (
    "EID"   INTEGER NOT NULL,
    "CellPhoneNumber"   INTEGER NOT NULL,
    PRIMARY KEY("EID","CellPhoneNumber")
);


CREATE TABLE IF NOT EXISTS "Department" (
    "DID"   INTEGER NOT NULL,
    "Name"  TEXT NOT NULL,
    "Description"   TEXT NOT NULL,
    "ManagerID" INTEGER,
    PRIMARY KEY("DID"),
    FOREIGN KEY("ManagerID") REFERENCES "OfficialEmployee"("EID") On Update cascade on delete no action
);

CREATE TABLE IF NOT EXISTS "Neighborhood" (
    "NID"   INTEGER NOT NULL,
    "Name"  TEXT NOT NULL,
    PRIMARY KEY("NID")
);

CREATE TABLE IF NOT EXISTS "Apartment" (
    "StreetName"    TEXT NOT NULL,
    "Number"    INTEGER NOT NULL,
    "Door"  INTEGER NOT NULL,
    "Type"  TEXT NOT NULL,
    "SizeSquareMeter"   INTEGER NOT NULL,
    "NID"   INTEGER NOT NULL,
    FOREIGN KEY("NID") REFERENCES "Neighborhood"("NID") on delete no action on update no action,
    PRIMARY KEY("Door","StreetName","Number")
);


CREATE TABLE IF NOT EXISTS "Project" (
    "PID"   INTEGER NOT NULL,
    "Name"  TEXT NOT NULL,
    "Description"   TEXT NOT NULL,
    "Budget"    INTEGER NOT NULL,
    "NID"   INTEGER NOT NULL,
    PRIMARY KEY("PID"),
    FOREIGN KEY("NID") REFERENCES "Neighborhood"("NID") On Update no action on delete no action
);

CREATE TABLE IF NOT EXISTS "ProjectConstructorEmployee" (
    "EID"   INTEGER NOT NULL,
    "PID"   INTEGER NOT NULL,
    "StartWorkingDate"  TEXT NOT NULL,
    "EndWorkingDate"    TEXT,
    "JobDescription"    TEXT NOT NULL,
    FOREIGN KEY("PID") REFERENCES "Project"("PID") On Update Cascade on Delete No Action,
    FOREIGN KEY("EID") REFERENCES "ConstructorEmployee"("EID") On Update Cascade On Delete No Action
);

CREATE TABLE IF NOT EXISTS "Resident" (
    "RID"   INTEGER NOT NULL,
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL,
    "BirthDate" TEXT NOT NULL,
    "StreetName"    TEXT NOT NULL,
    "Number"    INTEGER NOT NULL,
    "Door"  INTEGER NOT NULL,
    "CatalogID" INTEGER NOT NULL,
    PRIMARY KEY("RID"),
    FOREIGN KEY("Door") REFERENCES "Apartment"("Door") On update cascade on delete no action,
    FOREIGN KEY("StreetName") REFERENCES "Apartment"("StreetName") On Update cascade on delete no action,
    FOREIGN KEY("Number") REFERENCES "Apartment"("Number") On Update cascade on delete no action
);

CREATE TABLE IF NOT EXISTS "TrashCan" (
    "CatalogID" INTEGER NOT NULL,
    "CreationDate"  TEXT NOT NULL,
    "ExpirationDate"    TEXT NOT NULL CHECK(ExpirationDate>CreationDate),
    PRIMARY KEY("CatalogID")
);
CREATE TABLE IF NOT EXISTS "ParkingArea" (
    "AID"   INTEGER NOT NULL,
    "Name"  TEXT NOT NULL,
    "PricePerHour"  INTEGER NOT NULL,
    "MaxPricePerDay"    INTEGER NOT NULL,
    "NID"   INTEGER NOT NULL,
    PRIMARY KEY("AID"),
    FOREIGN KEY("NID") REFERENCES "Neighborhood"("NID") On Update cascade on delete cascade
);

CREATE TABLE IF NOT EXISTS "CarParking" (
    "CID"   INTEGER NOT NULL,
    "StartTime" TEXT NOT NULL,
    "EndTime"   TEXT NOT NULL CHECK(StartTime<EndTime),
    "Cost"  INTEGER NOT NULL CHECK(MaxPricePerDay>Cost),
    "AID"   INTEGER NOT NULL,
    "MaxPricePerDay"    INTEGER NOT NULL,
    PRIMARY KEY("CID","StartTime"),
    FOREIGN KEY("CID") REFERENCES "Cars"("CID") On Update Cascade on delete cascade,
    FOREIGN KEY("AID") REFERENCES "ParkingArea"("AID") On Delete set null on update cascade
);

SQLite does not support `CREATE Database`. Check this: https://alvinalexander.com/android/sqlite-how-create-sqlite-database for other options. — forpas, Dec 06 '19 at 13:40

score 0 · Answer 1 · answered Dec 06 '19 at 17:52

0

I don't believe you can create a database from within a database in SQLite

Just do this, on the command line:

UNIX: $ touch my.db

Windows: c:> type nul > my.db

Then you can open the database using DB Browser

answered Dec 06 '19 at 17:52

Neil McGuigan

46,580
12
123
152

Create a database and add tables

1 Answers1