MIS 325 Lecture Notes - Lecture 23: Referential Integrity, Foreign Key, Switch Statement
24 May 2018
School
Department
Course
Professor
MIS 325 – Designing Databases – 2.8
5 steps
• 1. identify the data elements
o build a list of data elements from a given document i.e. invoice
▪ look at document
▪ interview people who are in the department
▪ look at external sources (old databases, other docs)
▪ look at operations – understand who needs what by when
• 2. Subdivide each element into its smallest useful components
o i.e. subdividing a customer’s name into first name and last name > leaving
it as one long name
▪ better able to sort by last name, salutation automation (some
instances would need Dear. First name, other times need dear Ms.
Last name)
o i.e. phone number going into chunks of 3 number
• 3. Identify tables(entries) and assign columns (attributes)
o One to one relationship can be reduced to one table
• 4. Identify primary and foreign keys
o Referential integrity definition
▪ Relationships between tables are correct, so foreign keys must
have matching primary keys in other tables
o Enforced by
▪ Declarative referential integrity
• Defines foreign key to attain
• 5. Review whether the data structure is normalized
• why use the dual table
o to create a dummy space
o select statements require from statements – dual is a dummy table in the FROM
clause if it needs to be
o Actual code
▪ SET SERVEROUTPUT ON;
▪ DECLARE
• Count_balance_due NUMBER;
• Totoal_balance_due NUMBER(9,2);
▪ BEGIN
• SELECT COUNT(*), SUM(invoice_total – payment_total –
credit_total)
• INTO count_balance_due, total_balance)due
• FROM Invoices
• WHERE (invoice_total – payment_total – credit_total > 0);
• IF total_balance_due >= 50000 THEN
find more resources at oneclass.com
find more resources at oneclass.com
