sql server db: figure

Showing posts with label figure. Show all posts

Tuesday, March 27, 2012

Correct Schema for this business problem?

Here are the tables:

Reservation <----Customer_Reservation <--->Customers

GolfCourses <-----Customer_Golfing

TeeTimes <------Customer_TeeTime

Ok so here is the business rules. A reservation will be assigned one or
more customers. A reservation has one or more golfcourses. Golfcourses
will be assigned people. These people can only come from those people
assigned to the reservation that the golfcourse belongs to. GolfCourses
have one or more teetimes. TeeTimes will be assigned people. Those
people can come from only the people assigned to the golfcourse that
the teetime belongs to.

Basically, how can I setup the schema to enforce the business rules? I
can see only to do this at the application level. Maybe I have modeled
this incorrectly?Blast wrote:

Quote:

Originally Posted by

I need help modeling schema for a particular issue that i've never run
across before. And for the life of me, I cannot figure out how to model
it - at least in a way that feels correct. Please let me know if I need
to post this else where.
>
Here are the tables:
>
Reservation <----Customer_Reservation <--->Customers
>
>
>
GolfCourses <-----Customer_Golfing
>
>
TeeTimes <------Customer_TeeTime
>
>
Ok so here is the business rules. A reservation will be assigned one or
more customers. A reservation has one or more golfcourses. Golfcourses
will be assigned people. These people can only come from those people
assigned to the reservation that the golfcourse belongs to. GolfCourses
have one or more teetimes. TeeTimes will be assigned people. Those
people can come from only the people assigned to the golfcourse that
the teetime belongs to.
>
Basically, how can I setup the schema to enforce the business rules? I
can see only to do this at the application level. Maybe I have modeled
this incorrectly?

You didn't specify any keys so this is pure speculation:

CREATE TABLE CustomerReservations
(CustomerNo INT NOT NULL REFERENCES Customers (CustomerNo),
ReservationNo INT NOT NULL REFERENCES Reservations (ReservationNo),
PRIMARY KEY (CustomerNo,ReservationNo));

CREATE TABLE CourseReservations
(CourseNo INT NOT NULL REFERENCES Courses (CourseNo),
ReservationNo INT NOT NULL REFERENCES Reservations (ReservationNo),
PRIMARY KEY (CourseNo,ReservationNo));

CREATE TABLE CustomerCourseReservations
(CustomerNo INT NOT NULL,
CourseNo INT NOT NULL,
ReservationNo INT NOT NULL,
FOREIGN KEY (CourseNo,ReservationNo) REFERENCES CourseReservations,
FOREIGN KEY (CustomerNo,ReservationNo) REFERENCES
CustomerReservations);

... etc

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Blast wrote:

Quote:

Originally Posted by

You might get more help after you have posted your CREATE TABLE
statements. Make sure your constraints are included.

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Ok, i drew out your diagram and my design thoughts are exactly like
yours. And I was hoping it wouldn't be.

Let me explain. I'm using the windows form (.net) for my UI and there
are no controls that recognize this pattern, at least not right out of
the box (not to my knowledge).

For example, for the CourseReservations I want to have a comboBox that
lists all Customers in the reservation (CusomterReservations). Then
next to the customers name would be a checkbox, which would represent
existance of data entered into the CustomerCourseReservation. When you
toggle on and off the checkbox it would delete or add the entry into
the CustomerCourseReservations.

So. thats what I need to do keeping this schema. I was hoping I had
incorrectly created the schema and there was an easier way to model the
problem.

Thanks for your reply.|||http://www.condoresorts.com/problem.jpg
Hows that?|||
http://www.condoresorts.com/Problem.jpg
With a capital P|||Blast wrote:

Quote:

Originally Posted by

Ok, i drew out your diagram and my design thoughts are exactly like
yours. And I was hoping it wouldn't be.
>
>
Let me explain. I'm using the windows form (.net) for my UI and there
are no controls that recognize this pattern, at least not right out of
the box (not to my knowledge).
>

Obviously the UI code has nothing to do with constructing an effective
data model. The right solutions rarely come "out of the box".

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||>I need help modeling schema for a particular issue that i've never run across before. <<

I looked at your ER diagram. A good rule of thumb is that when it has
a cycle in it, you are in trouble. You also defined a lot of MVDs
which guarantee 5NF problems. You will also have what Tom Johnston
calls non-normal form redundancies. And the golf courses never appears
in the diagram.

I also had problems with your business rules:

Quote:

Originally Posted by

Quote:

Originally Posted by

> A reservation will be assigned one or more customers.

A reservation has one or more golfcourses. <<

Shouldn't a reservation put a group of cusotmers onto the SAME golf
course? You allow 5 poeple to play as a reservation on 10 different
courses.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Golfcourses will be assigned people. <<

Shouldn't that be the other way around? So I do not need a reservation
at all because I have my own course?

Quote:

Originally Posted by

Quote:

Originally Posted by

>These people can only come from those people assigned to the reservation that the golfcourse belongs to. <<

Very convoluted and inside out.

Quote:

Originally Posted by

Quote:

Originally Posted by

>GolfCourses have one or more teetimes. <<

No, a golf course has 18 holes with a par for each hole, etc. A
reservation is an event and events have times; a golf course is an
eneity and they have existence.

Quote:

Originally Posted by

Quote:

Originally Posted by

>TeeTimes will be assigned people. <<

So everyone in a resrevation can tee off at a different time, as well
as not beingon the same course?

Quote:

Originally Posted by

Quote:

Originally Posted by

>Those people can come from only the people assigned to the golfcourse that the teetime belongs to.<<

Very convoluted and inside out again.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Maybe I have modeled this incorrectly? <<

Yes. Let's try this:

A) Each reservation has
1) a single golf course - manditory
2) a single tee time - manditory
3) one or more customers - manditory
4) Reservations are identified by course and tee time

CREATE TABLE Customers
(customer_id INTEGER NOT NULL PRIMARY KEY,
customer_name VARCHAR(25) NOT NULL,
etc);

CREATE TABLE Golfcourses
(golfcourse_id CHAR(5) NOT NULL PRIMARY KEY,
golfcourse_name VARCHAR(25) NOT NULL,
etc.);

CREATE TABLE Parties
(party_name CHAR(25) NOT NULL PRIMARY KEY
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASACDE
ON DELETE CASACDE,
etc.);

CREATE TABLE Reservations
(golfcourse_id CHAR(5) NOT NULL
REFERENCES Golfcourses (golfcourse_id)
tee_time DATETIME NOT NULL,
party_name CHAR(25) NOT NULL
REFERENCES Parties (party_name)
ON UPDATE CASACDE
ON DELETE CASACDE,
PRIMARY KEY (golfcourse_id, tee_time));

A) I would think about getting the count of golfers ahead of your
current reservation and using a look-up table to compute the tee time
since you expect a larger party to play slower than a small one. You
can refine the estimates with actual data later.

Old joke:
wife to husband coming home from golf game: "You're back late! You go
out drinking?"
husband: "No. Eddie had a massive heart attack on the third hole."
wife: "I'm soory! That must have been awful!"
husband: "It was! What with hitting the ball and dragging the body, I
thought I'd never finish before sundown."

B) You will have to create a customer first, so I would allow for his
guests in the customer id scheme, thus:
12300 = 'Bobby Jones"
12301 = 'Bobby Jones - guest #1"
etc.
12312 = 'Bobby Jones - guest #12" or whatever the guest limit is

C) Since people tend to play with a regular group of friends (or
enemies), you build the Parties table entry next and retain them for
re-use. A single player is a party of one. You will need dummy party
identifiers to construct any ad hoc situations that might come up.

Does this do what you wanted?|||Well thanks for the reply. BUT. There is a reason why we want things
done the way we want things done. Let me explain.

Quote:

Originally Posted by

What are MVD's? We need the cycle. The problem is, we don't book the
golf directly. We're not a golf course. People who are in a reservation
don't always play golf. So, we assign people to a reservation. Some of
those people in the reservation might be playing golf, but they must be
in the reservation to play golf. Also, the tee times need be confirmed.
This isn't automatically done. So, we want to make sure the people
assigned to the golf course have tee times made. If they have no tee
times, then we need to be able to query that information becuase
someone needs to call the golf course and make the tee times.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

A reservation will be assigned one or more customers.

A reservation has one or more golfcourses. <<

No, it has 0 or more.

Quote:

Originally Posted by

Shouldn't a reservation put a group of cusotmers onto the SAME golf
course? You allow 5 poeple to play as a reservation on 10 different
courses.

Yeah, some people in the reservation will be playing golf, some won't.
2 people in the reservation might be playing 10 different golf courses.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Golfcourses will be assigned people. <<

>
Shouldn't that be the other way around? So I do not need a reservation
at all because I have my own course?

Yeah, we don't have our own course.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

These people can only come from those people assigned to the reservation that the golfcourse belongs to.

>Very convoluted and inside out.<<

Why is this inside out?

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

GolfCourses have one or more teetimes. <<

>
No, a golf course has 18 holes with a par for each hole, etc. A
reservation is an event and events have times; a golf course is an
eneity and they have existence.

Tee times are made in groups of 3 or 4 typically. So if you have a big
group, lets say 36 players you need many tee times.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

TeeTimes will be assigned people. <<

>
So everyone in a resrevation can tee off at a different time, as well
as not beingon the same course?

Yes, that's right.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Those people can come from only the people assigned to the golfcourse that the teetime belongs to.<<

>
Very convoluted and inside out again.

Yes this is right. You see, someone will put people in a reservation.
Weeks later can go by. Then, someone from that group might call in and
say, we want to play 3 different golf courses and we need these tee
times. But we can't give them tee times right away because maybe they
are reserving more then a year in a advance. So, we assign them to the
golf course and eventually make their tee times.

You see, I want to be able to query, how many reservations do we have
with people who aren't playing golf. How many people in reservations do
we have who are playing golf but dont have tee times. How do I answer
these questions with the ER diagram you gave me? I can't!

Let me further explain the business just in case anyone wants to
comment.

People make a reservation with us. We book golf, rental cars, other
activities, and rooms. Some people book and get a room, some people
book and just play golf through us. Some people just play activities
through us etc. So the relationship for these items are zero or more.
Since we don't actually own any of these items we have to first assign
people to the item and then assign them a time in which they are
confirmed for the event. We want to know who's assigned what, and then
who actually has been confirmed by the companies the event time.

So we start off by creating a group of people that belong to the
reservation. Then as they decide what they want to do, we start
assigning them different items. Those items need to have things
confirmed by third party organizations. Does that make sense?

Copying views

I have two databases A & B, I copied all of the tabels and data from A
to B. I can't figure out how to copy the Views and Stored procedures
from A to B. As you can see I'm new at this, can anyone point me in
the right direction? Oh... forgot running SQL 2000.

Thanks[posted and mailed, please reply in news]

Jim Davidson (raccoon@.icubed.com) writes:
> I have two databases A & B, I copied all of the tabels and data from A
> to B. I can't figure out how to copy the Views and Stored procedures
> from A to B. As you can see I'm new at this, can anyone point me in
> the right direction? Oh... forgot running SQL 2000.

If you want to make a complete copy of a database, there are certainly
easier ways to go. The below assumes that you run Query Analyzer:

exec sp_helpdb yourdb
-- Make notice of the values in the name and filename columns. (Cut
-- and paste to query window.)

BACKUP DATABASE yourdb TO DISK = 'C:\temp\yourbackup.bak'
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\temp\yourbackup.bak'
WITH MOVE 'yourdb' TO 'C:\MSSQL\Data\copyofyourdb.mdf',
MOVE 'yourdblog' TO 'C:\MSSQL\Data\copyofyourdb.ldf',
REPLACE
EXEC master..xp_cmdshell 'DEL C:\temp\yourbackup.bak'

In the RESTORE command replace yourdb and yourdblog with the values
from the name column in the sp_helpdb output, and in paths, replace
the directory paths with the value from the filename columns. You
must change the file name.

Note that RESTORE DATABASE creates the database if it does not exist.

You can also do the backup and restore stuff from Enterprise Manager,
but I am more confident with the T-SQL commands, so I cannot describe
those dialogs.

An alternative, is to use sp_detach_db, copy the database files and
then use sp_attach_db on the copy and the original.

As for the original question, the answer is that you should maintain
all your SQL objects under version control and reload from the source
there. You can also use the scripting facilities in Enterprise
Manager. Right-click the database, and select All Tasks and then
Generate SQL Scripts.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, March 25, 2012

Copying tables, finding number of columns in a table, & size of data in selected colum

I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo ]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
[ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.

> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'

> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'

> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapabl o.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>

Copying tables, finding number of columns in a table, & size of data in selected c

I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo
]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--mu
sic ]
[ http://www.2ra.org <--political | http://www.subuse.net <--di
scuss ]> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.

> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'

> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'

> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapablo.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>

Tuesday, March 27, 2012

Correct Schema for this business problem?

Copying views

Sunday, March 25, 2012

Copying tables, finding number of columns in a table, & size of data in selected colum

Copying tables, finding number of columns in a table, & size of data in selected c

sql server db

Blog Archive

About Me