relational-database

When and why are database joins expensive?

I'm doing some research into databases and I'm looking at some limitations of relational DBs. I'm getting that joins of large tables is very expensive, but I'm not completely sure why. What does the DBMS need to do to execute a join operation, where is the bottleneck? How can denormalization help to overcome this expense? How do other ...

What are the uses for Cross Join?

A cross join performs a cartesian product on the tuples of the two sets. SELECT * FROM Table1 CROSS JOIN Table2 Which circumstances render such an SQL operation particularly useful? ...

How to create relationships in mySQL

In class, we are all 'studying' databases, and everyone is using Access. Bored with this, i am trying to do what the rest of the class is doing, but with raw SQL commands with mySQL instead of using Access. I have managed to create databases and tables, but now how do i make a relationship between two tables? If i have my two tables li...

Are there canonical-forms for database queries?

Say I want to make an "Optimized query generator". Basically a SQL query optimizer that is a lot better than what can be put in an SQL server based on time/space limitations. It would take a query and DB stats as input and generate an SQL query tailored for the target system that will quickly optimize to a nearly ideal plan. How much of...

relational_database vs config_file vs spreadsheet usage

I have heard some genuine arguments for the use of relational database vs spreadsheet before. Relational database provides fast reporting and (relatively speaking) reliable data warehousing,where spreadsheets are lightweight, fast replicating, and easy to float around the organization to different audience. Although I notice the advantag...

How do the advanced features in Relational databases work?

To make a long question short, I know about the basics of a Relational Database, Indexing ,Replication, Locking, Concurrency, etc, and SQL syntax (SELECT,INSERT,UPDATE,DELETE, CREATE,DROP,ALTER,TRUNCATE) when used with simple expressions such as: SELECT EventID,EventName FROM Events WHERE CustomerID=5 ORDER BY EventType But I don't un...

When shouldn't you use a relational database?

Apart from the google/bigtable scenario, when shouldn't you use a relational database? Why not, and what should you use? (did you learn 'the hard way'?) ...

Version Controlled Database with efficient use of diff

I have a project involving a web voting system. The current values and related data is stored in several tables. Historical data will be an important aspect of this project so I've also created Audit Tables to which current data will be moved to on a regular basis. I find this strategy highly inefficient. Even if I only archive d...

Question about 3rd Normal Form

The following table : EmpId State Zip 1 FL 342008 2 CA 342001 is not in 3rd normal form because State is dependent on the primary key(EmpId) transitively. So, if I break it like this : (EmpId,State) (State,PIN) it's in 3NF. I could also break it like : (EmpId,State) (EmpId,PIN) and it will again be in 3NF. Bu...

Relational Database Options that work with .Net framework aside SQL (Possibly using Linq)?

Exactly what the title says...........any thoughts on other good options for relational database implementation aside from SQL Server DB's. It's necessary for the .Net Framework to be able to communicate with it also. ...

Standard database neutral XSD to describe a relational database schema.

Hi All, Is anyone aware of a vendor neutral XSD to describe a relational database schema? Our system needs to grab information about the structure of a database: Tables Columns and types Primary and Foreign Keys Constraints Indexes etc in a vendor independent manner and store it in an XML file for later processing. Before we...

Upper Limit for Number of Rows In Open Source Databases?

I have a project in which I'm doing data mining a large database. I currently store all of the data in text files, I'm trying to understand the costs and benefits of storing the data relational database instead. The points look like this: CREATE TABLE data ( source1 CHAR(5), source2 CHAR(5), idx11 INT, idx12 INT, ...

Relational UML Diagram inspired by SO

My homework was to create a relational UML diagram about a discussion site. I used the SO schema, and I did some modifications: removed Age in the User Information added representatives, which allows users to categorise posts by their usernames (voluntary) Otherwise, the schema is rather similar, but rudimentary. How would you improv...

Interfaces in a relational UML diagram inspired by SO

I have developed my homework from the post. Problem: to do posts similarly as in SO so that the first post is the question and the other posts are replies after the question. Question: How would you improve the interfaces? Would some simpler data structure make things easier? ...

Creating forms for relational data in Rails

I have a form that I need to display for a table that has a relationship with a couple of other tables. For instance, I have a table "cases" (think, investigator case), that has_one claimant and has_one client. I would like the form to display the fields to fill out for the case data, client data and claimant data. Is there an easy ...

What advantages do constraints provide to a database?

I realize this question may seem a little on the "green" side, but after the number of "enterprise" or "commercial" databases I've encountered I've begun to ask this question. What advantages to constraints provide to a database? I'm asking more about Foreign Key constraints rather than Unique constraints. Do they offer performance gains...

When do you opt to store XML in a relational database?

Possible Duplicate: Why would I ever choose to store and manipulate XML in a relational database? Although this question on the surface appears to be a rehash of what's already been previously asked, I'll state up front that it is not. My question is not how to store or retrieve XML from a relational database. The question at ha...

Resources and guidelines for porting an object database + data to a relational database

Does anyone have any pointers to information on techniques for object database to relational database conversion? I've done the standard searches and have only come up with Data Conversion from Object-Oriented to Relational database so far. It's a good paper, but I was hoping for more.... ...

Who are the relational database gurus ?

In general software development we have Martin Fowler and Steve McConnell. In relational database who are the gurus ? Precision because someone has told it's not programming related, the correct question should "who are the SQL gurus". But a relational database guru is always a SQL guru too. ...

When to use a key-value data store vs. a more traditional relational DB?

When would one choose a key-value data store over a relational DB? What considerations go into deciding one or the other? When is mix of both the best route? Please provide examples if you can. ...