May 3, 2009 at 1:05 pm
-- Create a Database table to represent the "child" entity.
CREATE TABLE child(
childIDINTEGER,
FirstNameVARCHAR(25) NOT NULL,
LastNameVARCHAR(25) NOT NULL,
AdressVARCHAR(25) NOT NULL,
fk_carerIDINTEGER,
-- Specify the PRIMARY KEY constraint for table "child".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_child PRIMARY KEY (childID)
) TYPE=INNODB;
-- Create a Database table to represent the "room" entity.
CREATE TABLE room(
RoomIDINTEGER,
RoomNAMEINTEGER NOT NULL,
RoomSizeVARCHAR(25) NOT NULL,
fk_childIDINTEGER,
-- Specify the PRIMARY KEY constraint for table "room".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_room PRIMARY KEY (RoomID)
) TYPE=INNODB;
-- Create a Database table to represent the "carer" entity.
CREATE TABLE carer(
carerIDINTEGER,
carerNameVARCHAR(25) NOT NULL,
-- Specify the PRIMARY KEY constraint for table "carer".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_carer PRIMARY KEY (carerID)
) TYPE=INNODB;
-- Create a Database table to represent the "payement" entity.
CREATE TABLE payement(
payementIDVARCHAR(8),
carerID*INTEGER,
childID*INTEGER NOT NULL,
carerAdressVARCHAR(8) NOT NULL,
fk_carerIDINTEGER,
-- Specify the PRIMARY KEY constraint for table "payement".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_payement PRIMARY KEY (payementID)
) TYPE=INNODB;
-- Create a Database table to represent the "medication" entity.
CREATE TABLE medication(
medicationIDINTEGER,
medicationNameVARCHAR(25) NOT NULL,
medicationTypeVARCHAR(25) NOT NULL,
fk_carerIDINTEGER,
-- Specify the PRIMARY KEY constraint for table "medication".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_medication PRIMARY KEY (medicationID)
) TYPE=INNODB;
-- Create a Database table to represent the "rota" entity.
CREATE TABLE rota(
staffIDINTEGER,
timeINTIME NOT NULL,
timeOUTTIME NOT NULL,
rotaDATEDATE NOT NULL,
RoomIDINTEGER,
-- Specify the PRIMARY KEY constraint for table "rota".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_rota PRIMARY KEY (staffID)
) TYPE=INNODB;
-- Create a Database table to represent the "staff" entity.
CREATE TABLE staff(
StaffIDINTEGER,
FirstNameVARCHAR(25) NOT NULL,
StaffAdressVARCHAR(25) NOT NULL,
LastNameINTEGER NOT NULL,
-- Specify the PRIMARY KEY constraint for table "staff".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_staff PRIMARY KEY (StaffID)
) TYPE=INNODB;
-- Create a Database table to represent the "activities" entity.
CREATE TABLE activities(
ActivityIDINTEGER,
ActivityNameVARCHAR(25) NOT NULL,
RoomID*INTEGER NOT NULL,
fk_RoomIDINTEGER,
-- Specify the PRIMARY KEY constraint for table "activities".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINTpk_activities PRIMARY KEY (ActivityID)
) TYPE=INNODB;
-- Create a Database table to represent the "staff/rota" entity.
CREATE TABLE staff/rota(
StaffIDINTEGER,
TimeIN*TIME NOT NULL,
TimeOUT*TIME NOT NULL,
May 3, 2009 at 3:23 pm
WHAT is your question?
Are you sure you're running SS2K5? (TYPE=INNODB and some other synthax throws a syntax errors - you won't even get to any permission check with the syntax you provided...)
Please post sample data as described in http://www.sqlservercentral.com/articles/Best+Practices/61537/.
Please note that the easier you make it for us to understand what issues you're struggling with the earlier someone is going to reply...
May 4, 2009 at 9:51 am
The code you posted looks like it is targeted to MySQL not MS SQL Server. As Lutz said "Type=INNODB" is not valid in SQL Server and prior to SQL Server 2008 there are not TIME and DATE data types in SQL Server there is just SMALLDATETIME and DATETIME.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 4, 2009 at 12:42 pm
Yup this is MySQL Code you are posting in the wrong forums!!!
* Noel
May 4, 2009 at 1:06 pm
no was sql server 2k5
i draw an erd diagram in qsee superlite and it generate
but my real question is i can't create any table this sql server watever i do
it always says acces denied in master
May 4, 2009 at 1:15 pm
Some thoughts:
First, you shouldn't use master database for your application data. Create a separate database.
Second, if you don't have any access to the master database you might have an issue with your login credentials. What are you logged in as? Please run [select current_user] in SSMS to see "who you are".
Third, what experience do you have with SS2K* (in order to know what information is most useful to you)?
May 4, 2009 at 1:20 pm
Well, first of all the code you have posted will NOT run successfully in SQL Server 2005 because it is using MySQL syntax and data types.
Next you should not be created tables in master. You need to create a user database, change to the context of the created database and then create tables. Like this:
CREATE DATABASE [MyDatabase] ON PRIMARY
( NAME = N'MyDatabase', FILENAME = N'C:\Data\MyDatabase.mdf' , SIZE = 20480KB , MAXSIZE = 512000KB , FILEGROWTH = 10240KB )
LOG ON
( NAME = N'MyDatabase_log', FILENAME = N'C:\Logs\MyDatabase_log.ldf' , SIZE = 32768KB , MAXSIZE = 102400KB , FILEGROWTH = 2048KB );
-- GO is a batch separator
GO
-- this changes your database context to the newly created database
Use MyDatabase;
Lastly you will to be in at least the dbcreator fixed server-role to create a database. Once you have created the database you will be the "owner" of the database and then be able to create the tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 4, 2009 at 2:15 pm
thank dude
i know i don't have much experience lukz
thats why i'm here to learn more
May 4, 2009 at 2:27 pm
Did you get a chance to figure out "who you are"? (see my post above)
PS: I have the strong feeling that most of the folks around here would find it inappropriate to be "dude'd".
Just consider this forum as being the company you're working for - with just over a million employees. 🙂
Question: How many of them you actually would call "dude"? 😉
Edited: typo fixed
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply