Database

6-Jul-09 3:13

Hi Guys

Heres a nice easy one for you DBA's out there to settle my curiosity. (Not sure if this is SQL Specific)

Up until recently, I thought that a database schema described the database objects (tables, fields, data types, relationships etc), where the schema is really a way of orgainising and securing data with a prefix. (eg. to restrict a table of orders to just sales, a schema of sales is created and only sales people will be granted access. The table name will be Sales.Orders)

My question is, what is what I originally thought was a schema called? (the description of the database)

Thanks... Big Grin | :-D

oooo, the Jedi's will feel this one....

DoctorMick6-Jul-09 6:24

6-Jul-09 6:24

Metadata?

David Mujica6-Jul-09 10:23

David Mujica

6-Jul-09 10:23

You are basically correct in your original thought.

"... the schema defines the tables, the fields in each table, and the relationships between fields and tables." (Wikipedia)

What you got confused is database object security. Something like Sales.Orders would indicate that there is a table,Orders, and it's owner is Sales.

To add to the confusion is the fact that Oracle calls things "schemas" within one database. So you can create a schema for Sales and it will hold all of the objects owned by Sales. Ugh. Have I confused you yet ?

Basically, you were right in the first place. Just don't confuse schemas with how database engines provide security to various objects. (tables,views,procedures,etc)

david
fluent in both MS-SQL and Oracle

Paul Unsworth6-Jul-09 21:42

6-Jul-09 21:42

Gotcha... So it's kind of dependent on database brand?

As with SQL, when you create a schema object, and assign a table to that schema to create Sales.Orders, this schema is more of a way of organising and defining ownership, rather than security?

I'm trying to get into databases a bit more. It seems a pretty meaty subject...

oooo, the Jedi's will feel this one....

David Mujica7-Jul-09 2:57

David Mujica

7-Jul-09 2:57

Well ... defining ownership implicitly has security implications right?

User "Sales" has full access to table Sales.Order, but user "A" will not have access to this table unless specifically granted.

Are you looking to develop databases for use with a website or a client-server windows application ? The reason for this is that with a website or N-Tier applicaiton typically the database and all of its objects are owned by "dbo" (database owner), you then create 1 user "myappuser" that is granted access to that database and you build your security in the application, not the database. The user, "myappuser" has full access to all aspects of the database and your individual users may need only read access to most tables and update to a select few.

Think about a large company with 10,000 users. Would you want the database administrator to have to create 10,000 user accounts and have 10,000 user connections when the system is running ? Nope. You have 1 user connected to the database and he makes all of the requests on behalf of the users. (This is a very simple description, but you should get the idea.)

CodeProject is a great resource for learning and the community is very helpful.

Keep asking questions and have fun learning. Smile | :)

David

Paul Unsworth7-Jul-09 3:14

Along with security, consider auditing

7-Jul-09 3:14

That is great. Thank you.

I'm mainly looking at building databases for applications. They will be large and complex databases, so I was trying to find ways of simplifiying things. As you said, as users will be using the application to make use of the databases, there would be no reason to complecate things by creating lots of users and schema's (as in the db owner), and to have one that the applicaiton controls.

That in itself saved me some potential headaches. Thanks. Big Grin | :-D

oooo, the Jedi's will feel this one....

David Mujica7-Jul-09 3:25

David Mujica

7-Jul-09 3:25

Remember that when you have one user connected to the database and your security is controlled by your application you cannot use triggers to build an auditing system. From the trigger's perspective, there is always one user making changes to the database. If your application needs auditing, remember that you will have to pass the applicaiton logon name through your routines to log it in your audit tables.

Also consider how you would handle security for reporting. You don't want anyone connecting to your database with a third-party tool because it circumvent any security you may have built.

You can solve this by:
1) Don't allow anyone else but your application to connect to the database
2) Create special reporting views or stored procedures that can be used by a third-party tool and still conform to your security requiremnets.

Jeez, lots of things to think about right? Can you tell that I've made a few mistakes in datatbase/application design in my past? I've got the lumps and gray hairs to show for it. Cool | :cool:

Re: Along with security, consider auditing

Paul Unsworth7-Jul-09 4:28

7-Jul-09 4:28

There is a lot to think about. Way more involved than I first thought. All good though. Creating something so complex (provided it all works ok) must give real job satisfaction. I've only done basic database stuff in the past, queries, basic stored procs. I've not really thought about automated auditing, and I didn't realise you could use triggers. This is good stuff.

Sounds like you have plenty of experiance in this game. I have to say, I can see database design and administration playing quite a major part in my career. Big Grin | :-D

oooo, the Jedi's will feel this one....

IF else or Case when

Vimalsoft(Pty) Ltd6-Jul-09 2:34

Vimalsoft(Pty) Ltd

6-Jul-09 2:34

Good Day all

i have a two Tables MTM_ACTV_STAFF_CYCLE and MTM_ACTV_STAFF

MTM_ACTV_STAFF

ID | ACTV | STAFF | CYCLETEMPLATE
==================================
1  | 1    | 3     | 3
2  | 4    | 2     | Null

and

MTM_ACTV_STAFF_CYCLE  

ID | IDL  | CYCLE   
=========================
1  | 1    | 3     
2  | 4    | 2

i have the Following SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[sp_Get_Staff_Cycles] 
(
@Subject_Descr varchar(30)
)
AS
--Check if the table Exists 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]
--Get the Cycle template description
Declare @CycletemplateTerm varchar(60)
set @CycletemplateTerm = (  select top 1 t.descr from MTM_ACTV_STAFF mtn
							inner join tbl_actv a on a.id  = MTN.actv
							inner join tbl_cntc cc on a.cntcID = cc.id
							inner join tbl_modl m on m.id = cc.modlid
							inner join tbl_term t on a.term = t.id 
							LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
							ON C.IDL = MTN.ID
							WHERE M.DESCR = @Subject_Descr
						)
 
SELECT [ID] = IDENTITY (int,1,1),A.DESCR AS [ACTIVITY], S.DESCR,
CASE MTN.CYCLETEMPLATE WHEN NULL THEN  C.CYCLES
ELSE @CycletemplateTerm
END AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
inner join tbl_actv a on a.id  = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr

now in the above Sp i have the Following Problem. In this Line of code

CASE MTN.CYCLETEMPLATE WHEN NULL THEN  C.CYCLES
ELSE @CycletemplateTerm
END AS CYCLETEMPLATE

If the field CYCLETEMPLATE in MTM_ACTV_STAFF table is null i want to use the Values found in table MTM_ACTV_STAFF_CYCLE and Field CYCLES
and another condition again, if there is a value in the MTM_ACTV_STAFF and on the CYCLETEMPLATE then
use the Value in the Variable @CycletemplateTerm

Now my Problem is that instead of bringing 3 its Brings a Sting "Year". there is Prbably something Wrong in my Condiution. Can you please help me in that Regard

Thanks

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: IF else or Case when

DoctorMick6-Jul-09 6:28

SQL Stored Procedure INSERT with random records from a 2nd table [modified]

6-Jul-09 6:28

When you're selecting into @cycletemplateterm you're setting it to t.descr, which I'd assume is where the text "Year" is coming from. Have another look and check which table and column should be populating the variable.

Re: IF else or Case when

Vimalsoft(Pty) Ltd6-Jul-09 19:58

Vimalsoft(Pty) Ltd

6-Jul-09 19:58

Good Morning

i have changed it to this and it works

ALTER PROC [dbo].[sp_Get_Staff_Cycles]  
(
@Subject_Descr varchar(30)
)
AS
--Check if the table Exists 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]
--Get the Cycle template description
Declare @CycletemplateTerm varchar(60)
set @CycletemplateTerm = (  select top 1 t.descr from MTM_ACTV_STAFF mtn
							inner join tbl_actv a on a.id  = MTN.actv
							inner join tbl_cntc cc on a.cntcID = cc.id
							inner join tbl_modl m on m.id = cc.modlid
							inner join tbl_term t on a.term = t.id 
							LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
							ON C.IDL = MTN.ID
							WHERE M.DESCR = @Subject_Descr
						)
 
