Lec07 - Internal External Data Design.pptx

Another downside is look at the raw table data you

Info icon This preview shows pages 12–19. Sign up to view the full content.

View Full Document Right Arrow Icon
Another downside is look at the raw table data, you will see numbers rather than descriptors for things like product type. Need to make sure descriptor field is included in any data export for analysis purposes.
Image of page 12

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Internal Data Design Specify referential integrity constraints Enforce? If so, decide if want / need: ON UPDATE CASCADE and/or ON DELETE CASCADE Mandatory Many Side: FK – set required (NOT NULL) Either no cascade delete – or – ON DELETE CASCADE Understand side effects of this decision Optional Many Side: FK – allow nulls Either no cascade delete – or – ON DELETE SET NULL
Image of page 13
Internal Data Design Specify Trigger Rules : Type of Trigger: Event – data manipulation operation that initiates rule. Ex) Someone makes a withdrawal request. Entity – entity being accessed and/or modified. Ex) Account Balance Record Condition – condition causing rule to trigger. Ex) Withdrawal Amount > ACCOUNT BALANCE. Specify action to take if rule triggered. Ex) Cancel Withdrawal, Display Message Determine if action will replace or occur before or after main add/update/delete action
Image of page 14

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Internal Data Design Specify indexes on fields Make querying & ordering (sorting) operations considerably faster when data is queried on index fields Will only be used by DBMS if: Indexed field has many unique values The table is fairly large Query WHERE or ORDER BY clauses reference the index fields (and for ORDER BY, references in same order as index) A (lastname,firstname) index will be used for lastname sort or (lastname,firstname) sort, but not for a (firstname,lastname) sort Take up memory & disk space Slow down add, update, & delete operations (very slightly) In most systems, updating is only 5-10% of all transactions (i.e., 90-95% of DBMS transactions are reads). If % of updates is substantially higher, take care in defining indexes
Image of page 15
Internal Data Design Indexes (continued) Candidate Fields for indexing: Primary keys are automatically indexed Foreign keys should be indexed (some DBMS do so automatically) Fields frequently used to filter data (WHERE clause) Fields frequently used to sort data (ORDER BY clause) Fields used in GROUP BY clauses
Image of page 16

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Internal Data Design Denormalization : In some cases, it may be desired to return to a less normalized state in order to make query processing more efficient by reducing the number of join operations needed Plusses: May be more efficient for cases where most access (r/w) will require multiple joins in the normalized case, and there are high volumes of reads/writes Minuses: Handling of data anomalies more complex Index use can become more cumbersome Data updates may become less efficient with bigger record structures/more data in a single table
Image of page 17
Internal Data Design Audit Many applications will require audit trail of who made what changes when.
Image of page 18

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 19
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern