Creating A Normalized Database
Apr 12, 2007
Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!
View 6 Replies
ADVERTISEMENT
Oct 26, 2001
I have an allergy table which has a patientid and an allergy id. i would like to create a view(or SQL statement) that will give me a crosstab of a patient and there allergies(like below)
PATID ALLERGY1 ALLERGY2 ALLERGY3 etc
100 MCS DAC004 DAC003
200 MCS DAC004
300 MCS DAC004 DAC003
The patients have from upto 9 allergies(but some may only have one or 2). Is there a way to do this?
Thanks
View 1 Replies
View Related
Aug 16, 2007
Hi there -
First of all I think of myself as a software developer and by no means a database expert. So I am posting a question here hoping that someone will lead me in the right direction.
I am somewhat following the database diagram from AdventureWorks with my own "normalized" database and have a question on how to update the table(s).
Here is my situation:
Table: Client
ClientID (PK)
Name
...
Table: Address
AddressID (PK)
AddressLine1
AddressLine2
City
...
Table: AddressType
AddressTypeID (PK)
Name
...
Table: ClientAddress
ClientID (PK1)
AddressID(PK2)
...
The tables should be referenced properly, etc... So my question is, when I add a new Client record, how and when do I update the Client table, Address table and ClientAddress table? If my PK on the Client and Address tables is an autoincriment, do I need it before I can update the ClientAddress table? If so, what do I have to look for, or how do I get the newly created ID to update the ClientAddress?
Is this making any sense? - I'm a little lost/confused. So if anyone can provide me links to where I can learn more about stuff like this and/or post some ideas/solutions, I would greatly appreciate it. Also, if I am not clear on my issue, please ask and I will see if I can clarify it more.
Your help is appreciated in advance!
Bob
View 4 Replies
View Related
Apr 17, 2006
Hello,
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
TIA
-Brian
View 1 Replies
View Related
Sep 24, 2007
I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables.
For example:
If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical.
But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that?
I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?
View 6 Replies
View Related
May 7, 2004
Hello, everyone:
Some tables werer sent from customer. They ask to check if these tables have been normalized. How to check them? Thanks a lot.
ZYT
View 2 Replies
View Related
Feb 23, 2007
Dear Experts,When I use a single table I can easily use constraints to enforce mybusiness logic, but what do I do when I normalize a single table intomultiple tables.For example, imagine that my initial table has the columns ID, Name,Salary with the constraint that Salary is not NULL. Now imagine thatI break this into two tables, one with ID and Name and another with IDand Salary. I would like to have a constraint that prevents thecreation of a row with (ID,Name) in the first table unless acorresponding row in the second table is also created.I can enforce this logic with triggers, but it looks ugly and isfairly brittle. Is there a better way or is this the dark side ofnormalization?Thanks.
View 6 Replies
View Related
Jul 20, 2005
I'm trying to generate a normalized XML document out of SQL serverthat reflects the datastructure of a table.Eg. This is what I would like to get<table name='MtFeedback'><field name="MtFeedbackIy" type="int"/><field name="Title" type="varchar" size="50"/><field name="FirstName" type="varchar" size="50"/><field name="Surname" type="varchar" size="50"/><field name="insertedon" type="datetime"/></table>I have tried two ways of and these are the results. The 1st techniqueis closeselect1 as Tag,null as Parent,'' as [table!1],null as [field!2!table-name],null as [field!2!name],null as [field!2!type],null as [field!2!size]UNION ALLselect2 as Tag,1 as Parent,null as [table!1],i.TABLE_NAME as [field!2!table-name],i.COLUMN_NAME as [field!2!name],i.DATA_TYPE as [field!2!type],i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]frominformation_schema.columns iwheretable_name = 'MtFeedback'FOR XML EXPLICIT<table><field table-name="MtFeedback" name="MtFeedbackIy" type="int"/><field table-name="MtFeedback" name="Title" type="varchar"size="50"/><field table-name="MtFeedback" name="FirstName" type="varchar"size="50"/><field table-name="MtFeedback" name="Surname" type="varchar"size="50"/><field table-name="MtFeedback" name="insertedon" type="datetime"/></table>The 2nd technique fails totally.select1 as Tag,null as Parent,i.TABLE_NAME as [table!1!name],null as [field!2!name],null as [field!2!type],null as [field!2!size]frominformation_schema.columns iUNION ALLselect2 as Tag,1 as Parent,null as [table!1!name],i.COLUMN_NAME as [field!2!name],i.DATA_TYPE as [field!2!type],i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]frominformation_schema.columns iwheretable_name = 'MtFeedback'FOR XML EXPLICIT<table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="categories"/><table name="categories"/><table name="products"/><table name="products"/><table name="products"/><table name="descriptions"/><table name="descriptions"/><table name="descriptions"/><table name="syssegments"/><table name="syssegments"/><table name="syssegments"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"><field name="MtFeedbackIy" type="int"/><field name="Title" type="varchar" size="50"/><field name="FirstName" type="varchar" size="50"/><field name="Surname" type="varchar" size="50"/><field name="insertedon" type="datetime"/></table>Cheers Dave
View 1 Replies
View Related
Feb 12, 2007
Jamie writes "I trying to write a SQL solution using embedded and/or dynamic SQL to convert a matrix file to a normalized format."
View 1 Replies
View Related
Feb 28, 2008
Hi
I am new to SSIS. Probaly What I am asking is the simple one. But I dont know. I have a requirement. My Data source is Excel file and Destination is SQL Server . Excel file consist around 10k unnormalized rows which should be loaded to the multiple normalized master and child tables into the destination with the Primary key (identity column) and foreign key constrains. Even sometimes package should check for destination database table for matching Source column (Excel) code with the lookup table code (destination lookup table) and get the Primary key of Lookup table and put it into the destination table. Please help me the way to find the solution for this
View 5 Replies
View Related
Aug 21, 2007
I'm working with a database that is poorly normalized and am doing my best to increase the speed of our queries (changing the database itself is not an option right now). Any help with the following situation would be very much appreciated.
There are two tables involved, described below.
DataTable:
Code Snippet
CodeA char(4),
CodeB char(4),
CodeC char(2),
ColA float,
ColB float,
ColC float,
...
ColZ float
NameTable:
Code Snippet
Abbr char(2),
FullName varchar(50)
In the DataTable, the columns named ColA, ColB, ColC, etc., are described in the NameTable where Abbr would be A, B, C, etc., and FullName would be a "nice" text version describing what's stored in that column. For instance, a record in the NameTable might include Abbr = 'A' and FullName = 'Computer Science', and that means that ColA in the DataTable refers to 'ComputerScience'.
The table I'm trying to optimize the creation of in our ASP.NET application needs to have column headings that could be retrieved like this:
SELECT DISTINCT CodeB, CodeC FROM DataTable WHERE CodeA = @CodeA
(Note that CodeC is really just a different representation of CodeB--think 4-digit year vs. 2-digit year--for any given CodeA.)
The row headings for the table could be retrieved like this:
SELECT FullName FROM NameTable
The cells of the table at any given intersection of a row heading and a column heading can be retrieved like this:
SELECT Col? FROM DataTable WHERE CodeA = @CodeA AND CodeB = @CodeB (this selects a single value)
Where the ? of Col? is determined by the Abbr version of FullName shown in the row heading, and @CodeB is determined by CodeB from the column heading.
Right now this table is generated using a lot of loops in the code of the page that require it to query the database for nearly every cell in the table. Even with this poor database design, there has to be a better way than that. I'm hoping there is a way to create a stored procedure that uses temporary tables and joins, or views, or something like that to create the table on the SQL Server and just send the data to the ASP.NET page in a form that can be directly databound to a GridView.
Thanks in advance for any help!
-Mathminded
View 1 Replies
View Related
Jul 23, 2007
Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama). My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:movie_id movie_title comments actor_first actor_last genre_name1 Casino blah blah Robert DeNiro Action1 Casino blah blah Robert DeNiro Drama1 Casino blah blah Joe Pesci Action1 Casino blah blah Joe Pesci Drama And here's the query that produced that:1 SELECT movies.movie_title, movies.comments, actors.actor_first,
2 actors.actor_last, genres.genre_name
3 FROM movies INNER JOIN movies_actors ON movies.movie_id = movies_actors.movie_id
4 INNER JOIN actors ON movies_actors.actor_id = actors.actor_id
5 INNER JOIN movies_genres ON movies_genres.movie_id = movies.movie_id
6 INNER JOIN genres ON movies_genres.genre_id = genres.genre_id
So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?
View 9 Replies
View Related
Aug 27, 2002
Thanks for reading.
This is pretty long, hopefully it isn't rambling.
I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way.
I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later.
Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables.
I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc.
I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables.
Any comments? Suggestions? All is welcome.
I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading.
Thanks again...
simplified example
(my denormalized tables are 20 - 30 colums wide)
denormalized table:
===================
name, address, city, state, cellphone, homephone
normalized tables:
==================
tblPerson [PK_person, name, age, height, weight]
tblAddress [PK_address, FK_person, street, city, state, zip, addressType]
tblContact [PK_contact, FK_person, data, contactType]
I'm breaking up the denormalized tables like this (*UNTESTED*):
=================================================
DECLARE @vars.... (one for each column in my normalized table structure, matching size and type)
DECLARE myCursor CURSOR
FAST_FORWARD FOR
SELECT name, address, city, state, cellphone, homephone
FROM _DNT_myWideTable
INTO
WHILE @@Fetch_Status = 0
BEGIN
-- grab the next row from the wide table
FETCH NEXT FROM myCursor
INTO @name, @address, @city, @state, @cellphone, @homephone
-- create the person first and get the ID with @@IDENTITY
INSERT INTO tblPerson (name) VALUES (@name)
SET @personID = @@IDENTITY
-- use that ID to coordinate inserts across other tables
INSERT INTO tblAddress (FK_person, address, city, state, addressType)
VALUES(@person, @address, @city, @state, 'HOME')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @cellphone, 'CELLPHONE')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @homephone, 'HOMEPHONE')
END
View 1 Replies
View Related
Jul 2, 2004
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
---DID (pk) char(1)
---LID (fk - schools) char(4)
---studentLast varchar(50)
---studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
---EID (pk - autonumber) bigint(8)
---EMID (fk - enrollmode) int(4)
---DID (fk - participants) char(10)
---LID (fk - schools) char(4)
---enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
---EMID (pk) int(4)
---mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
---LID (pk) varchar(4)
---CTID (fk - caltracks) char(1)
---AID (fk - agencies) char(1)
---SDID (fk - schooldist) char(1)
---COID (fk - countydist) char(1)
---sitename varchar(25)
---sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
---CTID (pk) char(1)
---legend char(4)
---trktitle varchar(15)
---trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
---AID (pk) char(1)
---legend varchar(4)
---agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?
View 4 Replies
View Related
Jul 20, 2005
I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.
View 3 Replies
View Related
Apr 14, 2015
I have a requirement to provide data in a denormalized form from normalized tables. Working in SSIS.
I have two tables: EmployeeCountry and Country.
EmployeeCountry
EmployeeId (PK)(FK)
CountryId(PK)(FK)
Country
CountryId (PK)
CountryName
There will only be a max of 3 Country entries for each Employee. So I want to select the EmployeeId and get the three CountryIds so it would look like this:
Employee
EmployeeId
CountryId1
CountryId2
CountryId3
View 9 Replies
View Related
May 20, 2015
I normalized the below tables but I am finding it difficult to copy data to the new tables. Â How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it.
CREATE TABLE SKU_DATA (
SKU Â Â
  Integer NOT
NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer).  SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer)
 BUYER(Buyer,Department)
