| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-12-10, 10:17
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 3
|
|
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.
|
|
03-12-10, 10:28
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
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?
|
|
03-12-10, 10:32
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
|
|
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.
|
|
03-12-10, 10:47
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 3
|
|
Thanks for the response.
Quote:
|
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?
Quote:
|
UNIQUE is not required if you define the column as PRIMARY KEY.
|
Thanks, it's very useful niggle
Quote:
|
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.
|
|
03-12-10, 10:51
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
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
|
|
03-12-10, 13:28
|
|
Registered User
|
|
Join Date: Jun 2004
Location: Long Island
Posts: 696
|
|
"niggle"? - watch your language 
|
|
03-12-10, 17:13
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 3
|
|
|
|
03-12-10, 19:21
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,407
|
|
Quote:
Originally Posted by kameleon
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))
|
|
03-13-10, 04:59
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Quote:
Originally Posted by shammat
SQL Server was not able to use a sub-select in a CHECK constraint
|
Correct. Although there is a way to implement this.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Creating simple database