SELECT [ID] = IDENTITY (int,1,1),A.DESCR AS [ACTIVITY], S.DESCR,
CASE
WHEN MTN.CYCLETEMPLATE IS NULL THEN  C.CYCLES
WHEN MTN.CYCLETEMPLATE IS NOT NULL THEN @CycletemplateTerm 
END AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
inner join tbl_actv a on a.id  = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr

Thank you

Select null value

yesu prakash6-Jul-09 2:20

yesu prakash

6-Jul-09 2:20

Hi,
How to select a row which contain null where i have a condition also.

eg:
field NUMBER have -1,null,1,2,3,4 values
how can i select the value excluding -1

i give a condition
select * from table where NUMBER <> -1,
its returning only 1,2,3,4
I want null row also

Regards,
YPKI

Re: Select null value

Eddy Vluggen6-Jul-09 3:28

Eddy Vluggen

6-Jul-09 3:28

SELECT * 
  FROM [table]
 WHERE [number] <> -1
    OR [number] IS NULL

I are troll Smile | :)

Re: Select null value

Niladri_Biswas6-Jul-09 15:51

Niladri_Biswas

6-Jul-09 15:51

SELECT *
FROM dbo.tblDisplayPositive
WHERE FieldNumber >= 0
OR FieldNumber IS NULL

Hope this helps
Smile | :)

Niladri Biswas

munklefish6-Jul-09 0:46

munklefish

6-Jul-09 0:46

Hi,

I need to submit data from a form to an SQL2005 database via a stored procedure. The difficult part is that i also need to get 5 random records from a secondary table and insert these as part of the inserted record in table 1.

My structure is akin to this:

Tbl_Campaigns (table to be inserted to)

Key | field_1 | field_2 | field_3 | field_4 | field_5 etc

Tbl_Organisations (table to get the 5 random records from)

Key | field_1

I need to get 5 unique / random records from 'tbl_Organisations', catch them and insert them into a single record along with the data input to the stored procedure. I understand arrays arent an option in SQL2005(?).

So how do i catch these records and then insert them as a single record along with the SP Inputs?

Any help would be greatly appreciated as i am trying my best to get to grips with the complexities of SQL

Thanks.

modified on Tuesday, July 7, 2009 6:04 AM

Re: SQL Stored Procedure INSERT with random records from a 2nd table

DoctorMick6-Jul-09 1:05

Re: SQL Stored Procedure INSERT with random records from a 2nd table

6-Jul-09 1:05

This may be useful....

select top 5 key, field_1 from tbl_organisations order by 100 * rand()

However, I haven't tested it cos I don't have SQL Server here so it may not work at all! Laugh | :laugh:

munklefish6-Jul-09 1:09

munklefish

6-Jul-09 1:09

Hi,

I would get a random record using the following:

SELECT TOP 5 (*) from tbl_organisations AS randomOrgs

However my issue is with how i would then catch these 5 records and insert them into the other table as a single record with the SP inputs.

Re: SQL Stored Procedure INSERT with random records from a 2nd table

Blue_Boy6-Jul-09 6:11

Blue_Boy

6-Jul-09 6:11

SELECT TOP 5 (*) from tbl_organisations order by newid()

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: SQL Stored Procedure INSERT with random records from a 2nd table

munklefish7-Jul-09 0:04

munklefish

7-Jul-09 0:04

Blue_boy,

Please read the original post in this thread. Thanks for your help but you appear to have misinterpretated my question.

i neeed help

mahmoudinirat5-Jul-09 23:32

mahmoudinirat

5-Jul-09 23:32

i have two tables the first has foreign to another like this
table1:
table1Id table1Name table2Id
1 mahm
2 kha 1

table2:
table2Id table2Name
1 cc
2 dd

i need the following result
table1Id table1Name table2Name
1 mahm
2 kha cc

Re: i neeed help

DoctorMick6-Jul-09 0:00