Database resources | Professional

chitu.okoli.org

Friday, 12 September 2008 09:51

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

Oracle

Official Oracle database website
Oracle 10g XE (freeware version of Oracle)
- Oracle Database 10g 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

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.

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

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

Last Updated on Thursday, 17 March 2011 21:36