kT6DNSyZ

1Database Design Chapter 3
2
3
4
5Why do Data Modeling?
6
7
8Model: Simplified representation of a complex object or process
9
10Data Modeling: A businesses way to represent the entities that comprise the business and way these entities interrelate. 
11
12Entity Set: The entire population of a single entity type is called an entity set. 
13
14
15
16Benefits of using a model:
17-Creating a model is cheaper than creating the real thing
18-A model is a concise means of communication
19-A model can be changed quickly without incurring a large cost.
20-A model only emphasizes selected, important aspects of the whole 
21
22
23Business Rule: A brief, precise, and unambiguous description of a policy, procedure, or principle within a business or organization. 
24-A business rule will indicate a fact about some aspect of the business that places a limit (or constraint) on the business. 
25-These rules provide a structure to the business and help describe what the business does and doesn’t do at a detail level. 
26
27
28Example of business rules:
29-Students register for classes.
30-Professors teach classes.
31-A grade is assigned to a student who takes a class.
32-A professor has an office.
33-A class has a maximum enrollment.
34-Student grade point average is calculated as quality points divided by credit hours attempted. 
35
36
37Other business rules that may be more complex and not necessarily obvious like examples below:
38-Stuends must be a high school graduate to be admitted.
39-A student may register for one class without transcripts on file.
40-A professor must teach at least 15 credit hours each term.
41-A student must have passed any prerequisite course to register for a subsequent course.
42
43
44Common sources used to identify business rules:
45-User interviews
46-Exisiting forms and reports
47-Local, state, and federal regulations
48-business policies 
49
50
51How business rules can be implemented:
52-Using a primary key (Student can only be admitted once)
53-Using a foreign key (Student can’t register unless transcript is on file)
54-Using a check constraint (gender of student can only be M or F)
55-Using a database trigger (increase student count by one each time a new row is added to the student registration table)
56-Using a stored procedure (Calculate grade point average when requested by application)
57
58
59Business rules are:
60-Communication between a user and a business analyst or database designer.
61-Way to document how the business or organization works
62-Method to determine entities, attributes, relationships, and some constraints.
63-Boundry for the data 
64
65
66
67Entity Relationship Diagram (ERD): Enables us to create a model of each entity in our data model and the relationships between them.
68
69Formats of ERD: Crows foot, (more commonly used) Other formats: Chen model, Kronke, and Unified Modeling Language (UML). 
70-Each goal is to provide a logical design of a relational database system.
71
72Crows Foot ER model:
73-Entities
74-Attributes for each entity
75-relationships between the entities including the cardinalities 
76-identifiers that uniquely identify each entity instance 
77
78
79Symbols/ Definitions 
80-Rectangle used to represent an entity
81-Horizontal Line: used to connect related entities. This represents a relationship
82-Pentagon: Used to represent the intersection of data (attribute)
83-Short vertical line: Represents one. 
84-Circle: Represents zero.
85-“Crows foot”: represents many.
86
87
88Cardinalities: The way the Crows foot model implements constraints on business rules.
89
90Two types of cardinality: Maximum and Minimum 
91Maximum cardinality can be written one of three ways:
92- one to one (Also written as 1:1)
93-one to many (Also written as 1:M)
94- many to many (Also written as M:M or M:N)
95
96Maximum Cardinality Constraints use 3 symbols: (Zero, one, many)
97-Used to represent how many of each entity (the maximum) are participating in the data relationship we are modeling.
98
99
100Minimum Cardinality: a pair in the symbol that represents the minimum number of entities participating. 
101
102
103Cardinality combinations:
104Symbol
105(See page 11 on CH3 powerpoint)
106
107
108
109Entity Relationships
1101. Degree of the relationship: How many entities are involved in the relationship.
1112. Maximum Cardinality: What is the maximum possible number of entity occurrences in the relationship?
1123. Minimum Cardinality: What is the minimum required number of entity occurences in the relationship? 
113
114
115Binary Relationship: A relationship between two entities is the most common occurrence. 
116-Example of binary relationship: “Student registers for a course”  Relationship between two entities “Student and course”.
117REGISTERS: The relationship has a name also  -STUDENT-COURSE. 
118
119
120Ternary relationship: A relationship involving three entities. 
121Example, “PROFESSOR-COURSE-CLASSROOM”
122
123
124Unary or recursive relationship: Unary is derived from unit or one. This is when a single entity is involved in a relationship. 
125Example: Assume our college has professors that manage other professors. Here we clearly have a single entity- professor.
126The relationship name is MANAGES. 
127
128Degrees of entitiy relationships you can have :
129-Unary = 1
130-Binary - 2
131-Ternary - 3 
132
133
134Maximum Cardinality  (How it’s determined)
135-Determined by examining one of the entities in the relationship and seeing how many of the other entities may be related.
136Example, professors are related to sections. (A section is a scheduled course). We might have a rule that says one professor can teach 5 sections. 
137-This would be written as 1:5 (One professor can teach a maximum of 5 sections).
138-1:M (One professor can teach an unlimited number of sections)
139
140Relationships are bi-directional:
141-Example: given two entities A and B, A has a relationship with B and B has a relationship with A. This means the relationship will have two maximum cardinalities - one for A and one for B.
142
143
144
145
146Minimum Cardinality
147-Determined by examine one of the entities in the relationship and seeing how few (instead of how many) entities must be related. 
148-Also called “Participation” 
149Example: What is the minimum amount of courses a professor can teach?” -zero, but the minimum must be met for this. So we would say “one”
150
151
152
153Relationship Name: combination of the degree and the maximum cardinalities involved in that relationship. 
154-Example: 1:1 unary  1:1 binary (One to one unary) (one to one binary)
155-Example: 1:M unary 1:M binary (one to many unary) , (One to many binary)
156-Example: M:N Unary , M:N Binary (Many to Many unary), (Many to Many binary)
157
158Most common relationship found in a business database - One to many binary relationship 
159
160Identifier- The attribute used to select an entity instance from all other entities. Each entity should indicate an identifier. Enables us to select an entity instance from an entity class.
161Unique Identifier- If the identifier has exclusive, non-duplicated values.  Example: Combination of a professors first and last name. (This is shaded to identify it’s a unique identifier) 
162Entity Class- A group of entities that all share the same attributes.
163Entity Instance- Just one member selected from the entity class
164
165
166One to One Binary Relationship
167-Involves two entities 
168Example, Professor and Office.  1:1 binary 
169
170
171PAGE 27 
172
173
174One to Many Binary Relationship
175-Just means “More than 1” identified by a crows foot 
176
177Many to Many Binary Relationship
178-Review page 33 video
179
180One to Unary Relationship 
181REVIEW VIDEOS ON PAGES 27- 60
182
183
184
185Page 60 
186
187Intersection Data: Data that exists due to the relationship (intersection) between STUDENT and SECTION. 
188
189Association Entity: Additional entity used in Intersection Data
190
191
192Weak entity: An entity that depends on another entity for it’s existence.
193Parent entity: The entity the weak one depends on.
194
195Strong entity: Any entity that is not a weak entity. 
196
197Note: Strong entities can exist on their own. Weak ones cannot.
198
199
200Type basic types of weak entities:
201-ID-dependent
202-Non-ID-dependent
203
204
205ID-dependent - means the weak entity has the identifier (primary key) from it’s parent entity as part of it’s identifier (parent key).
206Example, Employees and their dependents 
207
208Identifying Relationship: Means the weak entity contains the identifier (primary key) of it’s parent within it’s own identifier (primary key)
209
210Important notes about Weak Entities:
211-ID-dependent weak entities will always have a composite identifier (primary key)
212-Non-ID-dependent weak entities may or may not have a composite identifier
213-All ID-dependent entities are weak entities 
214-All weak entities will have a minimum cardinality of one
215-All weak entities will have a maximum cardinality of one
216-Since weak entities have a minimum and maximum cardinality of one, the parent is required to exist
217-Remember the requirement is the row (or rows) representing the weak entity cannot exist without the corresponding parent row. 
218
219Why are weak entities important to identify?
220-The parent row of a weak entity is added to the database before any row for a related weak entity row 
221-If the parent row of a weak entity is removed from the parent table, the row (or rows) representing the weak entity must also be removed (first)
222
223
224When dealing with Weak Entities and Referential Integrity rules
225-Always set to either Restrict option or Cascade 
226
227
228
229Modeling Current Versus Historical Data
230Examples fo when we only need to track current
231-A professor has at most one current computer
232-A professor isn’t required to have a computer
233-A computer is assigned to at most one professor
234-A computer is assigned to at least one professor. In other words, the college doesn’t have unused computers sitting around.