|
i dont have enugh posts so i couldnt post the image.
please copy that link to your browser
picasaweb.google.com/107426447530298612514/December22011#5681470995564484754
hi
i have been given this ERD (cant be changed)
i made this table model
i am not sure it is perfect please give me some feedback and suggestions
CREATE TABLE person(
ID INTEGER
,[user_name] CHARACTER(15) NOT NULL
,[name] CHARACTER(15) NOT NULL
,[password] CHARACTER(15) NOT NULL
,PRIMARY KEY (ID)
,UNIQUE([user_name])
);
CREATE TABLE casual(
ID INTEGER
,num_of_logins INTEGER
,last_login DATETIME
,PRIMARY KEY (ID)
,FOREIGN KEY (ID) REFERENCES person
);
CREATE TABLE site_type(
theType CHARACTER(15) PRIMARY KEY
,Category CHARACTER(15)
,description TEXT(100)
);
CREATE TABLE experts(
ID INTEGER
,outsorce BINARY
,theType CHARACTER(15) NOT NULL
,PRIMARY KEY (ID)
,FOREIGN KEY (ID) REFERENCES person
,FOREIGN KEY (theType) REFERENCES site_type(theType)
);
CREATE TABLE site(
siteID INTEGER PRIMARY KEY
,geometry TEXT(100) NOT NULL
,site_name CHARACTER(15) NOT NULL
,avg_casual_rating DOUBLE
,avg_expert_rating DOUBLE
,theType CHARACTER(15) NOT NULL
,FOREIGN KEY (theType) REFERENCES site_type(theType)
);
CREATE TABLE POI(
POI_ID INTEGER UNIQUE NOT NULL
,point TEXT(100) NOT NULL
,POI_name CHARACTER(15) NOT NULL
,expected_visitors INTEGER
,Last_year_visitors INTEGER
,siteID INTEGER UNIQUE NOT NULL
,FOREIGN KEY (siteID) REFERENCES site(siteID)
,PRIMARY KEY (siteID,POI_ID)
);
CREATE TABLE rate(
ID INTEGER
,siteID INTEGER
,notes TEXT(100)
,[date] DATETIME
,rate TINYINT
,CHECK ((rate>=0) AND (rate<=100))
,FOREIGN KEY (siteID) REFERENCES site(siteID)
,FOREIGN KEY (ID) REFERENCES person(ID)
,PRIMARY KEY(siteID,ID)
);
CREATE TABLE tourism_site(
siteID INTEGER PRIMARY KEY
,closing_time CHARACTER(5) NOT NULL
,opening_time CHARACTER(5) NOT NULL
,avg_visitors DOUBLE
,FOREIGN KEY (siteID) REFERENCES site(siteID)
,ID INTEGER NOT NULL
,FOREIGN KEY (ID) REFERENCES experts(ID)
,description TEXT(100) NOT NULL
,[date] DATETIME NOT NULL
);
CREATE TABLE road(
siteID INTEGER PRIMARY KEY
,num_of_lanes INTEGER
,speed_limit INTEGER
,FOREIGN KEY (siteID) REFERENCES site(siteID)
);
CREATE TABLE localities(
siteID INTEGER
,num_of_residents INTEGER
,roadID INTEGER NOT NULL
,FOREIGN KEY (siteID) REFERENCES site(siteID)
,FOREIGN KEY (roadID) REFERENCES road(siteID)
,PRIMARY KEY(siteID,roadID)
);
CREATE TABLE junction(
junctionID INTEGER PRIMARY KEY
,traffic_light BINARY
);
CREATE TABLE meets(
roadID1 INTEGER NOT NULL
,[connection] TEXT(100)
,roadID2 INTEGER NOT NULL
,junctionID INTEGER NOT NULL
,FOREIGN KEY (roadID1) REFERENCES road(siteID)
,FOREIGN KEY (roadID2) REFERENCES road(siteID)
,FOREIGN KEY (junctionID) REFERENCES junction
,PRIMARY KEY (junctionID,roadID1,roadID2)
);
|
