InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 05:36:04 GMT20http://www.sqlservercentral.com/Forums/Topic1444763-374-1.aspxHi everyone.I'm independent DBMS specialist from Russia. "Object AND relations" is my favorite theme for many years. If you google "impedance mismatch database" and you'll possible find my paper 'Impedance mismatch is not "Objects vs. Relations" problem' on the first page. I have other papers in English and in Russian, and also a blog on the [url=http://sql.ru]Russian analogue[/url] of this site. Unfortunately now everybody thinks that there is a fundamental contradiction (the object-relational impedance mismatch) between object-oriented and relation paradigm, so they usually use "VS." between these two definitions. There are a lot of historical and psychological reasons of such thought but generally it's a greatest mistake. To prove that this problem has very simple solution I've created DBMS prototype which fully unites core object-oriented and relational possibilities. I claim absolute novelty and fullness of this approach. This solution can be easy implemented in existing relational DBMS and after that they can be also deservedly named as object–oriented DBMS. It' doesn't require to change existing DB but makes possible to develop them in new direction. You can see its demonstration in [url=http://youtu.be/K9opP7-vh18]video[/url]. Also I have a [url=http://rxo-project.com]site[/url] dedicated to the problem and to the solution. I ask you to estimate the new possibilities. I would happy to hear any suggestions, advises, criticism, logical bug reports and plagiarism gravamen. Just be constructive, pleaseI clear understand how you doubt about what I claim above. The main doubt is "everybody knows that it's impossible". But you can see de visu how simple it is.Sat, 20 Apr 2013 17:27:52 GMTGrigoriev.Ehttp://www.sqlservercentral.com/Forums/Topic1439612-374-1.aspxHello,I have a question pertaining to the best way to design/add an addition to a database. The specs that I'm working with have a need for a work schedule table to indicate what days an employee works throughout the week. Currently, the assumption is that this is going to be the same for each and every week and the default should be Monday-Friday. Initially I had thought of building a separate table named "WorkSchedule" that would be something similar to the following:[code="sql"]create table WorkSchedule( EmployeeID int not null, WeekDayName varchar(9) not null -- Using the names of the days as our clients aren't guaranteed to all provide the same integer representation, constraint pk_WorkSchedule primary key (EmployeeID, WeekDayName), constraint ck_WorkSchedule_WeekDayName check (WeekDayName in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')))[/code]I could then set the defaults via a trigger populating this table trigger or we could assume in the front-end that having zero records in the WorkSchedule table means to default to Monday-Friday. Being that the current specs only allow for a one week schedule, would it make more sense to add individual weekday columns to the Employee table so it would be something like the following?[code="sql"]create table Employee( EmployeeID int not null, -- Other Columns For Employee Information (e.g. FirstName, LastName, etc..) ScheduledMonday bit not null, ScheduledTuesady bit not null, -- All the way to friday ScheduledFriday bit not null, constraint pk_Employee primary key (EmployeeID))[/Code]Although this doesn't appear to be normalized anymore, it would allow me to easily default an employees work schedule using default constraints rather than triggers. However, this structure would limit the ability for expansion should the specs change (e.g. add the hours an employee works for each day). I'm thinking that having a separate table would allow for more flexibility in the likely event that the specifications will change, but I wanted to get some other thoughts on this. Thank you very much!Sat, 06 Apr 2013 17:46:07 GMTNuNn DaddYhttp://www.sqlservercentral.com/Forums/Topic1429428-374-1.aspxGreetings,I am wondering if anyone could help me either define a design situation I have in the data warehouse environment I work in, or perhaps a possible way to correct it. There are several tables within our warehouse model that have what I like to call, "Indirect" Foreign Keys. These are columns that have an unofficial key relationship with another table in a separate database. [b]Example:[/b] DataBaseA.GIS.StateRef.StateId <--> DatabaseB.dbo.Organization.StateIdWe are currently relying on our ETL processes to enforce constraints at load time, but I am curious if these relationships can be established, and if so how? If not, is there a term that is used to describe this occurrence?Thanks very muchKJMon, 11 Mar 2013 12:11:15 GMTKJKKPSIhttp://www.sqlservercentral.com/Forums/Topic1427299-374-1.aspxAnyone have any experience of implementing a Data Vault design and Data Warehouse models.I have experience of the later, but I'm curios about the data vault design? what are the pro's and con's?Wed, 06 Mar 2013 04:29:33 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic1273123-374-1.aspxWhich is the dominant modeling language/notation? Is one more effective than the other and which are most often used in a real world setting? I ask because I have studied several but each claims to be the most robust. My personal belief is that ORM 2 is the most complete but I am still yet a student of the game.Finally, which should I invest most of my time in?Mon, 26 Mar 2012 19:00:09 GMTSqlNewJackhttp://www.sqlservercentral.com/Forums/Topic1309732-374-1.aspxHey all.I'm busy revising for an exam and I'm going through ER Modeling. I have an example that I've attempted but I'm not sure if it's correct.Perhaps somebody could point out if what I've done is correct?Thanks very much.[img]http://i47.tinypic.com/2cn9gk4.jpg[/img]Fri, 01 Jun 2012 07:46:13 GMTmmoreauzahttp://www.sqlservercentral.com/Forums/Topic1301875-374-1.aspxLooking to continue with a "contacts" database by following "Head First" eBook. Completed the tables but do no see where actual contact information is entered from where I am. Accessing SQL through Visual Studio 2008. Images of what the text says and what I am seeing is uploaded to:https://skydrive.live.com/redir.aspx?cid=52b21d2f3f75a51a&resid=52B21D2F3F75A51A!1027&parid=52B21D2F3F75A51A!577I write as a complete novice trying to complete a text excerciseThu, 17 May 2012 09:31:59 GMTphilpensehttp://www.sqlservercentral.com/Forums/Topic1288724-374-1.aspxDear members and administrator,Hello and a pleasant day. I was given a task to create a simple data model that will implement primary key and foreign keys in tables...just came up with this sample. It has a main table called [b][font="Courier New"]Person[/font][/b], in which [font="Courier New"][b]Student[/b][/font] table is "connected", but I will add [b][font="Courier New"]Employee[/font][/b] and [b][font="Courier New"]Faculty[/font][/b] table as well.I just want to ask guidance if I did the correct relationship between data. I do not know If I need a separate table for [font="Courier New"][b]Address[/b][/font] and [b][font="Courier New"]Phone[/font][/b] data just connected to another table.Attached herein is the print screen of the data model.I hope you could help me redesign this if it is not design appropriately.Thank you and more power.Respectfully Yours,Mark SquallTue, 24 Apr 2012 02:40:20 GMTmarksquallhttp://www.sqlservercentral.com/Forums/Topic1291175-374-1.aspxHello,I have one parent & child table and i have make relation ship using a foreign key. Primary key in parent table is forein key in child table.So want when i enter records in primary table, the id of each records automatically goes in to child table.how can i do this?waiting your reply....Regards,Gopal SinghThu, 26 Apr 2012 16:49:20 GMTGopal Singhhttp://www.sqlservercentral.com/Forums/Topic1280896-374-1.aspxHi, We have an excel spread sheet that contains some basic data about daily incomes. Id like to bring it into SQL as they are constantly updating it just to allow it to used (new sheet for each month etc). Could anyone look over my theory and tell me if im going the right way. The dates area is a concern. The excel spread sheet is made up : Date | Site | budget | sales | profit/lose ----------------------------------------Going the way of Codd, I think i need to break out Date to a new table? and Site. So would have DateTable:DateID | FullDate | Month | FinYear -----------------------------------DateID = primary key, identity (1,1) - link to data tableFullDate = 10/04/2012 - date to link toMonth = 04 : April - for doing monthly calculations easily FinYear = 2012/2013 - Fincial year is APril to April so wanted a way to work that out? I guess that should be a new table actually!?SiteTable :SiteID | SiteNameSiteID = primary key, identity (1,1) - link to data tableSiteName = Full Site NameData Table: DateID | SiteID | budget | sales | profit/lose DateID = link to full date detailsSIteID = link to full Site NameBudget = manual number Sales = Manual numberProfit/Lose = Calculation to create % from budget and SalesDoes that sound about right? With the date table? would you create a script to fill in all dates for 2 years or something? then update it every few years? or maybe create a SP to update it every month??? Views and corrections greatly received! ThanksTue, 10 Apr 2012 09:34:54 GMTn00bDBAhttp://www.sqlservercentral.com/Forums/Topic1265574-374-1.aspxHi everyoneI want to know what would be best practice for the following scenario:Article table with link to a Category and each Category can have sub categories.In my head there are 3 solutions, but don't know which is best:1. Separate Category and Sub Cat tables, with a link in SubCat to MainCat. The Article table will then just hold the SubCat id. You can use a lookup to get the MainCat2. Same as above, but you store both MainCat and SubCat id's in the Article table.3. All categories in one table, with an extra column defining parent/main category and then a linking table for category id to article id.In my mind, performance should be best in solution 2 as this uses the least joins?Please advise.Thank you.Mon, 12 Mar 2012 14:51:51 GMTJako de Wethttp://www.sqlservercentral.com/Forums/Topic1235860-374-1.aspxthe problem is an attendance tracker for an educational institution.The foreign keys would have student number, course number, and meeting date. The data (attendance) could be as small as a bit.Long story short, I used an array of characters and a nice scrolly matrix gadget with dates across the top and students down the side, the whole matrix being a course section, and I allowed a few extra attendance types. Maximum size of the group per student was greater than the max number of meetings I anticipated.On the surface, it looks like repeating groups, but according to database theory as I stubbornly choose to interpret it ([url]http://www.dbdebunk.com/page/page/622318.htm[/url] "...Maximizing performance is a physical optimization issue..."), as long as the one table with repeating groups was presented to the querying user as two or more joined tables, I'm all set, as the repeating group is simply a physical storage issue which is outside the scope of the topic of normalization.Its worth noting that since there was no sql server involved, I was essentially the storage engineer, and for reporting, I fed a stream of attendance rows which were normalized with the foreign keys to our reporting system.Whadya think? Anybody else ever see some funny normalization cases?Fri, 13 Jan 2012 11:29:36 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1228471-374-1.aspxi am working on e learning and certification i have this prob on maintaining the details of students who have registered for the various courses,courses they have completed,and if they are pursuing, i need 3 columns for every course for every student is there a solution for it?Fri, 30 Dec 2011 09:57:26 GMTkavyap.kphttp://www.sqlservercentral.com/Forums/Topic826002-374-1.aspxHi I am taking MIS class and i need help with the EER diagram and i tried making it myself but teacher told me that it is a process diagram not an ER diagram please help me here is what i have tried: please click on this link http://yfrog.com/j2mytryjand here is what another try here is the link http://yfrog.com/2s2ndxjplease let me know which one is correct i am having trouble defining the entities...and i also need to create table with attributes and normalize it...please help!Here is the scenario:MIS Corporation owns and operates nine large hardware stores and one centralized warehouse in major metropolitan areas of the west United States. The stores carry a wide variety of hardware merchandise for sale to the individual and business. The stock is stored in bins and is located by an inventory number in the warehouse.Whenever a retail store needs merchandise, a four-part merchandise request form is completed. One copy is kept by the store, and three copies are mailed to the warehouse the next day. If the merchandise requested is on hand, the goods are delivered to the store, accompanied by the third copy of the request. The second copy is filed at the warehouse. The fourth copy is forward to accounts receivable for cost and revenue calculation.If the quantity of goods on hand is not sufficient to fill the order, the warehouse sends the quantity available and notes the quantity shipped on the request form. A purchase memorandum for the shortage is then prepared by the warehouse. At the end of each day, all the memos are sent to the purchasing department.When ordered goods are received, they are checked at the receiving area of the warehouse, and a receiving report is prepared. One copy of the receiving report is retained at the receiving area, one is forwarded to accounts payable, and one is filed at the warehouse with the purchase memorandum.When the purchase memoranda are received from the warehouse, purchase orders are prepared. Vendor catalogs are used to select the best source for the requested goods, and the purchase order is prepared and mailed. Copies of the order are sent to account payable and the receiving area. One copy is retained in the purchasing department.When receiving report arrives in the purchasing department, it is compared with the purchase order on file. It is also checked with the invoice before the invoice is forwarded to accounts payable for payment.Each week a report of the open purchase orders is prepared to determine if any action should be taken on overdue deliveries. This report is prepared manually after scanning the file of outstanding purchase orders.The company has been successful in this field. Each retail store acquires its merchandise from the company's centrally located warehouse. Consequently, the warehouse must maintain an up-to-date and well-stocked inventory ready to meet the demand of the individual stores.The company wishes to hold its competitive position with similar stores of other companies in its market area. Therefore, MIS Company must improve its purchasing, inventory, and selling procedures. The number of company's store, the number of inventory items carried, and the volume of business has been providing pressures to change from basically manual data processing routines to a computerized processing system.After several discussions with the company's executive board, Nancy Tsai, the chief executive office of MIS Company, has decided to install several minicomputers in stores and warehouse in the late 1990s. Most of the applications that have been installed on the computer are in the accounting and financial areas. Most of these application programs are purchased from a software vendor company that has modified the programs to meet the requirements of the MIS Company.The computer applications at MIS Company have generally been successful. The software has enabled the company to reduce its paper work burden and improve its response to individual stores. In addition management is able to obtain better information concerning costs, sales, and profit.Nevertheless, managers at MIS Company are dissatisfied with several aspects of the computer systems. While the applications have helped improved the operations management function, there has been little impact on middle management and still less on top management. Nancy Tsai and the other managers at MIS Company have come to realize that there are basic limitations to traditional file processing systems. Some of these limitations are (1) uncontrolled redundancy, (2) inconsistent data, (3) inflexibility, (4) limited data sharing, (5) poor enforcement of standards, and (6) excessive program maintenance.Now the competition in hardware retailing has intensified. Competitors such as Home Base and Home Depot seem to response more rapidly than MIS Company to new business opportunities. Therefore, after analyzing the current individual file processing systems, the company's data processing consulting firm – the Oaks Associates has suggested replacing the existing systems with an integrated database management systems using Oracle.The recommendation of the Oaks Associates stated that the new database management systems should have the following characteristics: (1) minimal data redundancy, (2) consistency of data, (3) integration of data, (4) sharing of data, (5) ease of application development, (6) uniform, security, privacy, and integrity controls, (7) data accessibility and responsiveness, (8) data independence, and (9) reduced program maintenance.Nancy Tsai has accepted the excellent advice given by the Oaks Associates. She is intended to purchase Oracle as the MIS Company's database management systems. She has just hired your team for the data processing department. Your responsibility is to design a database oriented merchandise distribution systems to collect the information about inventory levels for the warehouse and individual stores, vendor, accounts payable, and accounts receivable. Nancy Tsai expects that your database design is flexible to handle both large volume transactional processing reports for the operation management and ad hoc reports for the top management.===========Please see the http://yfrog.com/06captureobp‏ document this is my new updated version please let me know if i have any mistakes as far as the tables and attributes and also the normalizationhere is the tablesMIS CorporationAttributesCompanyIDDescriptionHardware StoresAttributesStoreNoStoreAddStorePhoneCustNoWarehouseAttributesWarehouseNoWarehouseAddWarehousePhoneCustomersAttributesCustNoCustLnameCustFnameCustAddCustPhoneTransactionsAttributesTransNOTransDateProductNOProductAttributesProductNoProductDescriptionSerialNumberModel NumberPurchase Order ItemAttributesPOINoProductNoPOIDescriptionPOIQuantatyPOIPricePurchaseNoPurchaseAttributesPurchaseNoPurchaseDateVendorNoInvoiceNoStaffNoPOINoVendorAttributesVendorNoVendorAddVendorPhoneVendorTypeInvoiceAttributesInvoiceNoInvoiceDateInvoiceAmountVendorNoPurchaseNoPaymentAttributesPaymentNoPaymentDatePaymentAmountVendorNoPurchaseNoInvoiceNoStaffNoStaffAttributesStafftNoSLnameSFnameStoreNoHiredateSaddressOrderAttributesOrderNoOrderDateCustomerNoCust_paymentNoOLInoOrder Line ItemAttributesOLineNoProductNoOLineDescriptionOLineQuantatyOLinePriceOrderNoCust_PaymentAttributesCust_PaymentNoCust_PaymentDateCust_PaymentAmountStoreNoOrderNoStaffNoSun, 29 Nov 2009 22:26:37 GMTprincess_tahseenhttp://www.sqlservercentral.com/Forums/Topic844962-374-1.aspx[url]http://lambda-the-ultimate.org/node/3762[/url][p] Some of long-standing issues being revisited, but it is interesting to see the debate from functional programming angles. [/p]Sat, 09 Jan 2010 15:39:33 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic1189517-374-1.aspx[url=http://www.worthydvd.com/the-big-bang-theory-seasons-1-4-dvd-boxset.html]The Big Bang Theory DVD[/url]A funny show with one of the catchiest theme songs of all time (even if part of it doesn't make sense autotrophs don't drool, many heterotrophs do however) returns for its fourth season and... it may have jumped the shark. While there are some great episodes in this seasonTrue Blood is a dark series, both in tone and visual style. Given the fact that the show deals with vampires, copious night shots and goth-club interiors are a matter of course.[url=http://www.worthydvd.com/true-blood-seasons-1-4-dvd-boxset.html]True Blood DVD[/url] The musical numbers from this season have been rather entertaining. Fans of last years mash ups will be glad to see they have returned with a vengeance popping up multiple times through out the season[url=http://www.worthydvd.com/glee-seasons-1-2-dvd-boxset.html]Glee DVD[/url]Will Lois and Clark finally get together? Will Zod take over the world? Will the blur finally fly? Will Chloe find her true love? All these questions and many more will be answered in Smallville[url=http://www.worthydvd.com/smallville-seasons-1-10-dvd-boxset.html]smallville DVD[/url]The American version[url=http://www.worthydvd.com/the-office-seasons-1-7-dvd-boxset.html]The Office DVD[/url]of The Office faced an uphill battle from the start. Fresh off a disastrous, unwatchable reworking of the British hit Coupling (axed after only four episodes), it was quite the gamble for NBC to green-light Greg Daniels' American take on this superb and super-successful BBC series created by Ricky Gervais and Stephen Merchant.Wed, 12 Oct 2011 19:51:01 GMTlemotryhttp://www.sqlservercentral.com/Forums/Topic1092295-374-1.aspxI am doing a lot of cleanup on a database that desperately needs it! There are very few primary / foreign key relations, so adding those is obviously a big part of what I'm currently working on to be able to produce accurate DB diagrams.The database has a few tables that are self-referencing to produce tree structures. From what I've known in the past, it is common place to have the parent ID of the "root" level records be NULL. I know that's ok to do even with a foreign key, but a lot of people seem to think it's not necesarily the best approach. I've read a few places where people set the parent ID of "root" level records to the primary key of that record. I don't really like the idea of that, and I'm not even sure how it would work if your primary key is an identity field that auto-increments.What I think I have come up with for a solution is to insert a "dummy" record with a primary key value of -1 and a parent ID / foreign key value of -1 as well. That way any "root" level records can be inserted with a parent ID / foreign key of -1.Technically, this could be a solution for primary / foreign key relationships with 2 tables as well. I have seen arguments with one side saying that a foreign key field that references a lookup table should have NULL if it's not a required field and the user doesn't select a value. Then, the other side of the argument is that there should be a value in the lookup table of "Unknown" to be able to select. My solution would be similar to having "Unknown" as a lookup value, but you wouldn't have to display that in a dropdown (or whatever your input method is).This all makes sense in my head, but a lot of things that make sense in my head aren't the best solutions! So, I was curious if there would be any major drawbacks to using this as a way to be able to enforce key constraints.Tue, 12 Apr 2011 11:47:44 GMTlucidspoonhttp://www.sqlservercentral.com/Forums/Topic1085483-374-1.aspxNot sure where to post this and I'm not sure if I am going to gain anything by posting this. At the very least I could warn other developers out there.I have a very badly designed database. It has grown out of control and there is very little I can do about it.I cannot change the structure due to the application design. I'm also struggling to design an archive system, also due to the application design. The system is approaching a terrabyte and the more data that is loaded the slower the system becomes due to inneficient design.Our immediate problem is this. Sometimes when we do a large load of data the system grinds to a halt until we rebuild the database. The batch requests per second is much lower than usual, as well the very little IO, however the CPU is suffering. Our only solution is a rebuild. After shutting down application servers, we BCP the data out, drop the table, create the table, BCP the data back in, add the indexes. This is the only method we have had success with when this anomaly happens.Tue, 29 Mar 2011 07:03:10 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic612145-374-1.aspxI just discovered a problem in the database design for an application that was developed years ago. There is an audit table with OldValue and NewValue columns defined as nvarchar(255). However, in the mean time, new tables have been defined with columns longer than nvarchar(255). I want to propose that the audit table use a much larger value.However, nvarchar takes up twice as much space as varchar, AND not too long ago, there was a forum post about trailing blanks in nvarchar, and how that can be a problem in comparisons, etc. unless you use RTRIM().So I was wondering what the general consensus is on using varchar versus nvarchar, especially for longer strings. Is it considered best practice to always use nvarchar, or are there some other considerations?Thanks.Tue, 02 Dec 2008 09:21:13 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic1054121-374-1.aspxHello All,My partner thinks its ok to send me a data file where they mix numeric and text data in a single column. When I protest they seem to think I am being a difficult.Can anyone provide any links that detail the principle that I think they are violating?ThanksWed, 26 Jan 2011 11:55:55 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic883939-374-1.aspxHi,I was wondering if a Db design guru could provide guidance for a db newb??I have to design a database (and front-end app) that contain ID reference numbers that relate to groups of additional numbers.An exampleID No. 1124 (ref A) contains 30 numbers (ref B) 01, 02, 03 ect up to 30ID No. 1101 - contains 60 numbers 01, 02, 03 up to 60 ectID No. 1411 - contains 70 numbers 01, 02 up to 70ID No. 1024 - contains 20 numbersID No. 1045 - contains 50 numbersID No. 1058 - contains 40 numbersID No. 5214 - contains 120 numbersID No. 7845 - contains 160 numbersand so onThe Ref B groups of numbers can vary in size and need to be raised and lowered as and when needed.I'm assuming that each second set of numbers (ref B) need to have individual unique identifiers , but how would I go about creating and mapping to the first group of (ref A) numbers??Any pointers appreciated.Thanks,IpoTue, 16 Mar 2010 09:58:00 GMTIPOhttp://www.sqlservercentral.com/Forums/Topic1038611-374-1.aspxHello, I have been storing data regarding my cardio exercise in an Excel spreadsheet for the past three months. Each particular tab in the worksheet represents a new week. Every day that I exercise I open the current tab and enter the date, the day of week, the particular form of cardio (elliptical, treadmill, bike or running), the number of miles I ran, rode, etc, the calories I burned and the number of minutes the session lasted. Once those values have been entered, I have formulas in Excel calculate my minutes/mile and the number of grams of fat I burned during the workout session. I like this spreadsheet, but I have been wanting to put this into a database for a while now and then put a web application on top of it so that I can access the sucker from anywhere. I may also extend it and include other info as necessary. I came up with a relatively simple design for a SQL database and I think it's a good start. However, I do not have much experience with database design and can always use opinions. I have attached a screenshot of what I have thus far for design. One note, the FitnessDetails table has a PK of UserID in case I want to extend the DB and application to other data which I might want to track (though I don't know what that'd be right now). Can someone take a look and give me some opinions on my work?Thanks!!Wed, 22 Dec 2010 23:22:58 GMTPolymorphisthttp://www.sqlservercentral.com/Forums/Topic1012211-374-1.aspxHelloI have some doubts when should i use the normal form or when i shouldn't.I'm creating a very large application (kind of ERP) that has several modules, and sometimes in some places i see that if i put the data in denormalized form it's better to handle, but i don't know if i will get problems in the future if i take this decision.So if someone knows some guidelines, best practices when to choose the denormalized form over the normalized , please write them here.ThanksThu, 28 Oct 2010 05:16:21 GMTrootfixxxerhttp://www.sqlservercentral.com/Forums/Topic1010428-374-1.aspxHi folks,I've been trying to understand transactions better, so I've started reading Transactional Information Systems by Morgan-Kaufman Press. Unfortunately, I've gotten stuck in the second chapter, where they differentiate between total and partial orders. It states that a relation R that is a proper subset of the cartesian product of set A is a partial order on A if it is reflexive, antisymmetric and transitive. So far so good. Now total order is different only in the respect that the relation R is not reflexive but total (aRb ∨ bRa).Where I'm getting stuck is to find an example of a partial order that is NOT a total order. Could anyone help?Mon, 25 Oct 2010 15:06:59 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxI read an article today on this site today about Surrogate Keys.I attended my first relational database theory & design training in Princeton NJ.in 1988 and I'm familiar with the E. F. Codd & C.F Date Theories.I was very fortunate to have attended the class & my instructor was extremely intelligent and I will never forget her for I learned a lot from her. She was very interesting and as a result I developed a strong interest in Data Modeling & Database Design.But when Data Modeling I took what learned from the training and customized to make it work.I learned that something may look good on paper when implement it is often flawed and needs rework for it is not a solution to the problem.Despite the criticism of their theory, I learned a lot and I adapted their theory to deliver practical functional Database Applications. I attended the Oracle Data Modeling Course in 1997 and it was not so interesting because I had been performing Data Modeling on the Job for so long.It many cases it is appropriate to use a surrogate key when you do not have a candidate key or the key would induce performance problems etc.It has been my experience that it depends on the situation.I use Surrogate keys a lot but there are pros & cons.The decision should be based on the Business requirements, situation and a number of other factors. Listed below are some articles on why you should consider using a surrogate or an natural key.:-)[url]http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx[/url][url]http://www.agiledata.org/essays/keys.html[/url][url]http://www.dbdebunk.com/page/page/626995.htm[/url][url]http://en.wikipedia.org/wiki/Surrogate_key[/url]:-)Mon, 22 Mar 2010 21:18:06 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic970493-374-1.aspxThese questions will be an ongoing project where some of the main points will be added here. The questions will be reformulated as we learn more. This is going to be a group project where everyone's input will count.[b]What is a NULL?[/b] It represents an unknown. But can it also stand for "N/A"? As Jeff Moden implies below, it does not stand for "nothing" because "nothing" is a known quantity.[b]Where did NULLs come from anyway?[/b] The origin of NULLs is important for a thorough examination of them. Were they merely invented as an ad hoc solution for example?Aren't the unknown values in algebra (like x and y), NULLs before they're solved? If we say yes, then "unknown" quantities have been in use in mathematics at least as long as algebra has been around. So the term "NULL" from Codd (in respect to RDBMS) may be new, but the basic concept may not be - at least not in mathematics in general. [b]How are they used in Mathematics?[/b] [b]How are they used in Logic?[/b] Over 2,000 years ago, Socrates appreciated the idea of NULL in its broadest sense. He proclaimed that he knew nothing. He was a breathing, talking NULL. But he had some terrific questions! (It's one thing to be ignorant and know it and quite another to be ignorant and not know it.) [b][u]The Utility of NULLs[/u][/b][b]Should NULLs be avoided?[/b]Some would argue that they're a mistake from the outset. For instance, one issue that they create is they take away the "relational model" of a RDBMS in that one table can be joined to another and yet there are no rows that correspond (in an OUTER JOIN). What kind of "relation" is that? In that case, a NULL becomes a stand-in for a non-relation? Simply an ad hoc solution? (This position is coming from a previous thread, but I thought it was a persuasive argument.)-David Portas-According to David Portas, NULLs do not behave like "unknown values" and this is an issue. If NULLs were applied consistently in SQL, then they would be less of a problem. But this view is more of an argument about how NULLs are used by the RDBMS rather than NULLs themselves.[b]Would creating different Kinds of NULLs be worthwhile?[/b] For example, maybe "N/A" should be distinguished from NULL. Some would argue that a NULL already means that no data is available, so there is really nothing to add. Why complicate matters further? If the data is not there, it is not there - so what is it good for anyway? From a practical standpoint, there is no need to complicate the options.[b]NULLs can have differing meanings, depending upon how they are used and in what context. How can we easily document them to prevent confusion?[/b] [Hit tip to Steve Jones on this question.]Some of this has been discussed in another thread, we can take some of that and add it here. My position is subject to change, but thus far, I will argue that sometimes NULLs are useful, sometimes they're just an obstacle and can cause errors. (Yes, it depends.)Tue, 17 Aug 2010 09:09:16 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic981275-374-1.aspxIs it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?Mon, 06 Sep 2010 19:05:45 GMTthe_rutterhttp://www.sqlservercentral.com/Forums/Topic950555-374-1.aspxI've been cruising the web on the subject of Codd's 12 rules for a long time now. I've read what amounts to about 50 articles and hundreds of posts on the subject. If you compare the rules from all those, you find large disparities in what is said so I don't trust any of them to be correct. Instead of sifting through all the manure to find out what the horse was thinking, I just want to ask the horse. ;-) Unfortunately, Mr. Codd is no longer in the land of the living (may his soul rest in peace).The only link that I've found that may have had an actual copy of Mr. Codd's original white paper, which contained the 12 rules, is broken.With all of that in mind, does anyone have a link or a copy of the actual white paper or, perhaps a book by Codd where he mentions the rules? I'd really like to "hear" the rules from the horse's mouth because, from what I can see, too many people have mis-copied or misinterpreted the rules.Thanks for the help, folks.Sun, 11 Jul 2010 22:46:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic790913-374-1.aspxhttp://th.informatik.uni-mannheim.de/People/Lucks/reject.pdfSee the second case in the paper. Interesting historical perspective.Sun, 20 Sep 2009 13:28:31 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic950355-374-1.aspxHi, I am new to SQL databases and have been having trouble doing the following. I need to check that the the maximum value in one column of one table does not exceed the maximum value of one column in another table using a CHECK constraints. How do I do that if it does not allow me to use the MAX() function? The error message complains about the use of an aggregate function. I can do it with a TRIGGER but is required to do it using a CHECK.Help!!!Sat, 10 Jul 2010 09:49:26 GMTindex_ushttp://www.sqlservercentral.com/Forums/Topic424427-374-1.aspxI'm running a vb program that will insert data to my database from a text file for report generation. It will insert if the record does not exist. It has a validation that if you try to upload two or more same records, the program will not allow it. It runs weekly after extraction of records from another system. The problem is, when the report was generated for the month of October, each record has its duplicate. I checked the text file where the record came from, but it does not have any single duplicates. I test again the vb program, but the validation is working and does not allowed the records to be uploaded to the database. What would be the probable cause of the duplication of the records?Tue, 20 Nov 2007 23:09:31 GMTRamoncito Medinahttp://www.sqlservercentral.com/Forums/Topic552631-374-1.aspxwhilst enjoying a relatively quite period Im revisiting basics etc and dusting off some mental cobwebs.3nf .. is violated if a non-key attribute is a fact about another non-key attribute .. that I can live withfor example a book table with isbn, title , price publisher_name and a publisher_city.. the city is bad and should be in a separate entity etc..but to describe publisher city as functionally dependant on publisher I just cant get...if something is functionally dependant it can be uniquely determined by another attribute. But a publisher_city cant be said to be uniquely identified by a publisher..so I can see why it should be excluded due to being a fact about another column .. but as a way of checking to see of a table breaks 3nf I dont get how I'd arrive at that through functional dependency ?make any sense or do I need more/less coffee ?~simonThu, 14 Aug 2008 06:55:50 GMTSimon_Lhttp://www.sqlservercentral.com/Forums/Topic825279-374-1.aspxIn an earlier thread ("SQL Futures") it was suggested that more explanation is needed of the advantages of the Relational Model alternatives to SQL. Here are some examples of the benefits I would expect. I am making an assumption that any future RDBMS would at least meet the requirements of a “D”-compliant system - i.e. it would have features including relation variables, relational closure and so forth.[b]1. More cost effective OLAP[/b]. Many decision support systems make use of non-SQL data stores for their data even where that data is sourced from a SQL-based Data Warehouse, i.e. they use a HOLAP / MOLAP architecture. A principal reason for this is that SQL doesn’t provide the multi-dimensional data structures needed for business analytics. SQL returns its results as essentially “flat” two-dimensional tables - a 2D array or collection being the only data structures supported by SQL query interfaces.The need to support multi-dimensional data stores separately from SQL is an expensive burden. For Oracle’s OLAP option for example the licence cost is around $20K per processor. Quite apart from the software cost there is the expense of maintaining and managing data and storage in two places and keeping them consistent.The relational model is a n-dimensional data model. Relational queries preserve keys in their results, in other words they preserve the dimensional structure of the data which SQL does not. Therefore it is possible to use an RDBMS as a multi-dimensional data store to support analytical queries without a separate data store. Given that OLAP is a multi-billion dollar market I think the savings here could be very considerable indeed.[b]2. Better query optimization and storage strategies[/b]. SQL and RDBMS are both logical representations of data but relational databases have certain potential advantages when it comes to optimization. By freeing the database engine of the burden of supporting duplicates in tables and queries we would get the benefit of much smarter query optimisation and processing. The final benefit is of course hard to quantify but there is undoubtedly a large, well-documented body of research into relational database optimization which the industry is currently unable to take advantage of in SQL DBMSs. This ought to represent a very significant gain for RDBMS over SQL DBMS.[b]3. Developer productivity[/b]. SQL is a seriously deficient language for the 21st century when compared to other languages in the object-oriented world (C++, Java or C#). SQL’s 1980’s style type support, lack of type-inheritence and lack of relation types, relation variables or relation assignment are serious omissions that certainly cost development time and effort on practically every project. Incomplete support for set-based queries and the consequent need to rely on row-by-row processing are another feature of SQL. These defects are why abstraction layers that hide SQL complexity and limitations are so popular today.Take a few examples. The need to eliminate duplicates from queries or from tables without keys are very common requests in forums that deal with SQL problems. Assignment and comparison of tables or sets of rows are two other very frequent SQL problems. Since SQL doesn’t have any straightforward syntax for table assignment or comparison the code has to be written again and again for each new project. RDBMS doesn’t suffer from any of these problems.Inability to support anything other than a few basic types also causes big challenges for developers who are forced to write or duplicate code to emulate native or user-defined types in other languages.As a very conservative estimate I think it’s not unreasonable to assume a full-time developer writing complex SQL might save 1-2 days per month by using a more full-featured relational language instead. In other words it could be a 5-10% saving on development costs. In the above I've tried to concentrate on the benefits which I think are easily demonstrable and relatively easy to estimate. There are many other advantages too but these are pretty well covered in the work of Codd, Chris Date and others so I don't think there's much need to repeat them here.Thu, 26 Nov 2009 09:28:15 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic696381-374-1.aspxNot sure if this is in the right forum, but I thought maybe this is the best match.I am soon going to be starting a task to look at and investigate how several systems work. These systems all use SQL Server 2000/5/8 as a back end database.I need to understand what these systems do, but most importantly I need to document this in a way other SQL professionals can understand.Does anyone know of the best practices to do this or does anyone out there have any example templates I could look at to gain some inspiration?Thanks for your helpTue, 14 Apr 2009 03:16:14 GMTaaa-322853http://www.sqlservercentral.com/Forums/Topic809610-374-1.aspxThe idea that SQL DBMS and SQL language is much less than adequate for current and future enterprise data management needs is pretty mainstream now (some examples: alternative cloud storage models, "persistence-ignorant" development tools, business rules engines, OLAP, XML stores). I've long been of the view that we badly need a permanent replacement for the SQL model in core DBMS software. I strongly believe that its replacement(s) should be Relational, rather than some of the more fashionable, non-relational alternatives.The odd thing is there seems to be plenty of discussion about this topic [i]outside[/i] the SQL database professional community but almost total silence [i]within[/i] the SQL community (at least in the circles I move in)!So I'm wondering: Do other people in this forum recognise a need to move beyond the limitations of the SQL paradigm? What do we see as its potential future or future replacement? Are we doing enough to influence future trends and products through thought leadership and by feeding back our views to the DBMS software vendors.This is not just a Microsoft SQL Server question of course. It's a question about SQL DBMS software generally.Tue, 27 Oct 2009 15:01:27 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic593934-374-1.aspxDoes a database diagramming tool comes with OracleLite, MySQL and SQL Server Express Databases? What about Forms and Reporting Features?Thanks, KevinWed, 29 Oct 2008 14:22:03 GMTkevinsql7http://www.sqlservercentral.com/Forums/Topic736931-374-1.aspxIn reading about SQL Server and database design, I have seen many examples of joins between tables, and they all involved the Primary Key of the Parent matched to the Foreign Key of the Child. Is this a requirement, or just a best practice? Can a Parent be joined to a Child table on a field that has not been defined as a Foreign Key?Thanks for any comments, because I haven't been able to find a definitive answer to this.Wed, 17 Jun 2009 13:45:11 GMTHoward Kochmanhttp://www.sqlservercentral.com/Forums/Topic758915-374-1.aspxHow do we implement DIVIDE OPERATOR in SQL?Fri, 24 Jul 2009 04:16:31 GMTms65ghttp://www.sqlservercentral.com/Forums/Topic678804-374-1.aspxI have a Survey database with the following related tables:Surveys -> QuestionGroups -> Questions -> AnswersThese are all nested 1 to many relationships. Which of the following is a suggested practice design for the primary keys?A) Use composite keys where the keys are as follows:[u]Table -> Key[/u]Surveys -> SurveyIDQuestionGroups -> SurveyID & QuestionGroupNumberQuestions -> SurveyID & QuestionGroupNumber & QuestionLetterAnswers -> SurveyID & QuestionGroupNumber & QuestionLetter & AnswerOrdinalB) Use a single unique ID and foreign key in each table:[u]Table -> Key[/u]Surveys -> SurveyIDQuestionGroups -> fk_SurveyID & pk_QuestionGroupIDQuestions -> fk_QuestionGroupID & pk_QuestionIDAnswers -> fk_QuestionID & pk_AnswerIDAny thoughts will be appreciated!Wed, 18 Mar 2009 12:41:43 GMToutatimehttp://www.sqlservercentral.com/Forums/Topic641228-374-1.aspxI have a supertype table "Persons" (PK - "PersonID") and several subtype tables. Is it necessary/mandatory to name the PK of all the subtype tables "PersonID"? One of the subtype tables is "Employees". I want to use "EmployeeID" as the PK (i.e., PersonID in Persons is related 1-1 to EmployeeID in Employees). Thanks!Wed, 21 Jan 2009 15:46:33 GMTbarbararyan