View 2 Replies
View Related
May 25, 2006
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
View 1 Replies
View Related
Aug 7, 2014
I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.
Here is a small sample:
Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
The desired output:
Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696
and so on ......
The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.
View 9 Replies
View Related
Aug 16, 2007
Here is my code
ALTER PROCEDURE Test
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @From varchar(10)
DECLARE @To varchar(10)
DECLARE @DBName varchar
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo
WHERE TicketNum =
(SELECT TOP 1 TicketNum FROM CustomerInfo
WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP)
ORDER BY CreateDate DESC)
SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp'
CREATE DATABASE @DBName + ' ' + @From + ' ' + @To
END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
Thanks
Ganesh
View 2 Replies
View Related
Nov 24, 2006
Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
View 1 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Jun 26, 2006
How do you create a SQL database (Sql Express 2005) in real time using VB.Net 2005 ASP.net 2.0?
View 2 Replies
View Related
Dec 4, 2007
I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.
tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload
please guide me i dont have an idea.
thanks for spending ur valuable time for me.
View 8 Replies
View Related
Nov 21, 2003
Sorry, I'm a newbie out there when it comes to creating database. I currently have an ASP.Net project which requires a database. I spent the past few days thinking about how to create a proper database for the application but to no avail. I think tis thread might be out of point for tis forum, but i am really in need of help.
Here goes the storyline of my database:
The project is an online project management system, meant for students to submit their deliverables (reports, eg. Minutes, agenda, project monitor chart) Lecturers will access the web application to grade the student’s submission. The application must allow administrators to add in new subjects. When a new subject is added, the subject leader will set a dateline for a particular module to be submitted. Eg, minutes dateline. Once the dateline is due, the system will inform the lecturers in charge of who/which group did not submit the module. Lecturers and subject leaders can then generate warning letters to be sent via email to the students involved. Lecturers and subject leaders will be able to make announcements through the system as well.
A student can either submit his/her work as an individual or as a group.
A student will be studying more than 1 subject.
A subject will consist of more than 1 module.
Every module will have a dateline for its submission.
Students can make more than 1 submission for each module.
Students belong to different groups/classes for different subjects.
For different modules, there will be different fields to be inserted into the database.
Example, in the “Minutes module�, the student will have to be able to key in the objectives, the date of the meeting, the venue, its members, absent members, actual minutes, action by and meeting end time.
In the “Project monitor chart module�, the student will have to key in the week/date, the name of the member, task involved, as well as the status.
When a new subject is created, the subject leader will have to specify the percentage of each different module. (Eg, Exams – 40%, Term test 15%, and so on.)
A subject will also be taught by more than 1 lecturer. So subject leader will specify the lecturers teaching that particular subject.
I hope someone out there can help me out on the construction of a database for the above storyline. Thanks a lot.
View 1 Replies
View Related
Jan 14, 2004
Hi All,
Can anybody help me/does anybody have the code to create a database on ms sql server 2000 using asp.net?
This needs to be done without being asked for the sql server login credentials (i.e the sa username/password can be inserted in the code)
View 8 Replies
View Related
Aug 19, 2005
Is there any tools to create a database with tables, views, and all that jazz with a GUI. I went through the exammle on beta.asp.net but the way they created their db was with a script. I want to create everything with a GUI app. If this gui app doesn't exist can someone point me to where I can find more info about creating db, tables etc with sql server 2005 express? Thanks in advance.-Daticus
View 2 Replies
View Related
Oct 14, 2006
I have an excel document that Imported to access database. That database should count the number of tickets created by each tech rep; list the area, sub area and description of call created by a the tech rep. Can access perform this task and if yes, any idea I to approach that project or should i use SQL, And if access won't be able to do it. please make a suggestion how to approach this project. it will be nice if i can get the steps.
EXAMPLE
Request Id SRS Started Call Description Area Sub Area Request Status Closed Date Assign To Created By
View 2 Replies
View Related
Apr 12, 2004
I created a database using the following command :
create database krish on (Name='krish', filename='C:Program FilesMicrosoft SQL ServerMSSQLdatakrish.mdf', size=25, maxsize=50,filegrowth=5%)
This actually created the database. Now, I wanted to view this database info being stored inside the SQL Server system tables. I looked at "sysdatabases" table and found an entry as expected for "krish" but I could not trace where the info corresponding to the size of the database was stored ie.25MB . (I looked ad "sysdevices" but couldn't find any entry for the newly created database).
In which table is it stored ?
Any help is appreciated.
View 2 Replies
View Related
Jan 30, 2007
suppose if we want to create a database and all it relative things to a new server , for that if we restore the backup copy of that databas in a new server will it create the database and restore the data.
Is it enough to restore the backup copy or we need to restore the log also.
View 2 Replies
View Related
Jun 11, 2007
I've done a lot of stored procs and modified tables but now I need to create a very basic database from scratch and wonder on how the best way to get started or any good tutorials out there.
I need a db with the following
Content Table
Customer Table
User Table
View 1 Replies
View Related
Nov 26, 2007
Hello everybody, my name is Andy,
I need help for creating a database, it's an exercise for my school, we just began to learn SQL, about entities, relationships (one to one, one to many...), creating tables, but the thing is i don't know where to start, i think i need to practise but it's hard to transform a text into a database, i think i need some help from you guys and i hope you will help me.
I give you the exercise, and what i found :
Project wants users to be able to initiate debates, post articles and get to the achieved items. A user should be able to write as many articles as desired after having register with login and passport
However articles should contain these references for more http links, URL, summary of the content; 1 or more books, article, doc, case studies …
1)Identify the data objects and relationship and draft the initial ER Diagram with entities and relationships. Normalize model as far as you can
2)write the SQL query / link (url) and corresponding summary
About the 1)
Entities : users --> login and password associated (one to one relationships one user = one login/password)
Articles --> associated with the users accounts (one to many)
I am not sure for creating the different tables, im a bit lost... :s
About the 2) totally lost, i need the 1) to make the SQL Query (i know a bit about queries)
I hope you will help me, i am a beginner in SQL (only one course taken) and it is our first exercise, thanks for your help all!!!
View 2 Replies
View Related
Feb 20, 2008
Hi,
I am developing a Translator for a Mobile Phone and i have to create a database for storing English, Esperanto and French words. What are the SQL statements should i write to create the database and how can i deploy the database in a Mobile Phone/PDA?
View 1 Replies
View Related
