Your first schema will work just fine. When you put an index on the FullPath column, use either the case-sensitive BETWEEN operator for queries, or use LIKE with either COLLATE NOCASE on the index or with PRAGMA case_sensitive_like. Please note that this schema also stores all parents, but the IDs (the names) are all concatenated … Read more
Suppose that some relation satisifies a non-trivial functional dependency of the form A->B, where B is a nonprime attribute. 2NF is violated if A is not a superkey but is a proper subset of a candidate key 3NF is violated if A is not a superkey You have spotted that the 3NF requirement is just … Read more
There’s no real way to unit test a database other than asserting that the tables exist, contain the expected columns, and have the appropriate constraints. But that’s usually not really worth doing. You don’t typically unit test the database. You usually involve the database in integration tests. You typically use your favourite automated unit testing … Read more
In pgadmin you can create a new “Login Role” and name it Eric and give it permissions graphically, or from command line you can do something like this psql -U postgres -c “CREATE ROLE Eric LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE;” mydb see http://developer.postgresql.org/pgdocs/postgres/sql-createrole.html for information on the CREATE ROLE options.
I’d go for having a single table for all references, but additional tables like BookReferences and so on for metadata not applicable for all reference types. Searching and querying would not be more difficult – after all you could just create a view which aggregates all information as in the single-table solution, and then query … Read more
According to SQLite FAQ you have to declare either a INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT column to achieve that.
First, execute the following query in the psql terminal. select ‘drop database “‘||datname||'”;’ from pg_database where datistemplate=false; This will generate drop database command for all the databases. Copy the result in a text editor and exclude(delete) what you want to keep and save it as dd.sql file. And execute it like this: psql -d postgres … Read more
To get the minimal cover, you have to make two steps. To demonstrate, I’ll first split the dependencies into multiple (only one attribute on the right side) to make it more clean: A -> B ABCD -> E EF -> G EF -> H ACDF -> E ACDF -> G The following steps must be … Read more
1) Yes, it is allowed but very, very discouraged. Having this information in your database makes you an extremely attractive target for hackers. And if you think you can protect it, think again. Hackers have defeated the security of companies with excellent security. Your security won’t be any better. 2) You have to follow the … Read more
Three common strategies: Create a table for each class in the hierarchy that contain the properties defined for each class and a foreign key back to the top-level superclass table. So you might have a vehicle table with other tables like car and airplane that have a vehicle_id column. The disadvantage here is that you … Read more