30

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on ...


28

David Hay's Enterprise Model Patterns. This is a beast of a book, but has some great patterns. Conventions of Thought. More stuff on MRP. A Meta-Data Map . Haven't read this one. Len Silverston's Data Model Resource Book Vol. 1. Your main data model patterns. Data Model Resource Book Vol. 2. Case studies by industry. Data Model Resource Book Vol. 3. ...


10

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


10

If you put everything in one table, you will have a bigger, redundant table. If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.


9

While for tinyint vs int there are clear differences such as disk space, page splits and maintenance time, there wouldn't be any of these for varchar. So why not declare all text fields as varchar(4000), since it will anyway use up only the space needed? Even more you will be guaranteed that your data will never be truncated. The answer is of course: ...


9

Your second option is a better solution. Add a new column to your tickets table, called ticket_concert_number. The logic to begin at 1 for each new concert is something that's probably best controlled in your application. You should also have a column called concert_id so you know which concert the ticket is for. If you create a new table for each concert, ...


9

Junction tables are a very standard practice in relational database design. It's covered in database 101. If you have a many-to-many relationship between two entities, the standard way to represent them is with three tables. Two of the tables are entity tables, with a primary key. A junction table lies between them (logically) and contains two foreign ...


9

Are there official terms for this type of scenario? Yes. This is a Symmetric Relation. And "relation" here has the same meaning as in "Relational Database". An RDBMS is a database management system designed around storing relations. However RDBMSs don't have a native way to store symmetric relations. You either have to store both tuples, eg (a,b) and (...


8

If there is no read that happens prior to the first write then it is said to be a blind write. Example. Consider the following schedule: W3(X) is a blind write, as there is no read before write [R3(X) before W3(X)] W2(X) is not a blind write, as a read happens before write [R2(X) before W2(X)]


8

I think this post does a pretty good job of explaining the difference. To summarize: Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key. Primary Key – A Primary Key is a column or a ...


7

Best is to refer : Stairway to SQL Server Indexes You leave your house to run a few errands. When you return, you find a message from your daughter’s softball coach waiting for you. Three of the girls, Tracy, Rebecca, and Amy have lost their team caps. Could you please swing by the Athletic Products Store and buy caps for the girls. Their parents will ...


7

To long for a comment, so I'll just add this as a complement to David's solution. Even though the question was of theoretical nature, it may be of interest to see how that can be implemented in real life. In some situations, the anti-reflexive property, and the symmetric property is too strong, but we still want the "uniqueness" property to hold. One common ...


6

When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally. I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store. The biggest piece of the puzzle here is to move all ...


6

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different ...


6

What is the mathematical equality of two SQL statements? For me two queries are equivalent if, when given both the same of any dataset, they return the same result set. As you pointed out, SQL queries, a superset of relational algebra, can be very complex. We can mix subqueries, use stored procedures and functions (deterministic or not) which will make you ...


6

I agree with @Erik, these instruments are essentially equivalent, but I am going to add a few more ideas about this topic. Context In order to provide more context, it is necessary to have an authoritative definition for both terms. So, here are some excerpts from the relevant paper entitled Relational Completeness of Data Base Sublanguages, which was ...


5

The design doesn't meet third normal form, but not just because of the city. The fields STREET, CITY are functionally dependant on each other (if you change the city, the street should probably change as well and vice-versa). You could also have the same street, city combination represented in different ways (Foo St, Foo; Foo Street, Foo; etc.). To ...


5

In SQL Server 2012 you can have read-only secondaries, which allow you to use Availability Groups to mirror a database or set of databases to another server, and perform read-only queries against them even as they are being mirrored. The downside: this requires Enterprise Edition on both nodes, which can only be licensed per core (not CAL), and the ...


5

If you're trying to get to the youngsters who've never searched for anything in their life without using Google, then why not try something like this: Imagine iTunes messed up your music library and every song on your iPod was mixed up in random order but given a sequential name like "track1234". If you wanted to find a certain song, all you could do ...


5

I can only speak to MS SQL Server not MySQL I'm afraid. SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here. With a large text file any time you make a chance you re-write the entire file. With a SQL Server when you make changes you only write down the PAGES that have changed. ...


5

https://en.wikipedia.org/wiki/Relational_calculus: The relational algebra and the relational calculus are essentially logically equivalent: for any algebraic expression, there is an equivalent expression in the calculus, and vice versa. Basically they are two tools/methods that produce effectively the same output. Learning one might give insight into the ...


5

A multivalued dependency A->->B means that each value of A determines a set of values of B (and not a single value of B as in functional dependencies). For instance, suppose that programmers have an attribute programmer-id and an attribute known-language, and each programmer can know several languages, you have the multivalued dependency: programmer-id ->->...


5

The \ symbol is often used in mathematics for set difference, besides dash or minus symbol (-) Since tables and relations are very similar to sets and the operations of union, intersection and difference are similarly defined (see Wikipedia: Relational algebra, set operators), the \ symbol is also sometimes used for difference. If the Q = R\S expression ...


4

"What does it really mean that some attribute is a subset of some other attribute?" It means nothing. Functional dependencies exist between SETS of attributes. Whenever the treatment is supposed to be formal and precise (as in, e.g., when stating the very definition of the axioms), that's how X,Y and Z are supposed to be interpreted. Unfortunately, ...


4

Take a look at the views of Micheal Stonebraker here - pages 21 and 22. Stonebraker is both an academic and a serious commercial player in the database world (Postgres, Vertica, Informix, VoltDB inter alia). For your particular question, note his views on banking. Stonebraker is an advocate of "NewSQL". NewSQL is a two-pronged approach which addresses ...


4

"Please assume that all related fields are properly indexed between them." No, I won't do that. I see too many users who have never heard of "composite" indexes, much less understand their importance. In particular, you should have: CREATE TABLE user_location( # No surrogate id for this table user_id MEDIUMINT UNSIGNED NOT NULL, -- For ...


4

If you compare any two authorative references for a single subject they will employ different words even though they convey the same meaning. This is just the nature of English - it has a very rich and overlapping vocabulary, accumulated from many sources over centuries. Looking at the examples listed in the question they all contain the concepts of data, ...


3

The approach I take is broadly as follows (and this is by no means complete): Requirements You need to know the goals of the to be situation. specifics of platforms (hardware, software), versions of these. There may be dependencies in terms of feature availability features used in "from" situation and needed in "to" situation. Think of queueing clustering ...


3

The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps: Step 1: Make an Entity-Type for Each Tangible Thing Start with making a logical data model. For ...


3

Assuming SQL Server, the transactions that take place in the writeable database will need to be backed up and applied to a read-only copy of the database. This is known as log-shipping. The downside with this approach is that the read-only database must be put into single user mode while the restore is taking place. Either the connected users are forced ...


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