03 Database Models PART1 — Transcript

Full transcript

1. 0:01module 1 topic 3 entitled data models
2. 0:05this topic examines data modeling data
3. 0:08modeling is the first step
4. 0:10in the database design journey serving
5. 0:12as a bridge
6. 0:13between a real world object in the
7. 0:16computer database
8. 0:17you will learn first some basic data
9. 0:19modelling concepts
10. 0:21and how current data models have
11. 0:23developed from earlier models
12. 0:29database design focuses on how the
13. 0:31database structure
14. 0:32will be used to store and manage end
15. 0:34user data
16. 0:36data modeling the first step in
17. 0:38designing a database
18. 0:39refers to the process of creating a
19. 0:41specific data model
20. 0:43for a determined problem domain
21. 0:46a data model is a relatively simple
22. 0:49representation
23. 0:50usually graphical of more complex
24. 0:52real-world data structures
25. 0:55in general terms a model is an
26. 0:57abstraction
27. 0:58of a more complex real-world object or
28. 1:01event
29. 1:02a model's main function is to help you
30. 1:04understand
31. 1:05the complexities of the real-world
32. 1:08environment
33. 1:09within the database environment a data
34. 1:12model represents
35. 1:13data structures and their
36. 1:15characteristics
37. 1:16relations constraints transformations in
38. 1:20other constructs
39. 1:22with the purpose of supporting a
40. 1:23specific problem domain
41. 1:31the importance of data modeling cannot
42. 1:33be overstated
43. 1:35it facilitates communication data models
44. 1:38can facilitate interruption among the
45. 1:40designer
46. 1:41the application's programmer and the end
47. 1:43user
48. 1:44data models are a communication tool
49. 1:48data modeling gives various views of the
50. 1:50database
51. 1:51data constitutes the most basic
52. 1:53information employed by a system
53. 1:56applications are created to manage data
54. 1:59and to help transform data into
55. 2:00information
56. 2:02but data is viewed in different ways by
57. 2:04different people
58. 2:06data modeling organizes data for various
59. 2:09users
60. 2:10when a good database blueprint is
61. 2:11available it doesn't matter
62. 2:14that an application programmer's view of
63. 2:16the data is different
64. 2:17from that of the manager or the end user
65. 2:21provides an obstruction for the creation
66. 2:24of a good database
67. 2:26the data model is an abstraction you
68. 2:28cannot draw
69. 2:29the required data out of the data model
70. 2:39the basic building blocks of all data
71. 2:41models are entities
72. 2:42attributes relationships and constraints
73. 2:46an entity is a person place thing or
74. 2:49event
75. 2:50about which data will be collected and
76. 2:52stored
77. 2:53an entity represents a particular type
78. 2:55of object in the real world
79. 2:57which means an entity is distinguishable
80. 3:00that is each entity occurrence is unique
81. 3:04and distinct an attribute is a
82. 3:06characteristic
83. 3:07of an entity a relationship describes an
84. 3:11association among entities for example
85. 3:15a relationship exists between customers
86. 3:18and agents
87. 3:19that can be described as follows an
88. 3:21agent can serve many customers
89. 3:24and each customer may be served by one
90. 3:26agent
91. 3:27data models use three types of
92. 3:29relationships
93. 3:31one too many many too many and one to
94. 3:34one
95. 3:35database designers usually use the
96. 3:37shorthand notations
97. 3:39one is two m or one two asterisk
98. 3:43m is to n or asterisk to a series
99. 3:47and one is to one or one to one
100. 3:50respectively
101. 3:51a constraint is a restriction placed on
102. 3:53the data
103. 3:55constraints are important because they
104. 3:57help to ensure
105. 3:58data integrity constraints are normally
106. 4:02expressed in the form of
107. 4:03rules
108. 4:08from a database point of view the
109. 4:10collection of data becomes meaningful
110. 4:12only when it reflects
111. 4:14properly defined business rules a
112. 4:17business rule is a brief
113. 4:18precise and ambiguous description
114. 4:22of a policy procedure or principle
115. 4:25within a specific organization
116. 4:28business rules derive from a detailed
117. 4:31description of an organization's
118. 4:33operations
119. 4:34help to create and enforce actions
120. 4:36within that organization's environment
121. 4:39properly written business rules are used
122. 4:42to define entities
123. 4:43attributes relationships and constraints
124. 4:48the main sources of business rules are
125. 4:50company managers
126. 4:52policy makers department managers and
127. 4:56written documentation such as
128. 4:58a company's procedure standards and
129. 5:00operations manuals
130. 5:02a faster and more direct source of
131. 5:04business rules is direct interviews with
132. 5:06end users
133. 5:11the process of identifying and
134. 5:12documenting business rules
135. 5:14is essential to database design for
136. 5:17several reasons
137. 5:19it helps to standardize the company's
138. 5:21view of data
139. 5:23it can be a communication tool between
140. 5:25users and designers
141. 5:27it allows the designer to understand the
142. 5:30nature
143. 5:31role and scope of the data it allows a
144. 5:33designer to understand
145. 5:36business processes it allows the
146. 5:38designer also to develop
147. 5:40appropriate relationship participation
148. 5:42rules and constraints
149. 5:44and to create an accurate data model
150. 5:52business rules set the stage for the
151. 5:54proper identification of entities
152. 5:57attributes relationships and constraints
153. 6:00in the real world
154. 6:01names are used to identify objects as a
155. 6:04general rule
156. 6:05a noun in the business rule will
157. 6:08translate into an entity in the model
158. 6:10and the verb that associates the nouns
159. 6:14will translate into a relationship
160. 6:16among the entities for example
161. 6:19the business rule a customer may
162. 6:22generate many invoices
163. 6:24contains two nouns customer and invoices
164. 6:28and a verb generate that associates the
165. 6:31nouns
166. 6:32from this business rule you could deduce
167. 6:34the following
168. 6:35customer and invoice are objects of
169. 6:38interest for the environment
170. 6:40and should be represented by their
171. 6:41respective entities
172. 6:44there is a generate relationship between
173. 6:47customer
174. 6:48and invoice to properly identify the
175. 6:52type of relationship
176. 6:53you should consider that relationships
177. 6:56are bidirectional
178. 6:58that is they go both ways for example
179. 7:02the business rule a customer may
180. 7:04generate many invoices
181. 7:06is complemented by the business rule and
182. 7:09invoice is generated by only one
183. 7:12customer
184. 7:13in that case the relationship is one too
185. 7:16many
186. 7:17and customer is the one side and the
187. 7:20invoice is the many side
188. 7:23to properly identify the relationship
189. 7:25type you should generally
190. 7:27ask two questions how many instances of
191. 7:30b
192. 7:31are related to one instance of a how
193. 7:34many instances of a
194. 7:36are related to one instance of b for
195. 7:39example
196. 7:40you can assess the relationship between
197. 7:42student and class
198. 7:43by asking two questions in how many
199. 7:46classes
200. 7:47can one student enroll the answer
201. 7:50many classes how many students can role
202. 7:54in one class answer many students
203. 8:04during the translation of business rules
204. 8:06data model components
205. 8:08you identify entities attributes
206. 8:10relationships and
207. 8:12constraints this identification process
208. 8:15includes naming the object in a way
209. 8:17that makes it unique and distinguishable
210. 8:20from other objects in the problem domain
211. 8:23it is important to pay special attention
212. 8:26to how you name the objects you are
213. 8:28discovering
214. 8:29entity names should be descriptive of
215. 8:32the objects in the business environment
216. 8:34and use terminology that is familiar to
217. 8:37the users
218. 8:38an attribute name should also be
219. 8:40descriptive of the data
220. 8:42represented by that attribute it is also
221. 8:46a good practice
222. 8:47to prefix the name of an attribute with
223. 8:50the name or abbreviation
224. 8:51of the entity at which it occurs
225. 8:56the use of a proper naming convention
226. 8:58will improve the data model's ability to
227. 9:00facilitate
228. 9:01communication among the designer
229. 9:04application programmer
230. 9:06and the end user a proper naming
231. 9:08convention can go along towards
232. 9:11making your model self-documenting
233. 9:18the evolution of data models is the
234. 9:20quest for better data management
235. 9:22led to several models that attempt to
236. 9:25resolve the previous model's critical
237. 9:27shortcomings and to provide solutions to
238. 9:29ever
239. 9:30evolving data management needs the
240. 9:33hierarchical model was developed in the
241. 9:351960s to manage
242. 9:37large amounts of data for complex
243. 9:40manufacturing projects
244. 9:42such as the apollo rocket that landed on
245. 9:44the moon in 1969
246. 9:46the model's basic logical structure is
247. 9:49represented
248. 9:50by an upside down tree the hierarchical
249. 9:54structure contains
250. 9:55levels or segments a segment is the
251. 9:58equivalent
252. 9:59of a file system's record type within
253. 10:02the hierarchy
254. 10:03a higher layer is perceived as the
255. 10:06parent
256. 10:07of the segment directly beneath it
257. 10:10which is called the child the
258. 10:13hierarchical model depicts
259. 10:14a set of one-to-many relationships
260. 10:17between a parent
261. 10:18and its children segments each parent
262. 10:22can have many children but each child
263. 10:26has only one parent
264. 10:32network model was created to represent
265. 10:34complex data relationships
266. 10:36more effectively than the hierarchical
267. 10:38model to improve database performance
268. 10:41and to impose a database standard
269. 10:44in the network model the user perceives
270. 10:47the network database as a collection of
271. 10:49records
272. 10:50in one is too many relationships however
273. 10:53unlike the hierarchical model the
274. 10:56network model allows a record to have
275. 10:58more than one parent while the network
276. 11:01database model is generally not
277. 11:03used today the definitions of standard
278. 11:07database concepts that emerge with the
279. 11:09network model
280. 11:10are still used by the modern data models
281. 11:14the schema is the conceptual
282. 11:17organization of the entire database
283. 11:19as viewed by the database administrator
284. 11:22the sub-schema defines the portion of
285. 11:25the database
286. 11:26seen by the application programs that
287. 11:29actually produce
288. 11:30the desired information from the data
289. 11:32within the database
290. 11:34a data manipulation language or dml
291. 11:38defines the environment in which data
292. 11:41can be managed and is used to work
293. 11:44with the data in the database a schema
294. 11:47data
295. 11:48definition language enables a database
296. 11:50administrator to define
297. 11:52the schema components
298. 11:57the relational model's foundation is a
299. 11:59mathematical concept
300. 12:01known as a relation to avoid the
301. 12:04complexity of abstract mathematical
302. 12:06theory
303. 12:06you can think of a relation sometimes
304. 12:09called the table
305. 12:10as a two-dimensional structure composed
306. 12:12of intersecting rows and columns
307. 12:15each row in a relation is called a tuple
308. 12:19each column represents an attribute the
309. 12:22relational model also describes a
310. 12:24precise
311. 12:25set of data manipulation constructs
312. 12:28based on advanced mathematical concepts
313. 12:34the relational data model is implemented
314. 12:36through a very sophisticated relational
315. 12:39database management system
316. 12:41or rdbms this performs the same basic
317. 12:45functions provided by the hierarchical
318. 12:48and network dbms systems in addition to
319. 12:51a host of other functions
320. 12:53that make the relational data model
321. 12:55easier to understand
322. 12:56and implement the most important
323. 12:59advantage of the rdbms
324. 13:02is its ability to hide the complexities
325. 13:05of the relational
326. 13:06model from the user the rdbms manages
327. 13:10all of the physical details while the
328. 13:12user sees the relational database
329. 13:15as a collection of tables in which data
330. 13:17is stored
331. 13:19the user can manipulate and query
332. 13:22the data in a way that seems intuitive
333. 13:25and logical
334. 13:28figure 2.2 displays the relational
335. 13:31diagram that shows the connecting fields
336. 13:34agent code and the relationship type one
337. 13:37is too many
338. 13:38microsoft access the database software
339. 13:40application used to generate this
340. 13:43employs the infinity symbol to indicate
341. 13:45the many side
342. 13:47in this example the customer represents
343. 13:49the many
344. 13:50side because an agent can have many
345. 13:53customers
346. 13:54the agent represents the one side
347. 13:57because
348. 13:58each customer has only one agent
349. 14:01a relational table stores a collection
350. 14:03of related entities in disrespect
351. 14:06the relational database table resembles
352. 14:08a file
353. 14:09but there is a crucial difference
354. 14:11between a table in a file
355. 14:13a table yields complete data and
356. 14:16structural independence
357. 14:17because it is a purely logical structure
358. 14:21how the data is physically stored in the
359. 14:23database
360. 14:24is of no concern to the user or the
361. 14:27designer
362. 14:28the perception is what counts
363. 14:34another reason for the relational data
364. 14:36model's rise to dominance
365. 14:39is its powerful and flexible query
366. 14:42language
367. 14:43most relational database software uses
368. 14:46structured query language or sql
369. 14:49which allows the user to specify what
370. 14:52must be done without
371. 14:53specifying how the rdbms uses sql
372. 14:58to translate user queries into
373. 15:00instructions for retrieving the
374. 15:02requested data
375. 15:04sql makes it possible to retrieve data
376. 15:07with far less effort
377. 15:08than any other database or file
378. 15:10environment
379. 15:12from an end user perspective any sql
380. 15:15based
381. 15:15relational database application involves
382. 15:19three parts a user interface a set of
383. 15:22tables
384. 15:23stored in the database and the sql
385. 15:26engine
386. 15:31the next slide on this topic three data
387. 15:33models is about the entity relationship
388. 15:36model