Tag Info

Hot answers tagged

85

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 ...


15

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 ...


13

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 ...


8

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 ...


6

Given the schema Students(id:integer,grade:integer), you can solve the problem in tuple relational calculus by using the negation operator (¬). {T1.id | ∃T1 ∈ Students ¬(∃T2 ∈ Students (T2.grade > T1.grade))} This would return the id of all students in T1 where there is no student in T2 with a higher grade. Because T1 and T2 are from the same ...


4

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 ...


4

Dependent on context (chain) addressing to the same table can mean different sets of rows. So you have to point them by two different aliases to avoid ambiguity. Let's imagine we want to build the list of customers and their suppliers both with cities where they reside: SELECT w.name AS customer, s.city AS c_city, z.name AS supplier, ...


4

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 , ...


3

I would first rethink the design. You only need one table: CREATE TABLE c ( id integer PRIMARY KEY ,parent_id integer REFERENCES c(id) ,state boolean ); With the layout as presented in the question, the query could be: SELECT DISTINCT a.* FROM a JOIN b ON a.node_id = b.id LEFT JOIN a ca ON ca.child_node_id = a.node_id LEFT JOIN b cb ON cb.id ...


3

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?"


2

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 ...


2

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 ...


2

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 ...


2

But am I right, that every relation has at least one functional dependency? Relation can has no functional dependencies, if it has no non-key attributes, i.e. it is full-keyed relation.


2

Most brands provide methods to retrieve the key an insert created in an identity or auto_increment column. Unfortunately there is no general method for this, it will depend on the brand. For example in MySQL it's called LAST_INSERT_ID(), in Transact SQL (SQLServer) @@identity (better: scope_identity()).


1

I think you want a lookup table that shows the requirements for a particular event. In this case, generally I would also have attr1-3 in a separate table called 'Attribute' and a user_attribute table to assign attributes to a particular user_detail record. Then, you could assign a particular event to require n attributes through the lookup table- ...


1

The theory is all fine and good, but it only starts to really make sense once you understand the practice. The Lawyer's version of the first three Normal Forms is: The Key - there must be a Primary key for every relation being normalized. The Whole Key - There must not be any functional dependencies of attributes on any proper subset of the Primary Key. ...


1

Below I will take this structure through the normalization rules up to 4NF. I'm going to assume for a minute that you are using properties.ID as a surrogate key and that properties.Name is a valid candidate key. By this I mean that in practice you will only ever have 1 value for a given "Name", such as by creating a unique constraint. 1NF: Passes; all ...


1

Yes, there are cases where applying the filter after the join is preferable. But they are rare. Consider this example: SELECT ... FROM a INNER JOIN b ON b.key = a.key WHERE a.some_string LIKE '%foo%' Now, assume that very few rows in a has a matching row in b. It is now faster to first join and remove the rows from a that does not match and then apply ...


1

You can use a correlated subquery to solve this problem. Consider the following SQL Fiddle (which includes a lot of assumptions about your table and data): Working SQL Fiddle With this table definition and sample data: create table TXN ( ID int , TXN_DATE DATETIME ) insert into TXN (ID, TXN_DATE) values (1, '2013-01-01'); insert into TXN (ID, TXN_DATE) ...


1

Doing a natural self-join is like joining on all the columns, so like joining on the primary key (as the set of all columns is certainly a superset of the primary key.) Every row of the left copy of the table will be joined to exactly one row (its copy) of the right copy of the table. Therefore, the size of the resulting set R NATURAL JOIN R will be exactly ...


1

A isa usually indicates a subset. e.g. a male cat is a cat is a mammal. A union is the combination of two sets e.g. the union between cats and males is all cats and all males. Union is bidirectional whereas isa goes in one direction.


1

It is still a foreign key, because it's a feature of your data. However, it is not enforced by a referential integrity constraint. In my view this would mean that the foreign key would be present in the logical model ERD, but absent in the physical model.


1

Here's one attempt (this is not a day to day activity for me, so I may be doing some weird errors below): A and B are clearly candidate keys of R. Therefore C is the only non-prime attribute of R R is in 3NF iff: a) R is in 2NF b) Every non-prime attribute of R (C) is non-transitively dependent on every superkey of R. The superkeys of R is (A, B), (A, ...


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 ...


1

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 ...


1

It allows records to exist on either side of the relationship independently, with ties between records that happen to be related. If you have a real life question about this you should add the details.


1

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 ...


1

Taking the example from your original question, it is possible that the companies table has two foreign key columns both pointing at the locations table. One could be, say, HeadOfficeLocationID and the other FactoryLocationID. If you required both head office's address and the factory's address to be returned from a query you would have to join companies ...


1

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 ...



Only top voted, non community-wiki answers of a minimum length are eligible