Database resources

I’ve compiled various helpful links to websites and other resources related to databases.

Oracle

• Official Oracle database website
• Oracle Express Edition (freeware version of Oracle)
  • Oracle Database 11g XE Getting Started Guide – First place to start with Oracle XE
  • Oracle Database 10g Express Edition Tutorial – Interactive, hands-on, video-based tutorials
  • A few essential tech support notes for Oracle 10g XE
    • The default version of Oracle 10g XE works only with Microsoft Internet Explorer
      • Insurmountable incompatibilities will be encountered with other browsers
      • To use other browsers, you can
        • Upgrade the Application Express (apex) engine
        • Use IE viewer extensions in Firefox or Chrome
    • With the SQL command window, you can only enter one SQL command at a time
      • To run multiple commands at one time, save them in a plain text file and upload them in the Scripts section
    • If you have problems entering dates in SQL statements and scripts, then you can enter this exact command before your other commands:
      • alter session set nls_date_format = ‘dd-mm-yyyy’;
• To implement autonumber functionality in Oracle, you need to combine sequences with triggers. René Nyffenegger provides an excellent instruction guide which you can copy, modify, and cite.
• Oracle SQL functions
  • Long list with quick descriptions – useful for rapid reference
  • Another list with more user-friendly examples
  • Official complete Oracle list of 10g functions

Other database management systems

• IBM DB2
• Microsoft SQL Server
• MySQL
• Microsoft Access

Diagramming tools

• Gliffy – Online diagramming tool

General design tips

• On which side of a relationship should you place a foreign key?
  • M:N relationship: Always turn it into two 1:M relationships
  • 1:M relationship: Always place the foreign key on the many side
  • 1:1 relationship:
    • (1,1):(0,1): Foreign key goes on (0,1) side
    • (1,0):(0,1): Foreign key goes on whichever side will cause fewer nulls
    • (1,1):(1,1): This relationship almost never exists in the real world. You probably made a mistake. Verify your cardinalities, or merge the two tables.
• Although natural keys (e.g. e-mail address, username, social insurance number) are commonly used for primary keys, there are many possible problems with them; generally, it is probably best to always use surrogate keys.

Database naming conventions

• Many variations exist, but one rule is paramount: be consistent!
• Never uses spaces in names (“ ”); instead, use either CamelCase or underscores_between_words
• Specific (optional) tips:
  • Avoid redundant table prefixes such as tblCustomer
  • Always use a surrogate primary key (PK) for every table and call it ID, and always call the foreign key (FK) TableID or Table_ID.
    • For example, the PK for Customer table is ID (Customer.ID) and its corresponding FK in the Order table is CustomerID (Order.CustomerID)
• Detailed specifications:
  • Jason Mauss’s detailed guide
  • Justin Watts’ tips

Books

• Database Systems: Design, Implementation, and Management – A thorough guide on database theory for systems analysts and application programmers. Everything you need for real-world database design. However, nothing of mathematical value (not for computer scientists); very sparse or general on implementation (not for database administrators); not much on data objects of data APIs (not for how to implement databases in a software application). Very database neutral, though leans mainly towards Oracle and Microsoft Access (this is mainly a university textbook).

Database market share

• Top 10 Enterprise Database Systems to Consider
• April 2010 report from The Data Administration Newletter