《SSD7EXER5数据库练习五.doc》由会员分享，可在线阅读，更多相关《SSD7EXER5数据库练习五.doc（15页珍藏版）》请在三一文库上搜索。
1、心之所向，所向披靡管理员视图的ER 模型如下：PublisherPublish_id1.* Write1.*1.1Author1.*Publish1.* Promote0.*Author_idBookISBN1.*1.1In 0.*1.*Belongto1.11.*OrderCategoryContain Order_numCategory_idber0.*Has1.1Customer0.1Has 1.*Customer_idPromotionPromotion_idLineItem1.*Ship0.1ShipmentTracking_number（ 1）标识实体及有关的属性、属性域和主键如下
2、：Book Entity (Strong)- Title (single valued, simple string)- ISBN (single valued, simple alphanumeric string), pk- Edition (single valued, simple numeric)- Date of Publication (single valued, composite concatenation ofcharacters and numbers)- Price (single valued, simple floating point number)- Book
3、 Description (single valued, simple string) Author Entity (Strong)- Author Name- Author_id, pk- Publisher id (single value, simple numeric), pk- Publisher Name (single valued, simple string)- Address (single valued, simple string)- together the publisher name and address could make an alternate key
4、because no to publishers can have the same name and address- Customer_id (single valued, simple string), pk- Name (multivalued, one value for first, middle and last name, simple string)- Mailing Address (single valued, simple string)- Credit Card Number and Expiration Date (single value, simple nume
5、ric sequence), alternate key- Phone Number (single value, simple alphanumeric string)- Email Address (single valued, simple alphanumeric string) Shipment (strong)- Date of Shipment ( single valued, composite of strings and numbers)- Tracking Number (single valued, simple alphanumeric string), pk- Da
6、te of Expected Delivery (single valued, compoite of strings and numbers)- Order Number (single valued, simple number), pk- MailingAddress (single value, simple string)- Method of Shipment (single value, simple string)- Date and Time of Order (when the order was placed)- Total Price of the Order (mul
7、tivalue, composite) Promotion (strong entity type )- Promotion id number, pk- Percentage Discount Points (single value, simple float)- Duration of Promotion (start date and end date) (multivalued, composite attributes like the dates above)- Total price for each book that is ordered (single value, tw
8、o place precision float)- Quantity of each item ordered Category (strong entity)- Category ID (single value, simple numeric), pk- Category Name (single value, simple string)（ 2）标识关系及其参与实体的作用、基数和多样性如下：- Book_Author(AuthorWrite Book)Roles: Book fulfills book role and author fulfills author roleCardina
9、lity: M:N, many books have many authorsParticipation: Book and Author are both total- Book_category(BookBelongTo Category)Roles: book fulfills the book role and categoryfulfills the category roleCardinality: M:N, one book can belong to many categories,one category cancontain many booksParticipation:
10、 book is total, it must belong to some category,category is partial, a category can exist withoutany books in it.- Publisher_Book (PublisherPublishBook)Roles: Book fulfills book or product role, Publisher fulfillspublisher or producer roleCardinality: 1:N, 1 publisher publishes many booksParticipati
11、on: Publisher and book are total- Customer_Order (CustomerHas Order)Roles: Customer fulfills buyer role and order fulfillsbought product roleCardinality: 1:N, 1 customer can have many ordersParticipation: Customer is partial and order is total- Book_Promotion (PromotionPromote Book)Roles: Book fulfi
12、lls book or product role, Promotionfulfills sale roleCardinality: M:N, many books can have many promotions, that isbooks can be part of more than 1 sale and eachsale can have many booksParticipation: Book is partial and promotion is total- LineItem_Order (OrderContain LineItem)- Roles : line item fu
13、lfills the line item (one item per line in an order) and the order fulfills the order role.- Cardinality: 1:N, one order contains many line items- Participation: Both line item and order are total- LineItem_Book (BookIn LineItem)-Roles:Book fulfillsthe book role,lineitem fulfillsthe lineitem role- C
14、ardinality: 1:N, one book can be in many line items- Participation: line item is total and book is partial- Shipment_LineItem- Roles: Partial order fulfills the partial order role andline item fulfills the line item role.- Cardinality: 1:N, one partial order can have many line items- Participation:
15、partial order is total and line item is partial- Isa_Category (CategoryHas Category)Roles: Category fulfills the subclass role and Supercategoryfulfills the superclass role.Cardinality: n:1, a super category can have many subcategoriesParticipation: Supercategory is total, there must be a supercator
16、y foreach subcategory, but supcategory is partial, not everysupercategory must have a subcategory to exist.the database application lifecycle-ER ModelsCreate the complete ER schema for your E-Commercedatabase from theE-CommerceProjectDescriptionin the Appendix.Note that the specification is from a u
17、sers point of view, and hence does not tell you all of the keys (primary and alternate),all of the relationships, and the arity of relationships, etc. You should identify them.Make reasonable assumptions to complete the specification. Explicitly state all your assumptions. EVERY construct in your ER
18、 schema should be substantiated by either the specification above or your explicit assumptions.Make sure you identify all of the following:?The entity types (identify weak entities).Theattributesineachentitytype(identifyitassingle-valued/multi-valued, derived, simple/composite,etc).Leavetheattribute
19、soutofthediagram.Instead,list each entity in a textual form, and beneath each one, list its attributes.The relationship types (identify the roles, the cardinality constraints, and participation constraints).Use the ER diagramming notations to draw your schema. Turnin your ER schema in a file namedER
20、.gif, and the attribute listin a text file namedER.txtAppendix C. E-Commerce Project Description? Datao Book Titleo Publisher o Customer o Ordero Fulfilled Order o Promotion? Taskso Accessing Books o Queryingo Browsingo Purchasingo Checking Order StatusIn this course, you will design and implement t
21、he following project. Exercises in this course will ask you to build various components of the project. Towards the end of the course, all the pieces have to be combined to complete the project.DataThe system should capture information about books and customers and customer orders.Book TitleThe syst
22、em will contain the following information about each book title:? Author? Title?ISBN? Publisher? Edition? Date of Publication? Price? Book Description? Categories (the various categories to which the book belongs)The book category is essentially a hierarchy: a category can have subcategories, each o
23、f which can have further subcategories. A category can have a number of books listed under it.PublisherThe following information about the publisher should also be captured:? Publisher Name? AddressCustomerFor each customer, the system should record the following information:?Name (first name, middl
24、e initial, and last name)? Mailing Address? Credit Card Number and Expiration Date? Phone Number (optional)? Email AddressOrderFor each order, the system should capture the following information:? Order Number? Customer? Mailing Address? Credit Card (to pay for the order)? Method of Shipment (next d
25、ay delivery, regular post)? Shipping Date (date the order will be shipped)? Date and Time of Order (when the order was placed)? Price of the Order which includes:o ISBN and Price of each Book Title (list price andpurchase price after discount)o Quantity Purchased (for each book title bought)o Shippi
26、ng Costo TaxFulfilled OrderWhen a title is not in stock, an order can be shipped in multiple shipments. Assume that a title is shipped for an order only when there are enough book copies of that title in stock. Assume that a shipment cannot combine books from different orders. For each shipment, the
27、 system should keep track of the following information.? Order Number? Date of Shipment? Tracking Number (this could be the number assigned by the delivery agency)? Date of Expected Delivery? Titles Shipped and their QuantityPromotionPeriodically, the e-store will run promotions on a list of books.T
28、he promotions will consist of the following data:? Books being Promoted? Percentage Discount Points? Duration of Promotion (start date and end date)TasksIn the description of tasks below, there are hyperlinks to screen snapshots of a sample implementation of the E-Commerce system.Accessing BooksBook
29、s can be accessed by doing one of the following:?Queryingfor the attributesof a book (For example, a usermay want to find the books written by the author John Doe where the title contains the substring database fundamentals. The system you build should support this functionality.)? Browsing through
30、the book categories? Looking at the list of the top 10 books sold for the last monthTo browse or query the books, users do not have to enter any user information. User information is only collected when the user is ready to buy.Whenever a book is displayed, a checkbox or some such item will also be
31、displayed next to the book to add the book to the customers shopping cart.QueryingThe typical querying process is as follows:1. The user queries the system (for the authors name or the books title, or some other book attribute).2. The system displays the results 10 (books) to a page. Only the title,
32、 author, publisher, and price are shown.3. For results exceeding 10 books, links at the bottom of the page should display a Next and Previous button to goto the next or previous 10 books, respectively.4.Whenthe user clickson a particularbookname,allinformation about the bookis displayed.BrowsingThe
33、typical process for browsing is as follows:? At the beginning, there is a drop-down list box of top-level categories. The user chooses one of these categories.? The system displays the first 10 books in this category (with a next/previous button to display more books), and all thesubcategories withi
34、n this category. For each book that is displayed, only the title, author, publisher, and price are shown.? The user can traverse through the books or the categories.?When the user clicks on a particular book name, all information about the book is displayed.PurchasingThe typical process for purchasi
35、ng is as follows:? The user can click on a checkbox next to a book to add that book to a shopping cart.? The user continues his or her selection of books by adding more books to the shopping cart .? Finally, the user orders books from his or her shopping cart (user can also delete books from the sho
36、pping cart) by logging-in (if not already registered , then registering ). The system should provide a registration-success message onsuccessfully registering the user.?The user should provide all the shipping information (such as name, mailing address, etc).?The user should also provide credit-card
37、 information.? The final step in the ordering process is confirmation of order details by the user.? The system should respond with the order-success messageat the end.Checking Order StatusThe typical process for checking ones order status is as follows:? The user can type in his or her login ID and password.? After the user logs in, the system will provide an option to view the orders placed so far .?When the user chooses the option to view past orders, all the details of all past orders should be listed.