Constraints Across Normalized Tables

Feb 23, 2007

Dear Experts,

When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.

For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.

I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?

Thanks.

View 6 Replies


ADVERTISEMENT

How To Check Normalized Tables?

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

Displaying Data In Datagrid From A Normalized Set Of Tables

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

How-do-I: Add Records (multi-tables) In A Normalized Database

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

Denormalized Access Table To Normalized Database TableS

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

Crosstab Normalized Data To Non-normalized

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

SQL 2012 :: How To Provide Data In Denormalized Form From Normalized Tables

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

DB Design :: How To Copy Data From Existing Table To Normalized Tables

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

Copy Tables And Keep All The Constraints???

Feb 4, 2005

I want to make a copy of a table and this table has several constraints and
I would like to keep all of them

How should I do it? Thank you

View 1 Replies View Related

Getting All Constraints For Particular Tables In Database?

Dec 19, 2011

Trying to get all the constraints for particular Tables in my database. I try:

SELECT constraint_name, constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

and getting nada?

View 1 Replies View Related

Displaying Constraints From Tables

May 12, 2006

hey i was just wondering how to display the constraints from a particular table and display all the constraints and wat columns they apply to, someone told me they keyword u use but didnt tell me how to use it :P

View 5 Replies View Related

How Can I INSERT W/Constraints In Other Tables?

Jul 23, 2005

Three tables (all new, no data) will receive data from dBase and betransposed into them...All three have auto generated IDENTITY columnsand pk and fk constraints. Can someone provide me with anunderstandible sample?Thanks,Trint

View 3 Replies View Related

Create Tables With Constraints

Apr 19, 2007

Is there a way to create tables on the sql ce db that matches the tables I'm pulling from the main sql server?



example;

if I do a pull from Customers and ID is a primary key, how can i create this table on my handheld and have the ID set to primary key as well?

I want to do this on all my tables I need to pull from and create on my sql ce db

View 1 Replies View Related

Any Script For Disabling All The Constraints On All The Tables In A Db?

Apr 21, 2004

Any script out there for disabling all the constraints on all the tables in a database?

Since there are more than 100 tables I need to import the data into, is there any generic script that I can use to disable all the constraints and triggers etc on all the tables?

Thanks,

View 4 Replies View Related

Truncate Database Tables Based On Foreign Key Constraints

Nov 5, 2007

Guys,

I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.

For example

EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table

My truncate script should be

TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE

Is there any automated way to figure out parent and child tables and generate truncate script for the same.

Thanks

View 1 Replies View Related

Tables Created By SELECT INTO Inherit Parent Table Constraints!

Feb 1, 2008

Hey there,
Trying to build a temporary table based on a parent table such that:select * into #staging from tbl_parent where 1=0
The temp table (#staging) picks up any NOT NULL constraints from the parent (tbl_parent) table and frankly, it doesn't meet my needs.I also tried to build a view of the parent (tbl_parent) table from which to base my SELECT INTO and still, the constraints followed.
I'm thinking perhaps it possble to iterate though the temp table using syscolumns sysobjects and and set the column NULL properties on the temp table AFTER its been created but in this approach, I'm working to avoid directly referencing the columns by name.
Is this possible?  A better answer perhaps?
Purpose of exercie is to populate a record in memory before it hits the table.I'll be constructing the record on the fly so that at first, I won't have all the fields to de-Null the columns.Also, I don't wish to lose the fact that some of my fields are Null - once I've fully populated my temp record, I'll insert into my 'real' table and will depend on constraints to throw the appropriate error.  In this I won't have to built custom error checking in the stored procedure itself but instead depend on SQL SERVERS built capacity to the throw the error.  This opens a bunch of possibilities for extensibility since if at a later date a constraint rule changes, one need only change the parent table definition rather than cracking open the stored procedure.
Thank you for reading and a big thanks to you for replying :)
Also,
 

View 11 Replies View Related

SQL Server 2014 :: Find Common Link Between Two Tables In Database Without Key Constraints

Jul 16, 2015

Any tool, script, procedure, or otherwise that will solve this problem?

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )

CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

View 7 Replies View Related

SQL Server 2012 :: Find Common Link Between Two Tables In Database Without Key Constraints?

Jul 17, 2015

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

View 2 Replies View Related

UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 9, 2007

I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View 3 Replies View Related

Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 17, 2008

Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View 7 Replies View Related

SSIS Task Transfer SQL Server Objects Task And Default Constraints On Tables

Feb 21, 2008



I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.

The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.

I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)

Any option anyone knows will help.

Thanks.

View 17 Replies View Related

Using A Normalized (vertical) Table

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

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 View Related

Generate A Normalized XML Doc From SQL Server

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

Onverting A Matrix To A Normalized Format

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

Unnormalized Source Into Normalized Destination

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

Trouble With Query For Poorly Normalized Db

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

Split Wide, Denormalized Table Into Normalized Structure

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

Review Of DB Design - Normalized, Contraints, Foreign Keys, Etc.

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

Stored Proc To Copy Unnormalized To Normalized Table

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

Writing Insert And Update Stored Procedures For Normalized Schemas?

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

SQL Server 2012 :: Converting Large Excel Spreadsheet To Normalized Data

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

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

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







Copyrights 2005-15 www.BigResource.com, All rights reserved