Tag Info

Distributed Database Systems 101 Or, Distributed Databases - what the FK does 'web scale' actually mean? Distributed database systems are complex critters and come in a number of different flavours. If I dig deep in to the depths of my dimly remembered distributed systems papers I did at university (roughly 15 years ago) I'll try to explain some of the ...

Relational databases can cluster like NoSQL solutions. Maintaining ACID properties may make this more complex and one must be aware of the tradeoffs made to maintain these properties. Unfortunately, exactly what the trade-offs are depends on the workload and of course the decisions made while designing the database software. For example, a primarily OLTP ...

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*). It's just one of the extremely many inconsistencies in the SQL language. They make the language harder ...

The fundamental answer is that the consistency model is different. I am writing this to expand ConcernedOfTunbridge's answer which really ought to be the reference point for this. The basic point of the ACID consistency model is that it makes a bunch of fundamental guarantees as to the state of the data globally within the system. These guarantees are ...

My answer won't be as well-written as the previous one, but here goes. Michael Stonebraker of Ingres fame has created a MPP shared-nothing column-store (Vertica) and a MPP shared-nothing New SQL database (VoltDB) which distributes data between different nodes in a cluster and maintains ACID. Vertica has since been bought by HP. I believe other New SQL ...

I would define levels in the hierarchy: video_categories int id int level string name I would propagate the parent and child levels into your link table: video_category_links int parent_id int parent_level ((parent_id , parent_level) foreign_key to video_categories(id, level)) int child_id int child_level((child_id , ...

R: ABCDE F: C->AB, D->A, BE->CE, E->B BE->CE can be split in BE->C and BE->E. The trivial functional dependenciy BE->E can be skipped. BE->C can be replaced by E->C becuause from BE->C and E->B one can deduce E->C. Therefor the set of functional dependencies can be reduced to R: ABCDE F: C->AB, D->A, E->C, E->B A an B cannot be member of a key ...

The column specified in the ORDER BY is always retrieved from its underlying table even the specified column is not specified in the select list. An example to show the behavior, Execute the T-SQL below and include action execution plan, USE AdventureWorks; SELECT Title, LastName FROM Person.Person WHERE PersonType = 'EM' ORDER BY FirstName; In ...

To model the classical student - class - prof situation, you need to reverse your model. class can implement the n:m relationship between student and prof. To be precise: [student] m -- n [class] n -- 1 [prof] The n:m relationship between student and class would be implemented by another table. Like [participant]: [student] 1 -- n [participant] n -- 1 ...

First of all, mysql (and other sql databases) are RDMS meaning that they are based in the relational model. This means that the design should be about entities and their relations. In your case: Entities: locations, types. Relation: one location can be of one type (if I have understand you correctly). This is a one-to-one relation. The best way to store ...

If you have something in your ORDER BY clauses then it will be included in the relevant internal structures as if they were in the SELECT list - this definitely counts for base fields not otherwise being output by the statement and may count for computed values too. It just doesn't output these "extra" columns in the final stage. If it didn't include the ...

You could have a base table to store the common "person" attributes, and then specialized tables for the more specific fields. Example: Person ------ id ref_num reg_dt addrs_line1 addrs_line2 postal_code phone_num th_person -------- id person_id (FK to person.id) max_accepted_at_one_time animals ------- id accepted_by_th_person ...

In order for AC->B to be a partial key dependency, it has to satisfy these conditions. AC must be a candidate key. (Not necessarily the primary key.) One of these functional dependencies must hold. A->B, or C->B So the first question I'd ask myself is, "Is AC a candidate key of F?"

In a pragmatic sense the existing result of NULL is useful. Consider the following table and statements: C1 C2 -- -- 1 3 2 -1 3 -2 SELECT SUM(C2) FROM T1 WHERE C1 > 9; SELECT SUM(C2) FROM T1 WHERE C1 < 9; The first statement returns NULL and the second returns zero. If an empty set returned zero for SUM we would need another means to ...