By now the PARCELS database should be actual accustomed to you. Feel chargeless to analysis the PARCELS database schema. We’ll use a subset of the PARCELS database for this discussion: parcels, owners, fires, and tax. Treat anniversary of these tables as an entity. [NOTE: These addendum were originally accounting for use with Oracle. However, they use accepted SQL so there are few differences amid Oracle and Postgres.]
You can use Microsoft Access’s “Relationships” affection to actualize an entity-relationship (E-R) diagram for this database. To do so, articulation in to your Access database the tables listed above. Next, accessible the “Relationships” window appliance the Tools > Relationships menu item. You’ll be asked to add tables; add all the tables listed above. You ascertain relationships by clicking-and-dragging from the primary key to the adopted key, abundant like abacus joins to a query. Notice that a “Relationships” chat box appears aback you draw a relationship. This chat gives you the befalling to adapt the relationship. Back some of the tables accept multiple-column primary keys, you will charge to use this interface to add the added columns. You can accompany aback this dialog box by beat on one of atramentous accord curve in the window. Your diagram should include:
Since Access will not let you book out the relationships window, the alone way to bottle the diagram is to copy-and-paste an angel of it into in addition appliance such as Microsoft Word. To archetype the angel of the accepted window, columnist the “Alt” key, again columnist the “Print Screen” key. Accessible Word, again use Adapt > Adhesive to adhesive the angel into a Word document.
Consider advertisement of the adopted key relationships amid the tables in the afterward format:
Once you’ve created tables in Oracle appliance the CREATE TABLE statement, you can use the ALTER TABLE ADD CONSTRAINT account to add primary key and adopted key designations to the tables. For example, to ascertain the primary key parcelid for the accoutrements table, we would use the afterward SQL statement:
Constraints in Oracle accept names aloof as tables and angle do. Back a coercion can accept the aforementioned name as a table, I acclaim giving primary key constraints names the aforementioned name as that of the table it applies to. If you omit the coercion name, Oracle assigns a absence name of the form ‘SYS_Cnnnnn’, area nnnnn are digits. By defining coercion names in this fashion, it is accessible to analyze what a coercion is for. Aback you run a SQL account that violates a constraint, the Oracle will affectation the name of the coercion in the absurdity message. An advisory coercion name will save you the agitation of querying the abstracts concordance to amount out the attributes of the coercion you violated.
Similarly, we can ascertain the multi-column primary key (parcelid, fdate) for the fires table:
The adopted key parcelid in the fires table, refers to the primary key parcelid in the accoutrements table. We can specify this coercion with the afterward SQL statement:
If you charge to bead a constraint, use the ALTER TABLE DROP CONSTRAINT command. For example, to bead the primary key coercion we created on the table accoutrements earlier, we can could try the afterward SQL statement:
However, this account will abort if the adopted key coercion fires$parcelid we created beforehand still exists, back the adopted key constraint references this primary key constraint. To get about this, we can either bead the referencing constraints alone or we can add the keyword CASCADE to the end of the command; this instructs Oracle to bead all other constraints that depend on this constraint:
Similarly, we will not be able to bead the table accoutrements if any constraints accredit to primary (or unique) keys in the table. To circumvent this, we could either bead the referencing constraints alone first or we can add the CASCADE byword to the end of the DROP TABLE statement:
When creating new tables and defining constraints, I acclaim that you address a SQL calligraphy that performs these tasks in the afterward order:
Following this adjustment avoids best chicken-and-egg problems that may occur in this process. Keep in mind, however, that you cannot ascertain a foreign key in the fires table that references the primary key in the parcels table unless the primary key in accoutrements has been authentic first. (Technical note: a adopted key can absolutely advertence a uniqueness coercion instead of primary key constraint, but that it is of little acceptation here.)
This is all you charge to apperceive to use primary and adopted keys in Oracle. Note that while abounding avant-garde relational database systems acquiesce you to ascertain the primary and adopted keys, the SQL syntax for accomplishing so varies a bit from one database arrangement to another. Hence, while the concepts presented actuality should administer to best relational database systems, your SQL statements to apparatus them may attending hardly altered if you are not appliance Oracle.
Home | Syllabus | Lectures | Labs | CRL | MIT
For added advice about this page, amuse contact the 11.521 Staff Mailing List <11.521staff@MIT.EDU>. Created 5 November 1998Last modified: 28 February 2002
Simple Guidance For You In Er Diagram To Sql | Er Diagram To Sql – er diagram to sql
| Welcome to be able to my blog site, in this particular time We’ll explain to you concerning er diagram to sql