CSIS 115 Database Design and Applications for Business
CSIS 115 Database Design and Applications for Business Dr. Meg Fryling “Dr. Meg” Fall 2012 © 2012 Meg Fryling @Siena. Dr. Meg #csis 115
Agenda • Midterm Feedback/Questions • Chapter 6: Translating ER Diagrams to Relations – Enforcing minimum cardinalities • Chapter 3: The Relational Model and Normalization
Heads-Up • Next Quiz (Monday, 11/19) – Minimum cardinality and referential integrity (including cascade updates and deletes) • How to enforce and what those constraints mean. – As usual, can have cheat sheet!
Homework • Chapter 3: The Relational Model and Normalization • Project Part III – Convert ER Diagram to DB Design – First fix ER Diagram to reflect feedback from Project Part II (rewrite), as needed. – Make sure you read the assignment requirements! – Turn back in your last ER diagram 1 -4 – Due Wednesday, 11/14 by start of class
Project Part III A-5
Midterm Part III A-6
Midterm Part III • FKs in citation entity • Relationship between DRIVER and VEHICLE instead of VEHICLE and CITATION A-7
Midterm Part IV A-8
Midterm Part IV • Cascade update if PK can change • Cascade delete on weak tables and tables created to establish M: N relationship • Actor. Name one field • Name fields should be required • Index all FKs A-9
Midterm Part IV A-10
Open Database Blackboard Assignments > In-Class Activities > 5 - ER (Conceptual) to DB (Physical) A-11
Update Parent Primary Key? Your company has decided it wants the “Accounting and Finance” department to be department number 150 instead of 100. • Can you change it? • Why or why not? A-12
A-13
Warning! • Don’t use spaces in table or field names • You will run into problems with your queries. A-14
Let’s Try It! • Update to reflect this design. A-15
Let’s Try It! • Update to reflect this design. A-16
Summary 1: 1 Relationships • 1: 1 relationship, first entity optional – FK constraint – not required (NULL) – FK Indexed (No duplicates) • 1: 1 relationship, first entity required – FK constraint – required (NOT NULL) – FK Indexed (No duplicates) • NOTE: We do not have a way, without programming, to enforce a required second entity 6 -17
Summary 1: N Relationships • 1: N relationship, parent optional – FK constraint – not required (NULL) – FK Indexed (Duplicates OK) • 1: N relationship, parent required – FK constraint – required (NOT NULL) – FK Indexed (Duplicates OK) • NOTE: We do not have a way, without programming, to enforce a required child entity 6 -19
Hockey League ER Diagram 1: N – Mandatory-Optional A-20
Hockey League ER Diagram 1: N – Mandatory-Mandatory A-21
Hockey League ER Diagram M: N – Optional-Optional A-23
Recursive Relationships • Previous rules apply! • 1: 1 and 1: N add foreign key to table • M: N need to add a new table 6 -24
Hockey League ER Diagram M: N – Optional-Optional A-25
Now, you do it! Don’t forget your translation “cheat sheet” Blackboard Assignments > In-Class Activities > 5 – ER (Conceptual) to DB (Physical) Phase 4 only 6 -26
Transforming a Data Model into a Database Design 1) Represent each entity with a table • Entity may become primary key (or use surrogate key) • Entity attributes become table fields (columns) 2) Normalize tables as necessary 3) Represent relationships 5 -27 • Use foreign keys • Add additional tables for N: M relationships Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Normalization • Normalization is a process of analyzing a table to ensure that it is well formed and contains a single entity – We may need to split tables. • More specifically, if a relation (table) is normalized (well formed), rows can be inserted, deleted, or modified without creating anomalies KROENKE and AUER - DATABASE CONCEPTS (3 rd Edition) © 2008 Pearson Prentice Hall 2 -28
Normalization Example Blackboard Assignments > In-Class Activities A-29
Deletion Anomalies • If we delete Advisor 3, we lose all the information regarding the Marketing Department. • The structure of the table forces us to lose facts about 2 different things!!!
Insertion Anomalies • To enter department data we are forced to enter unrelated data • Structure of table forces us to enter facts about two entities when we just want to enter facts about one
Update Anomalies • If we need to update First. Name, Last. Name, or Email no problem. • What if we need to update “Computer Science” to “Computer Science - Information Science”? – What bad thing can happen?
Update Anomalies • If we need to update First. Name, Last. Name, or Email no problem • If we update Dept. Code or Dept. Name, we may create a data inconsistency – Which Depart. Name is correct?
Normalizing for Data Integrity • Data integrity problems happen when data are duplicated • Normalized tables eliminate data duplication • General goal of normalization is to construct tables so every table has a single topic or theme • One fact – one place! 5 -34 Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Normalization: Process of converting a poorly structured table into two or more well-structured tables. Problem with these tables below is they have two independent themes: Employees and Department. Table before update Table after update – what’s wrong? 5 -35 Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Normalization Steps 1. Create a new table for separate theme (repeated data). 2. Keep a copy of the new table’s primary key in the original table as a foreign key. 3. Create relationship between original and new table. 5 -36
- Slides: 34