Results 1 to 3 of 3

Thread: ERD To Table

  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Question Unanswered: ERD To Table

    hi

    i have been given this ERD (cant be changed) (attached)
    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)
    );
    Attached Thumbnails Attached Thumbnails 1234.jpg  

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    What's an ERD?

    I don't know how to read that flow diagram properly.

    You're also setting up multiple primary keys... Which is a problem, as a table can only have one primary key. Thus the use of the word 'Primary', Primary | Define Primary at Dictionary.com
    Looking for the perfect beer...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kez1304 View Post
    You're also setting up multiple primary keys... Which is a problem, as a table can only have one primary key.
    are you referring to this --
    Code:
    PRIMARY KEY (siteID,POI_ID)
    that's perfectly okay, dude, that's a single PK consisting of multiple columns

    you did not know that was allowed, did you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •