When designing a schema for a DB (e.g. MySQL) the question arises whether or not to completely normalize the tables. On one hand joins (and foreign key constraints, etc.) are very slow, and on the other hand you get redundant data and the potential for inconsistency. Is "optimize last" the correct approach here? i.e. create a by-the-bo...
I have a web application running over a MySQL database (in development). I'm considering to migrate my application to Google App Engine, and would like to better understand how my simple relational database model can be transformed to the non-relational approach. I'm a long time relational database person, and I have no experience with ...
Consider setting up a connection to a RDBMS, then yanking the network plug. (You'll get the same effect if the connection goes through a NAT gateway, and the gateway decides to purge that connection.) At that point the RDBMS server is waiting for a query, or whatever, which will never occur. And the TCP connection won't be closed by th...
Aheo asks if it is ok to have a table with just one column. How about one with no columns, or, given that this seems difficult to do in most modern "relational" DBMSes, a relation with no attributes? ...
Actual Question: As the title implies, I am having confusion in choosing a better embedded RDBMS for a simple website, only few pages need some server side scripting language otherwise all HTML. I have already chosen Python for that, now could you folks recommend me which embedded database system I should use. The requirement goes lik...
I have two tables, like these: Table People: VARCHAR Name INTEGER Age Table Message VARCHAR Message VARCHAR Name There are hundreds of insert and delete going on with the Message table with queries like this: insert into Message VALUES ('Hello there', 'John'); delete from Message where name = 'John'; My question is, is it worth whi...
There are quite a few independent and not-so-independent studies comparing traditional RDBMSs but I haven't managed to find any good material on in-memory databases. I am primarily interested in ones specialized for OLTP. So far, I managed to find generic white papers on TimesTen and MySQL Cluster, but I have yet to see a head-to-head c...
I'm writing a second interface to a database application to get around some shortcomings of the original interface. Unfortunately, if I create new records, expected audit trail records will not be generated. It sure would have been nice if the database design had worked such details into table triggers, or else provided a stored proced...
I'm looking for a book which combines relational database theory, design and SQL techniques in a generic sense if possible. I have a feeling this is a big ask, and if that's really not available then I don't mind having perhaps two separate specialist books (one for RDBMS theory and one for SQL techniques). Any recommendations? ...
I was recently tasked with architecting a new scalablable user phone directory. Basically you'll have phone resellers logging into a portal and selling their phones to customers (who also need to be added into the system). So if I bought 3 phones from the reseller for my family, then each phone is automatically on the network (done by ...
Hello guys! I've ran into a design issue; in a project, i have three tables: League, Players, and an association table LeagueToPlayers that maps multiple to multiple connections between the first two (as a player can simultaneously play in two or more leagues): League Players LeagueToPlayers Id Name Id Alias ...
I'm trying to choose between these query plans for a range query: Sequential table scan Bitmap index B+ tree index Hash index My instinct is that a bitmap index would work here based on what I've read. Does that sound right? ...
Why are batch inserts faster? Is it because the connection and setup overhead for inserting a single row is the same for a set of rows? What other factors make batch inserts faster? How do batch updates work? Assuming the table has no uniqueness constraints, insert statements don't really have any effect on other insert statements in th...
Let's say I have a function call on a select or where clause in Oracle like this: select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table A similar example can be constructed for MS SQLServer. What's the expected behavior in each case? Is the HASH function going to be called once for each row in the table, o...
I have the following simple table: CREATE TABLE tbl_test ( id serial NOT NULL, poly polygon NOT NULL ) WITH (OIDS=FALSE); I then try to insert a row with a polygon: insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))')) And run into this error: column "poly" is of type polygon but expression is of ...
I have to store scheduled events, (like say class times, for example) that can be organized on a weekly, daily or monthly basis. Events can occur, say, every Monday and Wednesday, or every second Thursday of the month. Is there a way to store this information in an RDBMS that adheres to 3NF? EDIT: This is not homework; I'm building some...
I'm looking at this data model I've come up with and not feeling comfortable. I've changed the entity names so it (hopefully) makes more sense. In any event, how would you model the following? I have 3 entities. GovernmentCustomer, PrivateCustomer, PublicCustomer. Private and Public Customer are both CorporateCustomers. Corporate and Go...
I've skimmed thru Date and Silberschatz but can't seem to find answers to these specific questions of mine. If 2 database users issue a query -- say, 'select * from AVERYBIGTABLE;' -- where would the results of the query get stored in general... i.e., independent of the size of the result set? a. In the OS-managed physical/virtual me...
Null value means No value Inapplicable,unassigned, unknown, or unavailable Which is true? ...
If inner subquery returns zero rows then what will be the answer? No rows return, outer query result will be shown, error… ...