I’ve compiled various helpful links to websites and other resources related to databases.
- 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
- 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
Other database management systems
- 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:
- 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).