MVC3发布有一段时间了，一直没有时间来学习。
最近总算有点闲余时间了，该抓紧搞搞学习，否则就什么都不懂了。
到http://www.asp.net/MVC 网站上面有个示例叫《Music Store》，把它荡下来学习学习。先从数据库入手。
将该示例的数据库附加到数据库服务器上。通过PowerDesigner反向生成PhysicalDataModel，然后再生成为ConceptualDataModel。（本人喜欢使用概念模型来分析设计数据库。原来生成出来的是英文内容，为了理解方便，我将英文添加了中文注释。）生成后的效果图如下：
该项目中有6个实体，其中专辑实体是整个案例中比较核心的部分。其它五个实体都是与它有关系的：
1.艺术家出了哪些专辑("艺术家"1对多"专辑")
2.专辑属于哪个流派("流派"一对多"专辑")
3.购物车中有哪些专辑("专辑"一对多"购物车")
4.订单中具体详情如何("订单"一对多"订单详情")
5.专辑存在于哪个订单详情中。("专辑"一对多"订单详情")
物理模型如下图所示:
看完之后心存疑问:
1.购物车是否需要用这个Carts表来保存...(等看完代码后再回头看看是个怎么回事)
购物车中的"购物车编号"怎么得来的?是否与用户有关系?
我认为"专辑"与"订单"是有直接关系的,应该是多对多的映射关系.(一张专辑存在多个订单中,一个订单里面有多个专辑).多对多关系在生成物理模型的时候会自动创建一个中间表,这个中间表就是这两个实体通过多对多产生出来的主外键组成.由于该中间表只有主外键还不能很好的描述订单里面专辑的详细信息(数量、单价),所以在这个反向生成出来的关系图上面多了个中间实体对象"订单详情".如果在该图上直接描述"专辑"与"订单"两个实体之间的关系的话,则将来需要在生成出来的物理模型上面的中间表添加上两个字段(数量、单价).
多对多的映射关系图可以建成如下所示:
生成出来的物理模型如下所示:
上图中就可以明显看出该中间表就少了两个字段(数量、单价),所以要自己加上.
生成出来的SQL代码如下:
1: /*==============================================================*/
2: /* DBMS name: Microsoft SQL Server 2008 */
3: /* Created on: 2011/2/25 16:29:13 */
4: /*==============================================================*/
5:
6:
7: if exists (select 1
8: from sysindexes
9: where id = object_id('Albums')
10: and name = 'FK_Album_Genre_FK'
11: and indid > 0
12: and indid < 255)
13: drop index Albums.FK_Album_Genre_FK
14: go
15:
16: if exists (select 1
17: from sysindexes
18: where id = object_id('Albums')
19: and name = 'FK__Album__ArtistId__276EDEB3_FK'
20: and indid > 0
21: and indid < 255)
22: drop index Albums.FK__Album__ArtistId__276EDEB3_FK
23: go
24:
25: if exists (select 1
26: from sysobjects
27: where id = object_id('Albums')
28: and type = 'U')
29: drop table Albums
30: go
31:
32: if exists (select 1
33: from sysobjects
34: where id = object_id('Artists')
35: and type = 'U')
36: drop table Artists
37: go
38:
39: if exists (select 1
40: from sysindexes
41: where id = object_id('Carts')
42: and name = 'FK_Cart_Album_FK'
43: and indid > 0
44: and indid < 255)
45: drop index Carts.FK_Cart_Album_FK
46: go
47:
48: if exists (select 1
49: from sysobjects
50: where id = object_id('Carts')
51: and type = 'U')
52: drop table Carts
53: go
54:
55: if exists (select 1
56: from sysobjects
57: where id = object_id('Genres')
58: and type = 'U')
59: drop table Genres
60: go
61:
62: if exists (select 1
63: from sysindexes
64: where id = object_id('OrderDetails')
65: and name = 'FK_OrderDetail_Album_FK'
66: and indid > 0
67: and indid < 255)
68: drop index OrderDetails.FK_OrderDetail_Album_FK
69: go
70:
71: if exists (select 1
72: from sysindexes
73: where id = object_id('OrderDetails')
74: and name = 'FK__InvoiceLi__Invoi__2F10007B_FK'
75: and indid > 0
76: and indid < 255)
77: drop index OrderDetails.FK__InvoiceLi__Invoi__2F10007B_FK
78: go
79:
80: if exists (select 1
81: from sysobjects
82: where id = object_id('OrderDetails')
83: and type = 'U')
84: drop table OrderDetails
85: go
86:
87: if exists (select 1
88: from sysobjects
89: where id = object_id('Orders')
90: and type = 'U')
91: drop table Orders
92: go
93:
94: /*==============================================================*/
95: /* Table: Albums */
96: /*==============================================================*/
97: create table Albums (
98: AlbumId numeric identity(1, 1),
99: ArtistId numeric not null,
100: GenreId numeric not null,
101: Title nvarchar(160) collate SQL_Latin1_General_CP1_CI_AS not null,
102: Price numeric(10,2) not null,
103: AlbumArtUrl nvarchar(1024) collate SQL_Latin1_General_CP1_CI_AS null constraint DF_Album_AlbumArtUrl default N'/Content/Images/placeholder.gif',
104: constraint PK_ALBUMS primary key nonclustered (AlbumId)
105: )
106: go
107:
108: declare @CurrentUser sysname
109: select @CurrentUser = user_name()
110: execute sp_addextendedproperty 'MS_Description',
111: '专辑编号',
112: 'user', @CurrentUser, 'table', 'Albums', 'column', 'AlbumId'
113: go
114:
115: declare @CurrentUser sysname
116: select @CurrentUser = user_name()
117: execute sp_addextendedproperty 'MS_Description',
118: '艺术家编号',
119: 'user', @CurrentUser, 'table', 'Albums', 'column', 'ArtistId'
120: go
121:
122: declare @CurrentUser sysname
123: select @CurrentUser = user_name()
124: execute sp_addextendedproperty 'MS_Description',
125: '流派编号',
126: 'user', @CurrentUser, 'table', 'Albums', 'column', 'GenreId'
127: go
128:
129: declare @CurrentUser sysname
130: select @CurrentUser = user_name()
131: execute sp_addextendedproperty 'MS_Description',
132: '标题',
133: 'user', @CurrentUser, 'table', 'Albums', 'column', 'Title'
134: go
135:
136: declare @CurrentUser sysname
137: select @CurrentUser = user_name()
138: execute sp_addextendedproperty 'MS_Description',
139: '价格',
140: 'user', @CurrentUser, 'table', 'Albums', 'column', 'Price'
141: go
142:
143: declare @CurrentUser sysname
144: select @CurrentUser = user_name()
145: execute sp_addextendedproperty 'MS_Description',
146: '专辑图片路径',
147: 'user', @CurrentUser, 'table', 'Albums', 'column', 'AlbumArtUrl'
148: go
149:
150: /*==============================================================*/
151: /* Index: FK__Album__ArtistId__276EDEB3_FK */
152: /*==============================================================*/
153: create index FK__Album__ArtistId__276EDEB3_FK on Albums (
154: ArtistId ASC
155: )
156: go
157:
158: /*==============================================================*/
159: /* Index: FK_Album_Genre_FK */
160: /*==============================================================*/
161: create index FK_Album_Genre_FK on Albums (
162: GenreId ASC
163: )
164: go
165:
166: /*==============================================================*/
167: /* Table: Artists */
168: /*==============================================================*/
169: create table Artists (
170: ArtistId numeric identity(1, 1),
171: Name nvarchar(120) collate SQL_Latin1_General_CP1_CI_AS null,
172: constraint PK_ARTISTS primary key nonclustered (ArtistId)
173: )
174: go
175:
176: declare @CurrentUser sysname
177: select @CurrentUser = user_name()
178: execute sp_addextendedproperty 'MS_Description',
179: '艺术家编号',
180: 'user', @CurrentUser, 'table', 'Artists', 'column', 'ArtistId'
181: go
182:
183: declare @CurrentUser sysname
184: select @CurrentUser = user_name()
185: execute sp_addextendedproperty 'MS_Description',
186: '艺术家名称',
187: 'user', @CurrentUser, 'table', 'Artists', 'column', 'Name'
188: go
189:
190: /*==============================================================*/
191: /* Table: Carts */
192: /*==============================================================*/
193: create table Carts (
194: RecordId numeric identity(1, 1),
195: AlbumId numeric not null,
196: CartId varchar(50) collate SQL_Latin1_General_CP1_CI_AS not null,
197: Count int not null,
198: DateCreated datetime not null,
199: constraint PK_CARTS primary key nonclustered (RecordId)
200: )
201: go
202:
203: declare @CurrentUser sysname
204: select @CurrentUser = user_name()
205: execute sp_addextendedproperty 'MS_Description',
206: '购物车实体',
207: 'user', @CurrentUser, 'table', 'Carts'
208: go
209:
210: declare @CurrentUser sysname
211: select @CurrentUser = user_name()
212: execute sp_addextendedproperty 'MS_Description',
213: '记录编号',
214: 'user', @CurrentUser, 'table', 'Carts', 'column', 'RecordId'
215: go
216:
217: declare @CurrentUser sysname
218: select @CurrentUser = user_name()
219: execute sp_addextendedproperty 'MS_Description',
220: '专辑编号',
221: 'user', @CurrentUser, 'table', 'Carts', 'column', 'AlbumId'
222: go
223:
224: declare @CurrentUser sysname
225: select @CurrentUser = user_name()
226: execute sp_addextendedproperty 'MS_Description',
227: '购物车编号',
228: 'user', @CurrentUser, 'table', 'Carts', 'column', 'CartId'
229: go
230:
231: declare @CurrentUser sysname
232: select @CurrentUser = user_name()
233: execute sp_addextendedproperty 'MS_Description',
234: '数量',
235: 'user', @CurrentUser, 'table', 'Carts', 'column', 'Count'
236: go
237:
238: declare @CurrentUser sysname
239: select @CurrentUser = user_name()
240: execute sp_addextendedproperty 'MS_Description',
241: '创建时间',
242: 'user', @CurrentUser, 'table', 'Carts', 'column', 'DateCreated'
243: go
244:
245: /*==============================================================*/
246: /* Index: FK_Cart_Album_FK */
247: /*==============================================================*/
248: create index FK_Cart_Album_FK on Carts (
249: AlbumId ASC
250: )
251: go
252:
253: /*==============================================================*/
254: /* Table: Genres */
255: /*==============================================================*/
256: create table Genres (
257: GenreId numeric identity(1, 1),
258: Name nvarchar(120) collate SQL_Latin1_General_CP1_CI_AS null,
259: Description nvarchar(4000) collate SQL_Latin1_General_CP1_CI_AS null,
260: constraint PK_GENRES primary key nonclustered (GenreId)
261: )
262: go
263:
264: declare @CurrentUser sysname
265: select @CurrentUser = user_name()
266: execute sp_addextendedproperty 'MS_Description',
267: '流派编号',
268: 'user', @CurrentUser, 'table', 'Genres', 'column', 'GenreId'
269: go
270:
271: declare @CurrentUser sysname
272: select @CurrentUser = user_name()
273: execute sp_addextendedproperty 'MS_Description',
274: '流派名称',
275: 'user', @CurrentUser, 'table', 'Genres', 'column', 'Name'
276: go
277:
278: declare @CurrentUser sysname
279: select @CurrentUser = user_name()
280: execute sp_addextendedproperty 'MS_Description',
281: '流派描述',
282: 'user', @CurrentUser, 'table', 'Genres', 'column', 'Description'
283: go
284:
285: /*==============================================================*/
286: /* Table: OrderDetails */
287: /*==============================================================*/
288: create table OrderDetails (
289: OrderDetailId numeric identity(1, 1),
290: AlbumId numeric not null,
291: OrderId numeric not null,
292: Quantity int not null,
293: UnitPrice numeric(10,2) not null,
294: constraint PK_ORDERDETAILS primary key nonclustered (OrderDetailId)
295: )
296: go
297:
298: declare @CurrentUser sysname
299: select @CurrentUser = user_name()
300: execute sp_addextendedproperty 'MS_Description',
301: '订单详情编号',
302: 'user', @CurrentUser, 'table', 'OrderDetails', 'column', 'OrderDetailId'
303: go
304:
305: declare @CurrentUser sysname
306: select @CurrentUser = user_name()
307: execute sp_addextendedproperty 'MS_Description',
308: '专辑编号',
309: 'user', @CurrentUser, 'table', 'OrderDetails', 'column', 'AlbumId'
310: go
311:
312: declare @CurrentUser sysname
313: select @CurrentUser = user_name()
314: execute sp_addextendedproperty 'MS_Description',
315: '订单编号',
316: 'user', @CurrentUser, 'table', 'OrderDetails', 'column', 'OrderId'
317: go
318:
319: declare @CurrentUser sysname
320: select @CurrentUser = user_name()
321: execute sp_addextendedproperty 'MS_Description',
322: '数量',
323: 'user', @CurrentUser, 'table', 'OrderDetails', 'column', 'Quantity'
324: go
325:
326: declare @CurrentUser sysname
327: select @CurrentUser = user_name()
328: execute sp_addextendedproperty 'MS_Description',
329: '单价',
330: 'user', @CurrentUser, 'table', 'OrderDetails', 'column', 'UnitPrice'
331: go
332:
333: /*==============================================================*/
334: /* Index: FK__InvoiceLi__Invoi__2F10007B_FK */
335: /*==============================================================*/
336: create index FK__InvoiceLi__Invoi__2F10007B_FK on OrderDetails (
337: OrderId ASC
338: )
339: go
340:
341: /*==============================================================*/
342: /* Index: FK_OrderDetail_Album_FK */
343: /*==============================================================*/
344: create index FK_OrderDetail_Album_FK on OrderDetails (
345: AlbumId ASC
346: )
347: go
348:
349: /*==============================================================*/
350: /* Table: Orders */
351: /*==============================================================*/
352: create table Orders (
353: OrderId numeric identity(1, 1),
354: OrderDate datetime not null,
355: Username nvarchar(256) collate SQL_Latin1_General_CP1_CI_AS null,
356: FirstName nvarchar(160) collate SQL_Latin1_General_CP1_CI_AS null,
357: LastName nvarchar(160) collate SQL_Latin1_General_CP1_CI_AS null,
358: Address nvarchar(70) collate SQL_Latin1_General_CP1_CI_AS null,
359: City nvarchar(40) collate SQL_Latin1_General_CP1_CI_AS null,
360: State nvarchar(40) collate SQL_Latin1_General_CP1_CI_AS null,
361: PostalCode nvarchar(10) collate SQL_Latin1_General_CP1_CI_AS null,
362: Country nvarchar(40) collate SQL_Latin1_General_CP1_CI_AS null,
363: Phone nvarchar(24) collate SQL_Latin1_General_CP1_CI_AS null,
364: Email nvarchar(160) collate SQL_Latin1_General_CP1_CI_AS null,
365: Total numeric(10,2) not null,
366: constraint PK_ORDERS primary key nonclustered (OrderId)
367: )
368: go
369:
370: declare @CurrentUser sysname
371: select @CurrentUser = user_name()
372: execute sp_addextendedproperty 'MS_Description',
373: '订单',
374: 'user', @CurrentUser, 'table', 'Orders'
375: go
376:
377: declare @CurrentUser sysname
378: select @CurrentUser = user_name()
379: execute sp_addextendedproperty 'MS_Description',
380: '订单编号',
381: 'user', @CurrentUser, 'table', 'Orders', 'column', 'OrderId'
382: go
383:
384: declare @CurrentUser sysname
385: select @CurrentUser = user_name()
386: execute sp_addextendedproperty 'MS_Description',
387: '订单日期',
388: 'user', @CurrentUser, 'table', 'Orders', 'column', 'OrderDate'
389: go
390:
391: declare @CurrentUser sysname
392: select @CurrentUser = user_name()
393: execute sp_addextendedproperty 'MS_Description',
394: '用户名',
395: 'user', @CurrentUser, 'table', 'Orders', 'column', 'Username'
396: go
397:
398: declare @CurrentUser sysname
399: select @CurrentUser = user_name()
400: execute sp_addextendedproperty 'MS_Description',
401: '名字',
402: 'user', @CurrentUser, 'table', 'Orders', 'column', 'FirstName'
403: go
404:
405: declare @CurrentUser sysname
406: select @CurrentUser = user_name()
407: execute sp_addextendedproperty 'MS_Description',
408: '姓氏',
409: 'user', @CurrentUser, 'table', 'Orders', 'column', 'LastName'
410: go
411:
412: declare @CurrentUser sysname
413: select @CurrentUser = user_name()
414: execute sp_addextendedproperty 'MS_Description',
415: '地址',
416: 'user', @CurrentUser, 'table', 'Orders', 'column', 'Address'
417: go
418:
419: declare @CurrentUser sysname
420: select @CurrentUser = user_name()
421: execute sp_addextendedproperty 'MS_Description',
422: '城市',
423: 'user', @CurrentUser, 'table', 'Orders', 'column', 'City'
424: go
425:
426: declare @CurrentUser sysname
427: select @CurrentUser = user_name()
428: execute sp_addextendedproperty 'MS_Description',
429: '省份/州',
430: 'user', @CurrentUser, 'table', 'Orders', 'column', 'State'
431: go
432:
433: declare @CurrentUser sysname
434: select @CurrentUser = user_name()
435: execute sp_addextendedproperty 'MS_Description',
436: '邮政编码',
437: 'user', @CurrentUser, 'table', 'Orders', 'column', 'PostalCode'
438: go
439:
440: declare @CurrentUser sysname
441: select @CurrentUser = user_name()
442: execute sp_addextendedproperty 'MS_Description',
443: '国家',
444: 'user', @CurrentUser, 'table', 'Orders', 'column', 'Country'
445: go
446:
447: declare @CurrentUser sysname
448: select @CurrentUser = user_name()
449: execute sp_addextendedproperty 'MS_Description',
450: '联系电话',
451: 'user', @CurrentUser, 'table', 'Orders', 'column', 'Phone'
452: go
453:
454: declare @CurrentUser sysname
455: select @CurrentUser = user_name()
456: execute sp_addextendedproperty 'MS_Description',
457: 'Email/电子邮件',
458: 'user', @CurrentUser, 'table', 'Orders', 'column', 'Email'
459: go
460:
461: declare @CurrentUser sysname
462: select @CurrentUser = user_name()
463: execute sp_addextendedproperty 'MS_Description',
464: '总计',
465: 'user', @CurrentUser, 'table', 'Orders', 'column', 'Total'
466: go
467:
