Relational database design

September 22, 2007 RENU

Relational d.base is a file that stores info in multiple tables, each holding a specific category of info & d.base can access these tables, extract, reorganize, display info contained within them in many diff ways without altering original table structures.
RDBMS: MS Access, Oracle, SQL Server, FoxPro r ex’s of relational d.base mgt systems, which is s.ware that uses a relational d.base model to hold appl data. Some RDBMS appl’s incl tools to create a complete appl to interact with data, such as forms for data entry & reports.
Table has horizontal rows/records & vertical columns/fields.
Relational d.base design process: 1st u’ve to plan d.base to prevent future problems like lost data.
Company policies: If ur company has an internal policy that comp’s r assigned to dept’s rather than to specific employees, this would affect the way u design tables in a comp inventory tracking d.base.
1. Identify d.base purpose. Statement of purpose: states scope of d.base & guides designing d.base by providing sorme info.
2. Review existing data(forms & docs client provides), create preliminary list of fields, incorporate as much info as u can that the end users of d.base need into field list. Calculated fields: r rarely stored in d.base as calc’s r done via queries based on current data always.
Existing data in paper form- all kinds of foms, docs, printed invoices, bills, slaes slips, address books
in electronic form- spreadsheets, word processing docs, web pages, other d.bases like personal info managers.
3. Group fields into tables- To maintain data, extract & relate info in d.base. Names of fields/tables shd be unique, not more than 30characters, concise. Avoid special characters like period, quotes etc, abbr’s, acronyms, reserved words(insert, delete), spaces(except access). Table names r usually plural, shd be descriptive ex: employees table so users know what they mean.
Data types: vary by RDBMS but most r basically same. A. Numeric/number: allows entry of only no’s. U can specify if integer or decimal no & size of no.
B. Currency: Fomats no’s as currency.
C. Text, string, character or alphanumeric: alphanumeric content & numeric data on which calc’s not done like phone nos or zipcode.
D. Memo: more char’s than txt data type ex: descriptions
E. Date, time, Date/time: dates, times.
F. Boolean: Only 1 of 2values ex: on/off, yes/no, true/false.
4. Identify key field or fields that uniquely identify a record in the table. A. Primary/unique key: Field or combination of fields that contains a value that uniquely identifies a record & thus used in establishing appropriate relationships betn tables. Primary key fields have values that r never blank, that rarely change, never allow duplicates, include as few fields as necessary, can be any data type. Well chosen primary key field ensures a unique identifier for each record in the table. A table may have many unique keys but at most 1primary key. ex: SSN. Unique columns may or may not be null but for primary key not null constraint is automatically enforced. when multiple fields used as primary key, they r called composite key.
B. Foreign key: field or combination of fields that is a primary key in a related table, provided both data types shd match. Duplicate values can appear in foreign key field.
C. Non key field: Not a foreign key, primary key or part of multi field primary key. Duplicate values can appear as not used to uniquely identify a record.
5. Identify how the tables r related(table relationships)- A. 1 to 1 relationship: Each record in 1table relates to 1record in 2nd table & either table can be considered to be parent/primary table. It is possible to combine data into 1table dependibg on situation.
B. 1 to many: 1record in 1table(primary) can be related to many records in 2nd table(related table).
C. many to many: many from 1table related to many in 2nd. Most RDBMS doesn’t support this.
ER(Entity relationship)diagram: graphical representation of tables/entities & relationships betn them. Symbols used. 1 indicates 1side of a relationship, N/infinity for many side of relationship, * for primary key.
Junction table: Many to many relationship is broken into 2 1-to many relationships by creating junc table which uses primary key fields from each of the tables creating multi field primary key. Thus Previous 2tables r now linked to new junc table by 1 to many relationship.
Referential integrity: Once this is enforced, u can’t enter a value in the foreign key field of 1table if there is no matching value in primary key of the related table. It prevents deletion of a record from primary table if matching record exists in related table. It prevents modification of value in primary key of primary table if there r related records in the related table.
6. Normalize tables- Review tables for inconsistent data & restructure tables to prevent any data maintainance problems.
Normalization: process that eliminates duplicate data & organizes tables to increase ease of managing data by dividing large tables into smaller. It consists of 6normal forms.
1st normal form: Table is in 1st nomal form if each field is atomic(shd hold smallest meaningful value, ex-name field shd be split into 1st name & last name) & there r no repeating groups of fields. 1NF.
2nd: ensures all non key fields r functionally dependant on the entire primary key. Table shd be already in 1NF. If table contains a multiple field primary key, then each non key field must be functionally dependant on all fields in primary key, but such tables mostly have 2NF.
Functional dependency is relationship betn fields in same table where value in 1field, the determinent field, determines value that is in the other field. 2NF.
3rd: ensures no fields r dependent on a non key field. 3NF. If no transitive dependencies & table is in 2NF. All non key fields shd provide info about primary key field. Mostly found in tables with single field primary key. If values occur no multiple times in non key field then table not in 3NF.
Transitive dependency: value in non key field is determined by value in another non key field instead of primary key.
Boyce-Codd normal form: ensures that when there is no single field for a primary key, that all fields in table can be combined to create the primary key.
4th: Ensures theat there r no blank values or redundant data bcos field doesn’t pertain to a record. It’s a sign that the field might belong in a seperate table. Blank values shd appear only if value is temperorily missing or unknown. 4NF if no multi valued dependencies. & it is in 3NF. Look for repeated or blank values in non key fields.
Multi valued dependency: occurs when for each value in fieldA, there is a set of values for fieldB & a set of values for fieldC, but fields B&C r not related. It occurs when table contains fields that r not logically related.
5th/prijection join normal form: Ensures all tables broken into smallest possible tables to eliminate redundant non key fields. 5NF. Tables formed from splitting large tables to reduce duplicate info. A projection is a new table holding a subset of fields from original table. when properly formed projection forms r joined, they must rslt in same set of data that was contained in original table.
Denormalization: Occurs when d.base designer combines data into 1table that the normalization process indicated shd be in 2tables. He does this for performance reasons or to keep similar data together or to keep overall structure simple etc.
7. Review & finalize d.base design with the client
8. Ensure data integrity by reviewing tables, fields so easy to query or sort data. Set field properties & use validation/look up tables to prevent errors. u can preformat how data will be entered in a field.
validation table: created to promote data integrity. Usually consists 2fields, 1 is primary key & other holds a descriptive value. Normally it contains a static set of values in 1location which u can reference instead of entering values directly in a field to ensure that consistent values r used
Field prop’s/elements/attributes: Enable u to place constraints to ease, speed, enter data accurately. A. Format property: way data is displayed. Diff based on data type.
B. Default value: Automatically enters value when u create new record but doesn’t alter values of existing data.
C. Reqd: if value reqd or can be left blank
D. Value list: limits permissible entries in a field to a list of values that u define.
E. Input mask: put dashes or other symbols in right place in pattern of nos or letters like phone no & thus guides through data entry.
An index is a field property that relates values of specified fields to the location of data on hard drive to speed up searches & sorting. Primary key is automatically indexed. A d.base index tells application where to find data. Choosing index fields will increase query performance. With an index on a referenced field, only the index has to be read before the data retrieval can begin.
9. Plan queries using info from interviews & test that queries work as intended. Its only when tables r joined, data has meaning & this is done by running query or creating a view.
SQL(structured query lang): std lang used by almost all RDBMS appl’s to access & manipulate d.bases. Sql statements can extract, add, delete & update data.
Inner join: selects only records from both tables that have matching values. Records with values in the joined field that do not appear in both tables will be excluded.
Outer join: selects all recs from 1table & only those recs in 2nd table that have matching values in joined field. Left outer join: selected recs will incl all recs of 1st table. Right: 2nd.
Crosstab query: Used to summarize table data. It enables u to take vertical data in a field & summarize it in horizontal spreadsheet like manner. It contains 3fields.
A. Row heading, holds values that will become values aligned vertically in 1st column of the query rslt.
B. Column Heading: holds values that will become headings of each additional column in query rslt.
C. Field holding values that will be summarized, such as totaled or averaged, by values for each row & column.
Action query: modifies data in underlying table/tables, efficient when bulk. Manipulates data instead of selecting. It can update field values, delete/append table recs or can even make a table. 4types.
A. Update query: updates table data
B. Append: Add recs to table
C. Delete: delete table recs
D. Make/create: new table


Entry Filed under: Computer

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to comments via RSS Feed




September 2007
« Aug   Oct »

Most Recent Posts

%d bloggers like this: