Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Question Unanswered: Creating simple database

    Hi guys,

    It's my first thread here and I'm not completely sure that it's the right place to post.

    I've had to do simple database with constraints (wherever they are possible). Database contains: Student, Course and association class named Pass. Here it is the sql code which I've written. Please check it out if constraints which I've made are good and enough.

    Code:
    CREATE TABLE Students(
    	idNumber VARCHAR( 6 ) NOT NULL UNIQUE,
    	name VARCHAR( 30 ) NOT NULL,
    	surname VARCHAR( 35 ) NOT NULL,
    	birthDate DATE NOT NULL CHECK (
    		birthDate > '1950-01-01' AND birthDate < YEAR(GETDATE()) - 18
    	),
    	CONSTRAINT pk_students PRIMARY KEY( idNumber )
    )
    
    CREATE TABLE Courses (	
    	idCourse VARCHAR( 7 ) NOT NULL UNIQUE DEFAULT 'Z00-00x',	
    	name VARCHAR( 30 ) NOT NULL,
    	CONSTRAINT pk_courses PRIMARY KEY( courseCode )
    	);
    	
    CREATE TABLE Passes(
    	idCourse VARCHAR( 7 ) NOT NULL REFERENCES Courses,	
    	idNumber VARCHAR( 6 ) NOT NULL REFERENCES Students,
    	passDate DATE CHECK (
    		SELECT birthDate FROM Studenci WHERE passDate > birthDate AND passDate <= GETDATE()
    	),
    	grade REAL DEFAULT 5.0 CHECK (
    		grade = 2.0 OR 
    		grade = 3.0 OR 
    		grade = 3.5 OR 
    		grade = 4.0 OR 
    		grade = 4.5 OR 
    		grade = 5.0 OR 
    		grade = 5.5
    	),
    	CONSTRAINT pk_Passes PRIMARY KEY(idCourse, idNumber)
    )
    p.s sorry for my English but I'm still learning.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Broadly good, but some errors. In particular, there will be a compilation error - that is not valid T-SQL DDL. Is your homework to produce ISO SQL DDL?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Some other stuff:
    UNIQUE is not required if you define the column as PRIMARY KEY.
    I would not use a floating point data type for grade.

    Some other things I might query but really the meat of it is fine.

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    Thanks for the response.
    In particular, there will be a compilation error - that is not valid T-SQL DDL. Is your homework to produce ISO SQL DDL?
    To be sure, I have no idea what ISO SQL DDL is and this term is not connected with my homework. I'm gonna compile it in SQL Server 2008 Developer Edition, but I don't have it installed yet (a lot of problems with installation in Windows 7 machine). Will be the compilation error in this environment? If yes, where?
    UNIQUE is not required if you define the column as PRIMARY KEY.
    Thanks, it's very useful niggle
    I would not use a floating point data type for grade.
    So what would you recommend? Notice, that 3.5, 4.5 values are required.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) that is good aircode for a student
    2) ISO determine the SQL standard - it was previously known as ANSI SQL
    3) T-SQL only allows scalar expressions in check constraints, not set based ones
    4) I would use the NUMERIC fixed precision data type

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    "niggle"? - watch your language

  7. #7
    Join Date
    Mar 2010
    Posts
    3
    @pootle flump - thank you I'm gonna check the 4th point and change this REAL type

    @PMASchemd - what's wrong with this word? - niggle - Definition from Longman English Dictionary Online

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by kameleon View Post
    CREATE TABLE Passes(
    idCourse VARCHAR( 7 ) NOT NULL REFERENCES Courses,
    idNumber VARCHAR( 6 ) NOT NULL REFERENCES Students,
    passDate DATE CHECK (
    SELECT birthDate FROM Studenci WHERE passDate > birthDate AND passDate <= GETDATE()
    ),
    grade REAL DEFAULT 5.0 CHECK (
    grade = 2.0 OR
    grade = 3.0 OR
    grade = 3.5 OR
    grade = 4.0 OR
    grade = 4.5 OR
    grade = 5.0 OR
    grade = 5.5
    ),
    CONSTRAINT pk_Passes PRIMARY KEY(idCourse, idNumber)
    )
    Did you run these statements?
    Last time I checked, SQL Server was not able to use a sub-select in a CHECK constraint (actually I don't think any DBMS can do that).
    Would be a pretty nifty feature though...

    Is there really no 1.0 grade?

    The check for the grade can be shortened by the way:
    Code:
    check (grade in (2.0, 3.0, 3.5, 4.0, 4.5, 5.0, 5.5))

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by shammat View Post
    SQL Server was not able to use a sub-select in a CHECK constraint
    Correct. Although there is a way to implement this.

Posting Permissions

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